Mysql数据类型之字符串的案例介绍(含latin1下varchar的最大长度是65532还是65533)

认识Mysql数据类型里的字符串

数据类型概述

Mysql里的数据类型主要有数值型(整形、浮点型)、时间类型、字符串类型、JSON类型、坐标类型、混合类型(整数或字符串)。本文主要介绍字符串和二进制类型的使用。

字符串与二进制类型概述

字符串是和二进制类型是比较灵活的可以存放任意数据的灵活类型。二进制类型和字符串的差别是字符串在存储时指定了字符集，即mysql能“读懂”这个字符串，而二级制则存的是裸的数据，需要特定或者专有的方法（程序）去解析。

行容量限制

因为mysql里每一个行的大小被限制在64k，所以varchar、varbinary最大只能存65533 个字符。其中如果该字段定义为NOT NULL为65533、NULL 为65532，这是因为定义为VARCHAR类型需要占2个字节，定义为NULL时存储标记位占1个字节，总存储为65535，所以有65532这个结果。

更多信息可参考： https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html#row-size-limits

字符集

utf8mb4是未来支持的默认字符集(即一个字符占4个字节)，当前版本(Mysql.8)的字符集是utf8mb3(一个字符占3个字节),尽管它是utf8的别名。

排序规则

排序规则经常用在字段排序和比较中，以utf8mb4_0900_ai_ci，来介绍其含义：

utf8mb4:所属的字符集

0900：该排序规则基于Unicode Collation Algorithm (UCA) 9.0.0。

ai: 排序规则可以是重音不敏感(ai)或重音敏感(as)。

ci: A collation can be case insensitive (ci) or case sensitive (cs)

数据类型与最大长度

数据类型	最大字节长度
char(M)	255字符
varchar(M)	utf8mb4下16383个字符、latin1下65532或65533个字符
tinytext	255个字节
text	65535个字节
mediumtext	16777216个字节
longtext	4294967296个字节
binary(M)	255个字节
varbinary(M)	65532个字节

代码示例

-- 1 char类型最大为255个字符。
CREATE TABLE tb_chartype
(charcol char(256))
> 1074 - Column length too big for column 'charcol' (max = 255); use BLOB or TEXT instead
> 时间: 0s
-- 2 超过255则报错。
INSERT INTO tb_chartype
select REPEAT('m', 256)
> 1406 - Data too long for column 'charcol' at row 1
> 时间: 0.004s

INSERT INTO tb_chartype
select REPEAT('m', 255);

INSERT INTO tb_chartype
select REPEAT('中', 255);


# 3 设置varchar的最大长度
ALTER TABLE tb_chartype ADD varcharcol VARCHAR(65532)
> 1074 - Column length too big for column 'varcharcol' (max = 21844); use BLOB or TEXT instead
> 时间: 0.518s

这里报错是因为当前表的字符集是utf8mb3即一个字符占3个字节，所以当前字符集下varchar最大存的是21844个字符。即65532/3=21844。


CREATE TABLE `tb_chartype` (
  `charcol` char(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;


-- 4 因为一个表定义时所有字段的总长度不超过65535字节(64k)，所以这里报错。
ALTER TABLE tb_chartype ADD varcharcol VARCHAR(21844)
> 1118 - 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
> 时间: 0.448s

ALTER TABLE tb_chartype ADD varcharcol VARCHAR(21589)
> OK
> 时间: 0.62s

--这里21844-255=21589

-- 5 插入21590个则会报错，因为最大长度是21589
INSERT INTO tb_chartype(varcharcol)
select REPEAT('国', 21590);

INSERT INTO tb_chartype(varcharcol)
select REPEAT('国', 21589)
> Affected rows: 1
> 时间: 0.101s

-- 6 如果指定表的字符集是latin1，则最大长度是65532。
CREATE TABLE tb_charvar2(
varcol varchar(65532)
) DEFAULT CHARSET=latin1
INSERT INTO tb_charvar2
SELECT REPEAT('A',65532)

-- 7 varchar类型单字段最大长度。NOT NULL为65533、NULL 为65532。Mariadb里统一为65532。
CREATE TABLE `a` (
  `c1` varchar(65533) not null
) ENGINE=InnoDB DEFAULT CHARSET=latin1


CREATE TABLE `b` (
  `c1` varchar(65532) null
) ENGINE=InnoDB DEFAULT CHARSET=latin1