Mysql DBA 高級(jí)運(yùn)維學(xué)習(xí)之路-mysql數(shù)據(jù)庫亂碼問題

1.在mysql數(shù)據(jù)庫中插入數(shù)據(jù)不亂碼的方法

1.1 建立測試數(shù)據(jù)

(1)創(chuàng)建linzhongniao測試數(shù)據(jù)庫并查看建表語句

mysql> create database linzhongniao;
Query OK, 1 row affected (0.00 sec)
mysql> show create database linzhongniao\G
 *************************** 1. row ***************************
   Database: linzhongniao
Create Database: CREATE DATABASE `linzhongniao` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)

提示：如果在安裝MySQL時(shí)未指定字符集或者指定的是latin字符集，則mysql默認(rèn)字符集是latin1。

(2)在linzhongniao庫下創(chuàng)建一個(gè)student表，并查看表結(jié)構(gòu)和建表語句

mysql> use linzhongniao
Database changed
mysql> show tables;
+--------------------+
| Tables_in_linzhongniao |
+--------------------+
| student|
+--------------------+
1 row in set (0.00 sec)

mysql> create table student( id int(4) NOT NULL AUTO_INCREMENT, name char(20) NOT NULL, PRIMARY KEY(id) );
mysql> desc student;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra  |
+-------+----------+------+-----+---------+----------------+
| id| int(4)   | NO   | PRI | NULL| auto_increment |
| name  | char(20) | NO   | | NULL||
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> show create table student\G
*************************** 1. row ***************************
   Table: student
Create Table: CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

提示：默認(rèn)建表不指定字符集則繼承庫的字符集，即latin1。

(3)批量插入數(shù)據(jù)到student表

mysql> insert into student values(1,'zhangsan'),(2,'lisi'),(3,'xiaozhang'),(4,'xiaohong');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+-----------+
| id | name  |
+----+-----------+
|  1 | zhangsan  |
|  2 | lisi  |
|  3 | xiaozhang |
|  4 | xiaohong  |
+----+-----------+
4 rows in set (0.00 sec)

提示：數(shù)字和英文的數(shù)據(jù)時(shí)正常的，不會(huì)有亂碼問題。

(4)插入兩條中文數(shù)據(jù)

mysql> insert into student values(5,'我是誰');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into student values(6,'你好啊');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+-----------+
| id | name  |
+----+-----------+
|  1 | zhangsan  |
|  2 | lisi  |
|  3 | xiaozhang |
|  4 | xiaohong  |
|  5 | ??? |
|  6 | ??? |
+----+-----------+
8 rows in set (0.00 sec)

出現(xiàn)問題：中文內(nèi)容亂碼

為什么插入中文數(shù)據(jù)會(huì)出現(xiàn)亂碼問題呢？

通過上面的例子我們可以看出客戶端字符集和庫，表字符集不一樣導(dǎo)致亂碼問題，所以我們?cè)诓迦霐?shù)據(jù)的時(shí)候要先查看系統(tǒng)字符集和客戶端，庫表字符集是否一樣，不一樣將字符集修改一致再插入數(shù)據(jù)。已經(jīng)插入的數(shù)據(jù)有亂碼可以將數(shù)據(jù)導(dǎo)出備份添加修改字符集命令后再重新導(dǎo)入。

1.2 方法一執(zhí)行set names命令

命令語法：set names 接指定字符集

(1)查看建表語句，注意默認(rèn)的字符集是latin1

mysql> show create table student\G
*************************** 1. row ***************************
   Table: student
Create Table: CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

(2)設(shè)置MySQL客戶端的字符集和建表的字符集latin1一致

設(shè)置插入數(shù)據(jù)的字符集為latin

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

(3)再插入一條中文數(shù)據(jù)

mysql> insert into student values(7,'林中鳥');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student where id='7';
+----+-----------+
| id | name  |
+----+-----------+
|  7 | 林中鳥 |
+----+-----------+
1 row in set (0.00 sec)

提示：不亂碼了，但是以前的數(shù)據(jù)就沒辦法解決了。

上面的是MySQL命令行插入數(shù)據(jù)不亂碼的方法，那么如果更新的數(shù)據(jù)多就需要執(zhí)行sql文件更新數(shù)據(jù)了，所以保證執(zhí)行sql文件也不亂碼怎么辦呢？

1.3 方法二執(zhí)行sql文件

(1)將要更新的多個(gè)sql語句放在文本文件中如test.sql

需要用system命令，執(zhí)行system命令可以不退出數(shù)據(jù)庫對(duì)系統(tǒng)的文件進(jìn)行引用和查看。當(dāng)然也可以退出數(shù)據(jù)庫這樣會(huì)比較麻煩。

mysql> system cat test.sql
set names latin1;
insert into student values(8,'不認(rèn)識(shí)');
mysql> system ls;
beifen.sh  test.sql

提示：必須要加入set names latin1，確保插入數(shù)據(jù)不亂碼。

(2)在MySQL命令行中通過source調(diào)用test.sql文件插入數(shù)據(jù)

用source命令執(zhí)行sql文件實(shí)現(xiàn)對(duì)數(shù)據(jù)庫的操作，可以恢復(fù)數(shù)據(jù)庫的數(shù)據(jù)當(dāng)然也可以退出數(shù)據(jù)庫用輸入重定向執(zhí)行sql文件對(duì)數(shù)據(jù)庫的數(shù)據(jù)進(jìn)行恢復(fù)。

mysql> source test.sql
Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

mysql> select * from student where id='8';
+----+-----------+
| id | name  |
+----+-----------+
|  8 | 不認(rèn)識(shí) |
+----+-----------+
1 row in set (0.00 sec)

小結(jié)：執(zhí)行DQL，DML語句的時(shí)候要set names 保持庫和表的字符集一致，還要調(diào)整客戶端的字符集。

1.3 方法三添加字符集參數(shù)解決導(dǎo)入數(shù)據(jù)亂碼問題

(1)把要更新的多個(gè)SQL語句放入文本中，這次不帶set names latin1

#set names latin1;
insert into student values(9,'小紅');

(2)通過MySQL命令加上字符集參數(shù)指定latin1字符集導(dǎo)入test.sql

[root@localhost ~]# mysql -uroot -p123456 --default-character-set=latin1 linzhongniao < test.sql

(3)通過-e參數(shù)在mysql庫外查看結(jié)果

[root@localhost ~]# mysql -uroot -p123456 -e "select * from linzhongniao.student where id='9'"
+----+--------+
| id | name   |
+----+--------+
|  9 | 小紅 |
+----+--------+

1.4 MySQL插入中文不亂碼5中方法小結(jié)

方法一：執(zhí)行set names命令再插入數(shù)據(jù)

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into student values(7,'林中鳥');
Query OK, 1 row affected (0.00 sec)

提示：確保test.sql文件格式正確

方法二：在sql文件中指定set names latin1;然后登錄mysql，通過如下命令執(zhí)行。

mysql> system cat test.sql
set names latin1;
insert into student values(8,'不認(rèn)識(shí)');
mysql> source test.sql
Query OK, 0 rows affected (0.00 sec)

方法三：在sql文件中指定set names latin1 然后通過mysql導(dǎo)入

[root@localhost ~]# mysql -uroot -p123456  linzhongniao < test.sql
[root@localhost ~]# mysql -uroot -p123456 -e "set names latin1;select * from linzhongniao.student"

提示：這里的linzhongniao是庫名不是表名。

方法四：通過指定mysql命令的字符集參數(shù)來實(shí)現(xiàn)

#set names latin1;
insert into student values(9,'李四');
[root@localhost ~]# mysql -uroot -p123456 --default-character-set=latin1 linzhongniao < test.sql

方法五：在配置文件里設(shè)置客戶端及服務(wù)端相關(guān)參數(shù)

(1)更改my.cnf客戶端client模塊的參數(shù)，可以實(shí)現(xiàn)set names latin1效果，并永久生效

[client]
default-character-set=latin1

提示：不需要重啟服務(wù)，退出重新登陸生效。

(2)在服務(wù)端mysqld模塊里面再指定latin1字符集

[mysqld]
default-character-set=latin1適合5.1 及以前呢版本
default-character-server=latin1 適合5.5

2.mysql數(shù)據(jù)庫字符集知識(shí)

2.1 MySQL數(shù)據(jù)庫字符集介紹

簡單的說是一套文字符號(hào)及其編碼、比較規(guī)則的集合。MySQL數(shù)據(jù)庫字符集包括字符集（CHARACTER）和校對(duì)規(guī)則（COLLATION）兩個(gè)概念。其中，字符集是用來定義MySQL數(shù)據(jù)字符串的存儲(chǔ)方式，而校對(duì)規(guī)則則是定義比較字符串的方式。前面建庫的語句中CHARACTER SET latin1即為數(shù)據(jù)庫字符集而COLLATE latin1_swedish_ci 為校對(duì)字符集，有關(guān)字符集詳細(xì)內(nèi)容參考mysql手冊(cè)，第10張字符集章節(jié)。

2.2 MySQL數(shù)據(jù)庫常見字符集介紹

使用MySQL時(shí)常用的字符集有下表四種

2.3 MySQL如何選擇合適的字符集

(1)如果處理各種各樣的文字，發(fā)布到不同國家和地區(qū)，應(yīng)選Unicode字符集。對(duì)mysql來說就是UTF-8（每個(gè)漢字三個(gè)字節(jié)），如果應(yīng)用需處理英文，有少量漢字使用UTF-8字符集更好。

(2)如果只需支持中文，并且數(shù)據(jù)量很大，性能要求也很高，可選GBK（定長，每個(gè)漢字占雙字節(jié)，英文也占雙字節(jié)），處理大量運(yùn)算，比較順序等定長字符集更快，性能高。

(3)處理移動(dòng)互聯(lián)網(wǎng)業(yè)務(wù)，可能需要使用utf8mb4字符集。

2.4 查看當(dāng)前MySQL系統(tǒng)支持的字符集

最常用的有四種：

[root@localhost ~]# mysql -uroot -p123456 -e "SHOW CHARACTER SET;"|egrep "gbk|utf8|latin1"|awk ' {print $0}'
latin1    cp1252 West European      latin1_swedish_ci    1
gbk       GBK Simplified Chinese    gbk_chinese_ci       2
utf8      UTF-8 Unicode             utf8_general_ci      3
utf8mb4   UTF-8 Unicode             utf8mb4_general_ci   4

查看mysql當(dāng)前的字符集設(shè)置情況

mysql> show variables like 'character_set%';
+--------------------------+----------------------------------+
| Variable_name| Value|
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results| utf8 |
| character_set_server | latin1   |
| character_set_system | utf8 |
| character_sets_dir   | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

提示：默認(rèn)情況下character_set_client，character_set_connection，character_set_results三者的字符集和系統(tǒng)的字符集是一致的，是同時(shí)修改的。即為:

[root@localhost ~]# cat /etc/sysconfig/i18n 
LANG="zh_CN.UTF-8"
[root@localhost ~]# echo $LANG
zh_CN.UTF-8

3.mysql數(shù)據(jù)庫默認(rèn)設(shè)置的字符集是什么？

(1)先看一下mysql默認(rèn)情況下設(shè)置的字符集

mysql> show variables like 'character_set%';
+--------------------------+----------------------------------+
| Variable_name| Value|
+--------------------------+----------------------------------+
| character_set_client | gb2312   |
| character_set_connection | gb2312   |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results| gb2312   |
| character_set_server | latin1   |
| character_set_system | utf8 |
| character_sets_dir   | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

(2)不同字符集參數(shù)的含義如下

| character_set_client | latin1  客戶端字符集
| character_set_connection | latin1  連接字符集
| character_set_database   | latin1數(shù)據(jù)庫字符集，配置文件指定或建庫建表指定
| character_set_results| latin1  返回結(jié)果字符集
| character_set_server | latin1服務(wù)器字符集，配置文件指定或建庫建表指定

更改linux系統(tǒng)字符集變量后，查看MySQL中字符集的變化

[root@localhost ~]# echo $LANG
zh_CN.UTF-8
[root@localhost ~]# mysql -uroot -p123456 -e "show variables like 'character_set%';"
+--------------------------+----------------------------------+
| Variable_name| Value|
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results| utf8 |
| character_set_server | latin1   |
| character_set_system | utf8 |
| character_sets_dir   | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

我們發(fā)現(xiàn)character_set_connection，character_set_client，character_set_server 三者的字符集和系統(tǒng)的一致也都改成utf8了。

4.執(zhí)行set names latin1到底做了什么

無論linux系統(tǒng)的字符集是gb2312還是utf8默認(rèn)情況下插入數(shù)據(jù)都是亂碼的。

(1)此時(shí)查看數(shù)據(jù)就是亂碼

mysql> use linzhongniao
Database changed
mysql> select * from student
-> ;
+----+---------------------+
| id | name|
+----+---------------------+
|  1 | zhangsan|
|  2 | lisi|
|  3 | wanger  |
|  4 | xiaozhang   |
|  5 | xiaowang|
|  6 | ??? |
|  7 | ?°?o￠  |
|  8 | ??è?¤èˉ?   |
|  9 | ?????  |
+----+---------------------+
9 rows in set (0.10 sec)

(2)執(zhí)行完set對(duì)應(yīng)的字符集操作，就解決亂碼問題了

mysql> show create database linzhongniao\G
*************************** 1. row ***************************
   Database: linzhongniao
Create Database: CREATE DATABASE `linzhongniao` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)
mysql> show create table student\G
*************************** 1. row ***************************
   Table: student
Create Table: CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

我們看庫和表的字符集都是latin1,所以執(zhí)行set names latin1保證字符集一樣就不會(huì)亂碼了。

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+----+-----------+
| id | name  |
+----+-----------+
|  1 | zhangsan  |
|  2 | lisi  |
|  3 | wanger|
|  4 | xiaozhang |
|  5 | xiaowang  |
|  6 | ???   |
|  7 | 小紅  |
|  8 | 不認(rèn)識(shí)|
|  9 | 李四  |
+----+-----------+

（3）執(zhí)行完set字符集操作的結(jié)果改變了如下字三個(gè)字符集character_set_client，character_set_connection，character_set_results的參數(shù)。

mysql> show variables like 'character_set%';
+--------------------------+----------------------------------+
| Variable_name| Value|
+--------------------------+----------------------------------+
| character_set_client | latin1   |
| character_set_connection | latin1   |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results| latin1   |
| character_set_server | latin1   |
| character_set_system | utf8 |
| character_sets_dir   | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

5.mysql命令參數(shù)—default-character-set=latin1在做什么？

（1）先查看一下mysql的字符集

[root@localhost ~]# mysql -uroot -p123456 -e "show variables like 'character_set%';"
+--------------------------+----------------------------------+
| Variable_name| Value|
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results| utf8 |
| character_set_server | latin1   |
| character_set_system | utf8 |
| character_sets_dir   | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

（2）帶—default-character-set=latin1 參數(shù)登錄mysql

[root@localhost ~]# mysql -uroot -p123456 --default-character-set=latin1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.32 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

（3）現(xiàn)在再查看mysql的字符集

mysql> show variables like 'character_set%';
+--------------------------+----------------------------------+
| Variable_name| Value|
+--------------------------+----------------------------------+
| character_set_client | latin1   |
| character_set_connection | latin1   |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results| latin1   |
| character_set_server | latin1   |
| character_set_system | utf8 |
| character_sets_dir   | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

（4）帶參數(shù)登錄也是臨時(shí)修改不帶參數(shù)登錄又變回去了

[root@localhost ~]# mysql -uroot -p123456 --default-character-set=latin1 -e "show variables like 'character_set%';"
+--------------------------+----------------------------------+
| Variable_name| Value|
+--------------------------+----------------------------------+
| character_set_client | latin1   |
| character_set_connection | latin1   |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results| latin1   |
| character_set_server | latin1   |
| character_set_system | utf8 |
| character_sets_dir   | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

[root@localhost ~]# mysql -uroot -p123456 -e "show variables like 'character_set%';"
+--------------------------+----------------------------------+
| Variable_name| Value|
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results| utf8 |
| character_set_server | latin1   |
| character_set_system | utf8 |
| character_sets_dir   | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

6.確保MySQL數(shù)據(jù)庫插入數(shù)據(jù)不亂碼解決方案

6.1 統(tǒng)一客戶端字符集

（1）MySQL數(shù)據(jù)庫的下面幾個(gè)字符集（客戶端和服務(wù)端）統(tǒng)一成一個(gè)字符集才能確保插入的中文數(shù)據(jù)庫可以正常輸出。當(dāng)然，linux系統(tǒng)的字符集也要盡可能和數(shù)據(jù)庫字符集統(tǒng)一。

