MySQL字元集中文亂碼終極解決方案和mysql查詢中文問題解決方案[轉貼]

MySQL字元集終極解決方案 [轉貼]

 

開來源資料庫MySQL從來都是中小企業構建web應用的首選，特別是和PHP配合簡直就是一對黃金搭檔，深受web開發人員的喜愛。但自從4.1以來MySQL加入了多字元集的支援，很多MySQL使用者發現中文居然不能使用了，顯示變成了一堆亂碼！以致於很多人還在使用3.24.58的老版本，最近上MySQL網站，發現居然不提供3.24版本的下載了，MySQL已經徹底放棄3.24版本了。好在我還留有一份windows版的copy，就當作紀念吧。
怎麼會產生亂碼現象的，怎麼解決？只要翻下網上的解決方案，馬上就可以得出答案：“在獲得串連之後執行一句set names 'gb2312'”，但這樣做的原因是什麼呢？總結一下我的經驗。

MySQL處理串連時，外部串連發送過來的SQL請求會根據以下順序進行轉換：
character_set_client           //客戶串連所採用的字元集
|
character_set_connection  //MySQL串連字元集
|
character_set_database    //資料庫所採用的字元集（表，列）
|
character_set_results        //客戶機顯示所採用的字元集

一. 產生亂碼的根本原因在於：
1.客戶機沒有正確地設定client字元集，導致原先的SQL語句被轉換成connection所指字元集，而這種轉換，是會丟失資訊的，如果client是utf8格式，那麼如果轉換成gb2312格式，這其中必定會丟失資訊，反之則不會丟失。一定要保證connection的字元集大於client字元集才能保證轉換不丟失資訊。
2. 資料庫字型沒有設定正確，如果資料庫字型設定不正確，那麼connection字元集轉換成database字元集照樣丟失編碼，原因跟上面一樣。

二.為什麼set names 'gb2312'就可以了呢
set names 'gb2312'相當於這三條語句:
set character_set_client = gb2312;
set character_set_connection = gb2312;
set character_set_results = gb2312;
這樣做的話，上述產生亂碼的原因1就不存在了，因為編碼格式都統一了，但是這樣做並不是萬金油。原因有：
1.你的client不一定是用gb2312編碼發送SQL的，如果編碼不是gb2312那麼轉換成gb2312就會產生問題。
2.你的資料庫中的表不一定是gb2312格式，如果不是gb2312格式而是其他的比如說latin1，那麼在儲存字元集的時候就會產生資訊丟失。

綜上，終極解決方案如下:
1.首先要明確你的用戶端時候何種編碼格式，這是最重要的（IE6一般用utf8，命令列一般是gbk，一般程式是gb2312)
2.確保你的資料庫使用utf8格式，很簡單，所有編碼通吃。
3.一定要保證connection字元集大於等於client字元集，不然就會資訊丟失，比如latin1<gb2312<gbk<utf8
若設定set character_set_client = gb2312，那麼至少connection的字元集要大於等於gb2312，否則就會丟失資訊
4.以上三步做正確的話，那麼所有中文都被正確地轉換成utf8格式儲存進了資料庫，為了適應不同的瀏覽器，不同的用戶端，你可以修改character_set_results來以不同的編碼顯示中文字型，由於utf8是大方向，因此web應用是我還是傾向於使用utf8格式顯示中文的。

以上就是我的心得了。附上串連源碼，現行設定，程式中就可以不考慮字元集問題了
include "conf/system.php";

class Connection {
private $conn;

function __construct() {
global $mysql_ipaddr, $mysql_port, $mysql_db, $mysql_user, $mysql_pass;

try {
$this->conn = new PDO("mysql:host=$mysql_ipaddr;port=$mysql_port;dbname=$mysql_db", $mysql_user, $mysql_pass);
} catch (PDOException $e) {
print "MySQL伺服器串連失敗: " . $e->getMessage() . "<br>";
die();
}
}

public function getConnection() {
if ($this->conn != null) {
$this->conn->query("set character_set_client = gb2312");    //用戶端使用gb2312格式
$this->conn->query("set character_set_connection = utf8"); //串連字元集使用utf8格式
$this->conn->query("set character_set_results = utf8");       //顯示字元集使用utf8格式
return $this->conn;
}
}

public function closeConnection() {
if ($this->conn != null) {
$this->conn = null;
}
}
}

 

 

我現在在mysql上遇到一個問題,我們的字元集是gb2312.在中文模糊尋找時,會有不相關的結果集.

從問題的根本原因分析，還有下面的問題。
例：
漢字“不”的第1、2位元組ascii值分別為：178與187
漢字“安”的第1、2位元組ascii值分別為：176與178
漢字“花”的第1、2位元組ascii值分別為：187與168
聰明的人已經看出來了：在字串“安花”中模糊尋找字元“不”字時，mysql系統也會認為兩者匹配!

