mysql - 尝试存储表情符号时出错

Question

我使用 sequelize 作为 node.js 的 ORM 来与 mysql 数据库交互。

我的数据库字符集是 utf8mb4, utf8mb4_general_ci

我的表字符集是 utf8mb4, utf8mb4_general_ci

我的列 TYPE 是 TEXT，字符集是 utf8mb4, utf8mb4_general_ci

在执行任何查询之前，我正在执行以下代码:

Sequelize.query("SET NAMES utf8mb4");
Sequelize.query("SET CHARACTER SET utf8mb4");

执行 Sequelize.query("SHOW VARIABLES LIKE 'character_set_%'") 后，我得到:

[ [ RowDataPacket { Variable_name: 'character_set_client', Value: 'utf8mb4' },
    RowDataPacket { Variable_name: 'character_set_connection', Value: 'utf8mb4' },
    RowDataPacket { Variable_name: 'character_set_database', Value: 'utf8mb4' },
    RowDataPacket { Variable_name: 'character_set_filesystem', Value: 'binary' },
    RowDataPacket { Variable_name: 'character_set_results', Value: 'utf8mb4' },
    RowDataPacket { Variable_name: 'character_set_server', Value: 'utf8' },
    RowDataPacket { Variable_name: 'character_set_system', Value: 'utf8' },
    RowDataPacket {
      Variable_name: 'character_sets_dir',
      Value: 'c:\\openserver\\modules\\database\\MySQL-5.7-x64\\share\\charsets\\' } ],
  [ RowDataPacket { Variable_name: 'character_set_client', Value: 'utf8mb4' },
    RowDataPacket { Variable_name: 'character_set_connection', Value: 'utf8mb4' },
    RowDataPacket { Variable_name: 'character_set_database', Value: 'utf8mb4' },
    RowDataPacket { Variable_name: 'character_set_filesystem', Value: 'binary' },
    RowDataPacket { Variable_name: 'character_set_results', Value: 'utf8mb4' },
    RowDataPacket { Variable_name: 'character_set_server', Value: 'utf8' },
    RowDataPacket { Variable_name: 'character_set_system', Value: 'utf8' },
    RowDataPacket {
      Variable_name: 'character_sets_dir',
      Value: 'c:\\openserver\\modules\\database\\MySQL-5.7-x64\\share\\charsets\\' } ] ]

但是当我试图保存包含表情符号的字符串时，我遇到了一个错误:

Unhandled rejection SequelizeDatabaseError: ER_TRUNCATED_WRONG_VALUE_FOR_FIELD: Incorrect string value: '\xF0\x9F\x91\x8D\xF0\x9F...' for column 'content' at row 1

Answer 1

我也遇到过这个问题，很容易就解决了，你只需要将你的数据库设置为utf8_general_ci即可。在 node.js 上进行连接时，您需要添加“字符集”参数:

var pool = mysql.createPool({
    host: "host",
    user: "username",
    password: "password",
    database: "database",
    connectionLimit: xxxx,
    charset : 'utf8mb4'
});

这对我有用。