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 insteadmysql> 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 BLOBsmysql> 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 BLOBsmysql> 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 BLOBsmysql> 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 insteadmysql> 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 BLOBsmysql> 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 BLOBsmysql> create table t1(name varchar(65527),hiredate datetime) default charset=latin1;Query OK, 0 rows affected (0.01 sec) |
