PHP MySQL字符集的问题

Key words: PHP, MySQL, Character set, 乱码

我的配置:

Apache2.2.2 + PHP 5.1.4 + MySQL 5.0.21 + Windows Server 2003

MySQL Server上有关character set的设置都设为utf8,但总是有乱码现象。而且phpMyAdmin, EMS SQL Manager和自己的php程序的乱码还互不相同,后来用在自己的php程序中查询show variables,发现character_set_client, character_set_connection, character_set_results都是latin1。其他工具的相关设置也没有设正确。

经验:一定要确认character setclientserver端一致,还要确认各个数据库管理工具(e.g. phpMyAdmin, EMS SQL Manager)的设置都正确。

 

MySQL 4.1开始支持character setcollation,安装的时候(Windows installer)人们往往没有留意这些设置,导致MySQL Serverclient使用的character set不一致,从而出现乱码。从此网络上关于“MySQL乱码”的讨论从未停息。很多人因为“乱码问题”对MySQL 4.1以上的版本敬而远之。

其实,MySQLcharacter setcollation的支持大大加强了对多国语言的支持。我们甚至可以在一张表中存储不同语言的字符串。

 

几个术语:

  • Encoding: 编码,在计算机字符显示系统中,每一个字符对应一个数值表示,字符和数值表示的对应规则成为编码
  • Character set: 字符集,规定了使用那些符号以及这些符号的编码方式(encoding)
  • Collation: 排序,确定一个字符集中各个字符的排列顺序的规则,常用于字符的比较

 

Encoding, character setcollation的设置应该一致,否则没有意义,还可能导致错误。所以这三个概念常常被混为一谈。各个公司的有关称呼也混乱不堪。不过,如今的公司都在向标准靠拢。

 

 

MySQL 5.1根据charecter set确定怎样储存字符,根据collation确定怎样比较。MySQL 5.1character setcollation的设置粒度很细,可以为server, database, table, column分别设置。这些设置是数据库的一部分,决定了数据的存贮。

 

乱码产生于数据传输阶段,原因是客户端和服务端的character set不一致。

执行 SHOW VARIABLES LIKE ‘Charater_set%’可查看MySQL关于Character set的各个设置:

| 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_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

 

所以,如果clientserver端的默认character set不一致,则需要在读取和写入数据之前先query一句SET CHARACTER SET ‘xSET NAMES ‘xxclient使用的character set。也可以使用mysqli_set_charset (PHP 5 >= 5.1.0RC1)

)

 

下面的文章对我非常有帮助:

另外,要注意MySQL认识utf8,而其他很多场合,例如HTML,http header使用更为标准的UTF-8

Advertisements
This entry was posted in PHP Rocks. Bookmark the permalink.

3 Responses to PHP MySQL字符集的问题

  1. Unknown says:

    item4sale xhjq09jj wow goldwow goldwow goldwow goldwow golditem4saleitem4saleitem4saleitem4saleitem4saleage of conan power levelingage of conan power levelingage of conan power levelingage of conan power levelingage of conan power leveling连锁干洗加盟连锁干洗加盟洗衣洗衣水洗机水洗机洗衣设备洗衣设备干洗店设备干洗店设备干洗机干洗机干洗店设备价格干洗店设备价格修鞋修鞋皮鞋美容机皮鞋美容机wow goldwow goldwow goldwow goldwow goldwow goldwow goldwow goldwow goldwow goldwow goldwow goldwow goldwow goldwow goldyll0918jj

  2. Unknown says:

    It\’s rather wow goldamazing how fast this innovation is moving. ffxi gilEven to keep the like of myself who are deeply involved in the in dustry to go and see the improvement and every element that are aoc goldtaking place on a yearly aoc goldbasis is quite fantastic. Of course one of the driving factors of this business is the exponential increase in processor performance. There is no doubt that the magic of chip capability has delivered through the advance in microprocessor allows us to think of wow goldapplication which never would have been possiblewow gold before. The PC industry is one maple story mesosof the few industries that can deliver lower price equipment at the same time as improving the capabilities. The storage systems are now delivering Gigabyte of storage as the standard maplestory mesoscapability.Over 80 million of PCs are being sold a year. And the server market, the higher performance machines thatmaple story meso these PCs networked with,are the fastest growing part of this business. The performance of those servers is increasing not only because processors are faster,wow gold but also because we are usingwow gold multiple-processor machines, so wow goldcalled SMP designs and clustering nodes together… Great chips, systems developers, partners wow goldwho are sponsoring this event, making this wow goldall possible. There is an incredible opportunity for developers. The applications that are written today will sell to an even larger base of machines out in the market. There is a lot that we\’re wow golddoing to increase the work of good wow golddevelopers-make sure they understand where the PC wow goldis going and how tools can help them now, more and more marketing type of activities wow goldmaking sure they got in with the customers.This is something that we are going to wow goldincrease year after year.

  3. Unknown says:

    dofus kamas kamas dofus dofus kamas kamas dofus dofus kamas kamas dofus dofus kamas kamas dofus dofus kamas kamas dofus wow leveling Runescape Money Runescape Gold Runescape Money Runescape Gold Runescape Power leveling Runescape Money Runescape Gold Runescape gold runescape money Runescape Money Runescape gold Runescape Money Runescape gold Runescape Power leveling Runescape Items runescape money runescape gold money runescape Runescape Money Runescape gold Runescape Gold runescape money age of conan gold aoc power leveling age of conan power leveling aoc leveling age conan gold archlord gold buy archlord gold anarchy online credits anarchy online credit city of heroes influence coh influence city of villains infamy cov infamy dofus kamas kamas dofus eve isk eve online isk everquest 2 gold eq2 plat ffxi gil final fantasy xi gil buy ffxi gil gaia online gold gaia gold guild wars gold gw gold hellgate london palladium cabal alz daoc gold daoc plat world of warcraft gold wow po wow or buy wow gold cheap wow gold wow power leveling wow powerleveling

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s