1. 数据
CREATE TABLE `encode` (
`id` bigint NOT NULL,
`latin1` varchar(30) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL COMMENT 'Latin1, 别名 ISO-8859-1 (定长, 1字节)',
`gbk` varchar(30) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT 'GBK(变长, 2字节)',
`utf8` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'utf8(变长, 3字节)',
`utf8mb4` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'utf8mb4(变长, 4字节)',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB;
INSERT INTO `test`.`encode` (`id`, `latin1`, `gbk`, `utf8`, `utf8mb4`) VALUES (1, 'abc123!@#', '中国人abc123!@#,。、', '中国人abc123!@#,。、', '中国人abc123!@#,。、❤️🍇💯');
2. 测试
select LENGTH(latin1), LENGTH(gbk), LENGTH(utf8), LENGTH(utf8mb4) from encode
union
select CHAR_LENGTH(latin1), CHAR_LENGTH(gbk), CHAR_LENGTH(utf8), CHAR_LENGTH(utf8mb4) from encode;
3. 结论
-
varchar存储最多65535个字节
lanti1(定长, 1字节): 可存65535个字符
gbk(变长, 2字节): 可存32767个字符
utf8(变长, 3字节): 可存21845个字符
utf8mb4(变长, 4字节): 可存16383个字符 -
varchar(n), 这个n, 指的是字符, 而不是字节
-
LENGTH: 字节长度, CHAR_LENGTH: 字符长度