1.初始化表
CREATE TABLE `cate_rel` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`categoryCode1` varchar(255) DEFAULT NULL COMMENT '类目一',
`categoryCode2` varchar(255) DEFAULT NULL COMMENT '类目二',
`categoryCode3` varchar(255) DEFAULT NULL COMMENT '类目三',
`categoryCode4` varchar(255) DEFAULT NULL COMMENT '类目四',
`updateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=647 DEFAULT CHARSET=utf8 COMMENT='类目关系';
CREATE TABLE `category` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(255) DEFAULT NULL COMMENT '类目名称',
`categoryCode` varchar(255) DEFAULT NULL COMMENT '类目code',
`updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_code` (`categoryCode`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=202 DEFAULT CHARSET=utf8 COMMENT='类目信息表';
2.插入数据
INSERT INTO test.cate_rel (categoryCode1,categoryCode2,categoryCode3,categoryCode4,updateTime) VALUES
('1','2','3','4','2021-05-22 00:44:26'),
('1','2','32','42','2021-05-22 00:45:02'),
('11','21','33','43','2021-05-22 00:45:18'),
('11','22','34','44','2021-05-22 00:45:39');
INSERT INTO test.category (name,categoryCode,updateTime) VALUES
('类目一','1','2021-05-22 00:46:54'),
('类目二','2','2021-05-22 00:47:04'),
('类目三','3','2021-05-22 00:47:14'),
('类目四','4','2021-05-22 00:47:21'),
('类目三二','32','2021-05-22 00:47:38'),
('类目四二','42','2021-05-22 00:47:47'),
('类目一一','11','2021-05-22 00:48:04'),
('类目二一','21','2021-05-22 00:48:22'),
('类目二二','22','2021-05-22 00:48:31'),
('类目三三','33','2021-05-22 00:49:04');
INSERT INTO test.category (name,categoryCode,updateTime) VALUES
('类目三四','34','2021-05-22 00:48:57'),
('类目四三','43','2021-05-22 00:49:26'),
('类目四四','44','2021-05-22 00:49:35');
展示数据情况
cate_rel
category
3.查询全排列:mysql使用多次left join
SELECT
categoryCode1,
ca1.name,
categoryCode2,
ca2.name,
categoryCode3,
ca3.name,
categoryCode4,
ca4.name
FROM
cate_rel as rel
left join category as ca1 on
rel.categoryCode1 = ca1.categoryCode
left join category as ca2 on
rel.categoryCode2 = ca2.categoryCode
left join category as ca3 on
rel.categoryCode3 = ca3.categoryCode
left join category as ca4 on
rel.categoryCode4 = ca4.categoryCode ;
展示结果,可以很容易的使用这种方式保存多层级类目,树状结构的数据,展示如下也很好理解。