MySQL数据类型与属性 – 运维那点事

一、数据类型的作用

在关系型数据库中，表是由记录组成，而记录是由字段组成。所以我们在创建一张表时需要定义好每个字段，而每个字段也需要显式定义字段类型，MySQL支持多种数据类型，包括整数型、浮点型、字符型、二进制型、枚举型等等。

数据类型在数据库中扮演着基础但又非常重要的角色，对数据类型的选择将影响与数据库交互的应用程序的性能。通常来说，如果一个页内可以存放尽可能多的行，那么数据库的性能就越好，因此选择一个正确的数据类型至关重要。另一方面，如果在数据库中创建表时选择了错误的数据类型，那么后期的维护成本可能非常大，用户需要花大量时间来进行ALTER TABLE操作。

在正式进入MySQL数据类型之前，可能还需了解一下关于数制的知识。

数制就是计数的方法，指用一组固定的符号和统一的规则来表示数值的方法。

在日常生活中，人们主要使用十进制（0-9），但在某些时候也使用其它进制，如十二进制（如1年有12个月、1打物品有12件），六十进制（如1小时有60分钟、1分钟有60秒），24进制（如一天有24小时）等等。由此，我们引入数制的概念。数制就是多位数码中每一位的构成方法以及从低位向高位的进位规则。

我们在日常生活中主要使用十进制，而计算机中数据的表示只使用二进制，就因为二进制简单只有0和1，计算机逻辑电路处理起来就方便很多。但二进制数码对人来说不便于读写。为了开发程序、阅读机器代码和数据的方便，我们经常使用八进制数和十六进制数来等价地表示二进制数，八进制和十六进制可以看着是二进制的压缩形式。

那么数制的表示方式也很简单，比如：

十进制（decimal number）

十进制数制的基数是10，从0-9这十个数字；如233表示为两百三十三，常用的表示方式有(233)₁₀和233D。其计算方式是逢十进一。

二进制（binary number）

二进制的基数是2，只有0和1这两个数字；如10010101，常用的表示方式有(10010101)₂和10010101B。其计算方式是逢二进一。

八进制（october number）

八进制的基数是8，只有0-7这个8个数字；如644，常用的表示方式有(644)₈和644O。其计算方式是逢八进一。

十六进制（hexadecimal number）

十六进制的基数是16，前十个数字是0-9，后面是ABCDEF，分别表示10、11、12、13、14、15；如9E，常用的表示方式为(9E)₁₆和9EH。其计算方式是逢十六进一。

以十进制数字125为例，它表示有1个10²，加2个10¹，加5个10⁰，用数学符号表示就是：109 ＝ 1 × 10²＋ 2 × 10¹＋ 5 × 10⁰

如果从二进制的逢二进一的角度上看，这个十进制也可以被表示为“1111101B”。这个转换也是有方法的，就是把十进制利用除余法转为二进制，把十进制125除以2，一直取余，直到除尽或无法除时，再把最后一位的商连同余数从下往上进行排序得到的结果就是二进制数。计算过程如下：

MySQL数据类型与属性

又因为计算机中一般用8个位表示一个字节，平时都是用若干个字节表示一个整数，假如用1个字节表示125的话，那效果就是这样：01111101，假如用两个字节表示十进制125的话，那效果就是这样：0000000001111101。很显然，使用的字节数越多，意味着能表示的数值范围就越大，但是也就越耗费存储空间。

知道了关于进制和存储方式后，我们就可以接着看MySQL数据类型了。

二、MySQL数据类型

MySQL支持的数据类型非常多，支持所有标准SQL数值数据类型。在设计表结构时使用正确的数据类型对于获得高性能至关重要。MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。下面介绍一下常用的数据类型如：数值型、字符型、日期时间型、BLOB和TEXT类型、ENUM型等等。

1. 数值型

数值类型又分为两种类型的数值：整数和实数。其中每个类型中的有符号表示可以存负数，无符号表示只能存整数。我们平时使用的十进制都需要被转换为二进制后被计算机存储，比如8位二进制，如果带符号，需要用1位表示符号（1表示负数，0表示正），剩下7位表示数据。那么表示范围是-128 ～ 127(包括-0和+0)。如果不带符号，8位全部表示数据，那么表示范围是0 ～ 255。这就是有符号和无符号的区别了。

类型	字节数	无符号数取值范围	有符号数取值范围	用途
TINYINT	1	0 ～ 255	-128 ～ 255	小整数型
SMALLINT	2	0 ～ 65535	-32 768 ～ 32 767	大整数型
MEDIUMINT	3	0 ～ 16 777 215	-8 388 608 ～ 8 388 607	中等整数型
INT	4	0 ～ 4 294 967 295	-2 147 483 648 ～ 2 147 483 647	标准整数型
BIGINT	8	0 ～ 18 446 744 073 709 551 615	-9 233 372 036 854 775 808 ～ 9 233 372 036 854 775 807	超大整数型

下面测试一下有符号和无符号：

# 有符号

mysql> create table test_1(id tinyint);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test_1 select 127;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into test_1 select 128;
ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> create table test_1(id tinyint);

