mysql类目树关系的保存和全排列查询

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 ;

展示结果，可以很容易的使用这种方式保存多层级类目，树状结构的数据，展示如下也很好理解。