python encoding_error ai_generated true

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1366, "第 1 行的 'message' 列出现不正确的字符串值：'\xF0\x9F\x98\x80'")

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1366, "Incorrect string value: '\xF0\x9F\x98\x80' for column 'message' at row 1")

ID: python/sqlalchemy-mysql-charset-issue

其他格式: JSON · Markdown 中文 · English
80%修复率
86%置信度
0证据数
2024-04-02首次发现

版本兼容性

版本状态引入弃用备注
1.4.x active — — —
2.0.x active — — —

根因分析

数据库/表/列的字符集不是 utf8mb4，无法存储像表情符号这样的 4 字节 Unicode 字符。

English

The database/table/column character set is not utf8mb4, which cannot store 4-byte Unicode characters like emojis.

generic

解决方案

  1. 90% 成功率 
    Use charset='utf8mb4' in the connection URL: engine = create_engine('mysql+pymysql://user:pass@host/db?charset=utf8mb4')
  2. 85% 成功率 
    Alter the table to use utf8mb4: ALTER TABLE messages CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

无效尝试

常见但无效的做法:

  1. 90% 失败

    utf8 in MySQL is actually utf8mb3, which still doesn't support 4-byte characters.

  2. 80% 失败

    Corrupts the data.