怎样正确导入中文(UTF-8)到 MySql 5.0 数据库

象我在自己的英文博客文章所讲的,我最近的一次从MySQL 4.0 数据库用mysqldump导出的中文到了MySql 5 里全变成了乱码。经检查,主要是我的空间服务商在德国,所有新的数据库的默认字符Collations 是德文 latin1_german1_ci。所以,就算我的原MySQL4 里面的字符是UTF-8,在导入时创建新Table时,所有的Table里的Column的Collation全变成了默认的Latin1_German1_ci,以至于中文全变成了乱码。

解决这个乱码问题,必须做到下面两步。

第一,确认新的数据库采用正确的UTF-8字符系列 Character set 和 Collation 格式

登陆到你的服务商提供的管理后台,然后到 Phpmyadmin 界面,键入下列代码:

ALTER DATABASE dbxxxxxx CHARACTER SET utf8 COLLATE utf8_general_ci

* dbxxxxxx 当然是你自己的数据名字

成功后, 可以用下列代码来验证修改是否成功。

show variables like "character_set_database";
show variables like "collation_database";

第二步,在导入命令里加入 “–default-character-set=utf8” 选项来强制导入UTF字符。

下面就是我采用的导入代码,希望对你也有所帮助:

mysql –default-character-set=utf8 -hdbxxxx.perfora.net -udboxxxxxxx -pxxxxxx dbxxxxxxx < /kunden/…/blogs_`date +%Y%m%d`.sql

当然,你需要在上述代码中填上你自己的数据库信息和你原导出的文件的位置和名称。

12 Responses to “怎样正确导入中文(UTF-8)到 MySql 5.0 数据库”

  1. 皱纹88

    晕啊,好像很复杂,我要从国外迁回来了,访问速度太慢了,你这个博客哪里申请的,我访问很快的。

    [Reply]

    超凡不脱俗 于 January 3rd, 2011 回复:

    1 And 1 host. 应该在我的博客页底部有该公司的链接

    [Reply]

  2. cat movies

    Hey! I know this is kinda off topic however , I’d figured I’d ask. Would you be interested in exchanging links or maybe guest authoring a blog article or vice-versa? My site covers a lot of the same subjects as yours and I feel we could greatly benefit from each other. If you’re interested feel free to shoot me an email. I look forward to hearing from you! Excellent blog by the way!

    [Reply]

  3. LiVivian

    mysql –default-character-set=utf8 -hHOST -u用户名 -p excheapshop2 < /home/your_path/dbbak.sql

    [Reply]

  4. Joseph Vida

    Thanks for the new stuff you have uncovered in your post. One thing I would really like to reply to is that FSBO interactions are built as time passes. By bringing out yourself to the owners the first weekend break their FSBO is announced, prior to a masses get started calling on Wednesday, you create a good relationship. By giving them tools, educational supplies, free reports, and forms, you become a great ally. By subtracting a personal curiosity about them along with their circumstances, you create a solid relationship that, on many occasions, pays off if the owners decide to go with a real estate agent they know along with trust – preferably you actually.

    [Reply]

  5. Krakow

    After i started to examine article i did not expect it could be advantageous information for my situation although now I am sincerely greatful that I noticed this website to browse. Regards to the publisher.

    [Reply]

  6. remote Video

    Greetings from Florida! I’m bored to death at work so I decided
    to browse your site on my iphone during lunch break.
    I love the information you provide here and can’t wait to take a look when I get
    home. I’m surprised at how fast your blog loaded onn myy phone ..
    I’m not even using WIFI, just 3G .. Anyhow, amazing blog!

    Here is my page remote Video

    [Reply]

Leave a Reply

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

Time limit is exhausted. Please reload the CAPTCHA.