Mysql修改已有數據的字元集

問題

在生產環境中跑了很久,發現MysqlClient連接的字元集是預設的latin1,我們一直以為都是utf8,造成這樣的誤解,是因為在內網環境中,我們是源碼編譯的Mysql,並指定了編譯選項字元集位utf8,這時Mysql的是預設字元接都是utf8.

而在外網,我們是二進位包安裝,預設是latin1,雖然在my.conf中指定了[client] [mysql] [mysqld] 中字元集為utf8,這並不能保證MysqlClient連接時的字元集為utf8.所以最好是在建立連接是指定字元集,這個是能保證的.如:

//C連接 需在建立連接前設置
mysql_options(m_mysql, MYSQL_SET_CHARSET_NAME, "utf8");

由於數據是通過laint1連接到Mysql存儲的,所以取數據也需要laint1的連接方式.但這個方式與預期不符,而且其他客戶端連接會通過utf8連接,這就會導致亂碼,必須修複成通過utf8連接存儲的數據.

修複原理

通過latin1的方式建立連接把數據取出,然後以通過utf8的方式建立連接把數據存回去.

修複事例

表結構定義如下:

CREATE TABLE IF NOT EXISTS `tbl_friend` (
  `Uin` bigint(20) unsigned NOT NULL,
  `Name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`Uin`),
  UNIQUE KEY `Name` (`Name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

select CONCAT('update tbl_friend set `Name`=\'', Name, '\' where Uin=', Uin,';') from tbl_friend into outfile '/tmp/UpdateFriendName.sql' CHARACTER SET 'latin1';

在mysql中執行上面的語句,然後在mysql中導入/tmp/UpdateFriendName.sql文件,數據就修複了. 需要註意的是,my.cnf的[mysql]段中字元集是你修改後的字元集或者通過 mysql --default-character-set=utf8 < /tmp/UpdateFriendName.sql  指定修複後的字元集.

修複後可以在mysql中通過set names utf8/latin1,然後select Name from tbl_friend limit 10; 來看變化,

可能遇到的阻礙

如果在執行當中遇到 The MySQL server is running with the --secure-file-priv option so it cannot execute this statement 需要在my.conf中的[mysqld]增加secure_file_priv =,然後重啟Mysql.