innodb下几个关于mysql字段类型的最大值测试

最新推荐文章于 2024-11-18 14:22:09 发布

cuanqiezhou0025

最新推荐文章于 2024-11-18 14:22:09 发布

阅读量80

点赞数

文章标签：数据库

varchar类型

关于varchar的最大值，官方手册说是“长度可以指定为0到65,535之间的值”，实际测试只有65532，
65532-65535之间会有错误提示“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”；
超过65535则默认设置为“mediumtext”类型

测试如下：、

mysql> CREATE TABLE t2 (a VARCHAR(65532)) TYPE=innodb;--最大只设65532
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE TABLE t3 (a VARCHAR(65533)) TYPE=innodb;---超过65532报错
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> CREATE TABLE t17(a VARCHAR(65535)) TYPE=innodb;;---65535仍然报错
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> CREATE TABLE t17(a VARCHAR(65536)) TYPE=innodb;---超过65535则之间建立mediumtext 类型
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> desc t17;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a | mediumtext | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

char类型

与官方文档一致最大只能255
超过定义的长度后，自动截断后面的字符
innodb不会截断字符串前后的空格；