用MySQL生成树形结构的一维表（MySQL一维表生成树形）
用MySQL生成树形结构的一维表
在数据库中，经常需要存储树形结构的数据，例如组织架构、商品分类等。但是在查询和展示数据时，常常会遇到树形结构不方便的问题。因此，本篇文章将介绍如何通过MySQL生成树形结构的一维表，方便后续查询和展示。
1. 创建数据表
我们需要创建一个名为tree的数据表来存储树形结构数据。表格包含四个字段：id、name、parent_id和level。
“`mysql
CREATE TABLE `tree` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`parent_id` int(11) DEFAULT NULL,
`level` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2. 插入测试数据
为了演示如何生成树形结构的一维表，我们需要插入一些测试数据。以下是一个简单的组织结构。
```mysqlINSERT INTO `tree` (`id`, `name`, `parent_id`, `level`) VALUES
(1, '总公司', NULL, 0),(2, '市场部', 1, 1),
(3, '人力资源部', 1, 1),(4, '技术部', 1, 1),
(5, '销售部', 2, 2),(6, '客服部', 2, 2),
(7, '招聘部', 3, 2),(8, '培训部', 3, 2),
(9, '开发部', 4, 2),(10, '测试部', 4, 2),
(11, '广州分公司', 1, 1),(12, '市场部', 11, 2),
(13, '销售部', 12, 3),(14, '客服部', 12, 3);
3. 生成树形结构的一维表
接下来，我们通过使用MySQL自定义变量和函数的方式生成树形结构的一维表。以下是生成树形结构的一维表的代码。
“`mysql
SET @N:=0;
SET @P:=”;
DROP FUNCTION IF EXISTS `get_children`;
DELIMITER $$
CREATE FUNCTION `get_children`(parent_id INT)
RETURNS VARCHAR(1024) CHARSET utf8
BEGIN
DECLARE sTempText VARCHAR(1024);
IF @P=” THEN
SET @P=parent_id;
END IF;
SET @N:=@N+1;
SELECT INTO @sTempText
CONCAT(‘-‘,LPAD(@N,10,’0′),’|’,LPAD(parent_id,10,’0′),’|’,LPAD(id,10,’0′))
FROM tree
WHERE parent_id=@P;
SELECT @sTempText;
IF @sTempText IS NOT NULL THEN
RETURN CONCAT_WS(‘,’,@sTempText,
get_children(SUBSTRING(@sTempText,16,10)),
get_children(SUBSTRING(@sTempText,27,10)),
get_children(SUBSTRING(@sTempText,38,10)),
get_children(SUBSTRING(@sTempText,49,10)),
get_children(SUBSTRING(@sTempText,60,10)),
get_children(SUBSTRING(@sTempText,71,10)),
get_children(SUBSTRING(@sTempText,82,10)),
get_children(SUBSTRING(@sTempText,93,10)),
get_children(SUBSTRING(@sTempText,104,10)));
ELSE
RETURN ”;
END IF;
END$$
DELIMITER ;
SELECT id, name, parent_id, level
FROM tree
UNION ALL
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.children,’,’,numbers.n),’,’,-1) AS id,
NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(t.children,’,’,numbers.n+1),’,’,1),”) AS name,
NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(t.children,’,’,numbers.n+1),’,’,-1),”) AS parent_id,
LENGTH(t.children) – LENGTH(REPLACE(t.children, ‘,’, ”)) AS `level`
FROM (SELECT get_children(1) AS children) AS t
CROSS JOIN (SELECT 1 + units.i + tens.i * 10 AS n
FROM
(SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
UNION ALL SELECT 8 UNION ALL SELECT 9) AS units
CROSS JOIN
(SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
UNION ALL SELECT 8 UNION ALL SELECT 9) AS tens
ORDER BY n) AS numbers
HAVING id IS NOT NULL;
运行以上代码，我们可以得到以下结果，即树形结构的一维表。
```mysql+------------+------------------+-----------+-------+
| id | name | parent_id | level |+------------+------------------+-----------+-------+
| 1 | 总公司 | NULL | 0 || 11 | 广州分公司 | 1 | 1 |
| 2 | 市场部 | 1 | 1 || 12 | 市场部 | 11 | 2 |
| 3 | 人力资源部 | 1 | 1 || 13 | 销售部 | 12 | 3 |
| 14 | 客服部 | 12 | 3 || 4 | 技术部 | 1 | 1 |
| 5 | 销售部 | 2 | 2 || 6 | 客服部 | 2 | 2 |
| 7 | 招聘部 | 3 | 2 || 8 | 培训部 | 3 | 2 |
| 9 | 开发部 | 4 | 2 || 10 | 测试部 | 4 | 2 |
+------------+------------------+-----------+-------+
我们可以了解到，在该表中，子节点的level值大于父节点的level值。我们可以方便地通过查询该表来实现树形结构数据的展示。
综上所述，我们可以通过MySQL生成树形结构的一维表，来实现树形结构数据的展示和查询。本篇文章详细介绍了如何通过自定义变量和函数来生成树形结构的一维表，并提供了相关代码以供参考。
