mysql 存储emoji 表情
近期订单模块发现奇怪bug,某个客户的订单一直更新不到系统中; 于是发现客户的订单号竟然有表情(🐝)…..真的奇葩;
特殊表情字符无法入库,而订单相关表设计的字符集为utf8,不支持特殊字符。存储emoji表情需要unicode字符集, 所以修改相关字符集, 得以解决。
需要将mysql 连接指定字符集utf8mb4
mysql+mysqldb://user:pwd@localhost/database?charset=utf8mb4
数据库字符集 首先查看字符集设置如下, character_set_system为utf8, 需要将其修改为utf8mb4
查看命令:SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+ | Variable_name | Value | +--------------------------+--------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | collation_connection | utf8mb4_unicode_ci | | collation_database | utf8mb4_unicode_ci | | collation_server | utf8mb4_unicode_ci | +--------------------------+--------------------+
改库、表、字段的字符集
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;