mysql> create table t0008(col1 varchar(65535))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 BLOBs
mysql> create table t0008(col1 varchar(65534))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 BLOBs
mysql> create table t0008(col1 varchar(65533))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 BLOBs
mysql> create table t0008(col1 varchar(65532))charset=latin1;
Query OK, 0 rows affected (0.02 sec)
latin1字符集下的表varchar上限为65532,即一个字符一个字节
mysql> create table t0009(col1 varchar(65533))charset=utf8;
ERROR 1074 (42000): Column length too big for column 'col1' (max = 21845); use BLOB or TEXT instead
mysql> create table t0009(col1 varchar(21845))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 t0009(col1 varchar(21844))charset=utf8;
Query OK, 0 rows affected (0.00 sec)
utf8字符集下的表varchar上限为21844,即一个字符三个字节 65535-1-2 结果除以3 ==21844
-1表示第一个字节不存数据,-2表示两个字节存放varchar的长度,除以3是utf8字符特性,一个字符三个字节。
varchar 字段是将实际内容单独存储在聚簇索引之外,内容开头用1到2个字节表示实际长度(长度超过255时需要2个字节),因此最大长度不能超过65535即 2的16次方(0-65535)
varchar:变长,最大65535个字符(既是单列的限制,又是整行的限制)
mysql> create table t0012(id int,name char(20),col3 varchar(N))chaset=utf8;
N的值为:(65535-1-2-4-20*3)/3=21822
mysql> create table t0012(id int,name char(20),col3 varchar(N))charset=latin1;
N的值为:65535-1-2-4-20=65508
length():Return the length of a string in bytes.返回字符串所占的字节数(受编码影响).
utf8编码下,一个汉字是占三个字节,一个数字或字母占一个字节。其他编码下,一个汉字占两个字节, 一个数字或字母占一个字节。
char_length():Return number of characters in argument.返回字符串中的字符数.在任何编码下, 不管汉字还是数字或者是字母都算是一个字符
示例:
mysql> show create table t2;
+-------+-------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
`id` int(11) DEFAULT NULL,
`name` varchar(60) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------
INSERT INTO t2(id,NAME) VALUES(1,'丹丹');
INSERT INTO t2(id,NAME) VALUES(2,'dandan');
SELECT id,NAME,CHAR_LENGTH(NAME),LENGTH(NAME) FROM t2;
id name char_length(name) length(name)
------ ------ ----------------- --------------
1 丹丹 2 6
2 dandan 6 6
char和varchar比较: