kettle创建并连接mysql资源库创建转换中文乱码

将MySQL的字符集编码修改为utf-8。

mysql安装目录，找到my.ini文件

[client]

character-set-server=utf8

[mysql]

character-set-server=utf8

[mysqld]

character-set-server=utf8

重启MySQL数据库：

停止
service mysql stop;


启动
service mysql start;

查看字符集编码：

[root@Hadoop102 usr]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| 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)

但回到kettle后发现转换无法保存，抛出java.sql.SQLException: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation ‘=’的错误，这是由于之前创建的表与现在数据库字符集不一致导致。

解决方法1：删库重来（一般不采取）。

解决方法2：修改与kettle相关的所有表（R_开头）的字符集与数据库目前字符集编码一致。

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| R_CLUSTER                 |
| R_CLUSTER_SLAVE           |
| R_CONDITION               |
| R_DATABASE                |
| R_DATABASE_ATTRIBUTE      |
| R_DATABASE_CONTYPE        |
| R_DATABASE_TYPE           |
| R_DEPENDENCY              |
| R_DIRECTORY               |
| R_ELEMENT                 |
| R_ELEMENT_ATTRIBUTE       |
| R_ELEMENT_TYPE            |
| R_JOB                     |
| R_JOBENTRY                |
| R_JOBENTRY_ATTRIBUTE      |
| R_JOBENTRY_COPY           |
| R_JOBENTRY_DATABASE       |
| R_JOBENTRY_TYPE           |
| R_JOB_ATTRIBUTE           |
| R_JOB_HOP                 |
| R_JOB_LOCK                |
| R_JOB_NOTE                |
| R_LOG                     |
| R_LOGLEVEL                |
| R_NAMESPACE               |
| R_NOTE                    |
| R_PARTITION               |
| R_PARTITION_SCHEMA        |
| R_REPOSITORY_LOG          |
| R_SLAVE                   |
| R_STEP                    |
| R_STEP_ATTRIBUTE          |
| R_STEP_DATABASE           |
| R_STEP_TYPE               |
| R_TRANSFORMATION          |
| R_TRANS_ATTRIBUTE         |
| R_TRANS_CLUSTER           |
| R_TRANS_HOP               |
| R_TRANS_LOCK              |
| R_TRANS_NOTE              |
| R_TRANS_PARTITION_SCHEMA  |
| R_TRANS_SLAVE             |
| R_TRANS_STEP_CONDITION    |
| R_USER                    |
| R_VALUE                   |
| R_VERSION                 |
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
74 rows in set (0.00 sec)

mysql> select * from R_USER 
    -> ;
+---------+-------+-----------------------------------+---------------+-------------------------+---------+
| ID_USER | LOGIN | PASSWORD                          | NAME          | DESCRIPTION             | ENABLED |
+---------+-------+-----------------------------------+---------------+-------------------------+---------+
|       1 | admin | 2be98afc86aa7f2e4cb79ce71da9fa6d4 | Administrator | User manager            |       1 |
|       2 | guest | 2be98afc86aa7f2e4cb79ce77cb97bcce | Guest account | Read-only guest account |       1 |
+---------+-------+-----------------------------------+---------------+-------------------------+---------+
2 rows in set (0.00 sec)

mysql> select * from R_TRANSFORMATION;
Empty set (0.00 sec)

