MySQL 5.7.12 导入无法从具有 CHARACTER SET 'binary' 的字符串创建 JSON 值

2021-12-25 00:00:00 json import mysql mysql-json

我导出了包含 JSON 列的数据库.迁移到新服务器后，我的导入每次都崩溃并出现如下错误:

I exported my database with JSON columns in it. After I migrated to a new server, my import crashed every time with an error like:

无法从带有 CHARACTER SET 'binary' 的字符串创建 JSON 值

cannot create a JSON value from a string with CHARACTER SET 'binary'

在 stackoverflow 上，我找到了这篇文章，但对我不起作用:mysqlimport 问题set @@character_set_database=binary"；这会阻止加载 json 值

On stackoverflow, I found this post but didn't work for me: mysqlimport issues "set @@character_set_database=binary" which prevents loading json values

文件大小为 2GB，无法打开.

The file is 2GB and isn't possible to open the file.

有人想导入我的数据库文件吗?

Anyone has an idea to import my database file?

推荐答案

您可以将正则表达式应用于您导出的 SQL 文本，这会将您的二进制字符串转换为可插入的格式.当我遇到这个问题时，这是我快速而肮脏的修复

You can apply a regex to the SQL text which you exported which will convert your binary strings into an insertable format. This was my quick and dirty fix when I faced this issue

(X'[^,)]*') CONVERT($1 using utf8mb4)

应用这个正则表达式意味着

Applying this regex means

INSERT INTO json_table (json_column) VALUES (X'7B22666F6F223A2022626172227D');

现在变成

INSERT INTO json_table (json_column) VALUES (CONVERT(X'7B22666F6F223A2022626172227D' using utf8mb4));

相关文章