4字节Emoji在MySQL存储异常，已配置utf8mb4，求技术指导

Hey there! Let's walk through your current setup and iron out the kinks to get those emojis storing correctly in MySQL.

What You're Already Doing Right #

First off, your existing steps are solid foundations:

• Setting the column to varchar(3000) CHARACTER SET utf8mb4 NOT NULL is exactly right—MySQL's standard utf8 only supports 3-byte characters, so utf8mb4 is mandatory for 4-byte emojis like U+1F601.
• Running conn.query('set character set utf8mb4') before transactions helps, but we can make this more reliable.

Key Adjustments to Complete the Fix #

Here's what else you need to check and configure:

1. Set Database & Table Default Character Sets
   Don't stop at the column level—make sure your entire database and table use utf8mb4 by default to avoid unexpected character set inheritance issues:

   -- Update existing database
   ALTER DATABASE your_database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   -- Update existing table
   ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   

   Use utf8mb4_unicode_ci for more accurate collation, or utf8mb4_general_ci if you need slightly faster performance.

2. Configure Node.js Database Connection Parameters
   Instead of running set character set per transaction, enforce the character set at the connection level (especially critical if you're using a connection pool, since connections get reused). For mysql or mysql2 modules, add these options to your connection config:

   const connection = mysql.createConnection({
     host: 'your_host',
     user: 'your_user',
     password: 'your_password',
     database: 'your_database',
     charset: 'utf8mb4',
     collation: 'utf8mb4_unicode_ci'
   });
   

   This ensures every connection starts with the correct character set, eliminating the need for per-transaction SET commands.

3. Verify MySQL Version Compatibility
   utf8mb4 is only supported in MySQL 5.5.3 and later. If you're running an older version, you'll need to upgrade first—no amount of configuration will fix this otherwise. Check your version with:

   SELECT VERSION();
   

4. Validate Storage Correctly
   To confirm emojis are stored properly, run this query in MySQL after inserting a record:

   SELECT HEX(your_emoji_column) FROM your_table WHERE id = your_record_id;
   

   For U+1F601 😁, you should see F09F9881 (the utf8mb4 hex encoding). If you get D83DDE01, that means the UTF-16 surrogate pair from JavaScript was stored directly, which indicates your connection character set isn't converting it to utf8mb4 correctly.

A Quick Note on JavaScript's Surrogate Pairs #

Your observation about JavaScript using surrogate pairs (like d83d and de01 for U+1F601) is totally normal—JavaScript uses UTF-16 internally, so 4-byte Unicode characters are split into two 16-bit code units. As long as your MySQL connection is set to utf8mb4, the driver will automatically convert these surrogate pairs to the correct 4-byte utf8mb4 encoding for storage. No extra conversion is needed on the Node.js side!

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