再談MySQL更新出現亂碼問題的解決

??璐達?http://www.wujianrong.com/archives/2007/05/mysql_10.html

浠ュ????杩?涓?绡??充?MySQL??绾у??.1?虹?頒貢??濡?浣?瑙ｅ?崇??甯?瀛?锛?浠?????璁茶堪浜?褰?浣?瀵煎?ョ???版???芥?ｅ父浜?锛?璇ユ??涔?浣跨?ㄥ??ySQL4.1浠ュ????????澶? 璇?瑷??瑰???浣???濡????ㄥ?煎?ョ??杩?绋?涓?灏遍???頒?瀛?绗???????棰?锛?璇ユ??涔??????灏辨????璁轟???涔?瀵煎?ワ???涔????懼??绗???锛??芥??涔辯??锛?浣???浣??版???ョ??浠諱?涓??℃?版???芥病?? ??棰?锛?杩?灏變?????绾у??璋??村??绗???????棰?浜?锛??????ㄥ?煎?哄?煎?ョ??宸ョ?涓?灏遍??瑕???????

UP杩?涓ゅぉ浼拌?¤?杩?涓???棰????劇??澶???锛??界?朵????懼?扮??????涓????ㄤ??版??搴?锛?浣???杩???杩???????绾х??杩?绋?璁闆?涓??ャ??浠?澶?p????涓?娆¤?哄??濡?浣???绾у??杞??㈢??锛???灏辮?闆?涓?澶?娓?妤?浜???

????宸辮??楠?????????涓ょ??瑰?

1锛?????瑕???浣????ョ???????版??搴???浠惰???闆?版?扮???版??搴???褰?涓?锛?浠?涔??戒??ㄤ慨?癸?杩?涓??跺??杈??哄?璇ユ??姝ｅ父??锛?榛?璁ら???ㄤ?latin1瀛?绗?????杩?澶ф?????绠??????瑰?浜?锛?涓?杩?杩?绉??瑰?涓?澶?濂界???版?瑰?ㄤ?涓?????ヨ?杞??㈠??绗???锛?寰???楹葷????

2锛???璁句?瑕?浠?MySQL4.0浠ヤ???????绾у??ySQL4.1浠ヤ?????锛?骞舵??纭???瀹???瑕???瀛?绗?????gb2312锛??ｄ?????姝ラ?ゆ??

a锛??ㄥ???ョ???版??搴?涓?瀵煎?轟?瑕????版??搴?锛???浠ヤ嬌?ㄤ??㈢???戒護锛?

$mysqldump -u username -p password databasename >xxx.sql

b锛?淇??瑰?煎?虹??sql??浠訛?棣???灏?瀵煎?虹???版??琛ㄥ??涓?榛?璁ゅ??绗???gb2312锛???浠ュ??i涓????ㄤ??㈢???挎?㈠?戒護

% s/engine=MyISAM;/engine=MyISAM DEFAULT CHARSET=GB2312;/g

褰??朵???浠ヤ嬌?ㄤ???娆㈢??缂?杈??ㄦ?ユ?挎????舵?℃??灏?杩?涓?sql??浠惰漿????gb2312缂????煎?????浠躲??浣???浠ュ??file????璇ユ??浠剁??瀛?绗???锛?濡?????ISO8859??锛??ｈ?涓???锛?濡?????Non-ISO?煎?锛??ｅ氨蹇?椤昏漿?????浠ヤ嬌?ㄤ??㈢???戒護

iconv -c -t gb2312 -o outout.sql xxx.sql

UP瀵煎?虹???版??灏辨??Non锛?ISO?煎???

c锛?杩??ユ?扮???版??搴????″?????寤洪??瑕????版??搴?

CODE:

[Copy to clipboard]

mysqladmin -uroot -p create databasename

d锛??ㄥ?煎?ユ?版??涔???锛?????榛?璁ょ??瀛?绗???锛???浠ヤ嬌?ㄤ??㈢???規?

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)

榛?璁ょ??瀛?绗????芥??latinn1??锛?杩?涓??跺??锛?浣???瑕?灏?latin1杞??㈡??gb2312锛??規?濡?涓?

mysql> set NAMES 'utf8';

Query OK, 0 rows affected (0.00 sec)

mysql> set character_set_database=utf8;

Query OK, 0 rows affected (0.00 sec)

