mysql 1351,MySQL下varchar最大存储字节数

MySQL中的varchar最大能放多少字符呢？看官方文档有如下说明：

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of aVARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. See Section C.10.4, “Limits on Table Column Count and Row Size”.

varchar类型的列是可变长的字符串。长度可以是0-65535。varchar有效的最大长度受限于最大的行大小(所以列加起来最大65535个字节)和字符集。

另外官方文档还有如下说明：

The following table illustrates the differences between CHAR and VARCHAR by showing the result of storing various string values into CHAR(4) and VARCHAR(4) columns (assuming that the column uses a single-byte character set such as latin1).

Value

CHAR(4)

Storage Required

VARCHAR(4)

Storage Required

''

'    '

4 bytes

''

1 byte

'ab'

'ab  '

4 bytes

'ab'

3 bytes

'abcd'

'abcd'

4 bytes

'abcd'

5 bytes

'abcdefgh'

'abcd'

4 bytes

'abcd'

5 bytes

通过上表可知道：

对于单字节的varchar(4)中的4是4个字符，而存储占用空间会多一个字节。如果是utf-8的则根据对应的字符占不同的字节数。

有了上面的理论知识，来实践一下：

先查询自己的数据库默认编码

mysql> show variables like 'character_set_database';+------------------------+-------+| Variable_name| Value |+------------------------+-------+| character_set_database | utf8|+------------------------+-------+1 row in set (0.09 sec)

建表

mysql> create table test_varchar(v1 varchar(21844));Query OK, 0 rows affected (0.03 sec)

如果长度设置成21845则会报错

mysql> create table test_varchar(v1 varchar(65535));ERROR 1074 (42000): Column length too big for column 'v1' (max = 21845); use BLOB or TEXT insteadmysql> create table test_varchar(v1 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中varchar在utf-8最大长度为：21844

喜欢 (1)or分享 (0)