1
回答
关于 mysql varchar 字段的长度
利用AWS快速构建适用于生产的无服务器应用程序,免费试用12个月>>>   

本文是对row_format为compact时,varchar长度的一个探讨。
《MySQL技术内幕 InnoDB存储引擎》中姜承尧提到MySQL varchar最大长度65535是指所有的varchar长度累加必须小于65535,这篇文章对这个进行一个探讨,发现65535中应该包含了所有字段的长度、变长字段长度标示位、NULL标示位的累计。在此感谢姜承尧的《MySQL技术内幕 InnoDB存储引擎》,对很多东西有了一个更清晰的了解。

注:下面的测试是
character_set=utf8
utf8下面一个字符占3个字节,因此最大是65535/3=21845,但是21845之后没有地方存储长度信息,所以单列的最大长度是21844

Every table (regardless of storage engine) has a maximum row size of 65,535 bytes。可以通过下面的测试有个了解。

SHOW TABLE STATUS LIKE "test";
Row_format:Compact
 
CREATE TABLE `test` (
  `c1` VARCHAR(21844) COLLATE utf8_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
 
mysql> ALTER TABLE test MODIFY COLUMN c1 VARCHAR(21844) NOT NULL; 
Query OK, 7 ROWS affected (0.07 sec)
Records: 7  Duplicates: 0  Warnings: 0
 
mysql> ALTER TABLE test MODIFY COLUMN c1 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. You have TO CHANGE SOME COLUMNS TO TEXT OR BLOBs
mysql> SELECT 21844*3+2;
+-----------+
| 21844*3+2 |
+-----------+
|     65534 |
+-----------+
1 ROW IN SET (0.00 sec)

因为null标示位占用了一个字节,所以可以去掉not null限制。

mysql> ALTER TABLE test MODIFY c1 VARCHAR(21844) COLLATE utf8_bin; 
Query OK, 7 ROWS affected (0.08 sec)
Records: 7  Duplicates: 0  Warnings: 0
 
mysql> ALTER TABLE test ADD COLUMN i1 INT NOT NULL;
ERROR 1118 (42000): ROW SIZE too LARGE. The maximum ROW SIZE FOR the used TABLE TYPE, NOT counting BLOBs, IS 65535. You have TO CHANGE SOME COLUMNS TO TEXT OR BLOBs
 
mysql> ALTER TABLE test MODIFY COLUMN c1 VARCHAR(21843) NOT NULL;   
Query OK, 7 ROWS affected (0.08 sec)
Records: 7  Duplicates: 0  Warnings: 0
 
mysql> ALTER TABLE test ADD COLUMN i1 INT NOT NULL;              
Query OK, 7 ROWS affected (0.08 sec)
Records: 7  Duplicates: 0  Warnings: 0
 
mysql> ALTER TABLE test ADD COLUMN i2 INT NOT NULL; 
ERROR 1118 (42000): ROW SIZE too LARGE. The maximum ROW SIZE FOR the used TABLE TYPE, NOT counting BLOBs, IS 65535. You have TO CHANGE SOME COLUMNS TO TEXT OR BLOBs
 
mysql> SELECT 21843*3+2+4;
+-------------+
| 21843*3+2+4 |
+-------------+
|       65535 |
+-------------+
1 ROW IN SET (0.00 sec)

这个时候去掉not null就不可以了

mysql> ALTER TABLE test MODIFY COLUMN c1 VARCHAR(21843);           
ERROR 1118 (42000): ROW SIZE too LARGE. The maximum ROW SIZE FOR the used TABLE TYPE, NOT counting BLOBs, IS 65535. You have TO CHANGE SOME COLUMNS TO TEXT OR BLOBs

参照如下:
1,http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html
2,MySQL技术内幕 InnoDB存储引擎

原文转自:http://www.themysql.com/mysql/%E5%85%B3%E4%BA%8Emysql-varchar%E9%95%BF%E5%BA%A6.html

举报
鉴客
发帖于6年前 1回/1K+阅
顶部