mysql> set character_set_server=utf8;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'character%';

+--------------------------+--------------------------------+

| 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?? | utf8?????????????????? |

| character_set_system?? | utf8?????????????????? |

| character_sets_dir???? | /usr/share/mysql/charsets/ |

+--------------------------+--------------------------------+

8 rows in set (0.00 sec)

杩??舵?版??搴?瀛?绗???宸茬?淇??癸???浠ュ?煎?ユ?版??浜?

mysql>source /path/to/xxx.sql

?跺???ㄤ???缃?椤典腑锛??ㄨ??ユ?版????锛???涓?涓??㈢??涓??℃??浠?

mysql_query(??set NAMES 'utf8'");

搴?璇ュ氨??浠ヤ?锛?褰??朵???缃?椤電?????韬?搴?璇ヤ?瑕?寮?gb2312????

?ｄ?UP涓轟?涔?浼?瑕????句袱澶╁???涓???浠???姝ラ?ゅ???規?????棰?锛???????浣?绯葷?????棰?锛???浣?绯葷?锛?ubuntu锛?

?? 棰?灏卞?虹?闆??conv杞??㈢??杩?绋?涓?锛???瑕?杞??㈡??GB2312锛??ｄ?涓???灏辨??浜?绌虹?斤??????風????浠訛??ㄥ?朵?Linux??浣?绯葷?涓?灏變?浼??虹?拌?涓???棰?锛??鵑??寮? ubuntu骞朵?????GB2312锛?杩???????????锛?涓?浜烘病??浣跨?ㄨ?ubuntu锛?涓?澶?娓?妤?浠???瀛?绗?????瀛?浣???????濡?浣???锛?涓?杩??蟲?ユ???ㄨ??? redhat锛?fedora锛?suse纭?瀹??藉?ㄥ??绗?????涓???瀛?浣??歸?㈡??浜?娆?缂恒??杩?涓??歸???绾㈡??Linux?劇?惰?浼?绉?涓?浜???

浠?MySQL 4.1寮?濮?寮??ョ??澶?璇?瑷?????纭?瀹?寰?妫?锛???涓?涓?浜??規?у凡缁?瓒?杩?浜??朵????版??搴?绯葷???涓?杩????ㄦ?璇?杩?绋?涓????頒嬌?ㄩ???ㄤ?MySQL 4.1涔?????PHP璇??ユ??浣?MySQL?版??搴?浼?????涔辯??锛??充嬌??璁劇疆杩?浜?琛ㄥ??绗???涔???濡?姝ゃ??

?? 浣跨??hpmyadmin?ユ??浣?mysql锛?浣跨?ㄤ?zh- cn-utf8??杩??ユ?瑰?锛??版??搴???琛ㄤ?浣跨?ㄤ?utf8??缂???锛???hpmyadmin???版???藉?姝ｅ父锛?浣???浣跨??hp杩??ュ苟???闆?烘?ヤ互????涓??锛???? ??utf8锛?涔?涓???gb2312锛??翠???iso8859锛?濡???浠?????琛ㄥ?????ユ?版??锛????劇ず涔辯??锛???浜???涓???锛???浜???涔辯?????ョ??浜?涓?涓?mysql 4.1????涓????沖??绗???????棰?锛???棰?瑙ｅ?籌??葷?濡?涓?锛?

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)

涓??㈠???虹???煎氨??绯葷???榛?璁ゅ?箋??(寰?濂???绯葷???涔?榛?璁ゆ??latin1?????歌????搴??瑰?)...

褰???浠????у???ョ???瑰???杩?PHP瀛???MySQL?版??搴??訛?灏辯??璁劇疆浜?琛ㄧ??榛?璁ゅ??绗???涓?tf8骞朵???杩?UTF-8缂????????ヨ???浣?浼????闆???ユ?版??搴???浠??舵??涔辯??????棰?灏卞?哄?ㄨ?涓?connection杩??ュ?涓???瑙ｅ?蟲?規????ㄥ?????ヨ?㈠???ц?涓?涓?涓??㈣??ワ?

SET NAMES 'utf8';瀹??稿?浜?涓??㈢??涓??ユ??浠わ?

SET character_set_client = utf8;

SET character_set_results = utf8;

SET character_set_connection = utf8;

??璇?璇???锛?姝ｅ父浜??э?^_^ Enjoy!

?