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个aERROR 1406 (22001): Data too long for column 'a' at row 1

 

mysql> insert into a select "aaaaaaaaaaaaaaaaaaa";                      //20个aQuery 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: 0mysql> 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: 0mysql> 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)