MySQL数据类型之CHAR与VARCHAR及row size max=65535bytes

来源：互联网发布：游戏数据修改器编辑：程序博客网时间：2024/06/08 03:23

CHAR与VARCHAR，字符类型。

比如CHAR(10)，表示定长的最大存储10个字符的数据类型。

我们知道mysql数据库表中的行记录的最大size为65535个byte。

并且就目前来看，mysql所支持的字符集中也只有utf8mb4需要4个byte来表示一个字符。

（说错了，4个字节的还有utf32和gb18030，以及utf16和utf16le。）

所以即使在utf8mb4字符集下，使用CHAR(255)时，也不会超过65535个byte的限制。

但是VARCHAR类型就不一定了。

由于行size最大为65535个byte。

所以在utf8字符集下，比如（这里的表都考虑为只有一个列name的情况下，如果多个列，同样遵循row size max = 65535bytes）：

C:\Users\Administrator>mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 55
Server version: 5.7.11-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT
-> SCHEMA_NAME,
-> DEFAULT_CHARACTER_SET_NAME
-> FROM
-> information_schema.`SCHEMATA`
-> WHERE SCHEMA_NAME LIKE 'test%';
+-------------+----------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME |
+-------------+----------------------------+
| test | utf8 |
| test1 | utf8mb4 |
| test2 | latin1 |
+-------------+----------------------------+
3 rows in set (0.00 sec)

mysql> use test
Database changed
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.25 sec)

mysql> create table t1(name varchar(65536));
ERROR 1074 (42000): Column length too big for column 'name' (max = 21845); use BLOB or TEXT instead
mysql>

提示我们列长度太大，max=21845，否则就使用blob或者text代替

ok，那么我们改成varchar(21845)试试：

mysql> create table t1(name varchar(21845));
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>
也失败，原因是row size超过65535。

为什么呢？

其实对于每个varchar类型列，除开本身存储的数据外，还需要2个byte来存储数据长度。

然后每row如果包含char/varchar列，那么还额外需要1个byte来存储其他信息（到底是什么，不太清楚），这1个字节是char和varchar共用的。

所以，在utf8（3字节）下，我们指定21845其实是错误的。

那么相应地，在utf8mb4（4字节）下，我们能最大指定：

mysql> select (65535-2-1)/4;
+---------------+
| (65535-2-1)/4 |
+---------------+
| 16383.0000 |
+---------------+
1 row in set (0.00 sec)

mysql> drop table if exists test1.t1;
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> create table test1.t1(name varchar(16383));
Query OK, 0 rows affected (0.56 sec)

mysql>

而在latin1（单字节）字符集下，我们能最大指定：

mysql> select (65535-2-1)/1;
+---------------+
| (65535-2-1)/1 |
+---------------+
| 65532.0000 |
+---------------+
1 row in set (0.00 sec)

mysql> drop table if exists test2.t1;
Query OK, 0 rows affected (0.22 sec)

mysql> create table test2.t1(name varchar(65534));
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 test2.t1(name varchar(65533));
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 test2.t1(name varchar(65532));
Query OK, 0 rows affected (0.32 sec)

mysql> desc test2.t1;
+-------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| name | varchar(65532) | YES | | NULL | |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql>

那么，如果我们有（只有）两个varchar列，那么每列最大宽度是多少呢？

根据运算，

在latin1下：

mysql> select (65535-2*2-1)/1/2;
+-------------------+
| (65535-2*2-1)/1/2 |
+-------------------+
| 32765.00000000 |
+-------------------+
1 row in set (0.00 sec)

mysql> create table t(name1 varchar(32765),name2 varchar(32766));
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 t(name1 varchar(32765),name2 varchar(32765));
Query OK, 0 rows affected (0.44 sec)

mysql>

可见，计算是正确的。

相应地，在utf8下：

mysql> select (65535-2*2-1)/3/2;
+-------------------+
| (65535-2*2-1)/3/2 |
+-------------------+
| 10921.66666667|
+-------------------+
1 row in set (0.00 sec)

mysql> create table t1(name1 varchar(10921), name2 varchar(10922)); #为什么一个是10921，另一个是10922呢？
Query OK, 0 rows affected (0.41 sec)

mysql> create table t1(name1 varchar(10922), name2 varchar(10922));
ERROR 1050 (42S01): Table 't1' already exists
mysql> create table t2(name1 varchar(10922), name2 varchar(10922));
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>

#为什么一个是10921，另一个是10922呢？

因为：

mysql> select (10921+10921)*3+2*2+1;
+-----------------------+
| (10921+10921)*3+2*2+1 |
+-----------------------+
| 65531 | #如果两个都是10921，那么嗨剩4个字节。所以生下来的4个字节，可以再存储一个3字节的utf8字符。即另一个宽度可以为10921+1=10922.
+-----------------------+
1 row in set (0.00 sec)

mysql> select (10921+10922)*3+2*2+1;
+-----------------------+
| (10921+10922)*3+2*2+1 |
+-----------------------+
| 65534 |
+-----------------------+
1 row in set (0.00 sec)

mysql>

这样，还剩1个字节，我们可以：

mysql> create table t2(name char(0),name1 varchar(10921), name2 varchar(10922));
Query OK, 0 rows affected (0.33 sec)

mysql>

这样，刚刚把65535字节占用完。char(0)表示啥子都不能存储（除了''和NULL）。

但是char(0)也需要额外1个字节来存储长度（刚刚占用了剩下的那1个字节）。

0 0