Mysql 实现无限级分类

by · 2018年04月21日 · 1265 Words · ~3min reading time | Improve on

原文链接： Mysql 实现无限级分类

在数据库中存储一棵树，实现无限级分类 ClosureTable

https://segmentfault.com/a/1190000014284076
我也是从别的地方找来的，分析后发现挺不错的，特来分享。

无法发图只能简单处理：

id name pid path

1 新闻 0 0

2 中国新闻 1 0-1

3 美国新闻 1 0-1

4 北京新闻 2 0-1-2

5 指甲哥新闻 3 0-1-3

一条语句搞定：

SELECT id,name,pid,path,concat(path,’-‘,id) AS newpath FROM cates ORDER BY newpath

查询后的结果：

id name pid path newpath

1 新闻 0 0 0-1

2 中国新闻 1 0-1 0-1-2

4 北京新闻 2 0-1-2 0-1-2-4

3 美国新闻 1 0-1 0-1-3

5 指甲哥新闻 3 0-1-3 0-1-3-5

可以看出，结构已经很清晰了。非常巧妙的运用了mysql的排序功能，用 concat() 函数把当前路径与当前的id的连接起来（很想连接字符串），起个别名 newpath ，在排序 newpath，巧的是排序时先比较0，在比较1，在比较2，在比较3…，这样查询的结果是直接按曾级排序好的，接下来只需要在前台循环输出就可以了，“层级关系占位符”也好解决，newpath 的 0-1-2-4 这个就相当于无限极占位符，php输出时要用上面的语句查询出来，在后台处理每一条数据的 newpath 字段值，先把值分割成数组，在统计这个数组，再在数组里添加一个键count（一组数据），把统计的数组个数放到这个键里。

这种方式，假设我们要查询电脑下的所有后代分类，只需要一条sql语句：

select id,name from category where path like ( select concat(path,'-',id,'%') path from category where id=1 );

优点：查询容易，效率高，path字段可以加索引。

缺点：更新节点关系麻烦，需要更新所有后辈的path字段。

DROP TABLE IF EXISTS `prefix_nodes_paths`;
DROP TABLE IF EXISTS `prefix_nodes`;

CREATE TABLE `prefix_nodes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `order` int(11) DEFAULT NULL,
  `name` varchar(128) NOT NULL,
  `is_deleted` tinyint(1) DEFAULT '0',
  `user_id` int(11) NOT NULL,
  `user_type` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  KEY `name` (`name`),
  CONSTRAINT `prefix_nodes_ibfk1` FOREIGN KEY (`parent_id`) REFERENCES `prefix_nodes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `prefix_nodes_paths` (
  `ancestor_id` int(11) NOT NULL,
  `descendant_id` int(11) NOT NULL,
  `path_length` int(11) NOT NULL,
  PRIMARY KEY (`ancestor_id`,`descendant_id`),
  KEY `descendant_id` (`descendant_id`),
  CONSTRAINT `prefix_nodes_paths_ibfk_1` FOREIGN KEY (`ancestor_id`)   REFERENCES `prefix_nodes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `prefix_nodes_paths_ibfk_2` FOREIGN KEY (`descendant_id`) REFERENCES `prefix_nodes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DELIMITER $$

USE `hierarchy_data`$$

DROP TRIGGER IF EXISTS `trigger_add_node`$$

CREATE
    TRIGGER `trigger_add_node` AFTER INSERT ON `prefix_nodes`
    FOR EACH ROW BEGIN
    CALL p_node_add(NEW.`id`, NEW.`parent_id`);
END;
$$

DELIMITER ;


DELIMITER $$

USE `hierarchy_data`$$

DROP TRIGGER IF EXISTS `trigger_move_node`$$

CREATE TRIGGER `trigger_move_node` AFTER UPDATE ON `prefix_nodes`
  FOR EACH ROW BEGIN
  IF OLD.`parent_id` != NEW.`parent_id` THEN
    -- http://www.mysqlperformanceblog.com/2011/02/14/moving-subtrees-in-closure-table/
    -- in the example, when change node D's parent to B.
    -- its sql has only D & B. so I think it should currentNode.id& newParent.id
    CALL p_node_move(NEW.`id`, NEW.`parent_id`);
  END IF;

  IF OLD.`is_deleted` != NEW.`is_deleted` THEN
      CALL p_node_hide(NEW.`parent_id`, NEW.`is_deleted`);
  END IF;
END;
$$

DELIMITER ;


DELIMITER $$

USE `hierarchy_data`$$

DROP PROCEDURE IF EXISTS `p_get_tree`$$

CREATE PROCEDURE `p_get_tree`(
    node_id INT UNSIGNED
) COMMENT 'Query all descendants nodes by a node id, return as a result set'
BEGIN
  SELECT
    node.`id`,
    node.`is_deleted`,
    node.`parent_id`,
    CONCAT(
        REPEAT('-', path.`path_length`),
        node.`name`
    ) AS name,
    path.`path_length`,
    GROUP_CONCAT(
        crumbs.`ancestor_id` SEPARATOR ','
    ) AS breadcrumbs
  FROM
    `prefix_nodes` AS node
    JOIN `prefix_nodes_paths` AS path
      ON node.`id` = path.`descendant_id`
    JOIN `prefix_nodes_paths` AS crumbs
      ON crumbs.`descendant_id` = path.`descendant_id`
  WHERE path.`ancestor_id` = `node_id`
    AND node.`is_deleted` = 0
  GROUP BY node.`id`
  ORDER BY breadcrumbs ;
END$$

DELIMITER ;


DELIMITER $$

USE `hierarchy_data`$$

DROP PROCEDURE IF EXISTS `p_node_add`$$

CREATE PROCEDURE `p_node_add`(
  param_node_new_id    INT UNSIGNED,
  param_node_parent_id INT UNSIGNED
)
COMMENT 'Adding new paths prefix_nodes_paths table'
BEGIN
  -- Update paths information
  INSERT INTO `prefix_nodes_paths` (
    `ancestor_id`,
    `descendant_id`,
    `path_length`
  )
  SELECT
    `ancestor_id`,
    `param_node_new_id`,
    `path_length` + 1
  FROM
    `prefix_nodes_paths`
  WHERE `descendant_id` = `param_node_parent_id`
  UNION
  ALL
  SELECT
    `param_node_new_id`,
    `param_node_new_id`,
    0 ;
END$$

DELIMITER ;


DELIMITER $$

USE `hierarchy_data`$$

DROP PROCEDURE IF EXISTS `p_node_hide`$$

CREATE PROCEDURE `p_node_hide` (
  `node_id` INT UNSIGNED,
  `deleted` INT UNSIGNED
) COMMENT 'Delete a node and its descendant nodes(update is_deleted = 1)'
BEGIN
  UPDATE
    `prefix_nodes` AS d
    JOIN `prefix_nodes_paths` AS p
      ON d.`id` = p.`descendant_id`
    JOIN `prefix_nodes_paths` AS crumbs
      ON crumbs.`descendant_id` = p.`descendant_id` SET d.`is_deleted` = deleted
  WHERE p.`ancestor_id` = node_id;
END $$

DELIMITER ;


DELIMITER $$

USE `hierarchy_data`$$

DROP PROCEDURE IF EXISTS `p_node_move`$$

CREATE PROCEDURE `p_node_move` (
    `node_old_parent_id` INT UNSIGNED,
    `node_new_parent_id` INT UNSIGNED
) COMMENT 'Update paths when parent_id column changed'
BEGIN
-- References:
-- http://www.mysqlperformanceblog.com/2011/02/14/moving-subtrees-in-closure-table/
-- The store procedure is used to update paths informations when the value of parent_id columns is changed (when move a node to a new parent)
-- If parent_id has chanaged
-- 1. Delete the paths between moved node and old ancestors
-- 2. Add the paths between moved node and new ancestors
  DELETE
    a
  FROM
    `prefix_nodes_paths` AS a
    JOIN `prefix_nodes_paths` AS d
        ON a.`descendant_id` = d.`descendant_id`
    LEFT JOIN `prefix_nodes_paths` AS x
        ON x.`ancestor_id` = d.`ancestor_id`
        AND x.`descendant_id` = a.`ancestor_id`
  WHERE d.`ancestor_id` = `node_old_parent_id`
    AND x.`ancestor_id` IS NULL ;

  -- Add the node to its new parent
  INSERT `prefix_nodes_paths` (
    `ancestor_id`,
    `descendant_id`,
    `path_length`
  )
  SELECT
    supertree.`ancestor_id`,
    subtree.`descendant_id`,
    supertree.`path_length` + subtree.`path_length` + 1
  FROM
    `prefix_nodes_paths` AS supertree
    JOIN `prefix_nodes_paths` AS subtree
  WHERE subtree.`ancestor_id` = `node_old_parent_id`
    AND supertree.`descendant_id` = `node_new_parent_id` ;
END$$

DELIMITER ;


INSERT INTO `prefix_nodes` VALUES(1, NULL, NULL, 'ROOT',DEFAULT, 0, 0);
INSERT INTO `prefix_nodes` VALUES(2, 1, NULL, 'C0',DEFAULT, 0, 3);
INSERT INTO `prefix_nodes` VALUES(3, 1, NULL, 'B0',DEFAULT, 0, 2);




INSERT INTO `prefix_nodes` VALUES(NULL, 3, NULL, 'A1',DEFAULT, 0, 1);
INSERT INTO `prefix_nodes` VALUES(NULL, 3, NULL, 'A2',DEFAULT, 0, 1);
INSERT INTO `prefix_nodes` VALUES(NULL, 3, NULL, 'A3',DEFAULT, 0, 1);
INSERT INTO `prefix_nodes` VALUES(NULL, 3, NULL, 'A4',DEFAULT, 0, 1);
INSERT INTO `prefix_nodes` VALUES(NULL, 3, NULL, 'A5',DEFAULT, 0, 1);
INSERT INTO `prefix_nodes` VALUES(NULL, 3, NULL, 'A6',DEFAULT, 0, 1);
INSERT INTO `prefix_nodes` VALUES(NULL, 3, NULL, 'A7',DEFAULT, 0, 1);
INSERT INTO `prefix_nodes` VALUES(NULL, 3, NULL, 'A8',DEFAULT, 0, 1);
INSERT INTO `prefix_nodes` VALUES(NULL, 3, NULL, 'A9',DEFAULT, 0, 1);

Mysql 实现无限级分类

在数据库中存储一棵树，实现无限级分类 ClosureTable

分类

标签云