Mysql 字符集问题2
Oracle 备份与恢复

mysql 字符集问题

marz posted @ Aug 11, 2010 07:03:09 AM in sql with tags SQL database mysql encoding , 1277 readers

 

字符集出现错误解决办法

出现的问题:

mysql> update users

-> set username='关羽'

-> where userid=2;

ERROR 1366 (HY000): Incorrect string value: '\xB9\xD8\xD3\xF0' for column 'usern

ame' at row 1

向表中插入中文字符时,出现错误。

 

mysql> select * from users;

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

| userid | username |

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

| 2 | ???? |

| 3 | ???? |

| 4 | ?í?ù |

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

3 rows in set (0.00 sec)

表中的中文字符位乱码。

解决办法:

使用命令:

mysql> status;

--------------

mysql Ver 14.12 Distrib 5.0.45, for Win32 (ia32)

 

Connection id: 8

Current database: test

Current user: root@localhost

SSL: Not in use

Using delimiter: ;

Server version: 5.0.45-community-nt MySQL Community Edition (GPL)

Protocol version: 10

Connection: localhost via TCP/IP

Server characterset: latin1

Db characterset: latin1

Client characterset: gbk

Conn. characterset: gbk

TCP port: 3306

Uptime: 7 hours 39 min 19 sec

Threads: 2 Questions: 174 Slow queries: 0 Opens: 57 Flush tables: 1 Open ta

bles: 1 Queries per second avg: 0.006

--------------

查看mysql发现Server charactersetDb characterset的字符集设成了latin1,所以出现中文乱码。

 

mysql> show tables;

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

| Tables_in_test |

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

| users |

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

1 row in set (0.00 sec)

 

更改表的字符集。

mysql> alter table users character set GBK;

Query OK, 3 rows affected (0.08 sec)

Records: 3 Duplicates: 0 Warnings: 0

 

 

-----------------------------------------------------------------------------------------------------------------------------------

 

 

 

(1)修改 MySql安装目录下面的my.ini(MySQL Server Instance Configuration 文件)。 设置

        default-character-set=gbk(注意,有2)

(2)修改data目录中相应数据库目录下的db.opt配置文件

        default-character-set=gbk

        default-collation=gbk_chinese_ci

重启数据库,关闭控制台窗口重新登录数据库,执行SQL,一切正常

 

源文档 <http://cske.javaeye.com/blog/104503>


Login *


loading captcha image...
(type the code from the image)
or Ctrl+Enter