mysql數據庫字符集學習總結

MYSQL數據庫字符集包括字符集（CHARACTER）和校對規則（COLLATION）兩個概念。

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

和字符集有關的變量

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)

這幾個字符集含義如下：

character_set_client       #客戶端字符集

character_set_connectio    #鏈接字符集

character_set_database     #數據庫字符集，配置文件指定或建庫建表指定。

character_set_filesystem    #文件系統字符集

character_set_results       #返回結果字符集

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

character_set_system       #系統字符集

要解決MYSQL 亂碼問題 需要做到字符集6個統一

1、統一客戶端字符集 可以通過 set names 命令修改 也可以單獨修改以下幾個變量

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

方法2：單獨修改下面三個參數

set character_set_client gbk;

set character_set_connectio gbk;

set character_set_results gbk;

方法3：也可以在進入MYSQL的時候加 --default-character-set=gbk 參數。

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

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

[client]

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

2、統一MYSQL 服務端字符集

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

[mysqld]

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

character-set-server=latin1   適合5.5

 對應變量中的以下兩個參數：

character_set_database

character_set_server

方法2：編譯的時候指定服務器端字符集：

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \

3、字段、建表、建庫字符集也要統一

指定字符集建庫：

create database oldboy_utf8 DEFAULT CHARACTER SETUTF8 COLLATE utf8_general_ci;

修改數據庫字符集命令：

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

修改數據表字符集命令：

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

alter table test CONVERT TO character set utf8 collate utf8_general_ci;

僅修表的默認字符集

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;

相關查看命令

show create database oldboy;

show create table test;

show FULL COLUMNS from test;

4、程序所使用的字符集要和數據庫 表 字段 的統一

5、LINUX系統字符集也要統一

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

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

如果數據庫有亂碼 可按以上6點 逐一進行排查

如何選擇適合的字符集？

1、如果是處理各種各樣的文字，發布到不同語言國家地區，應選UNICODE字符集，對MYSQL來說就是UTF-8 （每個漢字三字節），如果應用需處理英文，僅有少量漢字UTF-8更好。

2、如只需支持中文，并且數據量很大，性能要求也很高，可選GBK（定長每個漢字占雙字節，英文也占雙字節），如果需大量運算，比較排序等，定長字符集，更快，性能更高。

3、處理移動互聯網業務，可能需要使用utf8mb4字符集。

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

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

在數據庫中執行sql語句的方法

1）操作習慣：盡量不在MYSQL命令行插入數據（SSH客戶端影響）

2）sql文件的格式統一用“utf8沒簽名”。

3）導入文件的方式

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

b、命令方式導入數據 mysql -uroot -p123456oldboy<test.sql

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

更改數據庫字符集的方法：

分兩種情況：

1、空表無數據

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

2、如果表里已經有數據了

此時不能直接修改表的字符集了，因為修改后的字符集只會對新插入的數據生效，之前庫的數據并不起作用。此時我們可以按以下思路操作修改數據庫字符集

1）導出表結構

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

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

3）鎖表確保數據庫不再更新，導出所有數據（不帶表結構）

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

 --no-create-info 不導出表結構 

4）修改my.cnf 配置調整客戶端及服務端字符集，重啟生效

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

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

6）導入表結構（更改過字符集的表結構）

mysql-uroot -p dbname <alltable.sql

7）導入數據

mysql-uroot -p dbname <alldata.sql