Mysql 拆表案例

文档更新日期：2024年09月02日

需求描述：Tmp 数据库中的 goods 商品表被创建为二范式 2NF 规范，需要修正为三范式 3NF 规范

• 2NF 规范：满足 1NF（数据表中各列数据不可再分割） 的基础上，主数据与从数据直接依赖，即删除主数据，从数据连带删除
• 3NF 规范：满足 2NF 的基础上，主数据与从数据间接依赖，即删除主数据，不影响从数据

文档中，为了区分拆分关系，增加了关键词区分：

• 主表：要拆分的原始表，即 goods 商品表
• 子表：拆分后的新表，即 goods_cates 分类表、goods_brands 品牌表 、goods_cates_brands 关系表

Step1：准备数据

准备环境，包括创建数据库 Tmp、商品表 goods，插入初始数据。

# 创建数据库
create database if not exists Tmp char set 'utf8';

# 切库
use Tmp;

# 创建商品主表
create table goods(
    id int primary key auto_increment,  # 商品 ID，主键+自增
    name VARCHAR(20),   # 商品名称，字符串类型，最长 20 字符
    cate_name   VARCHAR(10),    # 商品类型，字符串类型，最长 10 字符
    brand_name  VARCHAR(10),    # 商品品牌，字符串类型，最长 10 字符
    price   DECIMAL(20,2),      # 商品价格，数值类型，最长 20位，其中小数位 2 位
    is_show int,    # 是否展示，数值类型
    is_saleoff  int # 是否预售，数值类型
);

# 查询表结构
desc goods;

# 插入数据
insert into goods values
    (null,'MacbookPro M1','笔记本','Apple',16999,1,0) ,
    (null,'MacbookPro M2','笔记本','Apple',18999,1,0) ,
    (null,'iMac','台式机','Apple',12999,1,0),
    (null,'Surface 7','平板电脑','Microsoft',8999,1,0),
    (null,'Thinkbook S 2024','笔记本','Lenovo',6799,1,0),
    (null,'小米 14','手机','小米',4999,1,0);

# 查询数据
select * from goods;

Step2：子表创建

创建商品分类表 goods_cates、商品品牌表 goods_brands、关系表 goods_cates_brands，准备进行数据拆分

# 创建商品分类 goods_cates 子表
create table goods_cates(
    id int primary key auto_increment,  # 分类 ID，主键+自增
    cname VARCHAR(10) not null          # 分类名称，非空
);

# 创建商品品牌 goods_brands 子表
create table goods_brands(
    id int primary key auto_increment,  # 品牌 ID，主键+自增
    bname VARCHAR(10) not null          # 名称，非空
);

# 创建关系表 goods_cates_brands 子表
create table goods_cates_brands(
    id int primary key auto_increment,  # 分类 ID，主键+自增
    gid INT,                             # 商品 ID，外键
    cid INT,                            # 分类 ID，外键
    bid INT,                            # 品牌 ID，外键
    constraint fk_goods foreign key (gid) references goods(id),     # 创建外键，与商品表的商品 ID 关联
    constraint fk_cates foreign key (cid) references goods_cates(id),     # 创建外键，与商品分类表的分类 ID 关联
    constraint fk_brands foreign key (bid) references goods_brands(id),     # 创建外键，与商品品牌表的品牌 ID 关联
    unique (gid,cid),                       # 确保一个商品只会有一个分类
    unique (gid,bid)                        # 确保一个商品只会有一个品牌
);

# 查询子表与关系表结构
desc goods_cates;       # 查看商品分类表结构
desc goods_brands;      # 查看商品品牌表结构
desc goods_cates_brands;    # 查看商品-商品分类-商品品牌关系表结构

Step3：拆表

从主表提取商品分类、商品品牌字段，分别同步至已创建的子表中

# 从商品表提取商品分类到子表
insert into goods_cates(cname) select cate_name from goods group by cate_name;
# 查询分类表数据
select * from goods_cates;
# 从商品表提取商品品牌到子表
insert into goods_brands(bname) select brand_name from goods group by brand_name;
# 查询品牌表数据
select * from goods_brands;

Step4：修改数据

依据子表数据反向修改主表中的商品分类和商品品牌为子表中的 ID

# 修改商品表中的 cate_name 值
update goods g join goods_cates gc on g.cate_name = gc.cname set  g.cate_name = gc.id ;
# 查询商品表中修改后的分类数据
select cate_name from goods;

# 修改商品表中的品牌字段 brand_name 值
update goods g join goods_brands gb on g.brand_name = gb.bname set g.brand_name = gb.id;
# 查询商品表中修改后的品牌数据
select brand_name from goods;

Step5 修改结构

修改主表分类、品牌字段的字段属性：int、外键

# 修改字段名为 cate_id ，修改数据类型为 int
alter table goods change cate_name cate_id int;
# 查询商品表中商品分类的字段结构
desc goods;
# 修改字段名称为 brand_id，并修改数据类型为 int
alter table goods change brand_name brand_id int;
# 查询商品表字段结构
desc goods;

优化

上述为拆分表需求，实际业务中，会严格按照三范式进行关系表创建，即：商品表、品牌表、分类表、商品_分类_品牌关系表，按照该实际需求优化思路为：删除商品表中存储的分类 id和品牌 id字段，后续业务使用时，如需查询商品关联的品牌、分类，则使用关系表查询

alter table goods drop column cate_id;      # 删除分类 ID
alter table goods drop column brand_id;     # 删除品牌 ID

# 查询商品表结构
desc goods;