客户端工具 Navicat Premium_11.2.7简体中文完美破解版(32位和64位) 支持mb4
修改MySQL配置文件
修改mysql配置文件my.cnf（windows为my.ini）
my.cnf一般在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’
重启数据库，检查变量
SHOW VARIABLES WHERE Variable_name LIKE ‘character_set_%’ OR Variable_name LIKE ‘collation%’;
String query = “set names utf8mb4”;
stat.execute(query);
mysql> INSERT INTO messages (message) VALUES ('What a nice emoji!');
http://www.oicqzone.com/tool/emoji/
QQ地带Emoji表情代码大全
下载这个插件Chromoji
实际批量操作
db改字符集
#!/bin/bash
if [ -f /root/.bash_profile ];then
source /root/.bash_profile
fi
MysqlUser=root
PassWord='xx'
Port=3306
Socket="/opt/$Port/mysql.sock"
MysqlCmd="mysql -u$MysqlUser -p$PassWord -S $Socket"
Database=`$MysqlCmd -e "show databases;"|egrep -v "Database|_schema|mysql"`
for dbname in $Database
do
$MysqlCmd -e 'ALTER DATABASE '$dbname' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci';
done
table改字符集
#!/bin/bash
if [ -f /root/.bash_profile ];then
source /root/.bash_profile
fi
MysqlUser=root
PassWord='xx'
Port=3306
Socket="/opt/$Port/mysql.sock"
MysqlCmd="mysql -u$MysqlUser -p$PassWord -S $Socket"
for database in `$MysqlCmd -e "show databases;"|sed '1,2d'|egrep -v "mysql|schema|sys"`
do
for table in `$MysqlCmd -e "show tables from $database;"|sed '1d'`
do
$MysqlCmd $database -e 'ALTER TABLE '$table' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
done
done
或则
参考https://my.oschina.net/xuqiang/blog/507629 批量修改mysql表、表、数据库的字符校对规则
查询是否修改
SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET UTF8 COLLATE utf8_unicode_ci', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')FROM information_schema.COLUMNSWHERE TABLE_SCHEMA = 'database'AND DATA_TYPE = 'varchar'AND(
CHARACTER_SET_NAME != 'utf8'
OR
COLLATION_NAME != 'utf8_unicode_ci');
新增
mysql 默认是autocommit=1 的，
我们线上都设置一般是init_connect='set autocommit=0; set names gbk;' 这样的
init_connect='SET NAMES utf8mb4'
skip-character-set-client-handshake = true
