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内容如下:

#-----------------------------------------------------------------------
# metadb.cresql
#   copyright by cheungmine
#   -- create sql for database metadb
# $ mysql -u root -p abc123 -h 127.0.0.1 --local-infile=1
#-----------------------------------------------------------------------
delimiter |
############################### BEGIN ##################################
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 |
show variables like 'character_set_%' |
show variables like 'collation_%' |

create database if not exists metadb character set utf8 |
alter database metadb default character set utf8 collate utf8_general_ci |

drop table if exists metadb.`city` |
drop table if exists metadb.`prov` |

create table metadb.`prov` (
  `prov_code` int(2) not null primary key comment '省代码',
  `prov_name` varchar(30) not null comment '省名称',
  unique key `uk_prov_name` (`prov_name`)
) engine=innodb default charset=utf8 comment='省、自治区表' |


create table metadb.`city` (
  `city_code` int(4) not null primary key comment '市代码',
  `city_name` v