原文连接:http://blog.csdn.net/liuzhujun/article/details/7854520
今天同事开发过程中遇到linux下mysql中文乱码问题,直接通过sql脚本插入中文数据乱码。将解决方法记录下:
1、首先确定server、数据库和表的默认字符级
- mysql> show variables like 'character_set_%';
- +--------------------------+----------------------------+
- | 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/ |
- +--------------------------+----------------------------+
- 8 rows in set (0.00 sec)
2、通过上面的查询结果,我们知道server和database默认字符集为拉丁文latin1,表毫无疑问是采用server字符集作为默认字符集。这样就导致了,我们通过sql脚本插入中文时乱码的问题。由于我们代码中统一采用了UTF-8作为工程的默认字符集编码,为了统一,我们这里需要将字符集修改为UTF-8。
3、准备通过mysql下my.ini启动文件来修改默认的字符集,找了半天没找到(由于是测试环境,不记得mysql是不是系统自带的了)。那就采用命令行的方式来修改,分别修改server、database、table默认的字符集
- mysql> show variables like 'collation_%';
- +----------------------+-------------------+
- | Variable_name | Value |
- +----------------------+-------------------+
- | collation_connection | latin1_swedish_ci |
- | collation_database | latin1_swedish_ci |
- | collation_server | latin1_swedish_ci |
- +----------------------+-------------------+
- 3 rows in set (0.00 sec)
- mysql> show variables like 'character_set_%';
- +--------------------------+----------------------------+
- | 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/ |
- +--------------------------+----------------------------+
- 8 rows in set (0.00 sec)
- mysql> use callcenter;
- Reading table information for completion of table and column names
- You can turn off this feature to get a quicker startup with -A
- Database changed
- mysql> alter database callcenter character set utf8;
- Query OK, 1 row affected (0.00 sec)
- mysql> set names 'utf8';
- Query OK, 0 rows affected (0.00 sec)
- mysql> use callcenter;
- Database changed
- mysql> select * from um_group;
- +----+------+----------------+------+
- | id | name | description | type |
- +----+------+----------------+------+
- | 1 | ?? | ????????????? | 2 |
- | 2 | ??? | ?????????????? | 2 |
- | 3 | aaaa | aaaaaa | 2 |
- | 3 | ?? | ?? | 2 |
- | 4 | ?? | ?? | 2 |
- | 5 | ?? | ?? | 2 |
- +----+------+----------------+------+
- 6 rows in set (0.00 sec)
- mysql> select * from test;
- +----+------+-------------+------+
- | id | name | description | type |
- +----+------+-------------+------+
- | 5 | ?? | ?? | 2 |
- +----+------+-------------+------+
- 1 row in set (0.00 sec)
- 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 | latin1 |
- | character_set_system | utf8 |
- | character_sets_dir | /usr/share/mysql/charsets/ |
- +--------------------------+----------------------------+
- 8 rows in set (0.00 sec)
- mysql> set character_set_server=utf8;
- Query OK, 0 rows affected (0.00 sec)
- mysql> select * from test;
- +----+------+-------------+------+
- | id | name | description | type |
- +----+------+-------------+------+
- | 5 | ?? | ?? | 2 |
- | 6 | ?? | ?? | 2 |
- +----+------+-------------+------+
- 2 rows in set (0.00 sec)
- mysql> drop table test;
- Query OK, 0 rows affected (0.04 sec)
- mysql> create table test(
- -> name varchar(20)
- -> );
- Query OK, 0 rows affected (0.04 sec)
- mysql> insert into test values('啊啊啊');
- Query OK, 1 row affected (0.00 sec)
- mysql> select * from test;
- +-----------+
- | name |
- +-----------+
- | 啊啊啊 |
- +-----------+
- 1 row in set (0.00 sec)