MYSQL編碼問題的匯總

MYSQL編碼問題的匯總

上一篇
/
下一篇
 2007-11-05 14:39:12

/ 個人分類：他山之石

查看( 882 )
/
評論( 1 )
/
評分( 0
/ 0
)

全文摘自[http://hi.baidu.com/kkwtre/blog/item/c5ef108254b732a30cf4d2ba.html]

1.   常規字元集


和校對

MySQL5.1能夠做這些事情：

·使用多種字元集來儲存字串

·使用多種校對規則來比較字串

·在同一台伺服器、同一個資料庫


或甚至在同一個表中使用不同字元集或校對規則來混合字串

·允許定義任何層級的字元集和校對規則

2. MySQL


中的字元集和校對

MySQL伺服器能夠支援多種字元集。可以使用SHOW CHARACTER SET語句列出可用的字元集：

mysql> SHOW CHARACTER SET

3. 確定預設字元集和校對

字元集和校對規則有4個層級的預設設定：伺服器級、資料庫級、表級和串連級。以下描述可能顯得複雜，但是在實際應用中可以發現使用多種層級會使結果自然而明顯。

MySQL按照如下方法


確定伺服器字元集和伺服器校對規則：

·當伺服器啟動時根據有效選項設定

·根據運行時的設定值

在
伺服器層級，確定方法很簡單。當啟動mysqld時，根據使用的初始選項設定來確定伺服器字元集和
校對規則。可以使用--default-character-set設定字元集，並且可以在字元集後面為
校對規則添加--default-collation。如果沒有指定一個字元集，那就與--default-character-set=latin1相
同。

當前的伺服器字元集和校對規則可以用作character_set_server和collation_server系統變數的值。在運行時能夠改變這些變數的值。

 

3.2. 資料庫字元集和校對

每一個資料庫有一個資料庫字元集和一個資料庫校對規則，它不能夠為空白。CREATE DATABASE和ALTER DATABASE語句有一個可選的子句來指定資料庫字元集和校對規則：
CREATE DATABASE db_name
     [[DEFAULT] CHARACTER SET charset_name]
     [[DEFAULT] COLLATE collation_name]

ALTER DATABASE db_name
     [[DEFAULT] CHARACTER SET charset_name]
     [[DEFAULT] COLLATE collation_name]
例如：

CREATE DATABASE db_name
     DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

MySQL這樣選擇資料庫字元集和資料庫校對規則：

·如果指定了CHARACTER SET X和COLLATE Y，那麼採用字元集X和校對規則Y。

·如果指定了CHARACTER SET X而沒有指定COLLATE Y，那麼採用CHARACTER SET X和CHARACTER SET X的預設校對規則。

·否則，採用伺服器字元集和伺服器校對規則。

如果在CREATE TABLE語句中沒有指定表字元集和校對規則，則使用資料庫字元集和校對規則作為預設值。

3.3. 表字元集和校對

每一個表有一個表字元集和一個校對規則，它不可為空。為指定表字元集和校對規則，CREATE TABLE 和ALTER TABLE語句有一個可選的子句：
CREATE TABLE tbl_name (column_list)
     [DEFAULT CHARACTER SET charset_name [COLLATE collation_name]]

ALTER TABLE tbl_name
     [DEFAULT CHARACTER SET charset_name] [COLLATE collation_name]
例如：

CREATE TABLE t1 ( ... )
DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;

MySQL按照下面的方式選擇表字元集和 校對規則：

·如果指定了CHARACTER SET X和COLLATE Y，那麼採用CHARACTER SET X和COLLATE Y。

·如果指定了CHARACTER SET X而沒有指定COLLATE Y，那麼採用CHARACTER SET X和CHARACTER SET X的預設校對規則。

·否則，使用資料庫字元集和校對規則作為預設值。

如果在列定義中沒有指定列字元集和校對規則，則預設使用表字元集和校對規則。表字元集和校對規則是MySQL的擴充;在標準SQL中沒有。

3.4. 列字元集和校對

每一個“字元”列（即，CHAR、VARCHAR或TEXT類型的列）有一個列字元集和一個列 校對規則，它不可為空。列定義文法有一個可選子句來指定列字元集和校對規則：
col_name {CHAR | VARCHAR | TEXT} (col_length)
     [CHARACTER SET charset_name [COLLATE collation_name]]
例如：

CREATE TABLE Table1

(

     column1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci

);

MySQL按照下面的方式選擇列字元集和校對規則：

·如果指定了CHARACTER SET X和COLLATE Y，那麼採用CHARACTER SET X和COLLATE Y。

·如果指定了CHARACTER SET X而沒有指定COLLATE Y，那麼採用CHARACTER SET X和CHARACTER SET X的預設校對規則。

·否則，採用表字元集和伺服器校對規則。

CHARACTER SET和COLLATE子句是標準的SQL。

舉例子：

樣本1：表和列定義

CREATE TABLE t1
(
     c1 CHAR(10) CHARACTER SET latin1 COLLATE latin1_german1_ci
) DEFAULT CHARACTER SET latin2 COLLATE latin2_bin;
在這裡我們有一個列使用latin1字元集和latin1_german1_ci校對規則。是顯式的定義，因此簡單明了。需要注意的是，在一個latin2表中儲存一個latin1列不會存在問題。

樣本2：表和列定義

CREATE TABLE t1
(
     c1 CHAR(10) CHARACTER SET latin1
) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;
這
次我們有一個列使用latin1字元集和一個預設校對規則。儘管它顯得自然，預設校對規則卻不是表級。相反，因為latin1的預設校對規則總是
latin1_swedish_ci，列c1有一個校對規則latin1_swedish_ci（而不是latin1_danish_ci）。

樣本3：表和列定義

CREATE TABLE t1
(
     c1 CHAR(10)
) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;
我們有一個列使用一個預設字元集和一個預設校對規則。在這種情況下，MySQL尋找表層級來確定列字元集和 校對規則。因此，列c1的字元集是latin1，它的 校對規則是latin1_danish_ci。

樣本4：資料庫、表和列定義

CREATE DATABASE d1
     DEFAULT CHARACTER SET latin2 COLLATE latin2_czech_ci;
USE d1;
CREATE TABLE t1
(
     c1 CHAR(10)
);
我
們建立了一個沒有指定字元集和校對規則的列。我們也沒有指定表級字元集和校對規則。在這種情況下，MySQL尋找資料庫級的相關設定。（資料庫的設定變為
表的設定，其後變為列的設定。）因此，列c1的字元集為是latin2，它的 校對規則是latin2_czech_ci。

3.6. 串連字元集和校對

一些字元集和校對規則系統變數與用戶端和伺服器的互動有關。
·伺服器字元集和校對規則表示為character_set_server和collation_server變數的值。

·預設資料庫的字元集和校對規則表示為character_set_database和collation_database變數的值。

考慮什麼是一個“串連”：它是串連伺服器時所作的事情。用戶端發送SQL語句，例如查詢，通過串連發送到服務
器。伺服器通過串連發送響應給用戶端，例如結果集。對於用戶端串連，這樣會導致一些關於串連的字元集和
校對規則的問題，這些問題均能夠通過系統變數來解決：

·當查詢離開用戶端後，在查詢中使用哪種字元集？

答：伺服器使用character_set_client變數作為用戶端發送的查詢中使用的字元集。

·伺服器接收到查詢後應該轉換為哪種字元集？

答：轉換時，伺服器使用character_set_connection和collation_connection系統變數。
它將用戶端發送的查詢從character_set_client系統變數轉換到character_set_connection

·伺服器發送結果集或返回錯誤資訊到用戶端之前應該轉換為哪種字元集？

答：character_set_results變數指示伺服器返回查詢結果到用戶端使用的字元集。包括結果資料，
例如列值和結果中繼資料（如列名）。

有兩個語句影響串連字元集：
SET NAMES 'charset_name'
SET CHARACTER SET charset_name
SET NAMES顯示用戶端發送的SQL語句中使用什麼字元集。

SET NAMES 'x'語句與這三個語句等價：
mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET character_set_connection = x;

摘自：
http://java.ccidnet.com/art/3737/20060605/571481_1.html

mysql 字元集

一. 顯示字元集

mysqladmin -uroot -proot variables | grep character

| 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/local/share/mysql/charsets/ |

資料庫預設使用latin1 (ISO-8859)

二. 配置字元集

1. 建庫時配置字元集

create database testxxx default charset=utf8

3. 建表時配置字元集

CREATE TABLE `t_agent` (

`ID` smallint(5) NOT NULL

) DEFAULT CHARSET=utf8 ;

4. 修改字元集

Windows平台

windows下的mysql設定檔是my.ini,一般在c:/windows/my.ini或者c:/winnt/my.ini可以直接在這個檔案裡面加上

default-character-set=gbk #或gb2312,big5,utf8

然後重新啟動mysql

service mysql restart

或

/etc/init.d/mysql restart

或用其他


方法重新啟動，就生效了。

[編輯]Unix平台

linux


下的mysql設定檔是my.cnf，一般是/etc/my.cnf，如果找不到可以用find命令找一下：

find / -iname my.cnf

在這個檔案裡面加上

default-character-set=gbk #或gb2312,big5,utf8

然後重新啟動mysql

三. 配置排序字元集

myisamchk -r -q --set-character-set=charset

摘自：http://imysql.cn/?q=node/20

小談MySQL字元集
首先,這片文章純粹是我的個人經驗之談,適用於我常見的環境及項目中.
個人建議,資料庫字元集盡量使用utf8(HTML


頁面對應的是utf-8),以使你的資料能很順利的實現遷移,因為utf8字元集是目前最適合於實現多種不同字元集之間的轉換的字元集,儘管你在命令列工具上可能無法正確查看資料庫中的內容,我依然強烈建議使用utf8作為預設字元集.
接下來是完整的一個例子：
1.建立資料庫表

mysql>CREATE DATABASE IF NOT EXISTS my_db default charset utf8 COLLATE utf8_general_ci;
#注意後面這句話 "COLLATE utf8_general_ci",大致意思是在排序時根據utf8校正集來排序
#那麼在這個資料庫下建立的所有資料表的預設字元集都會是utf8了

mysql>create table my_table (name varchar(20) not null default '')type=myisam default charset utf8;
#這句話就是建立一個表了,制定預設字元集為utf8

2.寫資料

例子1是通過php


直接插入資料:
a.php
<?php
mysql_connect('localhost','user','password');
mysql_select_db('my_db');

//請注意,這步很關鍵,如果沒有這步,所有的資料讀寫都會不正確的
//它的作用是設定本次資料庫聯結過程中,資料轉送的預設字元集
//其他程式設計語言/介面也類似，例如 .net/c#/odbc
//jdbc則設定連接字串為類似"jdbc:mysql://localhost/db?user=user&password=123456&useUnicode=true&characterEncoding=UTF-8"

mysql_query("set names utf8;");
//必須將gb2312(本地編碼)轉換成utf-8,也可以使用iconv()函數
mysql_query(mb_convet_encoding("insert into my_table values('測試');", "utf-8", "gb2312"));
?>

例子是通過頁面提交插入資料2:
b.php
<?php
//輸出本頁編碼為utf-8
header("content-type:text/html; charset=utf-8");

mysql_connect('localhost','user','password');
mysql_select_db('my_db');

mysql_query("set names utf8;");
if(isset($_REQUEST['name'))
{
//由於上面已經指定本頁字元集為utf-8了,因此無需轉換編碼
mysql_query(sprintf("insert into my_table values('%s');", $_REQUEST['name']));
}

$q = mysql_query("select * from my_table");
while($r = mysql_fetch_row($q))
{
print_r($r);
}
?>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<form action="" method="post">
<input type="text" name="name" value="">
<input type="submit" value='submit'>
</form>

自此,使用utf8字元集的完整的例子結束了.
如果你想使用gb2312編碼,那麼建議你使用latin1作為資料表的預設字元集,這樣就能直接用中文在命令列工具中插入資料,並且可以直接顯示出來.而不要使用gb2312或者gbk等字元集,如果擔心查詢排序等問題,可以使用binary屬性約束,例如:
create table my_table ( name varchar(20) binary not null default '')type=myisam default charset latin1;

附1：舊資料升級辦法
以原來的字元集為latin1為例，升級成為utf8的字元集。原來的表: old_table (default charset=latin1)，新表：new_table(default charset=utf8)。
第一步：匯出舊資料
mysqldump --default-character-set=latin1 -hlocalhost -uroot -B my_db --tables old_table > old.sql
第二步：轉換編碼(類似unix/linux環境下)
iconv -t utf-8 -f gb2312 -c old.sql > new.sql
或者可以去掉 -f 參數，讓iconv自動判斷原來的字元集
iconv -t utf-8 -c old.sql > new.sql
在這裡，假定原來的資料預設是gb2312編碼。
第三步：匯入
修改old.sql，在插入/更新語句開始之前，增加一條sql語句：

"SET NAMES utf8;"，儲存。
mysql -hlocalhost -uroot my_db < new.sql
大功告成！！

附2：支援查看utf8字元集的MySQL用戶端有
1.) MySQL-Front，據說這個項目已經被MySQL AB勒令停止了，不知為何，如果國內還有不少破解版可以下載（不代表我推薦使用破解版 :-P）。
2.) Navicat，另一款非常不錯的MySQL用戶端，漢化版剛出來，還邀請我試用過，總的來說還是不錯的，不過也需要付費。
3.) PhpMyAdmin，開源


