PHP连接MySQL的字符集问题新发现

  之前我在一篇文章里面讲到把故人居网站升级为UTF8字符集,看过的也知道一些有关PHP和MySQL字符集的问题,通常情况下MySQL默认使用的字符集是latin1,如果我们的系统要使用utf8或者别的字符集,就需要对MySQL进行配置,并且在PHP程序中做一点处理,大致的方法如下:

1.修改my.ini (或者my.cnf)文件,在文件的[mysqld]中加入下面一行
character_set_server = utf8

由于MySQL的字符集和连接校对有分级,分别是操作系统级、数据库服务器级、数据库客户端级,这几个级别是从上往下包含的,我上面的那一行 character_set_server 参数实际上就是设置了全局的字符集,这样不管是MySQL数据存储、数据连接、结果返回都将是utf8,当然,如果您愿意,您也可以进行分别的设置,比如做如下设置
default-character-set = utf8
default-collation = utf8_general_ci
这样的设置可以设置一个默认字符集,在您的应用中如果没有特殊指定字符集,那将默认使用utf8作为字符集,并且使用utf8_general_ci作为连接校对的默认字符集;

简单的处理方法就是如我前面的那样,加入一行即可。

2.PHP程序中连接MySQL的时候,或者在执行mysql_query的时候,执行以下一行SQL语句
SET NAMES utf8
就是说在执行mysql_query之前,或者在完成mysql_connect()之后,执行 mysql_query(“SET NAMES utf8)

  有了前面的两个处理,您就可以正常的进行utf8字符集的数据库操作,不管在页面显示还是数据库中,字符都不会出现乱码。

  前面说到的方法是以前用到的常用方法,最近在MySQL的官方发现了这样一个参数 init_connect ,它的意思呢就是在MySQL启动的时候,自动执行init_connect 参数里面的SQL语句,支持用分号间隔的多个语句,于是我们可以把 SET NAMES utf8语句加到my.ini文件里面,在PHP程序里面就不用单独处理了,加入参数的方法很简单,就是在my.ini文件的 [mysqld] 段加入下面一行
init_connect = ‘SET NAMES utf8’

  不过这个方法有一个需要注意的地方,那就是该参数对于连接数据库的用户是超级用户组的用户将被忽略,那么在PHP程序中用来连接MySQL数据库的用户不能是SUPER组的用户,比如root用户就肯定不行,这样是为了避免该参数导致数据库致命错误,而无法使用任何一个用户连接上修改该项配置,之前我搞了两天都没有发现这样的问题,害我郁闷了很久。。。

