mysql數(shù)據(jù)庫字符集學(xué)習(xí)總結(jié)

MYSQL數(shù)據(jù)庫字符集包括字符集（CHARACTER）和校對規(guī)則（COLLATION）兩個(gè)概念。

MYSQL 支持的字符集和校對規(guī)則可以通過命令showcharacter set;查看。

和字符集有關(guān)的變量

mysql> 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       |/application/mysql-5.5.32/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0.00 sec)

這幾個(gè)字符集含義如下：

character_set_client       #客戶端字符集

character_set_connectio    #鏈接字符集

character_set_database     #數(shù)據(jù)庫字符集，配置文件指定或建庫建表指定。

character_set_filesystem    #文件系統(tǒng)字符集

character_set_results       #返回結(jié)果字符集

character_set_server       #服務(wù)器字符集，配置文件指定貨建庫建表指定。

character_set_system       #系統(tǒng)字符集

要解決MYSQL 亂碼問題 需要做到字符集6個(gè)統(tǒng)一

1、統(tǒng)一客戶端字符集 可以通過 set names 命令修改 也可以單獨(dú)修改以下幾個(gè)變量

方法1：set names gbk  控制客戶端的字符集

方法2：單獨(dú)修改下面三個(gè)參數(shù)

set character_set_client gbk;

set character_set_connectio gbk;

set character_set_results gbk;

方法3：也可以在進(jìn)入MYSQL的時(shí)候加 --default-character-set=gbk 參數(shù)。

mysql-uroot -p123456 -S /data/3306/mysql.sock --default-character-set=gbk;

方法4：永久生效需要修改my.cnf 配置文件在[client]模塊中添加下面參數(shù)

[client]

default-character-set=gbk   如果是多實(shí)例配置需要修改/etc/my.cnf

2、統(tǒng)一MYSQL 服務(wù)端字符集

方法1：按如下要求更改my.cnf參數(shù)

[mysqld]

default-character-set=latin1  適合5.1之前的版本

character-set-server=latin1   適合5.5

 對應(yīng)變量中的以下兩個(gè)參數(shù)：

character_set_database

character_set_server

方法2：編譯的時(shí)候指定服務(wù)器端字符集：

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \

3、字段、建表、建庫字符集也要統(tǒng)一

指定字符集建庫：

create database oldboy_utf8 DEFAULT CHARACTER SETUTF8 COLLATE utf8_general_ci;

修改數(shù)據(jù)庫字符集命令：

alter database oldboy character set utf8 collate utf8_general_ci;

指定字符集建表：

CREATE TABLE `test` (
  `id` int(4)NOT NULL AUTO_INCREMENT,
  `name`char(20) NOT NULL,
  PRIMARY KEY(`id`),
  KEY`index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

修改數(shù)據(jù)表字符集命令：

把表默認(rèn)的字符集和所有字符列（CHAR,VARCHAR,TEXT）改為新的字符集

alter table test CONVERT TO character set utf8 collate utf8_general_ci;

僅修表的默認(rèn)字符集

alter table test DEFAULT character set utf8 collate utf8_general_ci;

修改字段的字符集

alter table test CHANGE name name varchar(16) character set utf8 collate utf8_general_ci;

相關(guān)查看命令

show create database oldboy;

show create table test;

show FULL COLUMNS from test;

4、程序所使用的字符集要和數(shù)據(jù)庫 表 字段 的統(tǒng)一

5、LINUX系統(tǒng)字符集也要統(tǒng)一

cat /etc/sysconfig/i18n
LANG="en_US.UTF-8"
SYSFONT="latarcyrheb-sun16"

6、LINUX客戶端 如 CRT  XSHELL 等的字符集也要統(tǒng)一 可以在軟件屬性里設(shè)置。

如果數(shù)據(jù)庫有亂碼 可按以上6點(diǎn) 逐一進(jìn)行排查

如何選擇適合的字符集？

1、如果是處理各種各樣的文字，發(fā)布到不同語言國家地區(qū)，應(yīng)選UNICODE字符集，對MYSQL來說就是UTF-8 （每個(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字符集。

建議：沒有特別需求，請選擇UTF8.

很多開源程序都會(huì)有多種字符集版本的程序。

在數(shù)據(jù)庫中執(zhí)行sql語句的方法

1）操作習(xí)慣：盡量不在MYSQL命令行插入數(shù)據(jù)（SSH客戶端影響）

2）sql文件的格式統(tǒng)一用“utf8沒簽名”。

3）導(dǎo)入文件的方式

a、可在MYSQL命令行中用source 執(zhí)行sql文件。

b、命令方式導(dǎo)入數(shù)據(jù) mysql -uroot -p123456oldboy<test.sql

4）sql文件里 set names utf8,或mysql -uroot -p123456 oldboy -default-character-set=utf8<test.sql。

更改數(shù)據(jù)庫字符集的方法：

分兩種情況：

1、空表無數(shù)據(jù)

直接通過以上命令修改即可

2、如果表里已經(jīng)有數(shù)據(jù)了

此時(shí)不能直接修改表的字符集了，因?yàn)樾薷暮蟮淖址粫?huì)對新插入的數(shù)據(jù)生效，之前庫的數(shù)據(jù)并不起作用。此時(shí)我們可以按以下思路操作修改數(shù)據(jù)庫字符集

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

mysqldump-uroot -p --default-character-set =latin -d dbname>alltable.sql

2）編輯表結(jié)構(gòu)語句alltable.sql 通過sed替換命令將所有原字符集的字符串改成新的字符集的。

3）鎖表確保數(shù)據(jù)庫不再更新，導(dǎo)出所有數(shù)據(jù)（不帶表結(jié)構(gòu)）

mysqldump-uroot -p --quick --no-create-info --extended-insert --default-character-set=latin dbname>alltable.sql

 --no-create-info 不導(dǎo)出表結(jié)構(gòu) 

4）修改my.cnf 配置調(diào)整客戶端及服務(wù)端字符集，重啟生效

5）通過新字符集建庫（可選）

刪除原庫，然后create database dbname default charset utf8;

6）導(dǎo)入表結(jié)構(gòu)（更改過字符集的表結(jié)構(gòu)）

mysql-uroot -p dbname <alltable.sql

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

mysql-uroot -p dbname <alldata.sql