的php項目，非常好。
4.) Linux下的終端工具（Linux terminal），把終端的字元集設定為utf8，串連到MySQL之後，執行 SET NAMES UTF8; 也能讀寫utf8資料了。

6步解決mysql字元集問題(php utf-8亂碼問題)   大 | 中 | 小   Tags： apache


, php , 伺服器配置
6步解決mysql字元集問題(php utf-8亂碼問題)

1.my.ini:
[mysql]
default-character-set=utf8
[mysqld]
default-character-set=utf8
default-storage-engine=MyISAM
在[mysqld]下加入:
default-collation=utf8_bin
init_connect='SET NAMES utf8'

2.在需要做資料庫操作的php程式前面加上
mb_internal_encoding('utf-8');

3.create table最後邊加上ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin

4.phpMyAdmin/config.inc.php
$cfg['DefaultCharset'] = 'utf-8';
$cfg['RecodingEngine'] = 'iconv';

5.phpMyAdmin/libraries/string.lib.php
將第41行的
mb_internal_encoding($GLOBALS['charset']);
改為：mb_internal_encoding('utf-8');

6.phpAdmin匯出資料時
把"二進位地區使用十六進位顯示"的勾去掉

簡單總結下：

執行 SHOW VARIABLES LIKE 'Charater_set%'可查看MySQL關於Character set的各個設定：

