php mysql emoji相关问题

服务器环境

centos、php、mysql

mysql数据库默认使用utf8字符集，客户端修改名称时，可以从输入法中输入emoji表情符号，或者?，?等符号，php执行sql语句时并没有报错，但是客户端再拉取数据的时候，名称变成空的了。

php中配置的mysql连接采用的charset是utf8，

$mysqli_conn = mysqli_connect($ip,$cfg["user"],$cfg["pswd"],$cfg["db_name"]);

$mysqli_conn->set_charset($cfg['charset']);

方案一：charset改成utf8mb4，php执行的sql语句会成功，但是emoji表情符号会变成问号(?),

直接修改charset是最简单的，不会影响正常字符的存储和显示，也不至于出错。

 

方案二：依然要修改php的mysql连接charset为utf8mb4，然后修改sql表中的字符集

#修改db_dev数据库
ALTER DATABASE db_dev CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

#修改tb_users表
ALTER TABLE tb_users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

#修改tb_users表的user_name字段
ALTER TABLE tb_users CHANGE user_name user_name VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
不写collate utf8mb4_unicode_ci也可以

修改表的字符集，表中字段的字符集默认会全部更新

 

 

批量修改表的字符集

select concat('alter table ', table_name, ' convert to character set utf8mb4 collate utf8mb4_unicode_ci;') from information_schema.`TABLES` where TABLE_SCHEMA = '你想要修改的数据库的名字' and table_name like '%tb_%' into outfile '/data/alter.sql';

select concat('alter table ', table_name, ' convert to character set utf8mb4 collate utf8mb4_unicode_ci;') from information_schema.`TABLES` where TABLE_SCHEMA = '你想要修改的数据库的名字' and table_name like '%sc_%' into outfile '/data/alter.sql';

主要思路是

select table_name from information_schema.TABLES where TABLE_SCHEMA = '你想要修改的数据库的名字' and table_name like '%sc_%'; 

获取所有的表名

然后concat拼接字符串，拼接成sql语句，

然后into out file '/data/alter.sql'；导出需要批量执行字符串到文件

然后再批量执行即可

mysql -uroot -p 你想要修改的数据库的名字  < /data/alter.sql

 

遇到的报错

Specified key was too long; max key length is 767 bytes

这是因为指定了一个组合unique key，其中wx_union_id设置的类型是varchar(255),

而mysql默认情况下单个列的索引不能超过767位，也就是256*3 - 1，所以要单独执行alter命令修改该字段的类型和长度，改为100也够够的了，

 

小贴士：

测试过程中，字符集还是utf8的时候，单独在mysql客户端执行包含emoji的表情符号的sql语句，居然成功了，并且能从mysql查询结果中看到，但是php中获取到结果执行json_encode的时候就死了。

修改php和mysql字符集后，可以正常获取并json_encode不崩溃了