本文是对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存储引擎
--转自