程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql字符集更改與導入數據

mysql字符集更改與導入數據

編輯：MySQL綜合教程

mysql字符集更改與導入數據

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` 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 metadb
use 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,北京市,11
1200,天津市,12
3100,上海市,31
5000,重慶市,50
7100,台灣,71
8100,香港,81
8200,澳門,82
1301,石家莊市,13
1302,唐山市,13
1303,秦皇島市,13
1304,邯鄲市,13
1305,邢台市,13
1306,保定市,13
1307,張家口市,13
1308,承德市,13
1309,滄州市,13
1311,衡水市,13
1401,太原市,14
1402,大同市,14
1403,陽泉市,14
1404,長治市,14
1405,晉城市,14
1406,朔州市,14
1407,晉中市,14
1408,運城市,14
1409,忻州市,14
1411,呂梁市,14
1501,呼和浩特市,15
1502,包頭市,15
1503,烏海市,15
1504,赤峰市,15
1505,通遼市,15
1506,鄂爾多斯市,15
1507,呼倫貝爾市,15
1508,巴彥淖爾市,15
1509,烏蘭察布市,15
1522,興安盟,15
1525,錫林郭勒盟,15
1529,阿拉善盟,15
2101,沈陽市,21
2102,大連市,21
2103,鞍山市,21
2104,撫順市,21
2105,本溪市,21
2106,丹東市,21
2107,錦州市,21
2108,營口市,21
2109,阜新市,21
2111,盤錦市,21
2112,鐵嶺市,21
2113,朝陽市,21
2114,葫蘆島市,21
2201,長春市,22
2202,吉林市,22
2203,四平市,22
2204,遼源市,22
2205,通化市,22
2206,白山市,22
2207,松原市,22
2208,白城市,22
2224,延邊朝鮮族自治州,22
2301,哈爾濱市,23
2302,齊齊哈爾市,23
2303,雞西市,23
2304,鶴崗市,23
2305,雙鴨山市,23
2306,大慶市,23
2307,伊春市,23
2308,佳木斯市,23
2309,七台河市,23
2311,黑河市,23
2312,綏化市,23
2327,大興安嶺地區,23
3201,南京市,32
3202,無錫市,32
3203,徐州市,32
3204,常州市,32
3205,蘇州市,32
3206,南通市,32
3207,連雲港市,32
3208,淮安市,32
3209,鹽城市,32
3211,鎮江市,32
3212,泰州市,32
3213,宿遷市,32
3301,杭州市,33
3302,寧波市,33
3303,溫州市,33
3304,嘉興市,33
3305,湖州市,33
3306,紹興市,33
3307,金華市,33
3308,衢州市,33
3309,舟山市,33
3311,麗水市,33
3401,合肥市,34
3402,蕪湖市,34
3403,蚌埠市,34
3404,淮南市,34
3405,馬鞍山市,34
3406,淮北市,34
3407,銅陵市,34
3408,安慶市,34
3411,滁州市,34
3412,阜陽市,34
3413,宿州市,34
3415,六安市,34
3416,亳州市,34
3417,池州市,34
3418,宣城市,34
3501,福州市,35
3502,廈門市,35
3503,莆田市,35
3504,三明市,35
3505,泉州市,35
3506,漳州市,35
3507,南平市,35
3508,龍巖市,35
3509,寧德市,35
3601,南昌市,36
3602,景德鎮市,36
3603,萍鄉市,36
3604,九江市,36
3605,新余市,36
3606,鷹潭市,36
3607,贛州市,36
3608,吉安市,36
3609,宜春市,36
3611,上饒市,36
3701,濟南市,37
3702,青島市,37
3703,淄博市,37
3704,棗莊市,37
3705,東營市,37
3706,煙台市,37
3707,濰坊市,37
3708,濟寧市,37
3709,泰安市,37
3711,日照市,37
3712,萊蕪市,37
3713,臨沂市,37
3714,德州市,37
3715,聊城市,37
3716,濱州市,37
3717,菏澤市,37
4101,鄭州市,41
4102,開封市,41
4103,洛陽市,41
4104,平頂山市,41
4105,安陽市,41
4106,鶴壁市,41
4107,新鄉市,41
4108,焦作市,41
4109,濮陽市,41
4111,漯河市,41
4112,三門峽市,41
4113,南陽市,41
4114,商丘市,41
4115,信陽市,41
4116,周口市,41
4117,駐馬店市,41
4201,武漢市,42
4202,黃石市,42
4203,十堰市,42
4205,宜昌市,42
4206,襄陽市,42
4207,鄂州市,42
4208,荊門市,42
4209,孝感市,42
4211,黃岡市,42
4212,鹹寧市,42
4213,隨州市,42
4228,恩施土家族苗族自治州,42
4301,長沙市,43
4302,株洲市,43
4303,湘潭市,43
4304,衡陽市,43
4305,邵陽市,43
4306,岳陽市,43
4307,常德市,43
4308,張家界市,43
4309,益陽市,43
4311,永州市,43
4312,懷化市,43
4313,婁底市,43
4331,湘西土家族苗族自治州,43
4401,廣州市,44
4402,韶關市,44
4403,深圳市,44
4404,珠海市,44
4405,汕頭市,44
4406,佛山市,44
4407,江門市,44
4408,湛江市,44
4409,茂名市,44
4412,肇慶市,44
4413,惠州市,44
4414,梅州市,44
4415,汕尾市,44
4416,河源市,44
4417,陽江市,44
4418,清遠市,44
4419,東莞市,44
4451,潮州市,44
4452,揭陽市,44
4453,雲浮市,44
4501,南寧市,45
4502,柳州市,45
4503,桂林市,45
4504,梧州市,45
4505,北海市,45
4506,防城港市,45
4507,欽州市,45
4508,貴港市,45
4509,玉林市,45
4511,賀州市,45
4512,河池市,45
4513,來賓市,45
4514,崇左市,45
4601,海口市,46
4602,三亞市,46
5101,成都市,51
5103,自貢市,51
5104,攀枝花市,51
5105,泸州市,51
5106,德陽市,51
5107,綿陽市,51
5108,廣元市,51
5109,遂寧市,51
5111,樂山市,51
5113,南充市,51
5114,眉山市,51
5115,宜賓市,51
5116,廣安市,51
5117,達州市,51
5118,雅安市,51
5119,巴中市,51
5132,阿壩藏族羌族自治州,51
5133,甘孜藏族自治州,51
5134,涼山彝族自治州,51
5201,貴陽市,52
5202,六盤水市,52
5203,遵義市,52
5204,安順市,52
5205,畢節市,52
5206,銅仁市,52
5223,黔西南布依族苗族自治州,52
5226,黔東南苗族侗族自治州,52
5227,黔南布依族苗族自治州,52
5301,昆明市,53
5303,曲靖市,53
5304,玉溪市,53
5305,保山市,53
5306,昭通市,53
5307,麗江市,53
5308,普洱市,53
5309,臨滄市,53
5323,楚雄彝族自治州,53
5325,紅河哈尼族彝族自治州,53
5326,文山壯族苗族自治州,53
5328,西雙版納傣族自治州,53
5329,大理白族自治州,53
5331,德宏傣族景頗族自治州,53
5333,怒江傈僳族自治州,53
5334,迪慶藏族自治州,53
5401,拉薩市,54
5421,昌都地區,54
5422,山南地區,54
5423,日喀則地區,54
5424,那曲地區,54
5425,阿裡地區,54
5426,林芝地區,54
6101,西安市,61
6102,銅川市,61
6103,寶雞市,61
6104,鹹陽市,61
6105,渭南市,61
6106,延安市,61
6107,漢中市,61
6108,榆林市,61
6109,安康市,61
6201,蘭州市,62
6202,嘉峪關市,62
6203,金昌市,62
6204,白銀市,62
6205,天水市,62
6206,武威市,62
6207,張掖市,62
6208,平涼市,62
6209,酒泉市,62
6211,定西市,62
6212,隴南市,62
6229,臨夏回族自治州,62
6301,西寧市,63
6321,海東地區,63
6322,海北藏族自治州,63
6323,黃南藏族自治州,63
6325,海南藏族自治州,63
6326,果洛藏族自治州,63
6327,玉樹藏族自治州,63
6328,海西蒙古族藏族自治州,63
6401,銀川市,64
6402,石嘴山市,64
6403,吳忠市,64
6404,固原市,64
6405,中衛市,64
6501,烏魯木齊市,65
6502,克拉瑪依市,65
6521,吐魯番地區,65
6522,哈密地區,65
6523,昌吉回族自治州,65
6527,博爾塔拉蒙古自治州,65
6528,巴音郭楞蒙古自治州,65
6529,阿克蘇地區,65
6531,喀什地區,65
6532,和田地區,65
6542,塔城地區,65
6543,阿勒泰地區,65