记一次MySql UTF-8问题

# 前言

来了新测试，他测试我们程序都发现了一个字符报错，因为他名字ID带了EMOJI表情，而之前那个测试是没有的，但是我每个数据库中有很多字符串字段都是utf8，只有utf8mb4才支持表情

# 解决

查看一下有哪些表是需要解决的

SELECT
	* 
FROM
	information_schema.`TABLES` 
WHERE
	TABLE_SCHEMA = 'scenic_sys' 
	AND TABLE_COLLATION != 'utf8mb4_general_ci'

1
2
3
4
5
6
7

scenic_tkt是数据库名字

找出所有执行sql语句

SELECT
    CONCAT(
        'ALTER TABLE ',
        TABLE_NAME,
        ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;'
    ) as 'SQL_STR'
FROM
    information_schema.`TABLES`
WHERE
 	TABLE_SCHEMA = 'scenic_tkt'
 	AND TABLE_COLLATION != 'utf8mb4_general_ci'

1
2
3
4
5
6
7
8
9
10
11

执行语句

ALTER TABLE ums_member CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE ums_member_login_log CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE ums_member_phone CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE ums_third_account CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE ums_traveler CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE undo_log CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

1
2
3
4
5
6

帮我改善此页面

上次更新: 2021/12/01, 08:19:14

← GitCommitId开发生产环境长度不一致 python发送邮件demo→