如何将JSON运动数据转换为MySQL的CREATE TABLE与INSERT语句？

1. 创建MySQL表结构 #

根据你的JSON数据，我们可以创建一个exercises表，对于数组类型的字段（比如primaryMuscles、instructions），使用MySQL的JSON类型存储最直接；如果需要更规范化的结构，可以拆分出关联表，这里先给出简单高效的单表方案：

CREATE TABLE exercises (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    force VARCHAR(50),
    level VARCHAR(50),
    mechanic VARCHAR(50),
    equipment VARCHAR(100),
    primaryMuscles JSON NOT NULL,
    secondaryMuscles JSON NOT NULL,
    instructions JSON NOT NULL,
    category VARCHAR(50) NOT NULL
);

说明：

• id作为自增主键，确保每条记录唯一
• name设为UNIQUE避免重复的运动条目
• 数组字段用JSON类型，能直接存储JSON数组格式的数据
• 允许mechanic为NULL，对应示例中的空值情况

2. 插入数据的两种方法 #

方法一：手动生成INSERT语句 #

直接把JSON中的每条记录转换成MySQL的INSERT语法，注意转义引号，数组直接用JSON格式字符串：

INSERT INTO exercises (name, force, level, mechanic, equipment, primaryMuscles, secondaryMuscles, instructions, category)
VALUES
('3/4 Sit-Up', 'pull', 'beginner', 'compound', 'body only', '["abdominals"]', '[]', '[
    "Lie down on the floor and secure your feet. Your legs should be bent at the knees.",
    "Place your hands behind or to the side of your head. You will begin with your back on the ground. This will be your starting position.",
    "Flex your hips and spine to raise your torso toward your knees.",
    "At the top of the contraction your torso should be perpendicular to the ground. Reverse the motion, going only ¾ of the way down.",
    "Repeat for the recommended amount of repetitions."
]', 'strength'),
('90/90 Hamstring', 'push', 'beginner', NULL, 'body only', '["hamstrings"]', '["calves"]', '[
    "Lie on your back, with one leg extended straight out.",
    "With the other leg, bend the hip and knee to 90 degrees. You may brace your leg with your hands if necessary. This will be your starting position.",
    "Extend your leg straight into the air, pausing briefly at the top. Return the leg to the starting position.",
    "Repeat for 10-20 repetitions, and then switch to the other leg."
]', 'stretching'),
('Ab Crunch Machine', 'pull', 'intermediate', 'isolation', 'machine', '["abdominals"]', '[]', '[
    "Select a light resistance and sit down on the ab machine placing your feet under the pads provided and grabbing the top handles. Your arms should be bent at a 90 degree angle as you rest the triceps on the pads provided. This will be your starting position.",
    "At the same time, begin to lift the legs up as you crunch your upper torso. Breathe out as you perform this movement. Tip: Be sure to use a slow and controlled motion. Concentrate on using your abs to move the weight while relaxing your legs and feet.",
    "After a second pause, slowly return to the starting position as you breathe in.",
    "Repeat the movement for the prescribed amount of repetitions."
]', 'strength');

方法二：用MySQL内置函数直接导入JSON数据 #

如果你的JSON数据保存在文件中（比如exercises.json），可以用LOAD_FILE结合JSON_TABLE直接解析插入，效率更高：

首先确保MySQL的secure_file_priv配置允许读取文件，然后执行以下SQL：

INSERT INTO exercises (name, force, level, mechanic, equipment, primaryMuscles, secondaryMuscles, instructions, category)
SELECT 
    name,
    force,
    level,
    mechanic,
    equipment,
    primaryMuscles,
    secondaryMuscles,
    instructions,
    category
FROM JSON_TABLE(
    LOAD_FILE('/path/to/exercises.json'),
    '$.exercises[*]' COLUMNS(
        name VARCHAR(255) PATH '$.name',
        force VARCHAR(50) PATH '$.force',
        level VARCHAR(50) PATH '$.level',
        mechanic VARCHAR(50) PATH '$.mechanic',
        equipment VARCHAR(100) PATH '$.equipment',
        primaryMuscles JSON PATH '$.primaryMuscles',
        secondaryMuscles JSON PATH '$.secondaryMuscles',
        instructions JSON PATH '$.instructions',
        category VARCHAR(50) PATH '$.category'
    )
) AS jt;

注意替换/path/to/exercises.json为你的实际文件路径。

3. 用Python脚本自动转换（适合大量数据） #

如果你的JSON数据量很大，写个简单的Python脚本自动生成INSERT语句或者直接插入数据库：

脚本示例： #

import json
import mysql.connector

# 读取JSON数据
with open('exercises.json', 'r', encoding='utf-8') as f:
    data = json.load(f)

# 连接MySQL数据库
conn = mysql.connector.connect(
    host='your_host',
    user='your_user',
    password='your_password',
    database='your_database'
)
cursor = conn.cursor()

# 插入数据的SQL模板
insert_sql = """
INSERT INTO exercises (name, force, level, mechanic, equipment, primaryMuscles, secondaryMuscles, instructions, category)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
"""

# 遍历每条运动数据
for exercise in data['exercises']:
    # 将数组转为JSON字符串
    primary_muscles = json.dumps(exercise['primaryMuscles'])
    secondary_muscles = json.dumps(exercise['secondaryMuscles'])
    instructions = json.dumps(exercise['instructions'])
    
    # 执行插入
    cursor.execute(insert_sql, (
        exercise['name'],
        exercise['force'],
        exercise['level'],
        exercise['mechanic'],
        exercise['equipment'],
        primary_muscles,
        secondary_muscles,
        instructions,
        exercise['category']
    ))

# 提交事务并关闭连接
conn.commit()
cursor.close()
conn.close()

说明：

• 需要先安装mysql-connector-python：pip install mysql-connector-python
• 替换脚本中的数据库连接信息为你的实际配置

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