mysqlimport问题" set @@ character_set_database = binary"这可以防止加载json值 [英] mysqlimport issues "set @@character_set_database=binary" which prevents loading json values

问题描述

很长一段时间以来，我一直在使用mysqlimport，而现在，随着mysql 5.7添加了对json数据类型的支持，我正在尝试将mysqlimport与包含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.

以下是csv文件中一行的示例，该行将使用mysqlimport导入:

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"}]

请注意，最后一列类型为json.现在，当使用mysqlimport时，如下所示:

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

我遇到以下错误:Cannot create a JSON value from a string with CHARACTER SET 'binary'., when using table: products

但是使用生成的LOAD DATA IN FILE代替mysqlimport可以正常工作！我打开mysql日志，并在运行mysqlimport时检查了生成的LOAD DATA IN FILE命令，然后将其复制并粘贴了，并且没有问题！我运行了类似的内容:

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

它奏效了！唯一的区别是在日志中，当运行mysqlimport时，也会生成此行

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 */

然后生成了LOAD DATA IN FILE命令，因此该行是问题的根源.

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

所以无论如何我都尝试将字符集设置为utf8，

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 ?

推荐答案

检查您的字符集配置:

显示类似％char％"的变量；

show variables like ‘%char%';

不响应--default-character-set可能仍然是mysqlimport的错误: https://bugs.mysql.com/bug.php?id=29712

Not responding to --default-character-set might still be a bug of mysqlimport: https://bugs.mysql.com/bug.php?id=29712

可能的解决方案:

1. 在导入之前尝试SET NAMES utf8;
2. 尝试将mysql与--default-character-set=utf8一起使用，而不是mysqlimport
3. 更改您的my.cnf:

1. Try SET NAMES utf8; before the import
2. Try using mysql with --default-character-set=utf8 instead of mysqlimport
3. 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

这篇关于mysqlimport问题" set @@ character_set_database = binary"这可以防止加载json值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！