影響伺服器和用戶端資料轉送的有

character_set_client 決定伺服器接受用戶端發送的資料時採用的字元集
character_set_connection 伺服器將用戶端傳來的資料由character_set_client轉換到character_set_connection
character_set_results 決定伺服器向用戶端發送資料採用的字元集

網上大部分文章中提到兩種方法指定傳輸時使用的字元集

SET NAMES 'charset_name'
SET CHARACTER SET charset_name

SET NAMES 'x' 等效於
SET character_set_client = x;
SET character_set_results = x;
SET character_set_connection = x;

SET CHARACTER SET 'x'   等效於
SET character_set_client = x;
SET character_set_results = x;
SET collation_connection = @@collation_database; --資料庫的collation

所
以，如果client和server端的預設character set不一致，則需要在讀取和寫入資料之前先query一句SET
CHARACTER SET 'x'或SET NAMES 'x'，'x'為client使用的character
set。也可以使用mysqli_set_charset (PHP 5 >= 5.1.0RC1)

摘自
http://kubbs.net/showart.asp?art_id=16989&cat_id=34

mysql4.1及其之後的版本，對字元集的支援分為四個層次: 伺服器(server)，資料庫(database)，資料表(table)和串連(connection)：
character_set_server：這是設定伺服器使用的字元集
character_set_client ：這是設定用戶端發送查詢使用的字元集
character_set_connection ：這是設定伺服器需要將收到的查詢串轉換成的字元集
character_set_results ：這是設定伺服器要將結果資料轉換到的字元集，轉換後才發送給用戶端

