目录

环境要求：

测试数据：

查看当前数据库的字符集：

将表t2的字符集由utf8更改utf8mb4:

方法一：alter table t2 default character set utf8mb4;

方法二：alter table t2 convert to character set utf8mb4;

mysqldump备份的情况：

mydumper的备份情况：

总结：

---

环境要求：

MySQL: 5.6.23字符集：utf8操作系统：centos6

测试数据：

# 测试表

CREATE TABLE `t2` ( `id` int(11) NOT NULL, `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

# 测试数据

insert into t2 values(1,'张三'),(2,'李四');

查看当前数据库的字符集：

(root@g1-db-test-v01:5623)[test]>show global variables like 'character%'; +--------------------------+---------------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /data/mysql/mha_mysql/share/charsets/ | +--------------------------+---------------------------------------+ 8 rows in set (0.00 sec)

查看当前数据库是否支持utf8mb4:

(root@g1-db-test-v01:5623)[test]>show character set like '%utf8%'; +---------+---------------+--------------------+--------+ | Charset | Description | Default collation | Maxlen | +---------+---------------+--------------------+--------+ | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | +---------+---------------+--------------------+--------+ 2 rows in set (0.00 sec)

将表t2的字符集由utf8更改utf8mb4:

方法一：alter table t2 default character set utf8mb4;

(root@g1-db-test-v01:5623)[test]>alter table t2 default character set utf8mb4; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 (root@g1-db-test-v01:5623)[test]>show create table t2 \G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `id` int(11) NOT NULL, `name` varchar(20) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '姓名', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec)

注意：

使用此方法后虽然表的字符集被更改为utf8mb4,但是字符串类型列·name·的字符集居然还是utf8。

方法二：alter table t2 convert to character set utf8mb4;

(root@g1-db-test-v01:5623)[test]>alter table t2 convert to character set utf8mb4; Query OK, 2 rows affected (0.06 sec) Records: 2 Duplicates: 0 Warnings: 0 (root@g1-db-test-v01:5623)[test]>show create table t2 \G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `id` int(11) NOT NULL, `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec)

因此，在将表的字符集由utf8更改为utf8mb4时应该使用方法二。

mysqldump备份的情况：

插入测试数据：

# 插入测试数据

(root@g1-db-test-v01:5623)[test]>select * from t2; +----+--------+ | id | name | +----+--------+ | 1 | 张三 | | 2 | 李四 | +----+--------+ 2 rows in set (0.00 sec) (root@g1-db-test-v01:5623)[test]>set names utf8mb4; Query OK, 0 rows affected (0.00 sec) (root@g1-db-test-v01:5623)[test]>insert into t2 values(3,'\U+1F337'); Query OK, 1 row affected (0.00 sec) (root@g1-db-test-v01:5623)[test]>select * from t2; +----+--------+ | id | name | +----+--------+ | 1 | 张三 | | 2 | 李四 | | 3 | 🌷 | +----+--------+ 3 rows in set (0.00 sec)

mysqldump备份脚本：

--default-character-set 参数默认值为utf8

mysqldump \ --host=127.0.0.1 --user=root -p --port=5623 \ --master-data=2 --single-transaction test t2 >/data/mysql/tmp/t2.sql

查看备份文件内容：vim /data/mysql/tmp/t2.sql

-- MySQL dump 10.13 Distrib 5.6.42-84.2, for Linux (x86_64) -- -- Host: 127.0.0.1 Database: test -- ------------------------------------------------------ -- Server version 5.6.23-72.1-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mha-mysql-bin.000039', MASTER_LOG_POS=13625726; -- -- Table structure for table `t2` -- DROP TABLE IF EXISTS `t2`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `t2` ( `id` int(11) NOT NULL, `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `t2` -- LOCK TABLES `t2` WRITE; /*!40000 ALTER TABLE `t2` DISABLE KEYS */; INSERT INTO `t2` VALUES (1,'张三'),(2,'李四'),(3,'?'); /*!40000 ALTER TABLE `t2` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2020-07-02 14:28:26

注意看id=3的记录name值为乱码。

 

接着使用--default-character-set=utf8mb4进行备份

mysqldump \ --host=127.0.0.1 --user=root -p --port=5623 \ --default-character-set=utf8mb4 \ --master-data=2 --single-transaction test t2 >/data/mysql/tmp/t2.sql

查看备份文件：

 

此时备份文件没有显示乱码。

-- MySQL dump 10.13 Distrib 5.6.42-84.2, for Linux (x86_64) -- -- Host: 127.0.0.1 Database: test -- ------------------------------------------------------ -- Server version 5.6.23-72.1-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mha-mysql-bin.000039', MASTER_LOG_POS=13625726; -- -- Table structure for table `t2` -- DROP TABLE IF EXISTS `t2`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `t2` ( `id` int(11) NOT NULL, `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `t2` -- LOCK TABLES `t2` WRITE; /*!40000 ALTER TABLE `t2` DISABLE KEYS */; INSERT INTO `t2` VALUES (1,'张三'),(2,'李四'),(3,'🌷'); /*!40000 ALTER TABLE `t2` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2020-07-02 14:31:13

mydumper的备份情况：

备份脚本如下：

mydumper \ --host=10.16.81.101 --user=dba --password=doumi1.q --port=5623 \ --database=test -T t2 -o /data/mysql/tmp/test

查看备份文件内容：

cat test.t2.sql /*!40101 SET NAMES binary*/; /*!40014 SET FOREIGN_KEY_CHECKS=0*/; /*!40103 SET TIME_ZONE='+00:00' */; INSERT INTO `t2` VALUES (1,"张三"), (2,"李四"), (3,"🌷");

总结：

更改表的字符集需要使用convert to character set utf8mb4命令；备份字符集utf8mb4的表如果使用mysqldump 需要指定参数--default-character-set=utf8mb4；如果字符集为utf8mb4，那么客户端连接需要使用set names utf8mb4；

 

参考文章：https://dev.mysql.com/doc/refman/5.6/en/charset-unicode-conversion.html