有关MySQL的配置文件中用到的参数可以参考官方的文档 : http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html

  呵呵,让人又爱又恨的MySQL!

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
10 Responses
  1. Marvinux says:

    你好,我有一个问题想问一下,我用的是mysql5.0.24,用的是utf8,使用WP很正常,但用phpmyadmin和命令行操作mysql的是候显示的中文还是乱码~~

  2. Michael says:

    [Comment ID #3939 Will Be Quoted Here]

    这个问题完全可以使用我说的方法搞定,你可以在my.cnf里面加入init_connect那一句,不过你需要重新安装一次wordPress才行。 出现你这样的原因是虽然你的WP设置了使用utf8,但是PHP在进行数据库连接的时候并没有使用utf8连接校验,这样插入到里面的数据存储方式虽然是utf8,但是数据本身并不是utf8的,PHP读出来的时候使用的方法和插入一样,所以WP里面看到的并不会是乱码,相反在更加聪明的phpmyadmin里面却看到了乱码,因为它使用了正确的utf8连接校验。

  3. Marvinux says:

    我按照你的方法试过了,可能我在windows系统上部署的PHP/MYSQL,所以没有my.cnf这个文件,但我把init_connect这句话放到my.ini文件里的时候一连接就报错。

  4. Michael says:

    [Comment ID #3972 Will Be Quoted Here]

    windows也是没有问题的,我这里除了FreeBSD的机器上外,在我的笔记本上也安装了MySQL5,如果使用默认的安装,应该会安装在 C:\Program Files\MySQL\MySQL Server 5.0\ 也有可能您定制安装在别的目录,您可以通过下面的命令得知您的MySQL主目录 basedir 所在位置:
    mysqladmin -uroot -p variables
    查看其中的basedir项,MySQL从4.x开始在Windows上的配置文件my.ini默认存放在basedir下,以前是存放在c:\my.cnf下,所以您提到的只有my.ini是正常的,同时我们也正是要配置这个my.ini文件,不知道您修改my.ini文件的方法是怎样的,下面是我的my.ini的部分内容,您可以参考一下:
    [client]
    port=3306
    default-character-set=utf8

    [mysqld]
    port=3306
    basedir=”C:/Program Files/MySQL/MySQL Server 5.0/”
    datadir=”C:/Program Files/MySQL/MySQL Server 5.0/Data/”
    character_set_server = utf8
    init_connect=’SET NAMES utf8′
    default-storage-engine=INNODB
    max_connections=100

  5. lochen says:

    我按照以上方法,将mysql server的character_set_server = utf8
    init_connect=’SET NAMES utf8′
    但重新安装mu的wordpress后,新增用户blog便不能使用刚新开的用户blog,出现404Page Not Found

    请问如将server的character_set设了做UTF8后是否会有影响?

    曾经尝试改回原来的设定character_set_server = utf8,重裝mu wordpress後,但问题仍旧
    请问有何方法可以解决这个问题?

  6. 深圳SEO says:

    谢谢,测试一下,很好用

  7. 华晨 says:

    不过有时候那些参数在虚拟主机改不了的

  8. Michael says:

    [Comment ID #31773 Will Be Quoted Here]

    如果是虚拟主机的话,的确有些麻烦,虚拟主机就只能照顾人家数据库的配置了,人家是什么你就用什么吧,不过你确保你连接的时候都是要utf-8,那存入和读取出来的数据都是统一的,只不过在数据库里面存储的可能是乱的,那也没什么关系。

  9. Je lis souvent, comment vas-tu? Quel genre d’information! Ici, sur ce site, merci administrer cette page.

  10. minecraft says:

    My developer is trying to convince me to move to .net from PHP.
    I have always disliked the idea because of the costs.
    But he’s tryiong none the less. I’ve been using WordPress on numerous websites for about a year and am
    worried about switching to another platform. I have
    heard fantastic things about blogengine.net. Is there
    a way I can import all my wordpress posts into it?
    Any help would be really appreciated!

  11. minecraft says:

    Hello, i think that i saw you visited my website so i came to “return the favor”.I am trying
    to find things to enhance my website!I suppose its ok to use some of your ideas!!

  12. It is perfect time to make some plans for the longer term and it’s time
    to be happy. I have learn this publish and if I could I desire to counsel you some interesting things or
    tips. Perhaps you could write subsequent articles regarding this article.
    I wish to learn even more issues approximately it!

  13. Hi everybody, here every person is sharing such knowledge, thus
    it’s good to read this weblog, and I used to pay a quick visit this weblog every day.

  14. Superb, what a weblog it is! This weblog provides helpful data to us, keep it up.

  15. Write more, thats all I have to say. Literally, it seems as though you relied on the video to make your point.
    You clearly know what youre talking about, why throw away your intelligence on just posting videos to your weblog when you could be giving us something enlightening to read?

  16. I think this is among the most vital info for me. And i’m glad
    reading your article. But wanna remark on some general things, The
    web site style is wonderful, the articles is really great : D.
    Good job, cheers

  17. I am extremely impressed with your writing skills as well as with the layout
    on your weblog. Is this a paid theme or did you customize it
    yourself? Either way keep up the nice quality writing, it’s rare to see
    a nice blog like this one these days.

  18. Have you ever thought about including a little bit more than just your articles?
    I mean, what you say is fundamental and everything.
    Nevertheless think of if you added some great graphics or videos to give your posts more,
    “pop”! Your content is excellent but with images and clips,
    this blog could definitely be one of the very best
    in its niche. Fantastic blog!

  19. Descargar facebook http://twitter.com/descargar_hq descargar facebook
    Hi there everyone, it’s my first visit at this web site, and article is in fact fruitful in support of me, keep up
    posting these types of articles. Descargar facebook http://twitter.com/descargar_hq descargar facebook

  20. Sling TV says:

    Wow, marvelous weblog layout! How long have you ever been blogging for?
    you made blogging look easy. The overall glance of
    your website is magnificent, as neatly as the content!

  21. Sling TV says:

    I really like it when folks come together and share ideas.
    Great website, stick with it!

  22. Sling TV says:

    When someone writes an piece of writing he/she
    maintains the thought of a user in his/her mind that how a user can understand it.
    Therefore that’s why this piece of writing is outstdanding.
    Thanks!

  23. Hello very cool website!! Man .. Beautiful .. Amazing ..
    I will bookmark your website and take the feeds also?
    I’m glad to search out so many useful information here within the put up, we want work out more strategies in this regard, thanks for sharing.
    . . . . .

  24. Asking questions are in fact nice thing if you are not understanding anything entirely, except this piece of writing gives good understanding yet.

  25. I think this is one of the most significant information for
    me. And i’m glad reading your article. But wanna remark on few
    general things, The website style is perfect, the
    articles is really great : D. Good job, cheers

  26. I really like your blog.. very nice colors & theme.
    Did you make this website yourself or did you hire someone to do it for you?
    Plz respond as I’m looking to construct my own blog and would like to find out where u got this from.
    kudos

  27. Heya i’m for the first time here. I came across this board and I to find
    It really helpful & it helped me out much. I hope to
    provide one thing back and aid others like you helped
    me.

Leave a Reply

Your email address will not be published. Required fields are marked *

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Anti-spam image