整個過程：
- client(如php程式)發送一個查詢；
- 伺服器收到查詢，將查詢串從character_set_client 轉換到character_set_connection，然後執行轉換後的查詢；
- 伺服器將結果資料轉換到character_set_results字元集後發送回用戶端。

你可以用下邊兩條命令查看一下系統的字元集和排序方式設定：
mysql> SHOW VARIABLES LIKE ‘character_set_%’;
mysql> SHOW VARIABLES LIKE ‘collation_%’;

mysql 預設用的字元集是latin1,串連校對用的latin1-_swedish_ci。看到這兒你應試有點明白了，
我們通過php發送的查詢一般是utf8或者GBK,GB2312,而它預設的是latin1,所以用phpmyadmin查看或
者直接進資料庫裡查看資料，都是一些亂碼。（存取出來放在網頁上大多是正常的。）phpmyadmin
在對mysql4.0以下會使用內建的庫來實現字元集的轉換，從而支援多語言版本，而對於4.1以上則完
全交給Mysql。所以以上幾個層次的轉換如果有字元集不匹配的情況，出現亂碼就很容易理解了。
說了半天，其實你只要把上述變數設定一致了，不管是直接在資料庫裡查看，還是在phpmyadmin裡
查看都不會出現中文亂碼了。

假如你的系統和php網頁是utf8編碼，你可以將mysql的編碼由預設的latin1改為utf8.
編輯mysql的設定檔，/etc/my.cnf，在[mysqld]中設定default-character-set=utf8即可。

