mysql5.0修改字符集_MySQL修改字符集

最新推荐文章于 2024-07-24 17:03:32 发布

weixin_39695323

最新推荐文章于 2024-07-24 17:03:32 发布

阅读量116

点赞数

文章标签： mysql5.0修改字符集

本文链接：https://blog.csdn.net/weixin_39695323/article/details/113227139

版权

MySQL迁移乱码

一、字符集

1.1字符集常识

查看目前使用字符集

mysql> show 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 | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

8 rows in set (0.00sec)

mysql>

查看mysql支持的所有字符集

mysql> show character set;+----------+-----------------------------+---------------------+--------+

+----------+-----------------------------+---------------------+--------+

| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |

| dec8 | DEC West European | dec8_swedish_ci | 1 |

| cp850 | DOS West European | cp850_general_ci | 1 |

| hp8 | HP West European | hp8_english_ci | 1 |

| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |

| tis620 | TIS620 Thai | tis620_thai_ci | 1 |

| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |

| cp1250 | Windows Central European | cp1250_general_ci | 1 |

| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |

| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |

| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |

| cp866 | DOS Russian | cp866_general_ci | 1 |

| cp852 | DOS Central European | cp852_general_ci | 1 |

| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |

| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |

| cp1256 | Windows Arabic | cp1256_general_ci | 1 |

| cp1257 | Windows Baltic | cp1257_general_ci | 1 |

| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |

| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |

| gb18030 | | gb18030_chinese_ci | 4 |

+----------+-----------------------------+---------------------+--------+

41 rows in set (0.00sec)

mysql>

查看数据库表所有字段的状态

mysql> show full columns fromtest01;+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+

+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+

| id | int(10) | NULL | YES | | NULL | | select,insert,update,references | |

+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+

2 rows in set (0.00sec)

mysql>

1.2常见字符集

utf8 --中英文混合环境长度：3(每个汉字三字节)

utf8mb4 --UTF-8 uniconde 长度：4

gbk --不是国际标准长度：2

latin1(默认字符集) 长度：1 [ˈlætɪn]

MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。为了节省空间，一般情况下使用utf8也就够了。

1.4如何选择字符集

如果处理各种各样的文字，发布到不同语言国家地区，应选Unicode字符集，对MySQL来说就是utf8(每个汉字三个字节)

如果只是需要支持中文，并且数据量很大，性能要求也高，可选GBK(定长，每个汉字占双字节，英文也占双字节)，如果是大量运算，比较排序等，定长字符集更快，性能也高

处理移动互联网业务，可能需要使用utf8mb4字符集

二、字符集错乱-数据迁移的过程

2.1解决迁移乱码方案

备份数据库

导出表结构，sed将字符集批量修改为utf8

导出表数据

删除之前的库、表以及数据

将服务器和客户端编码修改为utf8，重启数据库

导入建库和建表语句

导入数据库数据

2.2部分参数说明

--quick:用于转储大的表，强制mysqldump从服务器一次一行的检索数据而不是检索所有行，并输出当前cache到内存中

--no-create-info:不要创建create table语句

--extended-insert:使用包括几个values列表的多行insert语法，这样文件更小，IO也小，导入数据时会非常快

--default-character-set=latin1:按照原有字符集导出数据，这样导出的文件中，所有中文都是可见的，不会保存成乱码

-d表示只导出表结构，不会导出表数据

-B表示将整个库导出，没法精确到表

注意：选择目标字符集时，要注意最好大于等于原字符集(子库更大)，否则可能会丢失不被支持的数据

三、生产案例：修正字符集

3.1创建模拟数据

前期准备：安装数据库，建库建表

[root@localhost ~]# mysql -uroot -p123456

mysql> create databasetest01;

mysql> show create databasetest01;

mysql> usetest01;

mysql> create table test01( id int(10), city varchar(20) );

3.1.1模拟生产错误案例：

生产系统已经在运行，对于系统注册页面，一开始注册信息为拼音字母，新功能上线后，可以使用汉字，但是在使用过程中却因为报错无法进行。

插入英文字符(显示成功)：

mysql> insert into test01 values (1,'gz'), (2,'nn'), (3,'sh'), (4,'bj');

插入中文字符(报错HY000)：

mysql> insert into test01 values (1,'广州'), (2,'南宁'), (3,'上海'), (4,'北京');

ERROR1366(HY000):

mysql> select * fromtest01;+------+------+

| id | city |

+------+------+

| 1 | gz |

| 2 | nn |

| 3 | sh |

| 4 | bj |

+------+------+

3.1.2分析错因

报错原因：因为MySQL默认字符集为latin1，不支持中文编码，所以此时需要进行转换字符集

3.1.2创建对比表：