（2）mysql數(shù)據(jù)庫字符集的含義：

Variable_name              | Value 
+--------------------------+--------------------------------+
①character_set_client      | latin1  客戶端字符集
②character_set_connection  | latin1  連接字符集
③character_set_database    | latin1   數(shù)據(jù)庫字符集
④character_set_results     | latin1   返回結(jié)果字符集
⑤character_set_server      | latin1   服務(wù)器字符集，配置文件制定或建庫建表指定

其中，①②④三個(gè)參數(shù)默認(rèn)情況采用linux系統(tǒng)字符集設(shè)置，人工登錄數(shù)據(jù)庫執(zhí)行set names latin1以及mysql指定字符集登錄操作，都是改變mysql客戶端的client、connection、results3個(gè)參數(shù)的字符集都為latin1,從而解決插入亂碼問題，這個(gè)操作可以在my.cnf配置文件里修改mysql客戶端的字符集，配置方法如下：

[client]
Default-character-set=latin1
提示：不需要重啟
[root@localhost ~]# sed -n "18,22p" /etc/my.cnf 
[client]
#password    = your_password
port        = 3306
socket        = /usr/local/mysql/tmp/mysql.sock
default-character-set = latin1
[root@localhost ~]# mysql -uroot -p123456 -e "show variables like 'character_set%';"
+--------------------------+----------------------------------+
| Variable_name| Value|
+--------------------------+----------------------------------+
| character_set_client | latin1   |
| character_set_connection | latin1   |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results| latin1   |
| character_set_server | latin1   |
| character_set_system | utf8 |
| character_sets_dir   | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

（3）修改完客戶端字符集不用set查詢表數(shù)據(jù)就不會(huì)亂碼了

[root@localhost ~]# mysql -uroot -p123456 -e "select * from linzhongniao.student;"
+----+-----------+
| id | name  |
+----+-----------+
|  1 | zhangsan  |
|  2 | lisi  |
|  3 | wanger|
|  4 | xiaozhang |
|  5 | xiaowang  |
|  6 | ???   |
|  7 | 小紅|
|  8 | 不認(rèn)識(shí) |
|  9 | 李四|
+----+-----------+

6.2 統(tǒng)一MySQL服務(wù)端字符集

(1) 按下面要求修改my.cnf參數(shù)

[mysqld]
default-character-set = latin1 適合5.1及以前版本
character-set-server = utf8 適合5.5版本

(2) 修改前查看當(dāng)前字符集

[root@localhost ~]# mysql -uroot -p123456 -e "show variables like 'character_set%';"
+--------------------------+----------------------------------+
| Variable_name| Value|
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results| utf8 |
| character_set_server | latin1   |
| character_set_system | utf8 |
| character_sets_dir   | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

(3) 查看修改的參數(shù)

[root@localhost ~]# sed -n "26,27p" /etc/my.cnf 
[mysqld]
character-set-server = utf8  

(4) 重啟mysql服務(wù)（生產(chǎn)環(huán)境是不允許重啟的）

[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS!

(5) 查看更改后的字符集

[root@localhost ~]# mysql -uroot -p123456 -e "show variables like 'character_set%';"
+--------------------------+----------------------------------+
| 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/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

提示：以上在[mysqld]下設(shè)置的參數(shù)會(huì)更改下面2個(gè)參數(shù)的字符集設(shè)置。

| Variable_name            | Value|
| character_set_database   | utf8 |
| character_set_server     | utf8 |

這個(gè)時(shí)候我們?cè)傩薷南到y(tǒng)字符集mysql數(shù)據(jù)庫字符集就不亂碼了

[root@localhost ~]# cat /etc/sysconfig/i18n 
LANG="zh_CN.GB2312"
#LANG="zh_CN.UTF-8"
[root@localhost ~]# source /etc/sysconfig/i18n 
[root@localhost ~]# mysql -uroot -p123456 -e "show variables like 'character_set%';"
+--------------------------+----------------------------------+
| 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/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

6.3 統(tǒng)一字符集方法總結(jié)

