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

September 1, 2006 10 Comments

　　之前我在一篇文章里面讲到把故人居网站升级为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！

10 thoughts on “PHP连接MySQL的字符集问题新发现”

  2. [Comment ID #3939 Will Be Quoted Here]

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

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

  4. [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. 我按照以上方法,将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. Pingback: 一地鸡毛 - PHP连接MySQL的字符集问题新发现(zz)

  9. [Comment ID #31773 Will Be Quoted Here]

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

  11. 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!

  19. 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!

  29. What i don’t realize is actually how you’re now not actually
    much more well-preferred than you may be right now.
    You are very intelligent. You already know therefore considerably relating to this matter, produced
    me personally consider it from a lot of various angles.

    Its like women and men are not interested unless it’s one thing to do with Lady gaga!

    Your personal stuffs great. At all times care for it up!

  34. you are in reality a excellent webmaster. The web site
    loading pace is amazing. It sort of feels that you’re doing any distinctive trick.
    Also, The contents are masterpiece. you’ve done a
    magnificent activity on this subject!

  35. Excellent goods from you, man. I’ve take note your stuff prior to and you are just too
    great. I really like what you have acquired here, certainly like what you’re saying
    and the best way by which you say it. You’re making it enjoyable and you
    continue to take care of to stay it wise. I can’t wait to learn much
    more from you. That is really a tremendous site.

  36. Pingback: Https://www.examsgeek.com/

  39. Definitely believe that which you said. Your favorite reason seemed to be on the net the easiest thing
    to be aware of. I say to you, I certainly get annoyed while
    people consider worries that they plainly do not know about.

    You managed to hit the nail upon the top and defined out
    the whole thing without having side effect ,
    people can take a signal. Will probably be back to get more.

    Thanks

  45. Pingback: 3ycmt4ogehdt8ogugehm5yvetv
  50. Pingback: Buy Tramadolor
  53. Pingback: Https://github.com/victoriavargas370/Microsoft-MB2-707-Cheat-Sheet-Exam-Questions---Pass-In-First-Attempt/issues/1

  69. Does your blog have a contact page? I’m having a
    tough time locating it but, I’d like to send you
    an email. I’ve got some ideas for your blog you might be interested in hearing.

    Either way, great blog and I look forward to seeing it improve over time.

  74. Just desire to say your article is as amazing. The clearness to your post is simply nice and that i could suppose you are knowledgeable in this subject.
    Well with your permission allow me to snatch your RSS feed to keep
    updated with imminent post. Thanks a million and please continue the enjoyable work.

  75. Great post. I was checking continuously this blog and I am impressed!

    Very helpful information particularly the ultimate
    phase 🙂 I deal with such information a lot. I used to be seeking this particular info for a long
    time. Thank you and best of luck.

  77. Does your website have a contact page? I’m having trouble locating it
    but, I’d like to send you an email. I’ve got some recommendations for your blog
    you might be interested in hearing. Either way, great
    site and I look forward to seeing it improve over time.

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