mysql数据类型的验证_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以外的其它列。