教您如何將不同的MySQL字符集轉(zhuǎn)化成統(tǒng)一的字符集
作者:佚名
不同的MySQL字符集如何才能轉(zhuǎn)化成統(tǒng)一的字符集呢?這是很值得我們考慮的問題,下文就教您一個方法,可以輕松解決這個問題。
下面為您介紹的MySQL字符集處理方法是將不同的MySQL字符集,轉(zhuǎn)化成統(tǒng)一的字符集。 該方法供您參考,希望對您學(xué)習(xí)MySQL字符集方面能有所啟迪。
- After an upgrade to MySQL 4.1, the statement fails:
- mysql> SELECT SUBSTRING_INDEX(USER(),'@',1);
- ERROR 1267 (HY000): Illegal mix of collations
- (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE)
- for operation 'substr_index'
- The reason this occurs is that usernames are stored using UTF8 (see section 11.6 UTF8 for Metadata). As a result, the USER() function and the literal string '@' have different character sets (and thus different collations):
- mysql> SELECT COLLATION(USER()), COLLATION('@');
- +-------------------+-------------------+
- | COLLATION(USER()) | COLLATION('@') |
- +-------------------+-------------------+
- | utf8_general_ci | latin1_swedish_ci |
- +-------------------+-------------------+
- One way to deal with this is to tell MySQL to interpret the literal string as utf8:
- mysql> SELECT SUBSTRING_INDEX(USER(),_utf8'@',1);
- +------------------------------------+
- | SUBSTRING_INDEX(USER(),_utf8'@',1) |
- +------------------------------------+
- | root |
- +------------------------------------+
- Another way is to change the connection character set and collation to utf8. You can do that with SET NAMES 'utf8' or by setting the character_set_connection and collation_connection system variables directly.
表的編碼轉(zhuǎn)換可以用:(MySQL Version > 4.12)
- ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;
之前的版本可以用:
- ALTER TABLE tbl_name CHARACTER SET charset_name;
【編輯推薦】
責(zé)任編輯:段燃
來源:
互聯(lián)網(wǎng)