如何在MySQL中创建适配给定JSON数据结构的数据表

你可以根据业务场景从以下两种常见方案中选择：

方案1：拆分为独立字段存储（推荐，符合关系型数据库设计规范） #

把shipTo、billTo内部的属性全部拆成单独的表字段，适合需要经常按地址信息筛选、统计的场景，查询效率更高，也支持单独给地址字段加索引。
建表语句参考：

CREATE TABLE t1(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '建议新增主键作为唯一标识',
    name VARCHAR(200) COMMENT '订单所属人姓名',
    sku VARCHAR(200) COMMENT '商品SKU编号',
    -- 收件信息拆分字段
    ship_to_name VARCHAR(200) COMMENT '收件人姓名',
    ship_to_address VARCHAR(500) COMMENT '收件人详细地址',
    ship_to_city VARCHAR(100) COMMENT '收件人所在城市',
    ship_to_state VARCHAR(50) COMMENT '收件人所在州/省份',
    ship_to_zip VARCHAR(20) COMMENT '收件人邮编',
    -- 账单信息拆分字段
    bill_to_name VARCHAR(200) COMMENT '账单联系人姓名',
    bill_to_address VARCHAR(500) COMMENT '账单地址',
    bill_to_city VARCHAR(100) COMMENT '账单联系人所在城市',
    bill_to_state VARCHAR(50) COMMENT '账单联系人所在州/省份',
    bill_to_zip VARCHAR(20) COMMENT '账单地址邮编'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '订单信息表';

方案2：使用JSON类型存储（MySQL 5.7及以上版本支持） #

如果嵌套结构变动频繁，或者不需要经常对地址内部字段做查询筛选，可以直接用MySQL原生的JSON类型存储嵌套对象，建表更灵活。
建表语句参考：

CREATE TABLE t1(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '建议新增主键作为唯一标识',
    name VARCHAR(200) COMMENT '订单所属人姓名',
    sku VARCHAR(200) COMMENT '商品SKU编号',
    shipTo JSON COMMENT '收件信息JSON对象',
    billTo JSON COMMENT '账单信息JSON对象'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '订单信息表';

使用示例：

• 插入数据时可以直接传入符合格式的JSON字符串
• 查询内部字段可以用JSON函数，比如取收件人姓名：SELECT JSON_UNQUOTE(JSON_EXTRACT(shipTo, '$.name')) AS ship_name FROM t1;

两种方案的优劣势对比：

• 方案1优势：兼容所有MySQL版本，查询效率高，支持单独给地址字段加索引，数据校验更简单
• 方案2优势：建表逻辑简单，不需要因为JSON结构变动修改表结构，适合结构不固定的场景

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