LOAD DATA INFILE字符编码的问题

引用

load data infile 'C:\Users\Denny\Desktop\TestPage\xx.csv'
                     into table taobao_shop
                     CHARACTER SET  utf8
                     FIELDS TERMINATED BY ';'
                     ENCLOSED BY '' ESCAPED BY ''
                     LINES TERMINATED BY '\n' STARTING BY ''
                     (id, sid, cid, nick, display_name);

想把一个utf-8的格式化好的文本文件导入到数据库中， 文本中包含中文， 对应的数据库表也都是使用utf-8编码，但是使用LOAD DATA INFILE导入到数据库中发现中文是乱码？首先想到的是mysql client需要使用utf-8，于是在my.cnf文件中加入。

引用

default-character-set=utf8

重试发现问题依旧，然后打开mysql query browser里面LOAD DATA INFILE的文档仔细阅读， 发现有“CHARACTER SET ”这个参数， 第一感觉这个应该就是我想要的。

引用

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [FIELDS
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

于是加上character set参数， 果然ok了。

引用

LOAD DATA INFILE '/tmp/youku_keywords' INTO TABLE youku CHARACTER SET utf8 FIELDS TERMINATED BY ',';

注意character set是utf8 而不是 utf-8.