一、mysql中的编码
1mysql> show variables like 'collation_%'; 2+----------------------+-------------------+ 3| Variable_name | Value | 4+----------------------+-------------------+ 5| collation_connection | latin1_swedish_ci | 6| collation_database | latin1_swedish_ci | 7| collation_server | latin1_swedish_ci | 8+----------------------+-------------------+ 93 rows in set (0.00 sec) 10mysql> show variables like 'character_set_%'; 11+--------------------------+----------------------------+ 12 13| Variable_name | Value | 14+--------------------------+----------------------------+ 15| character_set_client | latin1 | 16| character_set_connection | latin1 | 17| character_set_database | latin1 | 18| character_set_filesystem | binary | 19| character_set_results | latin1 | 20| character_set_server | latin1 | 21| character_set_system | utf8 | 22| character_sets_dir | /usr/share/mysql/charsets/ | 23+--------------------------+----------------------------+ 248 rows in set (0.00 sec)
可见原来缺省是latin1编码，会导致中文乱码。
可以在mysql中设置编码，单个设置
mysql> alter database name character set utf8;
mysql> set character_set_connection=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_connection=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_results=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_server=utf8;
Query OK, 0 rows affected (0.00 sec)
但重启后会失效。
可以修改配置文件：
1[root@Hadoop48 ~]# vi /etc/my.cnf 2[mysql] 3default-character-set=utf8 4[client] 5default-character-set=utf8 6[mysqld] 7default-character-set=utf8 8character_set_server=utf8 9init_connect='SET NAMES utf8'
重启mysql，这样确保缺省编码是utf8
[root@Hadoop48 ~]# service mysqld restart
查看是否变成utf8：
1mysql> \s 2-------------- 3mysql Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (x86_64) using readline 5.1 4 5Connection id: 2 6Current database: toplists 7Current user: root@localhost 8SSL: Not in use 9Current pager: stdout 10Using outfile: '' 11Using delimiter: ; 12Server version: 5.0.95 Source distribution 13Protocol version: 10 14Connection: Localhost via UNIX socket 15Server characterset: utf8 16Db characterset: utf8 17Client characterset: utf8 18Conn. characterset: utf8 19UNIX socket: /var/lib/mysql/mysql.sock 20Uptime: 39 sec 21 22Threads: 1 Questions: 12 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 9 Queries per second avg: 0.308 23-------------- 24 25mysql> show variables like "char%"; 26+--------------------------+----------------------------+ 27| Variable_name | Value | 28+--------------------------+----------------------------+ 29| character_set_client | utf8 | 30| character_set_connection | utf8 | 31| character_set_database | utf8 | 32| character_set_filesystem | binary | 33| character_set_results | utf8 | 34| character_set_server | utf8 | 35| character_set_system | utf8 | 36| character_sets_dir | /usr/share/mysql/charsets/ | 37+--------------------------+----------------------------+ 388 rows in set (0.00 sec) 39 40mysql> show variables like "colla%"; 41+----------------------+-----------------+ 42| Variable_name | Value | 43+----------------------+-----------------+ 44| collation_connection | utf8_general_ci | 45| collation_database | utf8_general_ci | 46| collation_server | utf8_general_ci | 47+----------------------+-----------------+ 483 rows in set (0.00 sec)
将mysql编码改成utf8，并在命令行中指定编码为utf8，执行导出报错：
1[zhouhh@Hadoop46 ~]$ sqoop export --connect "jdbc:mysql://Hadoop48/toplists?useUnicode=true&characterEncoding=utf-8" -m 1 --table award --export-dir /user/hive/warehouse/hive_myaward/000000_0 --input-null-string "\\\\N" --input-null-non-string "\\\\N" --input-fields-terminated-by "\\01" --input-lines-terminated-by "\\n" 2 312/07/20 13:17:22 INFO mapred.JobClient: Task Id : attempt_201207191159_0233_m_000000_0, Status : FAILED 4java.io.IOException: java.sql.SQLException: Incorrect string value: '\xE6\x9D\x80\xE7\xA0\xB4...' for column 'nick' at row 1
原来我创建的表缺省编码不是utf8的，也需改变一下。
1mysql> ALTER TABLE award CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; 2Query OK, 0 rows affected (0.00 sec) 3Records: 0 Duplicates: 0 Warnings: 0
再导出
1[zhouhh@Hadoop46 ~]$ sqoop export --connect "jdbc:mysql://Hadoop48/toplists?useUnicode=true&characterEncoding=utf-8" -m 1 --table award --export-dir /user/hive/warehouse/hive_myaward/000000_0 --input-null-string "\\\\N" --input-null-non-string "\\\\N" --input-fields-terminated-by "\\01" --input-lines-terminated-by "\\n" 2... 312/07/20 14:03:10 INFO mapred.JobClient: map 0% reduce 0% 412/07/20 14:03:24 INFO mapred.JobClient: map 100% reduce 0% 5... 612/07/20 14:03:29 INFO mapreduce.ExportJobBase: Exported 2 record 7 8检查表 9mysql> select * from award; 10+-------------------------------+-----------+-----------+------+-----------+-----------+--------+------+-------------+-----------------+---------+ 11| rowkey | productid | matchid | rank | tourneyid | userid | gameid | gold | loginid | nick | plat | 12+-------------------------------+-----------+-----------+------+-----------+-----------+--------+------+-------------+-----------------+---------+ 13| 2012-04-27 06:55:00:402713629 | 5947 | 433203828 | 2 | 4027102 | 402713629 | 1001 | NULL | 715878221 | 杀破天A | ios | 14| 2012-04-27 06:55:00:406788559 | 778 | 433203930 | 19 | 4017780 | 406788559 | 1001 | 1 | 13835155880 | 亲牛牛旦旦 | android | 15+-------------------------------+-----------+-----------+------+-----------+-----------+--------+------+-------------+-----------------+---------+ 162 rows in set (0.00 sec)