最近用datax做两个数据库之间的数据同步,由于两个数据库位于两台服务器上,一个数据库是生产库,一个数据库是本地库.本地库编码格式设置为utf8mb4,但做同步时微信名还是出现了乱码
Incorrect string value: '\xF0\x9F\x98\x82\xE6\x81...' for column 'nick_name' at row 1
解决办法:
修改本地服务器MySQL配置文件/etc/mysql/my.cnf
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
character-set-client-handshake=FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
重启数据库
/etc/init.d/mysql restart
查看数据库编码格式
SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
出现以下信息说明修改成功
1
+--------------------------+--------------------+
| Variable_name | Value |
+--------------------------+--------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| collation_connection | utf8mb4_unicode_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+--------------------------+--------------------+
rows in set (0.00 sec)
还有一个坑,如果后面不加编码格式的话,数据库还是会乱码
"jdbcUrl":"jdbc:mysql://***.***.***.**:3306/ddepps?useUnicode=true&characterEncoding=utf8"
完成!