出現這個問題的原因是：MySQL在查詢字串時是大小寫不敏感的，在編繹MySQL時一般以ISO-8859字元集作為預設的字元集，因此在比較過程中中文編碼字元大小寫轉換造成了這種現象。

方法一:
解決方案是對於包含中文的欄位加上"binary"屬性，使之作為二進位比較，例如將"name char(10)"改成"name char(10)binary"。

方法二:
如果你使用源碼編譯MySQL，可以編譯MySQL時使用--with--charset=gbk 參數，這樣MySQL就會直接支援中文尋找和排序了。

方法三:
可以使用 Mysql 的 locate 函數來判斷。以上述問題為例,使用方法為:
SELECT * FROM table WHERE locate(field,'李') > 0;
本站使用的就是這種方法，感覺還不錯。:P

方法四:
把您的Select語句改成這樣,SELECT * FROM TABLE WHERE FIELDS LIKE BINARY '%FIND%'即可!

升級的根本，如果想使用“正確”的字元集，還是先用mysqldump匯出成檔案，然後匯入。

 

轉載聲明：本文轉自http://hi.baidu.com/xhero2008/blog/item/8d5b2f3fe2c617e955e7234c.html/cmtid/bd79d354f5c1da54d1090692

===============================================================================

 

mysql查詢中文問題解決方案[轉貼]

原文：

http://blog.sina.com.cn/u/4909c13c010003va

Q:
我在寫一個查詢條件時的問題如下：
如我想寫一個欄位中包含“李”字的所有記錄
$str="李";
select * from table where field like '%$str%' ;
顯示的記錄中除了包含”李”字的記錄，還有不包含“李”字的記錄。為什嗎？
A:
在MySQL中，進行中文排序和尋找的時候，對漢字的排序和尋找結果是錯誤的。這種情況在MySQL的很多版本中都存在。如果這個問題不解決，那麼MySQL將無法實際處理中文。

　　出現這個問題的原因是：MySQL在查詢字串時是大小寫不敏感的，在編繹MySQL時一般以ISO-8859字元集作為預設的字元集，因此在比較過程中中文編碼字元大小寫轉換造成了這種現象。

方法一:
　　解決方案是對於包含中文的欄位加上"binary"屬性，使之作為二進位比較，例如將"name char(10)"改成"name char(10)binary"。

方法二:
　　如果你使用源碼編譯MySQL，可以編譯MySQL時使用--with--charset=gbk 參數，這樣MySQL就會直接支援中文尋找和排序了。

方法三:
　　可以使用 Mysql 的 locate 函數來判斷。以上述問題為例,使用方法為:
SELECT * FROM table WHERE locate(field,'李') > 0;
本站使用的就是這種方法，感覺還不錯。:P

方法四:
　　把您的Select語句改成這樣,SELECT * FROM TABLE WHERE FIELDS LIKE BINARY '%FIND%'即可!

http://blog.sina.com.cn/u/4909c13c010003va

Q:
我在寫一個查詢條件時的問題如下：
如我想寫一個欄位中包含“李”字的所有記錄
$str="李";
select * from table where field like '%$str%' ;
顯示的記錄中除了包含”李”字的記錄，還有不包含“李”字的記錄。為什嗎？
A:
在MySQL中，進行中文排序和尋找的時候，對漢字的排序和尋找結果是錯誤的。這種情況在MySQL的很多版本中都存在。如果這個問題不解決，那麼MySQL將無法實際處理中文。

　　出現這個問題的原因是：MySQL在查詢字串時是大小寫不敏感的，在編繹MySQL時一般以ISO-8859字元集作為預設的字元集，因此在比較過程中中文編碼字元大小寫轉換造成了這種現象。

方法一:
　　解決方案是對於包含中文的欄位加上"binary"屬性，使之作為二進位比較，例如將"name char(10)"改成"name char(10)binary"。

方法二:
　　如果你使用源碼編譯MySQL，可以編譯MySQL時使用--with--charset=gbk 參數，這樣MySQL就會直接支援中文尋找和排序了。

方法三:
　　可以使用 Mysql 的 locate 函數來判斷。以上述問題為例,使用方法為:
SELECT * FROM table WHERE locate(field,'李') > 0;
本站使用的就是這種方法，感覺還不錯。:P

方法四:
　　把您的Select語句改成這樣,SELECT * FROM TABLE WHERE FIELDS LIKE BINARY '%FIND%'即可!

轉載聲明：本文轉自http://www.playhosts.com/bbs/read.php?tid=12357

===============================================================================

 

Mysql 字元集轉換及版本升級/降級的詳細教程 [轉貼]

原帖地址 http://club.muzone.cn/viewthread.php?tid=28605

