MySQL不同字符集所占用不同的字节大小

收心

发布于 2023-08-24 13:53:20

2890

发布于 2023-08-24 13:53:20

文章被收录于专栏：Java实战博客Java实战博客

不同字符集的数据库不代表其所有字段的字符集都是库所使用的字符集，每个字段可以拥有自己独立字符集！库的字符集是约束字段的字符集！

建表DDL语句 + Insert DML语句

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for testChatSet
-- ----------------------------
DROP TABLE IF EXISTS `testChatSet`;
CREATE TABLE `testChatSet` (
  `utf8mb4` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `utf16` varchar(255) CHARACTER SET utf16 COLLATE utf16_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of testChatSet
-- ----------------------------
BEGIN;
INSERT INTO `testChatSet` (`utf8mb4`, `utf16`) VALUES ('abc', 'abc');
INSERT INTO `testChatSet` (`utf8mb4`, `utf16`) VALUES ('你好么', '你好么');
INSERT INTO `testChatSet` (`utf8mb4`, `utf16`) VALUES ('123', '123');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

查询语句

SELECT
	utf8mb4 AS 'utf8',
	utf16 AS 'utf16',
	CHARACTER_LENGTH( utf8mb4 ) AS 'utf8字符长度',
	CHARACTER_LENGTH( utf16 ) AS 'utf16字符长度',
	CHARSET( utf8mb4 ) AS 'utf8字符集',
	CHARSET( utf16 ) AS 'utf16字符集',
	length( utf8mb4 ) AS 'utf8字节长度',
	length( utf16 ) AS 'utf16字节长度'
FROM
	testChatSet;

输出：