MySQL数据类型的验证

共计 4567 个字符，预计需要花费 12 分钟才能阅读完成。

MySQL 数据类型的验证

CHAR

char (M) M 字符，长度是 M * 字符编码长度，M 最大 255。

验证如下：

mysql> create table t1(name char(256)) default charset=utf8;
ERROR 1074 (42000): Column length too big for column 'name' (max = 255); use BLOB or TEXT instead
mysql> create table t1(name char(255)) default charset=utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 values(repeat(' 整 ',255));
Query OK, 1 row affected (0.00 sec)

mysql> select length(name),char_length(name) from t1;
+--------------+-------------------+
| length(name) | char_length(name) |
+--------------+-------------------+
|          765 |               255 |
+--------------+-------------------+
1 row in set (0.00 sec)

VARCHAR

VARCHAR(M)，M 同样是字符，长度是 M * 字符编码长度。它的限制比较特别，行的总长度不能超过 65535 字节。

mysql> create table t1(name varchar(65535));
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> create table t1(name varchar(65534));
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> create table t1(name varchar(65533));
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> create table t1(name varchar(65532));
Query OK, 0 rows affected (0.08 sec)

注意，以上表的默认字符集是 latin1，字符长度是 1 个字节，所以对于 varchar，最大只能指定 65532 字节的长度。

如果是指定 utf8，则最多只能指定 21844 的长度

mysql> create table t1(name varchar(65532)) default charset=utf8;
ERROR 1074 (42000): Column length too big for column 'name' (max = 21845); use BLOB or TEXT instead
mysql> create table t1(name varchar(21845)) default charset=utf8;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> create table t1(name varchar(21844)) default charset=utf8;
Query OK, 0 rows affected (0.07 sec)

注意：行的长度最大为 65535，只是针对除 blob，text 以外的其它列。

mysql> create table t1(name varchar(65528),hiredate datetime) default charset=latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> create table t1(name varchar(65527),hiredate datetime) default charset=latin1;
Query OK, 0 rows affected (0.01 sec)

确实，datetime 占了 5 个字节。

TEXT，BLOB

mysql> create table t1(name text(255));
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2(name text(256));
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (`name` tinytext) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (`name` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

通过上面的输出可以看出 text 可以定义长度，如果范围小于 2 ⁸（即 256）则为 tinytext，如果范围小于 2 ¹⁶（即 65536），则为 text，如果小于 2 ²⁴，为 mediumtext，小于 2 ³²，为 longtext。

上述范围均是字节数。

如果定义的是 utf8 字符集，对于 text，实际上只能插入 21845 个字符

mysql> create table t1(name text) default charset=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(repeat(' 整 ',21846));
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert into t1 values(repeat(' 整 ',21845));
Query OK, 1 row affected (0.05 sec)

DECIMAl

关于 Decimal，官方的说法有点绕，

Values for DECIMAL (and NUMERIC) columns are represented using a binary format that packs nine decimal (base 10) digits into four bytes. Storage for the integer and fractional parts of each value are determined separately. Each multiple of nine digits requires four bytes, and the“leftover”digits require some fraction of four bytes. The storage required for excess digits is given by the following table.

还提供了一张对应表

MySQL 数据类型的验证

对于以上这段话的解读，有以下几点：

1. 每 9 位需要 4 个字节，剩下的位数所需的空间如上所示。

2. 整数部分和小数部分是分开计算的。

譬如 Decimal(6,5)，从定义可以看出，整数占 1 位，整数占 5 位，所以一共占用 1 +3= 4 个字节。

如何验证呢？可通过 InnoDB Table Monitor

如何启动 InnoDB Table Monitor，可参考：http://dev.mysql.com/doc/refman/5.7/en/innodb-enabling-monitors.html

mysql> create table t2(id decimal(6,5));
Query OK, 0 rows affected (0.01 sec)

mysql> create table t3(id decimal(9,0));
Query OK, 0 rows affected (0.01 sec)

mysql> create table t4(id decimal(8,3));
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;
Query OK, 0 rows affected, 1 warning (0.01 sec)

结果会输出到错误日志中。

查看错误日志：

对于 decimal(6,5), 整数占 1 位，小数占 5 位，一共占用空间 1 +3= 4 个字节

对于 decimal(9,0), 整数部分 9 位，每 9 位需要 4 个字节，一共占用空间 4 个字节

对于 decimal(8,3), 整数占 5 位，小数占 3 位，一共占用空间 3 +2= 5 个字节。

至此，常用的 MySQL 数据类型验证完毕~

对于 CHAR，VARCHAR 和 TEXT 等字符类型，M 指定的都是字符的个数。对于 CHAR，最大的字符数是 255。对于 VARCHAR，最大的字符数与字符集有关，如果字符集是 latin1，则最大的字符数是 65532（毕竟每一个字符只占用一个字节），对于 utf8，最大的字符数是 21844，因为一个字符占用三个字节。本质上，VARCHAR 更多的是受到行大小的限制（最大为 65535 个字节）。对于 TEXT，不受行大小的限制，但受到自身定义的限制。

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2016-04/130150.htm