mysql 修改字符集 mysql 修改字符集【2】其他方法

时间：2024-03-08 22:27:28

【1】较好的常规办法

　　

2中导出来的mysql数据文件，用notepad++等编辑器 把文件转码为UTF8格式

【1.1】创建制定字符集的数据库

需求描述:

　　在创建DB的时候指定字符集.

操作过程:

【1.2】使用create database语句创建数据库

mysql> create database if not exists test03 default character set = \'utf8\';
Query OK, 1 row affected (0.00 sec)

备注:通过default character set = 语句来指定DB的字符集.

【1.3】查看创建的DB的字符集

show create database test;

【2】 select schema_name,default_character_set_name from information_schema.schemata where schema_name = \'test03\';
+-------------+----------------------------+
| schema_name | default_character_set_name |
+-------------+----------------------------+
| test03      | utf8                       |
+-------------+----------------------------+
1 row in set (0.00 sec)

以下部分转自：https://www.cnblogs.com/sxwen/p/7851031.html

注意，这种办法非常不推荐使用，太麻烦了；且只适用于新建的，没有数据存在的；

但也不是没有好处，处理的好，是可以不用停机的；比如：

　　（1）修改，库、表、列 等属性；

　　（2）改好之后，把数据mysqldump导出（只导出数据哦）

　　（3）然后把 导出的数据文件，通过notepadd之类的编辑器 转码成utf8

　　（4）最后修改一下数据文件中的 insert语句，以replaces的方式替换唯一ID然后插入

　　

【2.1】修改my.ini配置文件（mysql配置文件）

添加

[mysql]
default-character-set=utf8
[mysqld]
character_set_server=utf8 #设置字符集

重启mysql数据库服务

查看当前数据库字符集

show VARIABLES like \'character%\';

 

　　

【2.2】修改数据库字符集

alter database 数据库名 character set utf8;

ps:修改完数据库字符集，需要重启mysql数据库。

注意，这个修改只能对新表有用，修改之前已经存在的无用；

 

【2.3】修改表字符集

ALTER TABLE  表名 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

 注意，这个修改只能对该表的新行有用，修改之前已经存在行无用；

生成所有表修改字符集语句：

SELECT TABLE_NAME,CONCAT(\'ALTER TABLE  \',TABLE_NAME,\' DEFAULT CHARACTER SET \',a.DEFAULT_CHARACTER_SET_NAME,\' COLLATE \',a.DEFAULT_COLLATION_NAME,\';\') executeSQL 
FROM information_schema.SCHEMATA a,information_schema.TABLES b
WHERE a.SCHEMA_NAME=b.TABLE_SCHEMA
AND a.DEFAULT_COLLATION_NAME!=b.TABLE_COLLATION
AND b.TABLE_SCHEMA=\'数据库名\'

 

【2.4】修改列字符集

ALTER TABLE  表名 CHANGE  列名  列名  VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

生成修改所有列的SQL语句：

select b.table_name,b.column_name,b.character_set_name,b.collation_name
,CONCAT(\'ALTER TABLE \',b.table_name,\' MODIFY \',b.column_name,\' \',
 b.DATA_TYPE,\'(\',b.CHARACTER_MAXIMUM_LENGTH,\') \',
 CASE WHEN b.COLUMN_DEFAULT IS NULL THEN \'\'  ELSE CONCAT(\'DEFAULT \\'\',b.COLUMN_DEFAULT,\'\\'\') END,\' COMMENT \\'\',b.COLUMN_COMMENT,\'\\';\') executeSQL
from information_schema.TABLES a,information_schema.COLUMNS b where  b.character_set_name IS NOT NULL and a.TABLE_SCHEMA=b.TABLE_SCHEMA AND a.TABLE_NAME=b.TABLE_NAME
AND a.TABLE_COLLATION!=b.COLLATION_NAME
and a.TABLE_SCHEMA=\'数据库名\'

【2.5】修改客户端、服务器、连接、数据库等字符集

show variables like \'%char%\';

　　

 set names utf8;

 　　

 

 

【3】我的最佳实践（latin1转utf8）

【3.1】查看服务器字符集

character_set_server = utf8 #设置字符集

　　

 

假设服务器字符集已经是 utf8 或者 utf8mb4；这样我们就不用改my.cnf了；

如果不是，需要修改 my.cnf ，具体操作见【2.1】

【3.2】以latin1导出数据文件

假设我现在的 config 库，很多表是 latin1的字符集，有很多字段也是；

mysqldump --set-gtid-purged=OFF --default-character-set=latin1 config >config_20210225.sql

【3.3】导出的数据文件，用sed 替换 latin1 为 utf8

这一步一定要精细，用文档好好看看

sed -i \'s#latin1#utf8mb4#g\' config_20210225.sql

怕出问题的话，可以多个组合字符去替换，避免替换到正常业务数据，如下

sed -i \'s#CHARACTER SET latin1#CHARACTER SET utf8mb4#g\' config_20210225.sql   #this is a test

【3.4】导出的文件，用notepad之类的文本编辑工具 转换成 utf8 格式

　　

 

【3.5】然后使用转换后的文件，再覆盖需要修改、转换字符集的库

mysql config < config_20210225.sql

 

【3.6】核验

（1）客户端终端字符集设置 utf8

　　　　比如用的CRT

　　　　　　

　　如果用的是navicat，那就自动就可以了

　　　　　　

 

 

（2）set names utf8;

　　

 

 如果发现客户端还是 latin1

　　

 

 set names utf8; 之后，在查询，OK了

　　

 

 

 

 

相关文章