探究MySQL的基础类型-CSDN博客

前言

以下是对《高性能MySQL》中MySQL基础类型的学习总结

整数类型

有如下几种整数类型，以及它们相应的存储空间:

TINYINT，8位存储空间
SMALLINT，16位存储空间
MEDIUMINT，24位存储空间
INT，32位存储空间
BIGINT，64位存储空间

以int类型为例，它的存储空间是32位，其可以存储的值的范围是从-2^31到2^31-1。整数类型有可选的unsigned属性，表示不允许负值，对于int来说，其unsigned可以存储范围是0~2^32-1

在MySQL中我们可以为整数类型指定宽度，例如int(11)。实际上这表示能够显示的数值中的数字的个数也就是宽度，它不会限制值的合法范围，int依旧是32位存储空间，这里只是规定了MySQL的一些交互工具(比如Navicat)用来显示字符的个数，对于存储和计算来说，int(1)和int(20)是相同的,因为整型的显示宽度，对数值大小无影响，只是当对类型设置了zerofill的时候，在显示的时候补0而已。

注意：这里的指定宽度不同于varchar类型中指定长度

实数类型

实数是带有小数部分的数字。然而，它们不只是为了存储小数部分，也可以使用DECIMAL存储比BIGINT还大的整数

浮点型(float和double)

浮点型	含义
float(m,d)	单精度浮点型 8位精度(4字节) m表示浮点型数据总共位数，d表示小数位
double(m,d)	双精度浮点型 16位精度(8字节) m表示浮点型数据总共位数，d表示小数位

比如设一个字段定义为float(5,3),插入一个数据12.3334，实际数据库存的是12.333

定点型(decimal)

DECIMAL数据类型用于保留准确精确度的列，语法是 :

column_name  DECIMAL(M,D);
复制代码

上面的语法表示:

M是表示有效数字数的精度。 P范围为1〜65。
D是表示小数点后的位数。 D的范围是0~30,不得超过M

对于decimal列，可以指定小数点前后所允许的最大位数，这会影响列的空间消耗，比如decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节:小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节。

因为需要额外的空间和计算开销，所以应该尽量只对小数进行精确计算时才使用decimal——例如存储财务数据

字符串类型

varchar

varchar 用于存储可变字符串，它比定长类型更节省空间。

varchar 的长度在 MySQL5.0.3 版本之前是0到255字节，在5.0.3和以后的版本中长度是0到65535字节，重要一点是，MySQL5.0.3 和以后的varchar的有效最长长度取决于最大行的大小(65535字节，所有列共享)和所使用的字符集。

平时我们创建表时指定 varchar 的长度实际上是在我们数据库使用的字符集的基础上指定 varchar 能存储多少个字符，不管数字，字母，还是汉字。

举例说明

创建一个长度为5的varchar列

mysql> create table test1(
    -> name varchar(5)
    -> );
Query OK, 0 rows affected (0.12 sec)
复制代码

插入一个长度为6的英文和汉字，都报错提示数据长度太大

mysql> insert into test1(name) values("pjmike");
1406 - Data too long for column 'name' at row 1
mysql> insert into test1(name) values("你好呀大世界");
1406 - Data too long for column 'name' at row 1
复制代码

插入长度小于等于5的字母和汉字，成功

mysql> insert into test1(name) values("pj");
Query OK, 1 row affected (0.08 sec)
mysql> insert into test1(name) values("你好呀世界");
Query OK, 1 row affected (0.08 sec)
复制代码

以上说明 varchar 类型字段的长度实际上表示容纳的最大字符数，而这个最大字符数是 「行定义长度不超过65535字节」和「数据库的字符集」 这两者通过公式运算得来的

mysql> create table test2(
    -> name varchar(65535)
    -> );
1074 - Column length too big for column 'name' (max = 16383); use BLOB or TEXT instead
mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4     
+--------------------------+----------------------------+
8 rows in set (0.52 sec)

复制代码

由上面可以看出，该数据库的字符集为 「uft8mb4」对应的 varchar 最大字符数为16383

varchar 还有一点需要注意的是: varchar需要使用1或2个额外字节记录字符串的长度，如果列的最大长度小于或等于255字节，则使用1个字节表示，否则使用2个字节。

char

char类型是定长的: MySQL 总是根据定义的字符串长度分配足够的空间。当定义 char 值时，MySQL 会删除所有的末尾空格，适合存储短字符。

blob和text类型

blob 和 text 都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

实际上，它们分别属于两组不同的数据类型家族: 字符类型是 TINYTEXT，SMALLTEXT，TEXT，MEDIUMTEXT，LONGTEXT；对应的二进制类型是 TINYBLOB，SMALLBLOB，BLOB，MEDIUMBLOB，LONGBLOB。BLOB 是 SMALLBLOB的同义词，TEXT 是 SMALLTEXT 的同义词。