CHAR 详解
CHAR(20):20指的是表中的a字段能存储的最大字符个数
CREATE TABLE `a` (
`a` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
- 如果需要存储的字符串的长度跟所有值的平均长度相差不大,适合用char,如MD5。
- 对于经常改变的值,char优于varchar,原因是固定长度的行不容易产生碎片。
- 对于很短的列,char优于varchar,原因是varchar需要额外一个或两个字节存储字符串的长度。
mysql> insert into a select "aaaaaaaaaaaaaaaaaaaaa"; //21个a ERROR 1406 (22001): Data too long for column 'a' at row 1
mysql> insert into a select "aaaaaaaaaaaaaaaaaaa"; //20个a Query OK, 1 row affected (0.09 sec) Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into a select "我我我我我我我我我我我我我我我我我我我我"; //20个我 Query OK, 1 row affected (0.09 sec) Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into a select "我我我我我我我我我我我我我我我我我我我我我"; //21个我 ERROR 1406 (22001): Data too long for column 'a' at row 1
mysql> insert into a select "我我我我我我我我我我我我我我我我我我我1"; //19个我+1个字符"1“=20个字符 Query OK, 1 row affected (0.06 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into a select "我我我我我我我我我我我我我我我我我我我11";//19个我+2个字符"1“=21个字符
ERROR 1406 (22001): Data too long for column 'a' at row 1
char 存储极限
表为utf8字符集:
mysql> create table a1( a char(255)); Query OK, 0 rows affected (0.33 sec) mysql> create table a11( a char(256)); ERROR 1074 (42000): Column length too big for column 'a' (max = 255); use BLOB or TEXT instead
mysql> insert into a1 select repeat("a",255); Query OK, 1 row affected (0.06 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into a1 select repeat("a",256); ERROR 1406 (22001): Data too long for column 'a' at row 1
mysql> insert into a1 select repeat("我",256); ERROR 1406 (22001): Data too long for column 'a' at row 1
mysql> insert into a1 select repeat("我",255); Query OK, 1 row affected (0.11 sec) Records: 1 Duplicates: 0 Warnings: 0
mysql> select length(a) from a1;
+-----------+
| length(a) |
+-----------+
| 255 |
| 765 |
+-----------+
2 rows in set (0.00 sec)