json - 如何在 MySQL (10.2.36-MariaDB) 表中导入 JSON 值？

Question

我有以下 JSON 文件：

{
  "ID": 5464015,
  "CUSTOMER_ID": 1088020,
  "CUSOTMER_NAME": "My customer 1"
}
{
  "ID": 5220812,
  "CUSTOMER_ID": 523323,
  "CUSOTMER_NAME": "My customer 2"
}
{
  "ID": 5205039,
  "CUSTOMER_ID": 1934806,
  "CUSOTMER_NAME": "My customer 3"
}

从 shell 脚本中，我想将这些值导入到 MariaDB 表（MariaDB 服务器版本：10.2.36-MariaDB）中，并已创建相关列：

• ID
• 客户ID
• 顾客姓名

但是对于 CUSTOMER_NAME，我不想在值的开头和结尾导入双引号。有没有简单的方法来做到这一点？

或者如果不可能，如果我有这样的 txt 或 csv 文件：

5464015,1088020,"My customer 1"
5220812,523323,"My customer 2"
5205039,1934806,"My customer 3"

如何导入？

非常感谢

标签： jsonmariadb

Answer 1

CREATE TABLE test (ID INT, CUSTOMER_ID INT, CUSTOMER_NAME VARCHAR(255));

SET @data := '
[  { "ID": 5464015,
     "CUSTOMER_ID": 1088020,
     "CUSTOMER_NAME": "My customer 1"
   },
   { "ID": 5220812,
     "CUSTOMER_ID": 523323,
     "CUSTOMER_NAME": "My customer 2"
   },
   { "ID": 5205039,
     "CUSTOMER_ID": 1934806,
     "CUSTOMER_NAME": "My customer 3"
   }
]
';

INSERT INTO test
SELECT *
FROM JSON_TABLE(@data,
                "$[*]" COLUMNS( ID INT PATH "$.ID",
                                CUSTOMER_ID INT PATH "$.CUSTOMER_ID",
                                CUSTOMER_NAME VARCHAR(255) PATH "$.CUSTOMER_NAME")
               ) AS jsontable;

SELECT * FROM test;

ID	客户ID	顾客姓名
5464015	1088020	我的客户 1
5220812	523323	我的客户 2
5205039	1934806	我的客户 3

db<>在这里摆弄