mysql字符集調整總結

--http://xjsunjie.blog.51cto.com/999372/1355013

字符集是一套符號和編碼的規則，不論是在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修改的方法不能更新已有記錄的字符集，只是對新創建的表和記錄生效。對已有記錄字符集的調整，需要先將數據導出，經過適當調整后重新導入才可以完全修改編碼。

---

導出導入的字符調整方法:
0、導出表結構
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，不區分大小寫

總結

以上是生活随笔為你收集整理的mysql字符集调整总结的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。