MySQL如何存储表情

TABLE OF CONTENTS

1. MySQL默认的字符编码为什么不支持表情存储
2. MySQL如何存储表情
3. 附字符集查询常用命令

MySQL默认的字符编码为什么不支持表情存储

UTF-8编码规范是一种可变长度的字符编码（1~4字节），emoji表情需要4个字节存储，正常来说UTF-8规范的字符集应该可以存储emoji表情。MySQL 5.7版本默认字符集为utf8，排序规则为utf8_general_ci，utf8字符集实际上是一个不完整的 UTF-8 实现，只能存储最多 3 个字节的字符。可以说无法存储emoji表情是MySQL的锅，当然它提供了UTF-8的完整编码实现：utf8mb4字符集。

拓展下：UTF-8中的8是说每个字符的编码单位是8位，一个英文字符占用一个字节的存储空间，一个中文（含繁体）占用三个字节的存储空间。

查看当前MySQL默认的字符集和编码

-- select version(); 可查看当前mysql版本，本地测试的mysql使用的5.7，输出示例如下：
SHOW VARIABLES LIKE 'character_set_server'; -- utf8
SHOW VARIABLES LIKE 'collation_server'; -- utf8_general_ci

查看某个数据库或者表的编码

SHOW CREATE DATABASE sakila;
-- 输出示例：CREATE DATABASE `sakila` /*!40100 DEFAULT CHARACTER SET utf8 */

SHOW CREATE TABLE your_table_name;
-- 输出示例：
-- CREATE TABLE `actor` (
--   `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
--   `first_name` varchar(45) NOT NULL,
--   `last_name` varchar(45) NOT NULL,
--   `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
--   PRIMARY KEY (`actor_id`),
--   KEY `idx_actor_last_name` (`last_name`)
-- ) ENGINE=InnoDB AUTO_INCREMENT=202 DEFAULT CHARSET=utf8

MySQL如何存储表情

解决方案呼之欲出了，即使用UTF-8的完整编码实现：utf8mb4字符集。因此，建议建库时显式地设置字符集utf8mb4，排序规则根据大小写是否敏感选择。

检查字符集和排序规则的默认配置

-- 检查MySQL的字符集和排序规则的默认配置
SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';

-- 检查表的字符集和排序规则的默认配置
SHOW CREATE TABLE mytable;

创建数据库或表时指定字符集+排序规则（大小写是否敏感）

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

创建、修改表的字符集和排序规则

CREATE TABLE mytable (
    id INT PRIMARY KEY,
    content VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

修改表字段的字符集和排序规则

ALTER TABLE mytable MODIFY content VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

附字符集查询常用命令

-- 查询支持的字符集
SHOW CHARACTER SET;
-- 查询支持的排序规则
SHOW COLLATION;
SHOW COLLATION WHERE Charset = 'utf8mb4';

SELECT 
    @@character_set_database AS 'Database Character Set', 
    @@collation_database AS 'Database Collation';

-- 查询某个表的字符集和排序规则
SHOW TABLE STATUS LIKE 'actor';
-- 查询某个列的字符集和排序规则
SHOW FULL COLUMNS FROM actor;
SHOW FULL COLUMNS FROM actor where field = 'first_name';

-- 查询 INFORMATION_SCHEMA.COLUMNS 表来获取表各个列的字符集和排序规则
SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME 
FROM information_schema.COLUMNS 
WHERE TABLE_NAME = 'mytable';

-- 查询 INFORMATION_SCHEMA.SCHEMATA 表来获取数据库的字符集和排序规则
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'database_name';

声明：本站所有文章均为原创或翻译，遵循署名 - 非商业性使用 - 禁止演绎 4.0 国际许可协议，如需转载请确保您对该协议有足够了解，并附上作者名 (Tsukasa) 及原文地址