mysql> select * from R_TRANS_SLAVE;
Empty set (0.00 sec)
mysql> ALTER TABLE R_TRANSFORMATION CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;               
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE R_CLUSTER CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;       
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE R_CLUSTER_SLAVE CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;                              
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE R_CONDITION CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;               
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE R_DATABASE CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;           
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE R_DATABASE_ATTRIBUTE CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;          
Query OK, 13 rows affected (0.06 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE R_DATABASE_CONTYPE CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;                    
Query OK, 6 rows affected (0.05 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE R_DATABASE_TYPE CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;                  
Query OK, 54 rows affected (0.05 sec)
Records: 54  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE R_DEPENDENCY CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;               
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE R_DIRECTORY CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;            
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE R_ELEMENT CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;           
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE R_ELEMENT_ATTRIBUTE CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;         
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE R_ELEMENT_TYPE CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;                   
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE R_JOB CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;              
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE R_JOBENTRY CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;                     
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE R_JOBENTRY_ATTRIBUTE CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;          
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> Ctrl-C -- exit!
Aborted
[root@Hadoop102 usr]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.6.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> Ctrl-C -- exit!
Aborted
[root@Hadoop102 usr]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.6.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> ALTER   TABLE  R_JOBENTRY_COPY           CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_JOBENTRY_DATABASE       CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER   TABLE   R_JOBENTRY_TYPE           CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER   TABLE   R_JOB_ATTRIBUTE           CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_JOBENTRY_TYPE           CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 89 rows affected (0.01 sec)
Records: 89  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_JOB_ATTRIBUTE           CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_JOB_HOP                 CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_JOB_LOCK                CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER   TABLE   R_JOB_NOTE                CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_JOB_NOTE                CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER   TABLE   R_LOG                     CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_LOG                     CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_LOGLEVEL                CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER   TABLE   R_NAMESPACE               CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_NAMESPACE               CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER   TABLE   R_NOTE                    CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_NOTE                    CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER   TABLE   R_PARTITION               CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_PARTITION               CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER   TABLE   R_PARTITION_SCHEMA        CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_PARTITION_SCHEMA        CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER   TABLE   R_REPOSITORY_LOG          CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER   TABLE   R_SLAVE                   CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_REPOSITORY_LOG          CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 21 rows affected (0.02 sec)
Records: 21  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_SLAVE                   CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER   TABLE   R_STEP                    CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER   TABLE   R_STEP_ATTRIBUTE          CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_STEP                    CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER   TABLE   R_STEP_DATABASE           CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_STEP_ATTRIBUTE          CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER   TABLE   R_STEP_TYPE               CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_STEP_DATABASE           CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_STEP_TYPE               CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER   TABLE   R_TRANSFORMATION          CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 233 rows affected (0.02 sec)
Records: 233  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_TRANSFORMATION          CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_TRANS_ATTRIBUTE         CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_TRANS_CLUSTER           CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_TRANS_HOP               CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_TRANS_LOCK              CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_TRANS_NOTE              CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_TRANS_PARTITION_SCHEMA  CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_TRANS_SLAVE             CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_TRANS_STEP_CONDITION    CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_USER                    CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_VALUE                   CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER   TABLE   R_VERSION CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER   TABLE   R_VERSION CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from R_DATABASE_ATTRIBUTE;
+-----------------------+-------------+--------------------------------------+-----------------+
| ID_DATABASE_ATTRIBUTE | ID_DATABASE | CODE                                 | VALUE_STR       |
+-----------------------+-------------+--------------------------------------+-----------------+
|                     1 |           1 | USE_POOLING                          | N               |
|                     2 |           1 | PRESERVE_RESERVED_WORD_CASE          | Y               |
|                     3 |           1 | IS_CLUSTERED                         | N               |
|                     4 |           1 | SUPPORTS_TIMESTAMP_DATA_TYPE         | Y               |
|                     5 |           1 | SUPPORTS_BOOLEAN_DATA_TYPE           | Y               |
|                     6 |           1 | STREAM_RESULTS                       | Y               |
|                     7 |           1 | PORT_NUMBER                          | 3306            |
|                     8 |           1 | FORCE_IDENTIFIERS_TO_UPPERCASE       | N               |
|                     9 |           1 | EXTRA_OPTION_MYSQL.characterEncoding | utf8            |
|                    10 |           1 | PREFERRED_SCHEMA_NAME                | NULL            |
|                    11 |           1 | FORCE_IDENTIFIERS_TO_LOWERCASE       | N               |
|                    12 |           1 | SQL_CONNECT                          | set names utf8; |
|                    13 |           1 | QUOTE_ALL_FIELDS                     | N               |
+-----------------------+-------------+--------------------------------------+-----------------+
13 rows in set (0.00 sec)

mysql> Ctrl-C -- exit!
Aborted
[root@Hadoop102 usr]# 

至此结束，最后回到kettle工具创建转换，保存问题解决！

PS：最好在安装mysql时便将字符集修改为utf8，避免不必要的麻烦。