更改表的字符集（utf8 to utf8mb4）

最新推荐文章于 2025-02-12 16:52:36 发布
最新推荐文章于 2025-02-12 16:52:36 发布
阅读量9.9k 收藏 8
点赞数 3
分类专栏： MySQL 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wl21787/article/details/107090351
版权

目录

环境要求：

测试数据：

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

将表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,"🌷");

总结：

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

 

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

 

mysql修改表中所有字段字符集为utf8
Hello Word!
08-06 2万+
参考：https://blog.csdn.net/LUNG108/article/details/78285054 .一次性修改表中所有字段的字符集语句：alter table `表名` convert to character set utf8;
修改mysql数据库字符集为UTF8的
Mr. 亮先生的博客
08-26 7223
第一种 一、修改my.ini配置文件（MySQL配置文件） character_set_server = utf8 #设置字符集 重启mysql数据库服务 查看当前数据库字符集 show VARIABLES like 'character%'; 二、修改数据库字符集 alter database 数据库名 character s
mysql修改表、字段、库的字符集
morley_wang的专栏
07-03 1384
修改数据库字符集： ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...]; 把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集： ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE
MySQL表设计之字符集和校对集
最新发布
2403_84646142的博客
02-12 851
计算机采用二进制方式保存数据，用户输入的字符会被计算机按照一定规则转换为二进制保存，这个转换过程称为字符编码。将一系列字符的编码规则组合起来就形成了字符集。MySQL中的字符集规定了字符在数据库中的存储格式，不同的字符集有不同的编码规则。常用的字符集有GBK和UTF-8，UTF-8支持世界上大多数国家的语言文字，通用性较强，适用于大多数场合；而如果只需要支持简体中文、繁体中文、日文和韩文，不考虑其他语言，为了节省空间，可以采用GBK。
更改表的字符集
漫
01-08 434
alter table user_invite           convert to character set utf8mb4 collate utf8mb4_general_ci;
修改表的字符集
weixin_42602241的博客
01-10 1052
要修改 MySQL 表的字符集，可以使用以下 SQL 语句： ALTER TABLE 表名称 CONVERT TO CHARACTER SET 字符集名称; 例如，要将 my_table 表的字符集修改为 utf8mb4，可以使用以下语句： ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4; 请注意，修改字符集可能会改变表中的数据，因此...
MySQL字符集utf8修改为utf8mb4的方法步骤
09-09
如果你的数据库需要存储如 Emoji 这样的4字节字符，就必须将字符集从`utf8`升级到`utf8mb4`。`utf8mb4`是`utf8`的超集，意味着所有有效的`utf8`字符在`utf8mb4`中也是合法的，但反之不成立。 转换过程涉及多个步骤...
详解JDBC对Mysql utf8mb4字符集的处理
08-26
详解JDBC对Mysql utf8mb4字符集的处理 JDBC（Java Database Connectivity）是Java语言中用来连接数据库的API，而Mysql是目前最流行的开源关系数据库管理系统之一。在Java应用程序中，使用JDBC连接Mysql数据库是非常...
GBase 8a 支持调整表的字符集从UTF8到UTF8MB4
weixin_44961019的博客
04-25 768
新增列中char,varchar,text,lonegtext类型字符集为utf8mb4。原表字符集为utf8/utf8mb4，目标字符集为utf8mb4。原表中的char、varchar类型字符集转为utf8mb4。不支持修改列字符集，执行成功但不生效。
修改数据库和表的字符集
qq_25096749的博客
11-13 866
mysql修改字符集
字符集从 utf8 转换成 utf8mb4
SRE运维 网络 系统 安全
07-26 503
1、修改数据库配置文件/etc/my.cnf character-set-server = utf8mb4 collation-server = utf8mb4_general_ci 2、修改库 alter database 库名 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; 3、修改表 alter table 表名 convert to character set utf8mb4 COLLATE utf8mb4_general_ci;
mysql 更改表字符集_MySQL 修改表字符集
weixin_42282482的博客
01-18 2055
MySQL 修改表字符集如果在应用开始阶段没有正确的设置字符集，运行一段时间后发现不能满足要求，想要调整字符集但又不想丢弃原有的数据，怎么办呢？现我们修改 test数据库下的 t1表，把 t1表原有latin1字符集的数据表修改成gbk字符集的过程:1.导出test用户的表结构[root@testdb ~]# mysqldump -uroot -p --default-character-set=...
UTF8字符集的表怎么直接转UTF8MB4？
老叶茶馆
07-05 3938
导读 我的小密圈里有读者提到一个线上库是utf8字符集，想问怎样将其转成utf8mb4 问题 我的小密圈“「老叶茶馆」铁粉圈”上有读者提问：金** 提问：叶老师，有一个MySQL库是utf8的，不大，4g多，怎样转成utf8mb4.可以有一定的停机时间。 解读 utf8是utf8mb4的子集，一般情况下，应该是可以直接修改表字符集的。 修改字符集的几种方法 方法一
MYSQL中修改表的字符集
Yzx02155611的博客
03-16 2243
MySql中修改表的字符集
修改MySQL的字符集为utf8mb4
天才在左，疯子在右，而我只想做个不折不扣的乐天派
01-25 2620
修改MySQL的字符集为utf8mb4
mysql数据库修改表字符集_MySQL之对数据库库表的字符集的更改
weixin_31165655的博客
01-27 2828
数据字符集修改步骤：对于已有的数据库想修改字符集不能直接通过 "alter database character set *"或 "alter table tablename character set *"，这两个命令都没有更新已有记录的字符集，而只是对新创建的表或者记录生效。 已经有记录的字符集的调整，必须先将数据导出，经过修改字符集后重新导入后才可完成。 修改数据库默认编码： alter d...
MySQL 修改表的字符集和数据库的字符集，让其支持中文
刘大辉的博客
03-19 1195
`utf8mb4_unicode_ci` 是大小写不敏感的校对规则，可以很好地处理Unicode字符的排序和比较。- 如果出现以上更改后仍然无法正确存储或显示中文，检查应用程序代码中的数据库连接配置，确保在连接时指定了正确的字符集，比如在PHP的PDO连接字符串中加入`charset=utf8mb4`。- `utf8mb4` 是 `utf8` 的超集，支持更多的字符，包括emoji。- 修改字符集可能会影响表和索引的大小，因为不同的字符集编码方式可能有不同的存储要求。
tomcat数据库字符集utf8mb4
12-29
### 配置 Tomcat 以支持 MySQL 使用 UTF8MB4 字符集 为了使 Tomcat 连接池能够正确处理并传递 UTF8MB4 编码的数据给 MySQL 数据库，需确保三个层面都进行了适当设置： #### 修改 MySQL 的配置文件 MySQL服务器端需要调整其默认字符编码为UTF8MB4。这可以通过修改my.cnf (Linux) 或 my.ini (Windows) 文件来实现。 ```ini [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 ``` 上述配置使得客户端连接时不依赖于客户端发送过来的字符集握手信息而强制使用指定的全局字符集[^1]。 #### 创建数据库时指明字符集 当创建新的数据库实例时也应显式声明使用的字符集和校对规则: ```sql CREATE SCHEMA `test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ; ``` 此命令确保新建立的schema采用utf8mb4作为默认字符集以及对应的排序方式[^2]。 #### 设置 JDBC URL 参数 对于Tomcat应用来说，在定义数据源的时候要通过JDBC URL中的参数告知驱动程序期望接收什么样的字符编码格式。具体做法是在context.xml或其他合适位置中加入如下形式的URL字符串： ```xml <Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource" ... url="jdbc:mysql://localhost:3306/test?autoReconnect=true&useUnicode=yes&characterEncoding=UTF-8"/> ``` 这里特别注意的是`useUnicode=yes` 和 `characterEncoding=UTF-8`这两个参数用来指示JDBC驱动器应该怎样对待来自Java应用程序的数据流[^3]。 需要注意一点关于版本兼容性的说明：早期某些版本可能存在不完全支持的情况，但从官方文档来看，现代版MySQL Connector/J 已经全面支持UTF8MB4字符集[^4]。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值