mysql字元集調整總結

   字元集是一套符號和編碼的規則，不論是在oracle資料庫還是在mysql資料庫，都存在字元集的選擇問題。對於資料庫來說，字元集又是比較重要的，因為資料庫儲存的資料大部分都是各種文字，字元集對於資料庫的儲存、處理效能以及資料移轉都有重要的影響。

   如果在資料庫建立階段沒有正確選擇字元集，那麼可能在後期需要更換字元集，而字元集的更換是代價比較高的操作，也存在一定的風險，所以我們建議在應用開始階段，就按照需求正確的選擇合適的字元集，盡量避免後期不必要的調整。

   mysql編譯安裝時，指定字元集的方法：

./configure  --with-charset=utf8

mysql的字元集有4個層級的預設設定：伺服器級、資料庫級、表級和欄位級。分別在不同的地方設定，作用也不相同。
1、伺服器字元集設定，在mysql服務啟動的時候確定。
可以在my.cnf中設定：

[mysql]

### 預設字元集為utf8

default-character-set=utf8

[mysqld]
### 預設字元集為utf8

default-character-set=utf8

### 設定串連mysql資料庫時使用utf8編碼，以讓mysql資料庫為utf8運行）

init_connect='SET NAMES utf8'

或者在啟動選項中指定：
mysqld --default-character-set=utf8

如果沒有特別的指定伺服器字元集，預設使用latin1(ISO-8859-1的別名)作為伺服器字元集。上面三種設定的方式都只指定了字元集，沒有去做校對，我們可以用show variables like 'char%';命令查詢當前伺服器的字元
集和校對規則。

mysql>show variables like 'char%';

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

　　| 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/ |

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

註：如果增加default-character-set=utf8後，MYSQL啟動報錯。可以用character_set_server=utf8來取代default-character-set=utf8，就能正常啟動了。這是因為MYSQL不同版本識別的問題。

2、資料庫級

建立資料庫時指定字元集

mysql>CREATE DATABASE my_db default charset utf8 COLLATE utf8_general_ci;

#注意後面這句話 "COLLATE utf8_general_ci",大致意思是在排序時根據utf8編碼格式來排序

如果指定了資料庫編碼，那麼在這個資料庫下建立的所有資料表的預設字元集都會是utf8了

修改MYSQL資料庫編碼，如果是MYSQL資料庫編碼不正確，可以在MYSQL執行如下命令:

ALTER DATABASE my_db DEFAULT CHARACTER SET utf8;  

以上命令就是將MYSQL的my_db資料庫的編碼設為utf8

3、 表級

建立表時指定字元集

mysql>create table my_table (name varchar(20) not null default '')type=myisam default charset utf8;

#這句話就是建立一個表,指定預設字元集為utf8

修改MYSQL表的編碼：

ALTER TABLE my_table DEFAULT CHARACTER SET utf8;

以上命令就是將一個表my_table的編碼改為utf8

4、 欄位級

alter table test add column address varchar(110) after stu_id;

在stu_id後增加一個欄位address

alter table test add id int unsigned not Null auto_increment primary key;

修改欄位的編碼：

ALTER TABLE `test` CHANGE `name` `name` VARCHAR( 45 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL

以上命令就是將MYSQL資料庫test表中name的欄位編碼改為utf8

在命令列下插入漢字時如下代碼：

set names utf8;有時候這一句很關鍵！

insert into charset values('王達');

注意：alter修改的方法不能更新已有記錄的字元集，只是對新建立的表和記錄生效。對已有記錄字元集的調整，需要先將資料匯出，經過適當調整後重新匯入才可以完全修改編碼。

匯出匯入的字元調整方法:

匯出表結構

mysqldump -uroot -pmysql --default-character-set=latin1 -d  my_db> createtab.sql

手工修改createtab.sql表結構定義中的字元集為新的字元集

1、匯出所有記錄

mysqldump -uroot -pmysql --quick --no-create-info --extended-insert --default-character-set=latin1 --host=localhost  my_db> data.sql

2、開啟data.sql，將set names latin1修改成set names utf8

:%s/latin1/utf8/g

全文替換

3、使用新的字元集建立新的資料庫

create database  mydata  default charset utf8;

4、建立表，執行createtab.sql

mysql -uroot -pmysql mydata<creattab.sql

5、匯入資料

mysql -uroot -pmysql mydata<data.sql

注意一點就是目標字元集要大於等於源字元集，否則會丟失一部分不支援的漢字資料。

附：舊資料升級辦法

以原來的字元集為latin1為例，升級成為utf8的字元集。原來的表: old_table (default charset=latin1)，新表：new_table(default charset=utf8)。

第一步：匯出舊資料

mysqldump --default-character-set=latin1 -hlocalhost -uroot -B my_db --tables old_table > old.sql

第二步：轉換編碼

iconv -t utf8  -f latin1 -c old.sql > new.sql

在這裡，假定原來的資料預設是latin1編碼。

第三步：匯入

修改old.sql，增加一條sql語句： "SET NAMES utf8;"，儲存。

mysql -hlocalhost -uroot my_db < new.sql

大功告成！

Mysql collate規則：

*_bin: 表示的是binary case sensitive collation，也就是說是區分大小寫
*_cs: case sensitive collation，區分大小寫
*_ci: case insensitive collation，不區分大小寫

本文出自 “滴水穿石孫傑” 部落格，請務必保留此出處http://xjsunjie.blog.51cto.com/999372/1355013