Query OK, 0 rows affected (0.03 sec)

mysql> insert into test_1 select 127;

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into test_1 select 128;

ERROR 1264 (22003): Out of range value for column 'id' at row 1

# 无符号

mysql> create table test_2(id tinyint unsigned);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test_2 select 255;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into test_2 select 256;
ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> create table test_2(id tinyint unsigned);

Query OK, 0 rows affected (0.02 sec)

mysql> insert into test_2 select 255;

Query OK, 1 row affected (0.01 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into test_2 select 256;

ERROR 1264 (22003): Out of range value for column 'id' at row 1

看到测试结果跟我们表述的是一样的。

一般不推荐使用unsigned，在范围上并没有什么本质上的改变。而unsigned可能会有溢出现象发生，比如有(a int unsigned，b int unsigned)数据类型，当a=2，b=1时，使用select b-a就不会得到-1，而是会溢出。

整数型

MySQL可以为整数类型指定宽度，例如INT(11)，对大多数应用这是没有意义的；它不是用来限制值的合法范围，只是用来规定MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，使用INT(1)和INT(20)是相同的。虽然可以设置数据显示宽度，但是如果不设置零填充（zerofill），MySQL将会忽略那个字符宽度，也就是没有作用。

mysql> create table test_3(id int(5) zerofill);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test_3 select 11;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into test_3 select 111111;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into test select 4294967295;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from test;
+------------+
| id         |
+------------+
|      00011 |
|     111111 |
| 4294967295 |
+------------+
3 rows in set (0.00 sec)

mysql> create table test_3(id int(5) zerofill);

Query OK, 0 rows affected (0.01 sec)

mysql> insert into test_3 select 11;

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into test_3 select 111111;

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into test select 4294967295;

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from test;

+------------+

| id |

+------------+

| 00011 |

| 111111 |

| 4294967295 |

+------------+

3 rows in set (0.00 sec)

其实搞不懂MySQL这么设置有什么作用，当设置int(5) zerofill时，虽然不够5位时会补0，但是当插入大于5位数值时也一样可以显示，所以就不知道有什么用了。

浮点型

浮点型是带有小数部分的数字，我们平时使用的十进制小数也可以被转换成二进制后被计算机存储。比如9.875，这个小数可以被表示成这样：9.875 ＝ 8 ＋ 1 ＋ 0.5 ＋ 0.25 ＋ 0.125 ＝ 1 x 2³＋1 × 2⁰＋ 1 × 2^－1＋ 0 × 2^－2＋ 0 × 2^－3

也就是说，如果十进制小数9.875转换成二进制小数的话就是：1001.111。为了在计算机里存储这种二进制小数，我们统一把它们表示成a × 2ⁿ的科学计数法的形式，其中1≤|a|＜2，比如1001.111可以被表示成1.001111 × 2³，我们把小数点之后的001111称为尾数，把2³中的3称为指数，然后只需要在计算机中的二进制位中表示出尾数和指数就行了。用科学技术法来计数的时候，小数的小数点看起来就像移动了一样，所以这样的小数也叫做浮点数。

当然，如果你需要表示有符号小数，那还得用一个二进制位来表示正负号。如果我们用四个字节，也就是32个位来表示一个有符号小数的话，我们可以这么划分各个部分所表示的意义：

很显然，使用的字节数越多，表示尾数和指数的范围就越大，也就是说表示的小数范围就越大。MySQL根据小数需要的不同字节数定义了两种浮点类型：单精度的FLOAT类型及双精度的DOUBLE类型，这两种类型都是非精确的类型，经过一些操作后并不能保证运算的正确性，例如“M*G/G”并不一定等于M。

类型	字节数	绝对值最小非0值	绝对值最大非0值	用途
FLOAT	4	±1.175494351E 负38次方	±3.402823466E 正38次方	单精度浮点型
DOUBLE	8	±2.225073 858 5072014E 负308次方	±1.7976931348623157E 正308次方	双精度浮点型

这主要是因为，虽然有的十进制小数，比如1.875可以被很容易的转换成二进制数0.111，但是更多的小数是无法直接转换成二进制的，比如说0.3，它转换成的二进制小数就是一个无限小数，但是我们现在只能用4个字节或者8个字节来表示这个小数，所以只能进行四舍五入来近似的表示，所以我们说计算机的浮点数表示有时是不精确的。

设置有效位数和小数位数

对于我们用户而言，使用的都是十进制小数。表示一个小数需要的数字个数称为有效位数，小数点后的数字个数称为小数位数，11.2和-1.0021这两个小数的有效位数和小数位数见下表：

小数	有效位数	小数位数
11.2	3	1
-1.0021	5	4

如果我们知道某列属性需要的有效位数和小数位数，我们可以用这样的方式手动指定一下：FLOAT(M,D)或DOUBLE(M,D)；其中，M表示有效位数，D表示小数位数。例如，定义为FLOAT(7,4)的一个列取值范围可以为－999.9999 ～ 999.9999。MySQL在保存值时会进行四舍五入，因此在FLOAT(7,4)列内插入999.00009的近似结果是999.0001。

总结来说，在小数位数相同的情况下，有效位数越多，该类型的取值范围越大；在有效位数相同的情况下，小数位数越大，该类型的取值范围越小。当然，M和D的取值也不是无限大的，你要是把有效位数取成一个亿，那内存不得崩了么，别忘了单精度浮点数只有4个字节的存储空间，双精度浮点数只有8个字节的存储空间，超过了这个存储空间所能表达的小数就无效了。

定点型

正因为用浮点数表示小数可能会有不精确的情况，在一些情况下我们必须保证小数是精确的，所以MySQL提出了定点数的概念，它也是存储小数的一种方式。在MySQL中这种类型就是DECIMAL(M,D)，用于存储精确的小数，因为CPU不支持对DECIMAL的直接计算，所以在MySQL 5.0以及更高版本中，MySQL服务器自身实现了DECIMAL的高精度计算。相对而言，CPU直接支持原生浮点计算，所以浮点运算明显更快。

类型	字节数	取值范围	用途
DECIMAL(M, D)	取决于M和D	取决于M和D	定点数

此处的M指的就是有效位数，D指的就是小数位数，M减去D就是整数的位数，M必须大于等于D，否则数据库报错。M和D对取值范围的影响我们之前已经介绍过了，但是为啥M和D的取值还会影响到需要的字节数呢（DECIMAL(M,D)类型存储大小根据，如果M>D，则M＋2，否则D+2来计算）？

我们说定点数是一种精确的小数，为了达到精确的目的我们不能把它转换成二进制之后再存储（这可能会产生四舍五入的情况）。我们可以以小数点为界，把一个小数看成是两个整数被小数点分隔开来的样子，所以我们可以把一个小数分成3块来存储：

1. 小数点左边的整数

2. 小数点本身

3. 三是小数点右边的整数

这样就可以保证存储的小数肯定是精确的了。

以DECIMAL(6, 1)为例，这种类型的取值范围是-99999.9~99999.9。所以整数部分绝对值最大的就是99999，小数部分最大的就是9，而整数部分的最大值99999这个十进制数最少需要用3个字节才能存放，小数部分的最大值9只需要1个字节就可以存放，所以最后这个定点数类型就需要使用5个字节，各个部分的划分如下：

所以不同的有效位数和小数位数，最终需要的字节数也是不同的。可以看到，与浮点数相比，定点数需要更多的空间来存储数据，所以如果不是像工资一样特重要的数据，一般的小数用浮点数就足够了。

其中M和D都是可选的，默认的M的值是10（MySQL默认为10），默认的D的值是0，也就是说下列等式是成立的：

DECIMAL = DECIMAL(10) = DECIMAL(10, 0) = DECIMAL(n) = DECIMAL(n, 0)

1	DECIMAL = DECIMAL(10) = DECIMAL(10, 0) = DECIMAL(n) = DECIMAL(n, 0)

另外，有效位数和小数位数也不是无限大的（太大了内存受不了），限制M的范围是1~65，D的范围是0~30，且D的值不能超过M。

2. 字符型

在具体分析MySQL中各个字符串类型之前，我们一定要先搞明白字符和字节的区别。字符是面向人的概念，字节是面向计算机的概念。如果你想在计算机中表示字符，那就需要通过字符编码来将一个字符映射到一个二进制数据。不幸的是，这种映射关系并不是唯一的，不同的人制作了不同的字符编码，根据表示一个字符使用的字节数是不是相同的，编码方式可以分为下边两种：

固定长度的编码

采用相同的字节数来表示一个字符，比方说ASCII编码方式采用1个字节来表示一个字符，ucs2采用2个字节来表示一个字符。

变长编码

采用长度不一样的字节来表示不同的字符，比方说utf8编码方式采用1~3个字节来编码一个字符，gb2312采用1~2个字节来编码一个字符。

对于不同的字符编码方式来说，对同样一个字符可能会产生不一样的编码，比如同样一个字符：我，在utf8和gb2312这两种编码方式下被映射成如下的二进制数据如下：

utf8编码：111001101000100010010001 (3个字节，十六进制表示是：0xE68891；十六进制前边的“0x”是前缀，表示后边的是16进制数据)

gb2312编码：1100111011010010 (2个字节，十六进制表示是：0xCED2)

MySQL支持多种字符集，不同字符集的编码是不一样的，自然存储大小也是不一样的。关于字符集和字符编码具体要看相关的文章。

MySQL也提供了丰富的字符串存储类型，如下表（注：其中N代表你要存储的字符串中最多包含的字符数量，L代表在实际字符串在某个字符编码下所占用的字节数）：

类型	最大长度	存储空间大小	用途
CHAR(N)	0-255字符	不同编码方式下占用的存储空间计算方式不同	固定长度的字符串
VARCHAR(N)	0-65535字符	L+1 或 L+2个字节	可变长度的字符串
TINYTEXT	0-255字符	L+1个字节	非常小型的字符串
TEXT	0-65535字符	L+2个字节	小型的字符串
MEDIUMTEXT	0-16 777 215字符	L+3个字节	中型的字符串
LONGTEXT	0-4 294 967 295字符	L+4个字节	大型的字符串
BINARY(N)	0-255字节	N个字节	固定长度的二进制字符串
VARBINARY(N)	0-65535字符	L+1 或 L+2个字节	可变长度的二进制字符串
TINYBLOB(N)	0-255字节	L+1个字节	非常小型的二进制字符串
BLOB(N)	0-65535字节	L+2个字节	小型的二进制字符串
MEDIUMBLOB(N)	0-16 777 215字节	L+3个字节	中型的二进制字符串
LONGBLOB(N)	0-4 294 967 295字节	L+4个字节	大型的二进制字符串

MySQL支持多种字符串类型，每种类型还有很多变种。

CHAR(N)

CHAR(N)类型是定长字符，N的范围是0-255个字符，如果省略掉N的值，那它的默认值就是1，也就是说CHAR和CHAR(1)是一个意思。MySQL总是根据定义的字符串长度分配足够的空间，当存储CHAR类型字符串时，MySQL会自动对存储列的右边进行填充空格操作，直到字符串达到指定长度的N。而在读取该列时，MySQL会自动将填充的字符删除。

CHAR(M)在不同的编码方式下需要的存储空间也是不一样的。

固定长度编码方式下

在固定长度编码方式下，CHAR(N)占用的存储空间是固定的，与实际存储的数据无关，都是N×W（W代表某个固定长度编码方式中编码一个字符需要的字节数）个字节。比方说我们现在使用的字符串类型为CHAR(5)，所以现在N的值就是5，如果我们使用固定长度编码方式ASCII进行编码，不管存储的数据是啥，都会占用5×1个字节，也就是5个字节。

变长编码方式下

在变长编码方式下，比如说GBK、UTF-8之类的字符集编码方式下，其有些字符是以1字节存放的，有些字符是按2或3字节存放的，因此CHAR也需要1-2字节的空间来存储实际字符的长度。CHAR(N)占用的存储空间的计算方式比较麻烦，具体需要看InnoDB行格式才能了解。

这种CHAR(N)的字符串类型有一个非常不好的地方：一旦你确定了N的值，如果N的值很大，而你实际存储的字符串占用字节数又很少，会造成存储空间的浪费。CHAR适合存储很短的字符串，或者所有值都接近同一个长度。例如：存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率。

VARCHAR(N)

如果你表中的某个列需要存储字符串类型的数据，而且这些字符串长短不一，那么使用CHAR(M)可能造成很大程度上的浪费，VARCHAR(M)正是为了解决这个问题而生的。VARCHAR(N)类型是用于存储可变长字符，是一种变长字符类型，也是最常见的字符串数据类型。它比定长类型CHAR更节省空间，因为VARCHAR计算的是实际存储大小，而不是VARCHAR(N)

VARCHAR(N)中的N也是指你要存储的字符串中最多包含的字符数量，取值范围是1~65535个字符。但是MySQL中还有一个规定，就是某一行包含的所有列中存储的数据大小不得超过65535个字节，所以VARCHAR(N)实际能够容纳的字符数量肯定小于65535。

一个VARCHAR(N)类型表示的数据占用的存储空间由两部分组成：

1. 真正的字符串内容。

2. 占用字节数。

与CHAR类型不同的是，VARCHAR存储时需要在前缀长度列表额外记录实际存储的字符长度，该字符占用1-2字节的空间。当存储的字符串长度小于255字节时，其需要1个字节的空间，当大于255字节时，需要2个字节的空间。所以，对于固定长度的编码的ASCII来说，CHAR(10)和VARCHAR(10)最大占用的存储空间是不同的，CHAR(10)占用10个字节这是毫无疑问的，而VARCHAR(10)的最大占用空间是11个字节，因为其需要1字节来存放字符长度。

但对于变长编码字符集类型，其CHAR和VARCHAR在存放方法上是一样的，同样需要为长度列表额外加上实际存储字符串长度。对于GBK和UTF-8这些字符类型，其有些字符是以1字节存放的，有些字符是按2或3字节存放的，因此CHAR和VARCHAR一样都需要1-2字节的空间来存储实际字符的长度。

当字符串列的最大长度比平均长度大很多，列的更新很少，所以碎片不是问题时可以使用VARCHAR最合适的。

BINARY(N)与VARBINARY(N)

BINARY(N)和VARBINARY(N)与前面介绍的CHAR和VARCHAR类型有点相似，不同的是BINARY和VARBINARY类型是用来存储二进制字符串，而非字符型字符串。也就是说，BINARY和VARBINARY没有字符集的概念了，对其排序和比较都是基于二进制值进行对比。

BINARY(N)和VARBINARY(N)中的N指的是字节长度，而非CHAR(N)和VARCHAR(N)中的字符长度。对于BINARY(10)，其可存储的字节固定为10，而对于CHAR(10)，其可存储的字节视字符集情况而定。看下面一个例子：

mysql> create table t1(a binary(1)) engine=innodb charset=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 select('我');
ERROR 1406 (22001): Data too long for column 'a' at row 1

mysql> create table t1(a binary(1)) engine=innodb charset=utf8;

Query OK, 0 rows affected (0.03 sec)

mysql> set names utf8;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 select('我');

ERROR 1406 (22001): Data too long for column 'a' at row 1

表t1包含一个binary类型的列，因为binary(N)中的N代表字节，而UTF8字符集中的我需要占用3个字节，所以在插入时直接报错了（使用MySQL 5.7，默认严格SQL模式；如果使用MySQL 5.7之前版本会给警告，截断字符）。如果表t1的a列被换为CAHR或VARCHAR类型则完全不会有上述问题，例如：

mysql> create table t2(a char(1)) engine=innodb charset=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 select('我');
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> create table t2(a char(1)) engine=innodb charset=utf8;

Query OK, 0 rows affected (0.02 sec)

mysql> set names utf8;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 select('我');

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

BINARY和VARBINARY对比CHAR和VARCHAR，第一点不同就是(N)代表的是字节数，而非字符长度。第二点不同在于CHAR和VARCHAR在进行字符比较时，比较的只是字符本身存储的字节，忽略字符后的填充字符。而对于BINARY和VARBINARY来说，由于是按照二进制值来进行比较的，因此结果会非常不同，例如：

mysql> select hex('a'),hex('a'),'a'='a ';
+----------+----------+----------+
| hex('a') | hex('a') | 'a'='a ' |
+----------+----------+----------+
| 61       | 61       |        1 |
+----------+----------+----------+
1 row in set (0.00 sec)

mysql> select hex(binary('a')),hex(binary('a  ')),binary('a')=binary('a  ');
+------------------+--------------------+---------------------------+
| hex(binary('a')) | hex(binary('a  ')) | binary('a')=binary('a  ') |
+------------------+--------------------+---------------------------+
| 61               | 612020             |                         0 |
+------------------+--------------------+---------------------------+
1 row in set (0.00 sec)&nbsp;

mysql> select hex('a'),hex('a'),'a'='a ';

+----------+----------+----------+

| hex('a') | hex('a') | 'a'='a ' |

+----------+----------+----------+

| 61 | 61 | 1 |

+----------+----------+----------+

1 row in set (0.00 sec)

mysql> select hex(binary('a')),hex(binary('a ')),binary('a')=binary('a ');

+------------------+--------------------+---------------------------+

| hex(binary('a')) | hex(binary('a ')) | binary('a')=binary('a ') |

+------------------+--------------------+---------------------------+

| 61 | 612020 | 0 |

+------------------+--------------------+---------------------------+

1 row in set (0.00 sec) 

对于CHAR和VARCHAR来说，比较的是字符值，因此第一个比较的返回值是1。对于BINARY和VARBINARY来说，比较的是二进制的值，”a”的十六进制为61，”a “的十六进制为612020，显然不同，因此第二个比较的返回值是0。

第三个不同是，对于BINARY(N)和VARBINARY(N)两者填充方式也不一样。MySQL填充BINARY采用的是0x00，而CHAR的填充字符为0x20。可能是因为BINARY的比较需要，0x00显然是比较的最小字符。如下所示：

mysql> create table t3(a binary(3));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t3 select ('a');
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select hex(a) from t3;
+--------+
| hex(a) |
+--------+
| 610000 |
+--------+
1 row in set (0.00 sec)

mysql> select hex('a  ');
+------------+
| hex('a  ') |
+------------+
| 612020     |
+------------+
1 row in set (0.00 sec)

mysql> create table t3(a binary(3));

Query OK, 0 rows affected (0.03 sec)

mysql> insert into t3 select ('a');

Query OK, 1 row affected (0.01 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> select hex(a) from t3;

+--------+

| hex(a) |

+--------+

| 610000 |

+--------+

1 row in set (0.00 sec)

mysql> select hex('a ');

+------------+

| hex('a ') |

+------------+

| 612020 |

+------------+

1 row in set (0.00 sec)

BLOG和TEXT

BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，BLOB采用二进制存储、TEXT采用字符串方式存储。根据存储长度的不同，实际上，他们分别属于两组不同的数据类型家族：TEXT字符类型是TINYTEXT，SMALLTEXT，TEXT，MEDIUMTEXT，LONGTEXT，对应的存储大小为255byte，65k，16M，4G；比如存储一个utf8字符集的中文，那么就占用3个字节。BLOB对应的二进制类型是TINYBLOB，SMALLBLOB，BLOB，MEDIUMBLOB，LONGBLOB。其中BLOB是SMALLBLOB的同义词，TEXT是SMALLTEXT的同义词。

在大多数情况下，可以将BLOB类型的列视为足够大的VARBINARY类型的列。同样，也可以将TEXT类型的列视为足够大的VARCHAR类型的列。然而，BLOB和TEXT在以下几个方面又不同于VARBINARY和VARCHAR：

1. 在BLOB和TEXT类型的列上创建索引时，必须指定索引前缀的长度。而VARCHAR和VARBINARY的前缀长度是可选的。

2. BLOB和TEXT类型的列不能有默认值。

3. 在排序时只使用列的前max_sort_length个字节，max_sort_length默认值为1024，该参数是动态参数，任何客户端都可以在MySQL数据库运行时更改。

4. TEXT、MEDIUMTEXT、LONGTEXT这3个类型不受MySQL规定的某一行包含的所有列中存储的数据大小不得超过65535个字节的限制。

与其她类型不同，MySQL把每个BLOB和TEXT值当做一个独立的对象处理，存储引擎在存储时通常会做特殊处理。当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1-4个字节存储一个指针，然后在外部存储区域存储实际的值。

在数据库中，最小的存储单元是页（也可以称为块）。为了有效存储列类型为BLOB或TEXT的大数据类型，一般将列的值存放在行溢出页，而数据页存储的行数据只包含BLOB或TEXT类型数据列前一部分数据。MySQL中数据页由许多的行数据组成，每行数据由列组成，对于列类型为BLOB的数据，InnoDB存储引擎只存储前20字节，而该列的完整数据则存放在BLOB的行溢出页中。在这种方式下，数据页中能存放大量的行数据，从而提高了数据的查询效率。此外，在有些存储引擎内部，比如InnoDB存储引擎，会将大VARCHAR类型字符串（如VARCHAR(65530)）自动转化为TEXT或BLOB类型。

3. 日期时间型

我们有很多场景需要表示时间或日期，比如学生基本信息中的入学时间就需要用日期的格式保存。MySQL为我们提供了多种关于时间和日期的类型，各种类型能表示的范围如下：

类型	存储空间大小	取值范围	格式	用途
YEAR	1字节	1901-2155	YYYY	年份值
DATE	3字节	1000-01-01′ ～ ‘9999-12-31’	YYYY-MM-DD	日期值
TIME	3字节	-838:59:59′ ～ ‘838:59:59’	HH:MM:SS	时间值或持续时间
DATETIME	8字节	1000-01-01 00:00:00′ ～ ‘9999-12-31 23:59:59’	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4字节	1970-01-01 00:00:01′ ～ ‘2038-01-19 03:14:07’	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

TIME/DATE/DATETIME

DATETIME占用8个字节，它既能显示了日期，同时也能显示时间。其可以表达的日期范围为1001-01-01 00:00:00到9999-12-31 23:59:59年，精度为秒，它把日期和时间封装的格式为YYYYMMDDHHMMSS的整数中，与时区无关。默认情况下MySQL以一种可排序的方式显示DATETIME的值，如“2017-10-12 17:22:58”，这是ANSI标准定义的日期和时间的方法。

DATE占用3个字节，可显示的日期范围1000-01-01到9999-12-31。在MySQL中，对日期和时间的输入格式的要求是非常宽松的，以下的输入可以视为日期类型：2017-01-01 00:01:01、2017/01/01 00+01+01、20170101000101等。

TIME是只有时间的数据类型，其格式HHMMSS，大小占3个字节。与DATETIME类型一样，TIME类型同样可以显示微妙时间。

在MySQL 5.6.4版本之前，数据库的日期类型不能精确到毫秒、微妙级别；任何的毫秒、微妙值都会被数据库截断，例如：

mysql> create table d1(a datetime);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into d1 select('2017-01-01 00:01:01.123456');
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from d1;
+---------------------+
| a                   |
+---------------------+
| 2017-01-01 00:01:01 |
+---------------------+
1 row in set (0.00 sec)

mysql> create table d1(a datetime);

Query OK, 0 rows affected (0.03 sec)

mysql> insert into d1 select('2017-01-01 00:01:01.123456');

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from d1;

+---------------------+

| a |

+---------------------+

| 2017-01-01 00:01:01 |

+---------------------+

1 row in set (0.00 sec)

然而从MySQL 5.6.4版本开始，MySQL增加了对秒的小数部分的支持，具体语法为：type_name（fsp）。其中type_name可以为TIME、DATETIME和TIMESTAMP时间类型；fsp表示支持秒的小数部分的精度，由于毫秒、微秒都不到1秒，所以也被称为小数秒，MySQL最多支持6位小数秒的精度，各个位代表的意思如下：

我们可以选择TIME、DATETIME、TIMESTAMP这几种类型最多支持到小数点后几位的时间精度。比如DATETIME(0)表示精确到秒，DATETIME(3)表示精确到毫秒，DATETIME(5)表示精确到10微秒。如果你在选择TIME、DATETIME、TIMESTAMP这几种类型的时候添加了对小数秒的支持，那么存储空间需要相应的扩大，不同的小数秒精度需要的存储空间不同，如下表：

小数秒精度	存储空间要求
0	0字节
1或2	1字节
3或4	2字节
5或6	3字节

也就是说如果你选择使用DATETIME(1)，那么需要的存储空间就是在DATETIME的空间上再加上小数秒需要的空间，就是8+1=9个字节，类似的，DATETIME(3)就需要10个字节。所以，MySQL5.6.4这个版本之后的各个类型需要的存储空间和取值范围就如下：

类型	存储空间大小	取值范围	格式	用途
YEAR	1字节	1901-2155	YYYY	年份值
DATE	3字节	1000-01-01′ ～ ‘9999-12-31’	YYYY-MM-DD	日期值
TIME	3字节+小数秒存储空间	-838:59:59[.000000]’ ～ ‘838:59:59[.000000]’	HH:MM:SS.[小数秒]	时间值或持续时间
DATETIME	5字节+小数秒存储空间	1000-01-01 00:00:00[.000000]’ ～ ‘9999-12-31 23:59:59[.999999]’	YYYY-MM-DD HH:MM:SS.[小数秒]	混合日期和时间值
TIMESTAMP	4字节+小数秒存储空间	1970-01-01 00:00:01[.000000]’ ～ ‘2038-01-19 03:14:07[.999999]’	YYYYMMDD HHMMSS.[小数秒]	混合日期和时间值，时间戳

同时也为了兼容之前版本中的TIME、DATETIME和TIMESTAM类型。对于时间函数，如CURTIME()、SYSDATE()、current_timestamp()和UTC_TIMESTAMP()也增加了fsp（小数秒）的支持，例如：

mysql> select sysdate(6) as time;
+----------------------------+
| time                       |
+----------------------------+
| 2017-07-24 20:27:23.729135 |
+----------------------------+
1 row in set (0.00 sec)

mysql> select sysdate(6) as time;

+----------------------------+

| time |

+----------------------------+

| 2017-07-24 20:27:23.729135 |

+----------------------------+

1 row in set (0.00 sec)

TIMESTAMP

1970-01-01 00:00:00注定是一个特殊的时刻，这一天被称为位零日，也称为纪元，MySQL中把某个时刻距离1970-01-01 00:00:00的秒数称为时间戳。时间戳类型保存了从1970年1月1日午夜（格林尼治标准时间）到当前时间的秒数，比方说当前时间是2018-01-24 11:39:21，距离1970-01-01 00:00:00的秒数为1516765161，那么2018-01-24 11:39:21这个时刻的时间戳就是1516765161。不过在MySQL5.6.4之后，时间戳的值也可以加入小数秒。

TIMESTAMP类型使用4个字节存储，TIMESTAMP显示的值也依赖于时区，MySQL服务器、操作系统、以及客户端连接都有时区设置。用时间戳存储时间的好处就是，它展示的值可以随着时区的变化而变化。比方说我们把2018-01-24 11:39:21这个时刻存储到一个TIMESTAMP的列中，那么在中国你看到的时间就是2018-01-24 11:39:21，如果你去了日本，他们哪里的使用的是东京时间，比北京时间早一个小时，所以他们那显示的就是2018-01-24 10:39:21。而如果你用DATETIME存储2018-01-24 11:39:21的话，那不同时区看到的时间值都是一样的。

YEAR

YEAR类型占用1字节，根据SQL模式的不同在定义时可以指定显示的宽度为YEAR(4)或YEAR(2)，MySQL 5.7开始严格SQL模式只能定义YEAR宽度为4。对于YEAR(4)，其显示年份的范围为1901-2155。

4. 枚举型

ENUM和SET都是一种特殊的字符串类型，在从字符串列表中单选或多选元素的时候用得到它们。ENUM和SET类型都是集合类型，也称为枚举型，不同的是ENUM类型最多可枚举65536个元素，而SET类型最多枚举64个元素。

ENUM

ENUM枚举型有时候可以用来替代常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。ENUM类型因为只允许在集合中取得一个值，有点类似于单选框。在处理相互排拆的数据时容易让人理解，比如人的性别，男女只能选其一。

ENUM类型字段可以从集合中取得一个值或使用NULL值，除此之外的输入将会使MySQL在这个字段中插入一个空字符串。另外如果插入值的大小写与集合中值的大小写不匹配，MySQL会自动使用插入值的大小写转换成与集合中大小写一致的值。

ENUM类型在系统内部可以存储为数字，并且从1开始用数字做索引。一个ENUM类型最多可以包含65536个元素，其中一个元素被MySQL保留，用来存储错误信息，这个错误值用索引0或者一个空字符串表示。

MySQL认为ENUM类型集合中出现的值是合法输入，除此之外其它任何输入都将失败（配合严格SQL模式）。如下：

mysql> CREATE TABLE t5(sex ENUM('male','female'));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t5 select ('other');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1

mysql> set session sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t5 select ('other');
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> select * from t5;
+------+
| sex  |
+------+
|      |
+------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t5(sex ENUM('male','female'));

Query OK, 0 rows affected (0.02 sec)

mysql> insert into t5 select ('other');

ERROR 1265 (01000): Data truncated for column 'sex' at row 1

mysql> set session sql_mode='';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t5 select ('other');

Query OK, 1 row affected, 1 warning (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 1

mysql> select * from t5;

+------+

| sex |

+------+

| |

+------+

1 row in set (0.00 sec)

由于是在MySQL 5.7测试，默认严格SQL模式，当插入一个不存在的ENUM值时就会抛异常。当我关闭SQL模式时，再次插入给了一个警告，但是可以看到查询为空。

SET

SET类型与ENUM类型相似但不相同，SET类型可以从预定义的集合中取得任意数量的值，相当于多选框。并且与ENUM类型相同的是，任何试图在SET类型字段中插入非预定义的值都会使MySQL插入一个空字符串。如果插入一个即有合法的元素又有非法的元素的记录，MySQL将会保留合法的元素，除去非法的元素。

一个SET类型最多可以包含64项元素，在SET元素中值被存储为一个分离的“位”序列，这些“位”表示与它相对应的元素。“位”是创建有序元素集合的一种简单而有效的方式。并且它还去除了重复的元素，所以SET类型中不可能包含两个相同的元素。

5、BIT位类型

对于BIT(N)来说，N指的是该类型最多能存储的二进制位的个数，比如BIT(3)就是指最多能存放3个二进制位。而内存空间最少的分配单位就是字节，所以不足一字节的按一字节计算，所以存储空间就是(N+7)/8个字节。

三、常用的类型属性

这些属性并能应用在所有的数据类型上，对不同的数据类型有不同的属性可使用。

AUTO_INCREMENT

auto_increment能为新插入的行赋一个唯一的整数标识符。为列赋此属性将为每个新插入的行赋值为上一次插入的ID+1。MySQL要求将auto_increment属性用于作为主键的列，必须是正整数、不能为空此外，每个表只允许有一个auto_increment列。

NOT NULL

如果将一个列定义为not null，将不允许向该列插入null值。建议在重要情况下始终使用not null属性，因为它提供了一个基本验证，确保已经向查询传递了所有必要的值。（默认为NULL）

NULL

为列指定null属性时，该列可以保持为空，而不论行中其它列是否已经被填充。记住，null精确的说法是“无”，而不是空字符串或0。

UNSIGNED

UNSIGNED属性就是将数字类型无符号化，不允许为0或负数。例如，INT的类型范围是-2147483648 ~ 2147483647，INT UNSIGNED的范围类型就是0 ~ 4294967295。

PRIMARY KEY

Primary key属性用于确保指定行的唯一性。指定为主键的列中，值不能重复，也不能为空。为指定为主键的列赋予auto_increment属性是很常见的，因为此列不必与行数据有任何关系，而只是作为一个唯一标识符。

FOREIGN KEY

外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。

INDEX

如果所有其他因素都相同，要加速数据库查询，使用索引通常是最重要的一个步骤。索引一个列会为该列创建一个有序的键数组，每个键指向其相应的表行。以后针对输入条件可以搜索这个有序的键数组，与搜索整个未索引的表相比，这将在性能方面得到极大的提升。

UNIQUE KEY

被赋予unique key属性的列将确保所有值都有不同的值，只是null值可以重复。一般会指定一个列为unique key，以确保该列的所有值都不同，例如Email存储。

ZEROFILL

Zerofill属性可用于任何数值类型，表示用0填充数值型位长所有剩余字段空间。例如，无符号int的默认宽度是10；因此，当“零填充”的int值为4时，将表示它为0000000004。注意zerofill的字段默认是无符号的，就是大于等于零的数据，输入小与零的数字将会报错。

DEFAULT

Default属性确保在没有任何值可用的情况下，赋予某个常量值，这个值必须是常量，因为MySQL不允许插入函数或表达式值。此外，此属性无法用于BLOB或TEXT列。

四、数据库设计的简单原则

1. 更小的通常更好

一般情况下，应该尽量使用可以正确存储数据的最小的数据类型。更小的数据类型通常更快，因为他们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。

2. 简单就好

简单的数据类型的操作通常需要更少的CPU周期。例如，整型比字符串操作代价更低，因为字符串和校对规则（排序规则）使字符比整型更复杂。

3. 尽量避免NULL

很多表都包含可为NULL的列，即使应用程序并不需要保存NULL也是如此，这是因为可为NULL是列的默认属性。通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值。因为，如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使用得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间。通常把可为NULL的列改为NOT NULL带来的性能提升比较小。但是如果计划在列上建立索引，就应该尽量避免涉及设计成可为NULL的列。当然也有例外，InnoDB使用单独的位存储NULL值，所以对于稀疏数据有很好的空间效率。

4. ALTER TABLE操作

MySQL的ALTER TABLE操作的性能对大表来说是个大问题，MySQL执行大部分修改结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。这样操作可能需要花费很长时间，如果内存不足而表又很大，而且还有很多索引的情况下尤其如此。许多人都有这样的经验，ALTER TABLE操作需要花费数个小时才能完成。MySQL 5.6开始支持Online DDL操作了。

<延伸>

MySQL数据类型优化

https://dev.mysql.com/doc/refman/8.0/en/data-types.html

https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html

如果您觉得本站对你有帮助，那么可以支付宝扫码捐助以帮助本站更好地发展，在此谢过。