保證數(shù)據(jù)庫數(shù)據(jù)不亂碼的方法：建議中英文環(huán)境選擇utf8 ,linux系統(tǒng)，客戶端，服務(wù)端，庫，表，程序字符集統(tǒng)一。

(1)Linux系統(tǒng)字符集統(tǒng)一utf8

[root@localhost ~]# cat /etc/sysconfig/i18n 
LANG="zh_CN.UTF-8"

提示linux客戶端也要更改字符集 例如：xshell

例如：SecureCRT

(2)Mysql數(shù)據(jù)庫客戶端

臨時(shí)：

set names latin1

永久：

更改my.cnf客戶端模塊的參數(shù)，可以實(shí)現(xiàn)set names latin1效果，并永久生效。
[client]
Default-character-set=latin1

(3)服務(wù)端

更改my.cnf參數(shù)

[mysqld]
Default-character-set = latin1 適合5.1及以前版本
character-set-server = latin1  適合5.5

(4)庫表，程序指定字符集建庫

create database linzhongniao_utf8 DEFAULT CHARACTER SET UTF8 COLLATE  后面加校對(duì)規(guī)則

我們可以show一下查看支持的校對(duì)規(guī)則

[root@localhost ~]# mysql -uroot -p123456 -e "SHOW CHARACTER SET;"|egrep "gbk|utf8|latin1"|awk ' {print $0}'
latin1    cp1252 West European      latin1_swedish_ci    1
gbk       GBK Simplified Chinese    gbk_chinese_ci       2
utf8      UTF-8 Unicode             utf8_general_ci      3
utf8mb4   UTF-8 Unicode             utf8mb4_general_ci   4

7.如何更改生產(chǎn)MySQL數(shù)據(jù)庫庫表的字符集

對(duì)于已有數(shù)據(jù)庫想修改字符集不能直接通過“alter database character set ”或者”alter table tablename character set ”，這兩個(gè)命令都不能更新已有數(shù)據(jù)的字符集。而只是對(duì)新創(chuàng)建的表或者數(shù)據(jù)生效。

已經(jīng)有記錄的字符集的調(diào)整必須將數(shù)據(jù)導(dǎo)出，經(jīng)過修改字符集之后重新導(dǎo)入才可完成。

修改數(shù)據(jù)庫默認(rèn)編碼命令：

alter database [your db name] charset [your character setting]

下面模擬將latin1字符集的數(shù)據(jù)庫修改成GBK字符集的過程。

（1）導(dǎo)出表結(jié)構(gòu)

使用mysqldump的-d參數(shù)導(dǎo)出表結(jié)構(gòu)

mysqldump –uroot –p123456 –-default-character-set=latin1 –d dbname>alltable.sql –-default-character-set=gbk 表示以GBK字符集進(jìn)行連接 –d只導(dǎo)表結(jié)構(gòu)。

（2）然后編輯alltable.sql將latin1用sed替換成GBK

（3）確保數(shù)據(jù)不在更新導(dǎo)出所有數(shù)據(jù)

mysqldmup –uroot –p123456 –-quick –-no-create-info –-extended-insert –-default-character-set=latin1 dbname>alltables.sql

參數(shù)說明：

 --quick:用于轉(zhuǎn)儲(chǔ)大的表，強(qiáng)制mysqldump從服務(wù)器一次一行的檢索數(shù)據(jù)而不是檢索所有行并輸出前CACHE到內(nèi)存中。

 --no-create-info:不創(chuàng)建CREATE TABLE 語句。

 --extended-insert:使用包括幾個(gè)VALUES列表的多行INSERT語法，這樣文件更小節(jié)省IO導(dǎo)入數(shù)據(jù)非?？臁?
 --default-character-set=latin1按照原有字符集導(dǎo)出數(shù)據(jù)，這樣導(dǎo)出的文件中，所有中文都是可見的，不會(huì)保存成亂碼。

（4）打開alltable.sql將set names latin1修改成set names gbk(或者修改my.cnf配置文件)

（5）建庫

create database dbname default charset gbk;

（6）創(chuàng)建表執(zhí)行，alltable.sql

mysql –uroot –p123456 dbname<alltable.sql

（7）導(dǎo)入數(shù)據(jù)

mysql –uroot –p123456 dbname<alltables.sql