如何将字段定义与数据分离的JSON导入MySQL？

你需要导入的JSON由fields（字段标签）和data（数据内容）两部分组成，结构如下：

{
  "fields": [
    "id",
    "name",
    "email",
    "phone"
  ],
  "data": [
    [
      42,
      "Bernice",
      "bernice@gmail.com",
      "212-555-1212"
    ],
    [
      64,
      "Jimmy",
      "jimmy@gmail.com",
      "203-555-1212"
    ],
    [
      3,
      "Marvin",
      "marvin@gmail.com",
      "415-555-1212"
    ]
  ]
}

这类结构完全可以通过MySQL的JSON_TABLE()函数完成导入，以下是具体实现步骤：

1. 创建目标表 #

首先创建与fields对应结构的MySQL表：

CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20)
);

2. 直接导入JSON字符串 #

如果JSON内容以字符串形式存在，可直接使用以下SQL插入数据：

INSERT INTO users (id, name, email, phone)
SELECT id, name, email, phone
FROM JSON_TABLE(
    -- 替换为你的JSON字符串
    '{
      "fields": ["id", "name", "email", "phone"],
      "data": [[42,"Bernice","bernice@gmail.com","212-555-1212"],[64,"Jimmy","jimmy@gmail.com","203-555-1212"],[3,"Marvin","marvin@gmail.com","415-555-1212"]]
    }',
    '$.data[*]' COLUMNS (
        id INT PATH '$[0]',
        name VARCHAR(50) PATH '$[1]',
        email VARCHAR(100) PATH '$[2]',
        phone VARCHAR(20) PATH '$[3]'
    )
) AS user_data;

3. 导入本地JSON文件 #

如果JSON存储在本地文件中，可通过LOAD_FILE()函数读取文件内容后导入：

INSERT INTO users (id, name, email, phone)
SELECT id, name, email, phone
FROM JSON_TABLE(
    -- 替换为你的JSON文件路径
    LOAD_FILE('/var/lib/mysql-files/users.json'),
    '$.data[*]' COLUMNS (
        id INT PATH '$[0]',
        name VARCHAR(50) PATH '$[1]',
        email VARCHAR(100) PATH '$[2]',
        phone VARCHAR(20) PATH '$[3]'
    )
) AS user_data;

注意事项 #

• 使用LOAD_FILE()时，需确保MySQL服务有权限读取目标文件，且secure_file_priv参数已配置允许访问该文件路径（可通过SHOW VARIABLES LIKE 'secure_file_priv';查看配置）。
• 若fields的顺序或数量有变化，只需对应调整JSON_TABLE()中PATH '$[n]'的索引值即可。

内容的提问来源于stack exchange，提问作者Jon