mysql 字符集问题

 

字符集出现错误解决办法

出现的问题:

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>

Mysql 字符集问题2

 

修改mysql字符编码成为UTF8

关键字: mysql 编码

 

安装后

/etc/init.d/mysql start (stop) 为启动和停止服务器

/etc/mysql/ 主要配置文件所在位置 my.cnf

/var/lib/mysql/ 放置的是数据库表文件夹,这里的mysql相当于windows下mysql的date文件夹

 

启动mysql后,以root登录mysql

isher@isher-ubuntu:~$ mysql -u root

>show variables like 'character%'; #执行编码显示

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

| Variable_name | Value |

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

| character_set_client | latin1 |

| character_set_connection | latin1 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | latin1 |

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

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

 

在某些时候,我们续要修改mysql默认数据库的编码,以保证某些迁移的程序可以正常显示,编辑my.cnf文件进行编码修改,windows可以直接用Mysql Server Instance Config Wizard 进行设置

 

在linux下修改3个my.cnf的1个/etc/mysql/my.cnf文件

 

找到客户端配置[client] 在下面添加

default-character-set=utf8 默认字符集为utf8

在找到[mysqld] 添加

default-character-set=utf8 默认字符集为utf8

init_connect='SET NAMES utf8' (设定连接mysql数据库时使用utf8编码,以让mysql数据库为utf8运行)

 

修改好后,重新启动mysql 即可,查询一下show variables like 'character%';

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

| Variable_name | Value |

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

| 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_sets_dir | /usr/share/mysql/charsets/ |

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

 

 

 

此方法用于标准mysql版本同样有效,对于/etc/my.cnf文件,需要从mysql/support-files的文件夹cp my-large.cnf一份到/etc/my.cnf