mysql 字符集更改与导入数据

mysqldb经常有中文乱码的问题，解决起来很恼火。其实所有开发和数据库统一为一种编码就可以了: utf8。

1 下面修改mysql的编码

1) 永久修改. 在/etc/mysql/my.cnf中添加下面二行：

[client]
...
default-character-set=utf8
...

[mysqld]
...
character-set-server=utf8
...

$ sudo /etc/init.d/mysql restart
$ mysql -u root -p abc123 -h 127.0.0.1 --local-infile=1
# 显示字符集
#   mysql> show variables like 'character_set_%';
# 显示字符排列顺序
#   mysql> show variables like 'collation_%';

应该如下显示：

mysql> show variables like 'character_set_%';
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

mysql> show variables like 'collation_%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.01 sec)

2) 临时修改，登录mysql后执行下面的命令：

delimiter |
SET GLOBAL character_set_client = utf8 |
SET GLOBAL character_set_connection = utf8 |
SET GLOBAL character_set_database = utf8 |
SET GLOBAL character_set_results = utf8 |
SET GLOBAL character_set_server = utf8 |
SET GLOBAL collation_connection = utf8_general_ci |
SET GLOBAL collation_database = utf8_general_ci |
SET GLOBAL collation_server = utf8_general_ci |
delimiter ;

这之后，所有的数据文件，程序都统一为utf8编码。

2 下面导入数据，首先创建表prov和city：

登录mysql (注意--local-infile=1启用数据导入功能):

mysql -u root -p abc123 -h 127.0.0.1 --local-infile=1

mysql> source metadb.cresql

1) metadb.cresql内容如下: