轉自：

在生產環境中，會經常碰到Mysql字元的設定問題

如果在應用開始階段沒有正確的設定字符集，在執行一段時間以後才發現存在不能滿足要求需要調整，那麼就需要進行字符集的修改。字符集的修改不能直接透過alter dataabase character set ***; 或者　alter table tablename character set ***; 命令進行，這兩個命令都沒有更新已有記錄的字符集，而只是對新建立的表或者記錄生效。

在筆者的資料庫最為常用的是latin1、utf8，由於專案的整合，字元的要求也開始規範，統一為utf8，那麼就需要把之前的latin1字元轉換成才utf8格式，那樣才不會出現亂碼的情況。已有的記錄的字符集調整，需要先將資料匯出，經過適當的調整重新匯入後才可完成。

以下模擬的是將latin1字符集的資料庫修改成GBK字符集的資料庫的過程。操作較為簡單，只要花的時間是在匯入和匯出資料。下面就來簡單的介紹Mysql字元變數和latin1轉換到utf8的過程，大家可以根據自己實際情況，舉一反三。

一、MySQL字符集設定

? 系統變數：

– character_set_server：預設的內部操作字符集

– character_set_client：客戶端來源資料使用的字符集

– character_set_connection：連線層字符集

– character_set_results：查詢結果字符集

– character_set_database：當前選中資料庫的預設字符集

– character_set_system：系統後設資料(欄位名等)字符集

– 還有以collation_開頭的同上面對應的變數，用來描述字元序。

? 用introducer指定文字字串的字符集：

– 格式為：[_charset] ‘string’ [COLLATE collation]

– 例如：

? SELECT _latin1 ‘string’;

? SELECT _utf8 ‘你好’ COLLATE utf8_general_ci;

–由introducer修飾的文字字串在請求過程中不經過多餘的轉碼，直接轉換為內部字符集處理。

二、MySQL中的字符集轉換過程

1. MySQL Server收到請求時將請求資料從character_set_client轉換為character_set_connection；

2. 進行內部操作前將請求資料從character_set_connection轉換為內部操作字符集，其確定方法如下：

? 使用每個資料欄位的CHARACTER SET設定值；

? 若上述值不存在，則使用對應資料表的DEFAULT CHARACTER SET設定值(MySQL擴充套件，非SQL標準)；

? 若上述值不存在，則使用對應資料庫的DEFAULT CHARACTER SET設定值；

? 若上述值不存在，則使用character_set_server設定值。

3. 將操作結果從內部操作字符集轉換為character_set_results。

三、latin1轉換到utf8

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

mysql> show create database databasename;
+--------------+-------------------------------------------------------------------------+
| Database | Create Database |
+--------------+-------------------------------------------------------------------------+
| databasename | CREATE DATABASE `databasename` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+--------------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

1> 匯出表結構：

mysqldump -uroot -p --default-character-set=utf8 -d databasename > createtab.sql

其中--default-character-set=utf8 表示設定以什麼字符集連線，　-d 表示只匯出表結構，不匯出資料。

2>修改　createtab.sql 中表結構定義中的字符集為新的字符集。

sed -i s/CHARSET=latin1/CHARSET=utf8/g `grep -rl "CHARSET=latin1" createtab.sql `

3>確保記錄不再更新，匯出所有記錄。

mysqldump -uroot -p --no-create-info databasename > data.sql

可選引數：

--quick: 該選項用於轉儲大的表。　它強制　mysqldump 從伺服器一次一行地檢索表中的行而不是　檢索所有行，並在輸出前將它快取到記憶體中。

--extended-insert: 使用包括幾個　values 列表的多行insert語法，這樣使轉儲檔案更小，過載檔案時可以加速插入。

--no-create-info: 不寫重新建立每個轉儲表的create table 語句。

--default-character-set=latin1: 按照原有的字符集匯出所有資料，這樣匯出的檔案中，所有中文都是可見的，不會儲存成亂碼。不新增該引數以預設字元匯出。

4>開啟data.sql,將　set names latin1 修改成　set names utf8 。

sed -i s/CHARSET=latin1/CHARSET=utf8/g `grep -rl "CHARSET=latin1" data.sql `

(PS：sed工作效率是非常高的，在測試中60G的資料4分鐘就轉換完畢了）

5>使用新的字符集建立新的資料庫。

create database new_databasename default charset utf8;

6>建立表，執行　createtab.sql

mysql -uroot -p new_databasename < createtab.sql

7>匯入資料，執行data.sql

mysql -uroot -p new_databasename < data.sql

8、檢視新資料庫的字元資訊

mysql> show create database new_databasename;
+------------------+-----------------------------------------------------------------------------+
| Database | Create Database |
+------------------+-----------------------------------------------------------------------------+
| new_databasename | CREATE DATABASE `new_databasename` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+------------------+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table type;
+--

MySQL字符集設定及字元轉換（latin1轉utf8）

相關文章