摘自
http://www.syict.com/html/2/2-10322.html

的一部分

a.   Mysql一定有一個字元集，可以通過啟動時加參數指定 ，也可以編譯時間指定，也可以在設定檔裡指定。
Mysql伺服器字元集，只是做為資料庫級的預設值。建立資料庫時，你可以指定字元集，如果沒指定，
就使用伺服器的字元集。同理，建立表時，你可以指定表級的字元集，如果沒指定，使用資料庫的字元集
做為表的字元集。建立列時，你可以指定某列的字元集，如果沒指定，就使用表的字元集。 通常情況下，
您只需設定伺服器級的字元集，其它的資料庫級，表級，以及列級的字元集，都繼承自伺服器級字元集。
由於UTF8是最廣的字元集，所以，一般情況下，我們設定Mysql伺服器級的字元集為UTF8！

b.   HTML是文字檔。儲存HTML檔案的時候，需要使用一個編碼，並且，在HTML檔案裡，也使用HTML文法，
指定了該檔案所使用的編碼。如果HTML檔案沒有指定編碼，則瀏覽器自動識別檔案的編碼。
如果HTML指定了編碼，則瀏覽器使用HTML指定的編碼。 通常情況下，HTML檔案指定的charset和HTML檔案
自身的編碼是一致的，但也有不一致的情況，如果不一致，就會導致網頁亂碼(此處亂碼，只和文字檔有關，
和資料庫無關。)使用專門的網頁編輯工具（比如Dreamwave），會自動根據網頁中的charset值來編碼檔案。

c.php+mysql的字元集問題

要保證不亂碼（包括瀏覽器顯示不是亂碼），就必須將三個編碼統一：
一是網頁自身的編碼，
二是HTML裡指定的編碼（即建議瀏覽器顯示所用的編碼），
三是PHP告訴Mysql的編碼
(包括character_set_client和character_set_results)。

第一和第二個編碼，如果使用DW之類的編輯器寫的網頁，通常是一致的，但用記事本寫的網頁，有可能不一致。 瀋陽化工學院.學生站,E ] /&C O1a T"]
第三個編碼，需要手工通知Mysql。這步可以通過在PHP裡使用mysql_query(“set names characterX”)來實現。

總結：
1.資料庫盡量使用utf8儲存（修改/etc/my.cnf，在[mysqld]段加上default-character-set=utf8）
(已有的資料庫，先轉成UTF8格式)

2.PHP程式在查詢資料庫之前，執行mysql_query(“set names xxxx”);
其中xxxx是你網頁的編碼(charset=xxxx)，如果網頁中charset=utf8，則xxxx=utf8，
如果網頁中charset=gb2312，則xxxx=gb2312，幾乎所有WEB程式，都有一段串連資料庫的公用代碼


，
放在一個檔案裡，在這檔案裡，加入mysql_query(“set names”)就可以了。

3.PHPMyAdmin不需要做改動。

4.需要注意的是，為保證網頁實際編碼(Windows儲存對話方塊裡的編碼)和他聲明的編碼(charset=?)是一致的，請用DW之類的工具做網頁。