MySQL 4.1開始，對多語言的支援有了很大變化 (這導致了問題的出現)。儘管大部分的地方 (包括個人使用和主機供應商)，MySQL 3、4.0 仍然佔主導地位；但 MySQL 4.1 乃至5.0是 MySQL 官方推薦的資料庫，已經有主機供應商開始提供並將會越來越多；因為 latin1 在許多地方 (下邊會詳細描述具體是哪些地方) 作為預設的字元集，成功的蒙蔽了許多 PHP 程式的開發人員和使用者，掩蓋了在中文等語言環境下會出現的問題。

MySQL 4.1開始把多國語言字元集分的更加詳細，所以導致資料庫遷移，或則dz論壇升級到4.0後（dz4.0開始使用gbk或utf-8編碼）出現亂碼問題。

MySQL 4.1的字元集支援(Character Set Support)有兩個方面：字元集(Character set)和排序方式(Collation)。對於字元集的支援細化到四個層次: 伺服器(server)，資料庫(database)，資料表(table)和串連(connection)。

查看系統的字元集和排序方式的設定可以通過下面的兩條命令：

引用:mysql> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
7 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'collation_%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

MySQL 4.1 對於字元集的指定可以細化到一台機器上安裝的 MySQL，其中的一個資料庫，其中的一張表，其中的一欄，應該用什麼字元集。但是，傳統的 Web 程式在建立資料庫和資料表時並沒有使用那麼複雜的配置，它們用的是預設的配置，那麼，預設的配置從何而來呢？

編譯 MySQL 時，指定了一個預設的字元集，這個字元集是 latin1；
安裝 MySQL 時，可以在設定檔 (my.ini) 中指定一個預設的的字元集，如果沒指定，這個值繼承自編譯時間指定的；
啟動 mysqld 時，可以在命令列參數中指定一個預設的的字元集，如果沒指定，這個值繼承自設定檔中的；
此時 character_set_server 被設定為這個預設的字元集；
當建立一個新的資料庫時，除非明確指定，這個資料庫的字元集被預設設定為 character_set_server；
當選定了一個資料庫時，character_set_database 被設定為這個資料庫預設的字元集；
在這個資料庫裡建立一張表時，表預設的字元集被設定為 character_set_database，也就是這個資料庫預設的字元集；
當在表內設定一欄時，除非明確指定，否則此欄預設的字元集就是表預設的字元集；
這個字元集就是資料庫中實際儲存資料採用的字元集，mysqldump 出來的內容就是這個字元集下的；
當我們按照原來的方式通過PHP存取MySQL資料庫時，就算設定了表的預設字元集為utf8並且通過UTF-8編碼發送查詢，你會發現存入資料庫的仍然是亂碼。問題就出在這個connection串連層上。
想要進行“正確”的儲存和得到“正確”的結果，最方便的是在所有query開始之前執行一下：

SET NAMES 'gbk';
其中gbk是資料庫字元集。

它相當於下面的三句指令：
SET character_set_client = gbk;
SET character_set_results = gbk;
SET character_set_connection = gbk;

4.1和5.0預設使用的是latin1字元集(木頭:媽的,老外真霸道,妄想讓全世界都是使用瑞典字元集嗎)
如果我們只想使用gbk字元集儲存和擷取資料,
我們在編譯mysql 4.1和 5.0的時候，需要注意在my.ini或者my.cnf中添加兩處參數

CODE:[mysqld]
default-character-set=utf8{
CopyCode(document.getElementById('code1'));
}" href="javascript:">[複製到剪下板]

CODE:#settings for clients (connection, results, clients)
[mysql]
default-character-set=utf8{
CopyCode(document.getElementById('code2'));
}" href="javascript:">[複製到剪下板]

下面我們來說主題，如何轉換資料庫字元集
兩種方法，

引用:第一種----更改儲存字元集
主要的思想就是把資料庫的字元集有latin1改為gbk，big5,或者utf8; 以下操作必須擁有主機許可權。假設當前操作的資料庫名為：database

匯出
首先需要把資料導為mysql4.0的格式，具體的命令如下：
mysqldump -uroot -p --default-character-set=latin1 --set-charset=gbk --skip-opt databse > d4.sql

--default-characte-set 以前資料庫的字元集，這個一般情況下都是latin1的，
--set-charset 匯出的資料的字元集，這個可以設定為gbk，utf8,或者big5
匯入
首先使用下面語句建立一個GBK字元集的資料庫（test）

CREATE DATABASE `d4` DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
然後把剛才匯出的資料匯入到當前的資料庫中就ok了。

mysql -uroot -p --default-character-set=gbk -f d4<d4.sql
通過以上的匯出和匯入就把資料庫的字元集改為正確的儲存方式了。

