mysql json_set多维,mysqlimport问题“设置@@ character_set_database = binary”；防止加载json值...

I have been using mysqlimport without problems for a long time, now as mysql 5.7 added json data type support, I'm trying to use mysqlimport with rows containing json data.

Here is an example of a row in csv file that will be imported using mysqlimport:

column_A_value,column_B_value,[{"x":20,"y":"some name"}]

Notice that the last column type is json. Now when using mysqlimport as the following:

mysqlimport -u user -ppass -h localhost --columns='col_A,col_B,col_C' --local --fields-terminated-by=',' some_db /path/to/products.txt

I got the following error: Cannot create a JSON value from a string with CHARACTER SET 'binary'., when using table: products

However using the generated LOAD DATA IN FILE instead of mysqlimport worked without problems! I opened mysql log and checked the generated LOAD DATA IN FILE command when running mysqlimport, then copied and pasted it, and it worked without problems! I ran something like:

LOAD DATA LOCAL INFILE '/path/to/products.txt'

INTO TABLE products

And it worked! The only difference is that in the log, when running mysqlimport this line was generated also

Query /*!40101 set @@character_set_database=binary */

Then LOAD DATA IN FILE command was generated, so that line is the source of the problem.

So anyway I tried setting character set to utf8,

mysqlimport -u user -ppass -h localhost --columns='col_A,col_B,col_C' --local --fields-terminated-by=',' --default-character-set=utf8 some_db /path/to/products.txt

but in vain, same error happened.

So any clue how to solve this character set issue please ?

解决方案

Check your character set configuration:

show variables like ‘%char%';

Not responding to --default-character-set might still be a bug of mysqlimport:

https://bugs.mysql.com/bug.php?id=29712

Possible Solutions:

Try SET NAMES utf8; before the import

Try using mysql with --default-character-set=utf8 instead of mysqlimport

Alter your my.cnf:

[mysqld]

init-connect='SET NAMES utf8'

character-set-server=utf8

collation-server=utf8_general_ci

default-character-set=utf8

[client]

default-character-set=utf8