如何在MySQL 5.7中使用mysql-connector-python正确存储非拉丁字符

1. 修正连接配置的字符集参数 #

当前连接配置中charset参数取值为utf8，MySQL 5.7中内置的utf8为阉割版本的utf8mb3，仅支持最多3字节的Unicode字符，无法存储4字节的emoji、特殊符号、部分小语种字符，将取值修改为utf8mb4即可，可额外添加use_unicode参数确保强制使用Unicode传输数据：

sg_titles_db_settings = {
    'user': <user>,
    'password': <password>,
    'host': <host>,
    'port': <port>,
    'database': <db>,
    'charset': 'utf8mb4',
    'use_unicode': True
}

2. 修正连接初始化的编码设置SQL #

原手动执行的SET CHARACTERS SET utf8mb4存在语法错误，获取连接后可直接执行以下三条语句完成全局编码初始化：

def get_mysql_connection():
    try:
        db_connection = mysql.connector.connect(**sg_titles_db_settings)
        if db_connection.is_connected():
            cursor = db_connection.cursor()
            cursor.execute("SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci")
            cursor.execute("SET CHARACTER SET utf8mb4")
            cursor.execute("SET character_set_connection = utf8mb4")
        return db_connection
    except Error as e:
        print("Error: ", e)
        return False

3. 确认库、表、字段的字符集配置 #

仅修改连接编码无法解决存量表/字段的字符集不兼容问题，需依次检查对应层级的字符集配置：

• 检查数据库字符集：执行SQL SHOW VARIABLES LIKE '%character_set_database%';
• 检查目标表字符集：执行SQL SHOW CREATE TABLE 你的表名;
• 检查存储字符串的字段字符集：执行SQL SHOW FULL COLUMNS FROM 你的表名;

若上述查询结果不是utf8mb4，可通过对应SQL修改：

• 修改数据库字符集：ALTER DATABASE 你的库名 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
• 修改表字符集：ALTER TABLE 你的表名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
• 单独修改字段字符集：ALTER TABLE 你的表名 MODIFY COLUMN 字段名 字段原有类型 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

4. 兼容优化建议 #

• 升级mysql-connector-python到8.0及以上版本，旧版本对utf8mb4的支持存在缺陷，升级命令：pip install --upgrade mysql-connector-python
• 若修改后仍存在乱码，检查数据源读取环节的编码配置，确认写入MySQL的字符串本身没有出现转码错误。

内容的提问来源于stack exchange，提问作者Kevin G