mysql解决用户昵称和输入带有emoji符号写入错误的问题，以及设置所有DB所有TABLE所有COLUMN的charset方法

最新推荐文章于 2023-11-23 16:05:39 发布

望海无疆

最新推荐文章于 2023-11-23 16:05:39 发布

阅读量471

点赞数

CC 4.0 BY-SA版权

文章标签： MySQL emoji charset utf8

本文链接：https://blog.youkuaiyun.com/avatarm/article/details/98852295

本文介绍了在MySQL中遇到emoji符号写入错误的问题，详细讲解了通过终端处理和服务器端处理的解决方案，包括修改column、table、db的charset，以及在各种编程语言中更改连接参数为utf8mb4。对于大量数据库和表的charset批量修改，探讨了使用mysql procedure编程的尝试和挑战。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

往mysql写入用户昵称或输入字段时，常遇到以下类似错误：
Error 1366: Incorrect string value: ‘\xF0\x9F\x8D\x92\xF0\x9F…’ for column ‘nickname’ at row 1
这个问题网上讲的已经很多，因为出现了emoji符号是4字节，当初我们设计数据库的时候通常把charset设置的是utf8，utf8最长到3字节，所以mysql就报错了，2个解决方法：

终端处理：

把带emoji的字段编码成utf8再提交给服务器，以后读到的时候再做一次解码。这个方法我们尝试了，ok可行，但不那么优雅，如果终端有Android，iOS，H5，小程序，各得处理。具体不陈。

服务器端处理：

这是今天重点讲的内容，网上也说的很多，感谢技术er们的分享，但都不完全准确（搞技术不易，都是挤时间写出来分享），我们最终确定：

环境：centos 7.6 + mysql 8.0

改mysql配置文件（非必须，不用改），网上普遍说到改这个，其实不用改。
通过sql改db的charset（非必须，视情况），同上。
通过sql改table的charset（非必须，视情况），同上。
通过sql改column的charset（视情况）。

上述的视情况是什么意思呢？

其实mysql对charset的采用策略就如同变量的作用域，比如创建column时指定了charset，那就采用具体column的而不是table的，这时你就执行步骤4即可，没有指定那就跟着table走，这时你就执行步骤3即可，table跟db之间也是如此。

那么这里明白了，接下来还有一个关键步骤：

改你的server程序端跟mysql连接的参数，把uft8改成utf8mb4

上述这个一定要改，漏了就没效果，你是用java，php，python，go，c++等等，在对应的地方修改。

上面都理解并修改了，一定有效果。但是我们遇到了更麻烦的事：

一个mysql server里面有几十个db（原因不陈），每个库里面十几张table，每个表里面几十个column，创建时把charset指定到了column。

现在我想全改成utf8mb4（强迫症），怎么办？

网上搜了一圈，还是感谢所有分享er（敬礼），提供的思路是有价值的，也因为问题的场景是千差百异的，确实没找到一个准确且优雅的例子。

方法是mysql procedure编程，我们做了以下几个摸索和尝试，并得出最终得到成功运行或不通的结果。

改所有db的charset
没有实现，因为通过编程动态生成的sql语句要通过execute执行，而execute不支持alter schema这个操作。你会得到如下错误：

Error Code: 1295 This command is not supported in the prepared statement protocol yet

还好，改db的charset不是必须的，所以没关系，如有大神有其它思路的解决方法，望不吝提出赐教。

改所有table的charset（语法不陈，语句也很容易理解，注意避开4大系统db）

drop procedure if exists `test`;
delimiter //
create procedure test()
begin
	declare done int default false;
    declare tname1 varchar(500); 
    declare tname cursor for select concat('ALTER TABLE `',TABLE_SCHEMA,'`.`',TABLE_NAME,'` CHARACTER SET = utf8mb4 , COLLATE = utf8mb4_unicode_ci;') from information_schema.tables where TABLE_SCHEMA <> "mysql" and TABLE_SCHEMA <> "information_schema" and TABLE_SCHEMA <> "performance_schema" and TABLE_SCHEMA <> "sys";
    declare continue handler for not found set done = true;
    open tname;
    read_loop: loop
		fetch from tname into tname1;
		if done then
			leave read_loop;
		end if;
        set @sqlStmt = tname1;
		prepare stmt from @sqlStmt;
		execute stmt;
        deallocate prepare stmt;
    end loop;
	close tname;
end;
//
delimiter ;
call test();

改所有column的charset（select语句得到cursor的where过滤根据自己需求调整）

drop procedure if exists `test`;
delimiter //
create procedure test()
begin
	declare done int default false;
    DECLARE cname1 varchar(500); 
    DECLARE cname cursor for select concat('ALTER TABLE `',TABLE_SCHEMA,'`.`',TABLE_NAME,'` CHANGE COLUMN `',COLUMN_NAME,'` `',COLUMN_NAME,'` VARCHAR(2000) CHARACTER SET `utf8mb4` COLLATE `utf8mb4_unicode_ci` NOT NULL DEFAULT \'\';') from information_schema.COLUMNS where TABLE_SCHEMA <> "mysql" and TABLE_SCHEMA <> "information_schema" and TABLE_SCHEMA <> "performance_schema" and TABLE_SCHEMA <> "sys" and TABLE_SCHEMA <> "virtual" and DATA_TYPE = "varchar";
    declare continue handler for not found set done = true;
    open cname;
    read_loop: loop
		fetch from cname into cname1;
		if done then
			leave read_loop;
		end if;
        set @sqlStmt = cname1;
		prepare stmt from @sqlStmt;
		execute stmt;
        deallocate prepare stmt;
    end loop;
	close cname;
end;
//
delimiter ;
call test();

其实步骤2在我们的场景里也不是必须的，步骤3即可，实现目标。