其中d4為建立庫的名稱，d4.sql為匯出檔案的名字

但是這種方法，探索資料庫資料存放區量無端變大30%，真是鬱悶

引用:另外一種其實原理相同，但是需要手動操作，一般用於第一種方法失敗後的選擇
不過這種方法如果資料庫很大，估計很難做，因為光開啟檔案就能讓你死機

首先還是用phpmyadmin或者用mysql本身的dump匯出 .sql檔案

然後用UltraEdit開啟你備份的所有xxxx.sql檔案，尋找CODE:DEFAULT CHARSET=latin1{
CopyCode(document.getElementById('code3'));
}" href="javascript:">[複製到剪下板]

latin1這裡也許是別的，反正是你不想要的，要轉成gbk或者big5的字元集
把這個替換為“空”
在尋找CODE:CREATE TABLE cdb_sessions (
sid char(6) character set latin1 collate latin1_bin NOT NULL default '',
ip1 tinyint(3) unsigned NOT NULL default '0',
ip2 tinyint(3) unsigned NOT NULL default '0',
ip3 tinyint(3) unsigned NOT NULL default '0',
ip4 tinyint(3) unsigned NOT NULL default '0',
uid mediumint(8) unsigned NOT NULL default '0',
username char(15) NOT NULL default '',
groupid smallint(6) unsigned NOT NULL default '0',
styleid smallint(6) unsigned NOT NULL default '0',
invisible tinyint(1) NOT NULL default '0',
`action` tinyint(1) unsigned NOT NULL default '0',
lastactivity int(10) unsigned NOT NULL default '0',
fid smallint(6) unsigned NOT NULL default '0',
tid mediumint(8) unsigned NOT NULL default '0',
nickname char(15) NOT NULL default '',
UNIQUE KEY sid (sid)
) ENGINE=HEAP MAX_ROWS=1000;{
CopyCode(document.getElementById('code4'));
}" href="javascript:">[複製到剪下板]

替換為CODE:CREATE TABLE `cdb_sessions` (
`sid` char(6) binary NOT NULL default '',
`ip1` tinyint(3) unsigned NOT NULL default '0',
`ip2` tinyint(3) unsigned NOT NULL default '0',
`ip3` tinyint(3) unsigned NOT NULL default '0',
`ip4` tinyint(3) unsigned NOT NULL default '0',
`uid` mediumint(8) unsigned NOT NULL default '0',
`username` char(15) NOT NULL default '',
`groupid` smallint(6) unsigned NOT NULL default '0',
`styleid` smallint(6) unsigned NOT NULL default '0',
`invisible` tinyint(1) NOT NULL default '0',
`action` tinyint(1) unsigned NOT NULL default '0',
`lastactivity` int(10) unsigned NOT NULL default '0',
`fid` smallint(6) unsigned NOT NULL default '0',
`tid` mediumint(8) unsigned NOT NULL default '0',
`nickname` char(15) NOT NULL default '',
UNIQUE KEY `sid` (`sid`)
) TYPE=HEAP MAX_ROWS=2000;{
CopyCode(document.getElementById('code5'));
}" href="javascript:">[複製到剪下板]

這一步更為簡單的辦法就是刪除掉關於cdb_sessions表的這一段，將來全新裝一個d4，將這個表匯出
將其內容複寫，粘貼到 sql檔案的最後面[code][/code]

儲存後，再把這個sql檔案匯入到你的庫中

就OK了

用這兩種方法就可以很方便的把4.1和5.0的mysql資料庫降級到4.0
簡單的過程就是
A匯出4.1/5.0的庫
B進行處理，轉換成gbk字元集
C徹底卸載4.1或者5.0
D安裝4.0.26
E然後匯入處理完的庫

降級的時候匯出庫可以用這個方法
mysqldump -uroot -p --default-character-set=latin1 --set-charset=gbk --skip-opt databse --compatible=mysql40 > d4.sql
這樣匯出的就是4.0的庫勒

至於mysql版本的升級，
如果資料檔案中有中文資訊，那麼將MySQL 4.0的資料檔案，直接拷貝到MySQL 4.1中就是不可以的，即便在my.ini中設定了default-character-set為正確的字元集。雖然貌似沒有問題，但MySQL 4.1的字元集有一處非常惱人的地方，以gbk為例，原本MySQL 4.0資料中varchar,char等長度都會變為原來的一半，這樣儲存中文容量不變，而英文的儲存容量就少了一半。這是直接拷貝資料檔案帶來的最大問題。

所以，升級的根本，如果想使用“正確”的字元集，還是先用mysqldump匯出成檔案，然後匯入。

轉自：http://www.sd9981.com/mysql/?p=22

轉載聲明：本文轉自http://www.playhosts.com/bbs/read.php?tid=12357

===============================================================================