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=1mysql> 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` varchar(30) not null comment '市名称',  `prov_code` int(2) not null comment '省代码',  constraint `fk_city_prov_code` foreign key (`prov_code`) references metadb.`prov` (`prov_code`),  unique key `uk_city_name` (`city_name`),  key `idx_city_prov` (`prov_code`)) engine=innodb default charset=utf8 comment='省辖市表' |load data local infile './metadb-prov.dat' into table metadb.`prov`    fields terminated by ','    lines terminated by '\n' |load data local infile './metadb-city.dat' into table metadb.`city`    fields terminated by ','    lines terminated by '\n' |# show encoding for metadbuse metadb |SET character_set_client = utf8 |SET character_set_connection = utf8 |SET character_set_database = utf8 |SET character_set_results = utf8 |SET character_set_server = utf8 |SET collation_connection = utf8_general_ci |SET collation_database = utf8_general_ci |SET collation_server = utf8_general_ci |show variables like 'character_set_%' |show variables like 'collation_%' |################################ END ###################################delimiter ;

2) utf8编码的数据文件内容如下：

metadb-prov.dat:

11,北京市12,天津市13,河北省14,山西省15,内蒙古自治区21,辽宁省22,吉林省23,黑龙江省31,上海市32,江苏省33,浙江省34,安徽省35,福建省36,江西省37,山东省41,河南省42,湖北省43,湖南省44,广东省45,广西壮族自治区46,海南省50,重庆市51,四川省52,贵州省53,云南省54,西藏自治区61,陕西省62,甘肃省63,青海省64,宁夏回族自治区65,新疆维吾尔自治区71,台湾省81,香港特别行政区82,澳门特别行政区

metadb-city.dat:

1100,北京市,111200,天津市,123100,上海市,315000,重庆市,507100,台湾,718100,香港,818200,澳门,821301,石家庄市,131302,唐山市,131303,秦皇岛市,131304,邯郸市,131305,邢台市,131306,保定市,131307,张家口市,131308,承德市,131309,沧州市,131311,衡水市,131401,太原市,141402,大同市,141403,阳泉市,141404,长治市,141405,晋城市,141406,朔州市,141407,晋中市,141408,运城市,141409,忻州市,141411,吕梁市,141501,呼和浩特市,151502,包头市,151503,乌海市,151504,赤峰市,151505,通辽市,151506,鄂尔多斯市,151507,呼伦贝尔市,151508,巴彦淖尔市,151509,乌兰察布市,151522,兴安盟,151525,锡林郭勒盟,151529,阿拉善盟,152101,沈阳市,212102,大连市,212103,鞍山市,212104,抚顺市,212105,本溪市,212106,丹东市,212107,锦州市,212108,营口市,212109,阜新市,212111,盘锦市,212112,铁岭市,212113,朝阳市,212114,葫芦岛市,212201,长春市,222202,吉林市,222203,四平市,222204,辽源市,222205,通化市,222206,白山市,222207,松原市,222208,白城市,222224,延边朝鲜族自治州,222301,哈尔滨市,232302,齐齐哈尔市,232303,鸡西市,232304,鹤岗市,232305,双鸭山市,232306,大庆市,232307,伊春市,232308,佳木斯市,232309,七台河市,232311,黑河市,232312,绥化市,232327,大兴安岭地区,233201,南京市,323202,无锡市,323203,徐州市,323204,常州市,323205,苏州市,323206,南通市,323207,连云港市,323208,淮安市,323209,盐城市,323211,镇江市,323212,泰州市,323213,宿迁市,323301,杭州市,333302,宁波市,333303,温州市,333304,嘉兴市,333305,湖州市,333306,绍兴市,333307,金华市,333308,衢州市,333309,舟山市,333311,丽水市,333401,合肥市,343402,芜湖市,343403,蚌埠市,343404,淮南市,343405,马鞍山市,343406,淮北市,343407,铜陵市,343408,安庆市,343411,滁州市,343412,阜阳市,343413,宿州市,343415,六安市,343416,亳州市,343417,池州市,343418,宣城市,343501,福州市,353502,厦门市,353503,莆田市,353504,三明市,353505,泉州市,353506,漳州市,353507,南平市,353508,龙岩市,353509,宁德市,353601,南昌市,363602,景德镇市,363603,萍乡市,363604,九江市,363605,新余市,363606,鹰潭市,363607,赣州市,363608,吉安市,363609,宜春市,363611,上饶市,363701,济南市,373702,青岛市,373703,淄博市,373704,枣庄市,373705,东营市,373706,烟台市,373707,潍坊市,373708,济宁市,373709,泰安市,373711,日照市,373712,莱芜市,373713,临沂市,373714,德州市,373715,聊城市,373716,滨州市,373717,菏泽市,374101,郑州市,414102,开封市,414103,洛阳市,414104,平顶山市,414105,安阳市,414106,鹤壁市,414107,新乡市,414108,焦作市,414109,濮阳市,414111,漯河市,414112,三门峡市,414113,南阳市,414114,商丘市,414115,信阳市,414116,周口市,414117,驻马店市,414201,武汉市,424202,黄石市,424203,十堰市,424205,宜昌市,424206,襄阳市,424207,鄂州市,424208,荆门市,424209,孝感市,424211,黄冈市,424212,咸宁市,424213,随州市,424228,恩施土家族苗族自治州,424301,长沙市,434302,株洲市,434303,湘潭市,434304,衡阳市,434305,邵阳市,434306,岳阳市,434307,常德市,434308,张家界市,434309,益阳市,434311,永州市,434312,怀化市,434313,娄底市,434331,湘西土家族苗族自治州,434401,广州市,444402,韶关市,444403,深圳市,444404,珠海市,444405,汕头市,444406,佛山市,444407,江门市,444408,湛江市,444409,茂名市,444412,肇庆市,444413,惠州市,444414,梅州市,444415,汕尾市,444416,河源市,444417,阳江市,444418,清远市,444419,东莞市,444451,潮州市,444452,揭阳市,444453,云浮市,444501,南宁市,454502,柳州市,454503,桂林市,454504,梧州市,454505,北海市,454506,防城港市,454507,钦州市,454508,贵港市,454509,玉林市,454511,贺州市,454512,河池市,454513,来宾市,454514,崇左市,454601,海口市,464602,三亚市,465101,成都市,515103,自贡市,515104,攀枝花市,515105,泸州市,515106,德阳市,515107,绵阳市,515108,广元市,515109,遂宁市,515111,乐山市,515113,南充市,515114,眉山市,515115,宜宾市,515116,广安市,515117,达州市,515118,雅安市,515119,巴中市,515132,阿坝藏族羌族自治州,515133,甘孜藏族自治州,515134,凉山彝族自治州,515201,贵阳市,525202,六盘水市,525203,遵义市,525204,安顺市,525205,毕节市,525206,铜仁市,525223,黔西南布依族苗族自治州,525226,黔东南苗族侗族自治州,525227,黔南布依族苗族自治州,525301,昆明市,535303,曲靖市,535304,玉溪市,535305,保山市,535306,昭通市,535307,丽江市,535308,普洱市,535309,临沧市,535323,楚雄彝族自治州,535325,红河哈尼族彝族自治州,535326,文山壮族苗族自治州,535328,西双版纳傣族自治州,535329,大理白族自治州,535331,德宏傣族景颇族自治州,535333,怒江傈僳族自治州,535334,迪庆藏族自治州,535401,拉萨市,545421,昌都地区,545422,山南地区,545423,日喀则地区,545424,那曲地区,545425,阿里地区,545426,林芝地区,546101,西安市,616102,铜川市,616103,宝鸡市,616104,咸阳市,616105,渭南市,616106,延安市,616107,汉中市,616108,榆林市,616109,安康市,616201,兰州市,626202,嘉峪关市,626203,金昌市,626204,白银市,626205,天水市,626206,武威市,626207,张掖市,626208,平凉市,626209,酒泉市,626211,定西市,626212,陇南市,626229,临夏回族自治州,626301,西宁市,636321,海东地区,636322,海北藏族自治州,636323,黄南藏族自治州,636325,海南藏族自治州,636326,果洛藏族自治州,636327,玉树藏族自治州,636328,海西蒙古族藏族自治州,636401,银川市,646402,石嘴山市,646403,吴忠市,646404,固原市,646405,中卫市,646501,乌鲁木齐市,656502,克拉玛依市,656521,吐鲁番地区,656522,哈密地区,656523,昌吉回族自治州,656527,博尔塔拉蒙古自治州,656528,巴音郭楞蒙古自治州,656529,阿克苏地区,656531,喀什地区,656532,和田地区,656542,塔城地区,656543,阿勒泰地区,65