Mysql

告訴 MySQL 開始使用 utf-8 編碼而不使用convert to

  • January 24, 2013

在一個相當獨特的情況下,我的團隊最終在一個認為數據被編碼為 latin1 的數據庫中使用 UTF-8 字節。

至少,我有 85% 的把握這是目前的情況。

例如,一個沒有編碼概念的程式語言(Ruby 1.8)將右單引號傳遞給數據庫,只是將數據視為原始字節(0xE2 0x80 0x99)。據我所知(如何驗證?),這些數據被儲存為那些實際字節。所以現在當數據被更智能的程式語言(Ruby 1.9)讀取時,數據庫會很有幫助地說“哦!0xE2 是 ‘â’, 0x80 是 ‘€’, 0x99 是 ‘™’”,而不是“邁克的”,我們最終得到“邁克的”。這也是我在選擇該值時在 mysql 提示符中得到的。

所以,本質上,我們有一堆 utf-8 編碼的數據儲存在一個認為數據被編碼為 latin1 的數據庫中。

這讓我以某種方式告訴數據庫“不,不管你怎麼想,這東西實際上是 utf-8”。CONVERT TO似乎不是正確的工具,因為那樣我最終會得到永久的“邁克”。


失敗/愚蠢的嘗試#1

我注意到了這一點:

> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+-------------------+
| 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     | latin1            |
| character_set_system     | utf8              |
| collation_connection     | utf8_general_ci   |
| collation_database       | utf8_unicode_ci   |
| collation_server         | latin1_swedish_ci |
+--------------------------+-------------------+

並認為可能更改character_set_resultslatin1會欺騙它不進行任何字節轉換,從而在我的 utf8 作業系統上正確顯示數據。

果然,SET character_set_results=latin1;結果代替’. 涼爽的!

所以我將此添加到我的~/.my.cnf(這是唯一的 my.cnf,我檢查過):

[mysqld]
...
character-set-results=latin1

當我回到 MySQL 提示符並檢查character_set_%變數時,它仍然是utf8.

是的,我突然想到這mysqld是一個守護程序,這意味著我可能需要重新啟動整個 mysql 程序才能使其生效。但是在這台機器上安裝 MySQL 的人使用了 dmg 而不是 brew(不是我!)並且 MySQL 首選項窗格目前告訴我 MySQL 沒有執行,即使它很明顯是,無論如何在我去那隻兔子之前洞,我想與真正的 DBA 核實一下,看看這有多荒謬,或者是否有更好、更清潔的方法來做到這一點。

使用CONVERT TO切換整個表是壞消息,原因有幾個,正如mysql 性能部落格中所討論的那樣(概要:CONVERT TO可能會將您的text-type 欄位更改為mediumtext)。

但是,將您的數據實際轉換為 utf8 似乎也比使用 my.cnf 調整來破解它更好。

為此,請通過並

ALTER TABLE `t1`
MODIFY COLUMN `c1` text CHARACTER SET binary,
MODIFY COLUMN `c2` varchar(255) CHARACTER SET binary;
ALTER TABLE `t1`
DEFAULT CHARSET=utf8,
MODIFY COLUMN `c1` text CHARACTER SET utf8,
MODIFY COLUMN `c2` varchar(255) CHARACTER SET utf8;

對於數據庫中的每個表及其相關列。

這是做什麼的:更改為binary編碼使 MySQL 保持相同的字節,但忽略它們的含義。然後,當您切換到 時utf8,字節仍然保持不變。成功!

此轉換將破壞這些欄位上的任何 FULLTEXT 索引(binary編碼不支持 FULLTEXT 索引),因此請確保重新創建它們(如果有)。

我編寫了一個 bash 腳本來自動執行此操作。它不處理 FULLTEXT 索引問題,但它應該給你一個很好的起點。

解決方案並不完全相同,但這個問題是我最初為類似問題找到方向的地方,那裡的概念應該帶你去你想去的地方。MySQL 有一個BINARY字元集,從所有外觀上看,通過轉換它,您可以防止 MySQL 意識到您實際上在做什麼並且“太有幫助”。

character_set_client = utf8 的測試案例:

mysql> select CONVERT(CONVERT(CONVERT('Mike’s' USING latin1) USING binary) USING utf8);
+--------------------------------------------------------------------------------+
| CONVERT(CONVERT(CONVERT('Mike’s' USING latin1) USING binary) USING utf8)     |
+--------------------------------------------------------------------------------+
| Mike’s                                                                         |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

您可以使用該邏輯來填充 MySQL 認為是 utf8 的新列。

引用自:https://dba.stackexchange.com/questions/33365