Java读取Mysql中文乱码

birdskyws

发布于 2018-09-12 15:51:41

6K0

发布于 2018-09-12 15:51:41

load data infile "sql.txt" table a fields terminate by "," 当sql.txt中有中文，用java或者client端输出出现中文乱码。

一、locale 查看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=

二、locale修改mysql服务器编码

>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

三、修改mysql默认编码

查看编码

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;