問題描述
對我昨天問的一個問題的回應(yīng)之一 建議我應(yīng)該確保我的數(shù)據(jù)庫可以正確處理 UTF-8 字符.我如何用 MySQL 做到這一點?
One of the responses to a question I asked yesterday suggested that I should make sure my database can handle UTF-8 characters correctly. How I can do this with MySQL?
推薦答案
更新:
簡短回答 - 您應(yīng)該幾乎總是使用 utf8mb4 字符集和 utf8mb4_unicode_ci 歸類.
Short answer - You should almost always be using the utf8mb4 charset and utf8mb4_unicode_ci collation.
修改數(shù)據(jù)庫:
ALTER DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
見:
Aaron 對此答案的評論 如何制作MySQL正確處理UTF-8
兩者有什么區(qū)別utf8_general_ci 和 utf8_unicode_ci
轉(zhuǎn)換指南:https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-conversion.html
原答案:
MySQL 4.1 及更高版本的默認字符集為 UTF-8.你可以在你的 my.cnf 文件中驗證這一點，記得設(shè)置 both 客戶端和服務(wù)器(default-character-set 和 character-set-server).
MySQL 4.1 and above has a default character set of UTF-8. You can verify this in your my.cnf file, remember to set both client and server (default-character-set and character-set-server).
如果您有要轉(zhuǎn)換為 UTF-8 的現(xiàn)有數(shù)據(jù)，請轉(zhuǎn)儲您的數(shù)據(jù)庫，然后將其作為 UTF-8 重新導(dǎo)入，確保:
If you have existing data that you wish to convert to UTF-8, dump your database, and import it back as UTF-8 making sure:
- 在查詢/插入數(shù)據(jù)庫之前使用
SET NAMES utf8 - 在創(chuàng)建新表時使用
DEFAULT CHARSET=utf8 - 此時您的 MySQL 客戶端和服務(wù)器應(yīng)該是 UTF-8(參見
my.cnf).請記住，您使用的任何語言(例如 PHP)也必須是 UTF-8.某些版本的 PHP 將使用自己的 MySQL 客戶端庫，可能不支持 UTF-8.
- use
SET NAMES utf8before you query/insert into the database - use
DEFAULT CHARSET=utf8when creating new tables - at this point your MySQL client and server should be in UTF-8 (see
my.cnf). remember any languages you use (such as PHP) must be UTF-8 as well. Some versions of PHP will use their own MySQL client library, which may not be UTF-8 aware.
如果您確實要遷移現(xiàn)有數(shù)據(jù)，請記住先備份！當事情沒有按計劃進行時，可能會發(fā)生許多奇怪的數(shù)據(jù)截斷！
If you do want to migrate existing data remember to backup first! Lots of weird choping of data can happen when things don't go as planned!
一些資源:
- 完整的 UTF-8 遷移 (cdbaby.com)
- 關(guān)于 php 函數(shù)的 UTF-8 就緒性 的文章(注意其中的一些信息已過時)
- complete UTF-8 migration (cdbaby.com)
- article on UTF-8 readiness of php functions (note some of this information is outdated)
這篇關(guān)于如何讓 MySQL 正確處理 UTF-8的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！