Laravel中解决utf8mb3转utf8mb4排序规则转换错误方案

问题详情 #

表结构 #

1   id  bigint(20) unsigned NULL    NULL    NO  NULL    auto_increment      
2   viewed  tinyint(1)  NULL    NULL    NO  1           
3   type    varchar(191)    utf8    utf8_unicode_ci YES NULL            
4   url longtext    utf8    utf8_unicode_ci YES NULL            
5   p_id    varchar(191)    utf8    utf8_unicode_ci NO  NULL            
6   client_id   varchar(191)    utf8    utf8_unicode_ci NO  NULL        clients(id) 
7   created_at  timestamp   NULL    NULL    YES NULL            
8   updated_at  timestamp   NULL    NULL    YES NULL            

插入数据代码 #

// 向浏览日志表添加记录
DB::table('client_p_views_log_30days')->insert([
    'client_id'     => $client_id,
    'p_id'          => $p_id,
    'type'          => $type,
    'url'           => $landing_page,
    'created_at'    => now()->toDateTimeString(),
    'updated_at'    => now()->toDateTimeString()
]);

数据库配置（Laravel database.php） #

'charset' => env('DB_CHARSET', 'utf8mb4'),
'collation' => env('DB_COLLATION', 'utf8mb4_unicode_ci'),

报错信息 #

[2024-02-07 12:07:12] production.ERROR: PoptinAPI->newViewsCount()->Exception {
    "client_id": "3dae67a03936e",
    "p_id": "5486bde1f0d03",
    "Exception": "SQLSTATE[HY000]: General error: 3988 Conversion from collation utf8mb3_unicode_ci into utf8mb4_unicode_ci impossible for parameter (SQL: insert into `client_p_views_log_30days` (`client_id`, `p_id`, `type`, `url`, `created_at`, `updated_at`) values (hfafafhah, agadfsgag, desktop, https://test.com/💉-testfoo/, 2024-02-07 12:07:12, 2024-02-07 12:07:12))"
} []

已尝试的无效方法 #

使用mb_convert_encoding转换URL编码：

'url' => mb_convert_encoding($landing_page, 'UTF-8', 'HTML-ENTITIES'),

结果emoji被破坏为https://test.com/ð-testfoo/，无法还原。

解决步骤 #

1. 升级表和列的字符集到utf8mb4 #

MySQL中的utf8是utf8mb3的别名，仅支持3字节字符，无法存储4字节的emoji。必须把表和相关列的字符集改为utf8mb4，排序规则匹配为utf8mb4_unicode_ci：

执行以下SQL：

-- 整体转换表的字符集和排序规则（优先使用这个）
ALTER TABLE client_p_views_log_30days CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 如果上述语句未完全生效，单独修改各列
ALTER TABLE client_p_views_log_30days MODIFY type varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE client_p_views_log_30days MODIFY url longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE client_p_views_log_30days MODIFY p_id varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE client_p_views_log_30days MODIFY client_id varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. 确认数据库连接配置一致性 #

检查.env文件中的DB_CHARSET和DB_COLLATION是否设置为：

DB_CHARSET=utf8mb4
DB_COLLATION=utf8mb4_unicode_ci

确保Laravel连接数据库时使用的字符集与表/列一致，避免转换冲突。

3. 移除错误的编码转换代码 #

直接使用原始的$landing_page值插入即可，无需额外编码转换，字符集配置正确后emoji会被正常存储。

4. 验证MySQL全局字符集设置（可选） #

检查MySQL实例的全局字符集配置，避免库级别的字符集不匹配：

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

确保character_set_database和collation_database的值为utf8mb4和utf8mb4_unicode_ci。

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