mysql> create table test02( id int(10), city varchar(20) )engine=innodb default charset=utf8;

mysql> insert into test02 values (1,'广州'), (2,'南宁'), (3,'上海'), (4,'北京');

mysql> select * fromtest02;+------+--------+

| id | city |

+------+--------+

| 1 | 广州 |

| 2 | 南宁 |

| 3 | 上海 |

| 4 | 北京 |

+------+--------+

4 rows in set (0.00 sec)

3.2解决乱码

迁移工具：mysqldump

语法格式(八大部分组成)：

mysqldump -h localhost -uroot -p123456 -[option] db_name table_name >backup.sql

注意：如果不加上table_name会默认将整个库的表导出来，以下实验是针对库进行，所以涉及的是整个库(所有的表)

3.2.1备份数据

(备份当前准备修改的库，全库备份)--最重要的一步！！！

[root@localhost ~]# mysqldump -uroot -p123456 test01 >test01_backup0224.sql

3.2.2导出表结构，并修正字符集

-d表示只导出表结构，不会导出表数据，常用

[root@localhost ~]# mysqldump -uroot -p123456 -hlocalhost --default-character-set=latin1 -d test01 >test01_table.sql

[root@localhost~]# sed -i 's/latin1/utf8/g' test01_table.sql

3.2.3导出表数据

数据库不在更新的情况下

[root@localhost ~]# mysqldump -uroot -p123456 -hlocalhost --quick --no-create-info --extended-insert --default-character-set=latin1 test01 >test01_data.sql

补充：同时导出表结构和表数据：

-B表示将整个库导出，没法精确到表

mysqldump -uroot -p123456 -hlocalhost -B test01>xxx.sql

如果不加-B选项也是默认导出表结构和数据，但是不加选项可以选择到表

mysqldump -h localhost -uroot -p123456 test01 test01 >xxx.sql

3.2.4修改mysql服务和client的字符集

[root@localhost ~]# vim /etc/my.cnf

在[mysqld]下加入代码：character_set_server=utf8

在[client]下加入代码：default-character-set=UTF8

[root@localhost~]# systemctl restart mysqld

3.2.5删除原数据库

[root@localhost ~]# mysql -uroot -p123456

mysql> drop database test01;

3.2.6建库、导入表结构和表数据

依下顺序

a.建库：

mysql> create database test01; --已经设置默认为utf-8

或是：

mysql> create database test01 default character set=utf8;

或是：

mysql> create database test01 default charset=utf8;

mysql> show create databasetest01;+----------+-----------------------------------------------------------------+

| Database | Create Database |

+----------+-----------------------------------------------------------------+

| test01 | CREATE DATABASE `test01` /*!40100 DEFAULT CHARACTER SET utf8*/ |

+----------+-----------------------------------------------------------------+

mysql> exit

b.导入表结构：

[root@localhost ~]# mysql -uroot -p123456 test01

c.导入表数据：

[root@localhost ~]# mysql -uroot -p123456 test01

3.3登录验证test02

3.3.1test02乱码

mysql> select * fromtest02;+------+------+

| id | city |

+------+------+

| 1 | ?? |

| 2 | ?? |

| 3 | ?? |

| 4 | ?? |

+------+------+

反思：为什么test02反而乱码了呢？？？

3.3.2分析test02乱码

插入中文试验：

mysql> mysql> insert into test02 values (1,'广州'), (2,'南宁'), (3,'上海'), (4,'北京');

mysql> select * fromtest02;+------+--------+

| id | city |

+------+--------+

| 1 | ?? |

| 2 | ?? |

| 3 | ?? |

| 4 | ?? |

| 1 | 广州 |

| 2 | 南宁 |

| 3 | 上海 |

| 4 | 北京 |

+------+--------+

3.3.3test02乱码原因

在导出数据的时候指定了字符集为Latin1，而test02本身是utf-8的所以导出后再导入就乱码了。

检查导出文本：

小结：所以综合考虑，一开始进行全库备份是非常有必要的。另外，防止出现这种情况，在导出数据的时候可以单表导出(只导出乱码的表)，单表修改，尽量不做全库导出。

3.4登录验证test01

3.4.1在test01插入中文验证

mysql> insert into test01 values (1,'广州'), (2,'南宁'), (3,'上海'), (4,'北京'); Query OK, 4 rows affected (0.00sec)

mysql> select * fromtest01;+------+--------+

| id | city |

+------+--------+

| 1 | gz |

| 2 | nn |

| 3 | sh |

| 4 | bj |

| 1 | 广州 |

| 2 | 南宁 |

| 3 | 上海 |

| 4 | 北京 |

+------+--------+

综上，test01已经正常显示中文，至此，MySQL迁移中文乱码问题得以解决

原创帖，转载需注明出处

weixin_39695323

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