load data infile "sql.txt" table a fields terminate by "," 当sql.txt中有中文,用java或者client端输出出现中文乱码。
>locale
LANG="zh_CN.UTF-8"
LC_COLLATE="zh_CN.UTF-8"
LC_CTYPE="zh_CN.UTF-8"
LC_MESSAGES="zh_CN.UTF-8"
LC_MONETARY="zh_CN.UTF-8"
LC_NUMERIC="zh_CN.UTF-8"
LC_TIME="zh_CN.UTF-8"
LC_ALL=
>export LC_ALL="zh_CN.UTF-8"
locale: Cannot set LC_CTYPE to default locale: No such file or directory
locale: Cannot set LC_MESSAGES to default locale: No such file or directory
locale: Cannot set LC_ALL to default locale: No such file or directory
....
服务器端不支持中文编码,运行下面的命令安装中文支持。
yum install kde-l10n-Chinese
yum reinstall glibc-common
设置服务器端LC_ALL,此时服务器端和Client端编码相同
>export LC_ALL="zh_CN.UTF-8"
>locale
LANG=
LC_CTYPE="zh_CN.UTF-8"
LC_NUMERIC="zh_CN.UTF-8"
LC_TIME="zh_CN.UTF-8"
LC_COLLATE="zh_CN.UTF-8"
LC_MONETARY="zh_CN.UTF-8"
LC_MESSAGES="zh_CN.UTF-8"
LC_PAPER="zh_CN.UTF-8"
LC_NAME="zh_CN.UTF-8"
LC_ADDRESS="zh_CN.UTF-8"
LC_TELEPHONE="zh_CN.UTF-8"
LC_MEASUREMENT="zh_CN.UTF-8"
LC_IDENTIFICATION="zh_CN.UTF-8"
LC_ALL=zh_CN.UTF-8
查看编码
show variables like '%char%';
设置全局编码为UTF-8,修改my.cnf文件。
[mysqld]
character-set-server=utf8
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
配置完成,重启mysql,查看编码。
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| 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.00 sec)
DEFAULT CHARSET=utf8
create table shop_info(
`shop_id` int(10) PRIMARY KEY,
`city_name` varchar(100),
`location_id` varchar(10),
`per_pay`int(10),
`score` int(10),
`comment_cnt` int(10),
`shop_level` int(10),
`cate_1_name` varchar(50),
`cate_2_name` varchar(50),
`cate_3_name` varchar(50)
)DEFAULT CHARSET=utf8;
当完成以上全部配置,load data infile 文件装载到数据库,用client端读取,能正确显示中文。