mysql中char(n)中的n_varchar(N)和char(N)中的N解析

"root@localhost:mysql6666.sock  [china]>show create table a1;

+-------+-----------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                  |

+-------+-----------------------------------------------------------------------------------------------+

| a1    | CREATE TABLE `a1` (

`name` varchar(21844) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------+-------------------------------------

varchar(N) 类型支持的最大字节长度是65535，65535-2-1=65532(最大支持65532字节)，2是标识位，1标识null。

(N)代表的是字符个数，不是字节大小。

utf-8 占3个字节，能够存储65532/3=21844.个字符。如果设置21845则会报错提示行数据大小超限制。

"root@localhost:mysql6666.sock  [china]>create table a3 (name varchar(21845) not null);

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

=================================================================================================================

1个汉字占3个字符，1个英文字母或者数字占1个字符。

select length(X)统计字符占的大小。

*************************** 3. row ***************************

length(name): 1

name: d

*************************** 4. row ***************************

length(name): 3

name: 我

--------------------------------------------------------------------------

select char_length(X) 统计的是字符的个数。

*************************** 3. row ***************************

char_length(name): 1

name: d

*************************** 4. row ***************************

char_length(name): 1

name: 我

4 rows in set (0.00 sec)

"root@localhost:mysql6666.sock  [china]>select length(name) from a1;

+--------------+

| length(name) |

+--------------+

|        21844 |

|        21844 |

|            1 |

|            3 |

+--------------+

=====================================================================================================

char(N) N代表字符个数，最大字符个数255,代表能存255个字符。不是字符大小限制在255个字符。

#中文超出个数被截断

"root@localhost:mysql6666.sock  [china]>insert into a11 select repeat('中',255);

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

"root@localhost:mysql6666.sock  [china]>insert into a11 select repeat('中',256);

Query OK, 1 row affected, 1 warning (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 1

"root@localhost:mysql6666.sock  [china]>show warnings;

+---------+------+-------------------------------------------+

| Level   | Code | Message                                   |

+---------+------+-------------------------------------------+

| Warning | 1265 | Data truncated for column 'name' at row 1 |

+---------+------+-------

#英文超出个数被截断

"root@localhost:mysql6666.sock  [china]>insert into a11 select repeat('e',255);

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

"root@localhost:mysql6666.sock  [china]>insert into a11 select repeat('e',256);

Query OK, 1 row affected, 1 warning (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 1

"root@localhost:mysql6666.sock  [china]>show warnings;

+---------+------+-------------------------------------------+

| Level   | Code | Message                                   |

+---------+------+-------------------------------------------+

| Warning | 1265 | Data truncated for column 'name' at row 1 |

+---------+------+-------------------------------------------+

1 row in set (0.00 sec)

汉字占3个字节的大小，英文占一个字节大小

"root@localhost:mysql6666.sock  [china]>select length(name) from a11;

+--------------+

| length(name) |

+--------------+

|          255 |

|          255 |

|          255 |

|            3 |

|            1 |

|          255 |

|          258 |

|          765 |我

|          765 |

|          255 |e

|          255 |

+--------------+