mysql 修改字符集
【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)
【2】其他方法
以下部分转自: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了
【4】最佳实践(UTF8转UTF8MB4)
(4.1)对现有表数据也转换
1> rename table 表 to 表_utf8,表utf8mb4 to 表; -- 新建同表结构utf8mb4表,然后切换。
2> mysqldump --default-character-set=utf8mb4 表_utf8 >dump.sql 出来,再mysql --defaults-character-set=utf8mb4 表< dump.sql
(4.2)对表进行字符集修改,但只影响新插入数据
alter table store_warehouse convert to character set utf8;