mysql - 列出 MySQL 分类中每个元素的所有属性

Question

我有一个 MySQL 数据库，需要提供与分类法相关的属性。我希望有一个所有属性的列表，包括分类中给定元素的“继承属性”。我不是 SQL 专家，并且从这个网站学到了很多东西(并且抄袭了)。我在这里学到的一件事是使用闭包表来表示我的层次结构。我需要为我的数据库做的是将大量属性与层次结构中的元素相关联。但是，我似乎无法弄清楚如何获取与给定节点及其所有父节点关联的所有属性。为了解决这个问题，我创建了以下示例数据库(请随意评论有关此模式的任何内容，假数据只是噪音)。

我的示例 MySQL 数据库结构如下所示:

    -- Simple Sample
    SET FOREIGN_KEY_CHECKS=0;
    DROP TRIGGER IF EXISTS inheritance_tree_insert;
    DROP TRIGGER IF EXISTS inheritance_tree_update;
    DROP TABLE IF EXISTS inheritance_paths;
    DROP TABLE IF EXISTS inheritance_tree;
    DROP TABLE IF EXISTS attributes;
    SET FOREIGN_KEY_CHECKS=1;


    CREATE TABLE `inheritance_tree` (
        `it_id`       INT NOT NULL,           -- PK
        `parent`      INT,                    -- Parent id & FK
        `child_order` INT,                    -- Oder of siblings 
        `name`        VARCHAR(500) NOT NULL,  -- Name for the entry
        PRIMARY KEY (`it_id`),
        FOREIGN KEY (`parent`) REFERENCES inheritance_tree(`it_id`) ON DELETE CASCADE,
        INDEX(`name`)
    ) ENGINE = INNODB;

    -- Trigger to update the paths table for new entries
    DELIMITER //

    CREATE TRIGGER `inheritance_tree_insert` AFTER INSERT ON `inheritance_tree` FOR EACH ROW 
    BEGIN
        INSERT INTO `inheritance_paths` (`ancestor`, `descendant`, `len`)
            SELECT `ancestor`, NEW.`it_id`, len + 1 FROM `inheritance_paths`
                WHERE `descendant` = NEW.`parent`
                UNION ALL SELECT NEW.`it_id`, NEW.`it_id`, 0;
    END; //
    DELIMITER ;


    DELIMITER //
    CREATE TRIGGER `inheritance_tree_update` BEFORE UPDATE ON `inheritance_tree` FOR EACH ROW 
    BEGIN
        -- From http://www.mysqlperformanceblog.com/2011/02/14/moving-subtrees-in-closure-table/
        IF OLD.`parent` != NEW.`parent` THEN
            -- Remove the node from its current parent
            DELETE a FROM `inheritance_paths` AS a
            JOIN `inheritance_paths` AS d ON a.`descendant` = d.`descendant`
            LEFT JOIN `inheritance_paths` AS x
            ON x.`ancestor` = d.`ancestor` AND x.`descendant` = a.`ancestor`
            WHERE d.`ancestor` = OLD.`it_id` AND x.`ancestor` IS NULL;

            -- Add the node to its new parent
            INSERT `inheritance_paths` (`ancestor`, `descendant`, `len`)
            SELECT supertree.`ancestor`, subtree.`descendant`, supertree.`len`+subtree.`len`+1
            FROM `inheritance_paths` AS supertree JOIN `inheritance_paths` AS subtree
            WHERE subtree.`ancestor` = OLD.`it_id`
            AND supertree.`descendant` = NEW.`parent`;
        END IF;
    END; //
    DELIMITER ;


    CREATE TABLE `inheritance_paths` (
        `ancestor`      INT NOT NULL,
        `descendant`    INT NOT NULL,
        `len`           INT NOT NULL,
        PRIMARY KEY (`ancestor`, `descendant`),
        FOREIGN KEY (`ancestor`) REFERENCES inheritance_tree(`it_id`) ON DELETE CASCADE,
        FOREIGN KEY (`descendant`) REFERENCES inheritance_tree(`it_id`) ON DELETE CASCADE
    ) ENGINE = INNODB;

    INSERT INTO `inheritance_tree` VALUES(1, NULL, NULL, 'Animal');
    INSERT INTO `inheritance_tree` VALUES(2, 1, 1, 'Mammal');
    INSERT INTO `inheritance_tree` VALUES(3, 1, 2, 'Bird');
    INSERT INTO `inheritance_tree` VALUES(4, 1, 3, 'Reptile');
    INSERT INTO `inheritance_tree` VALUES(5, 2, 2, 'Feline');
    INSERT INTO `inheritance_tree` VALUES(6, 2, 1, 'Bovine');
    INSERT INTO `inheritance_tree` VALUES(7, 1, 3, 'Fish');
    INSERT INTO `inheritance_tree` VALUES(8, 4, 1, 'Snake');
    INSERT INTO `inheritance_tree` VALUES(9, 4, 2, 'Lizard');
    INSERT INTO `inheritance_tree` VALUES(10, NULL, NULL, 'Machine');
    INSERT INTO `inheritance_tree` VALUES(11, 10, 1, 'Automobile');
    INSERT INTO `inheritance_tree` VALUES(12, 10, 2, 'OfficeMachine');
    INSERT INTO `inheritance_tree` VALUES(13, 10, 3, 'Robot');

    DELIMITER ;

    CREATE TABLE `attributes` (
      `a_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'the unique identifier',
      `attribute_name` varchar(255) DEFAULT NULL,
      `attribute_type` int(11) NOT NULL,
      PRIMARY KEY (`a_id`),
      KEY `fk_attributes_attribute_type1_idx` (`attribute_type`),
      CONSTRAINT `fk_attributes_attribute_type1_idx` FOREIGN KEY (`attribute_type`) REFERENCES `inheritance_tree` (`it_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

    INSERT INTO `attributes` VALUES(1, 'IsAlive', 1);                   -- Animal
    INSERT INTO `attributes` VALUES(2, 'HasMaximumLifeSpan', 1);        -- Animal
    INSERT INTO `attributes` VALUES(3, 'IsNotAlive', 10);               -- Machine
    INSERT INTO `attributes` VALUES(4, 'HasIndeterminantLifeSpan', 10); -- Machine
    INSERT INTO `attributes` VALUES(5, 'BreathesAir', 2);               -- Mammal
    INSERT INTO `attributes` VALUES(6, 'CanFly', 3);                    -- Bird
    INSERT INTO `attributes` VALUES(7, 'HasFeathers', 3);               -- Bird
    INSERT INTO `attributes` VALUES(8, 'ColdBlooded', 4);               -- Reptile
    INSERT INTO `attributes` VALUES(9, 'HasFourLegs', 5);               -- Feline
    INSERT INTO `attributes` VALUES(10, 'HasFourLegs', 6);              -- Bovine
    INSERT INTO `attributes` VALUES(11, 'ColdBlooded', 7);              -- Fish
    INSERT INTO `attributes` VALUES(12, 'HasNoLegs', 8);                -- Snake
    INSERT INTO `attributes` VALUES(13, 'HasFourLegs', 9);              -- Lizard
    INSERT INTO `attributes` VALUES(14, 'ConsumesGasoline', 11);        -- Automobile
    INSERT INTO `attributes` VALUES(15, 'ConsumesElectricity', 12);     -- OfficeMachine
    INSERT INTO `attributes` VALUES(16, 'ConsumesElectricity', 13);     -- Robot
    INSERT INTO `attributes` VALUES(17, 'WarmBlooded', 2);              -- Mammal
    INSERT INTO `attributes` VALUES(18, 'WarmBlooded', 3);              -- Bird
    INSERT INTO `attributes` VALUES(19, 'BreathesWater', 7);            -- Fish
    INSERT INTO `attributes` VALUES(20, 'HasScales', 8);                -- Snake
    INSERT INTO `attributes` VALUES(21, 'HasSkin', 9);                  -- Lizard

假设:

1.分类中的所有实体都有唯一的命名。这意味着在任何分类树中的任何位置都存在且仅有一个名为“鱼”的子类型。

2.属性不唯一，可能重复

目标:

给定输入类型“Lizard”，我想要一个返回以下属性记录列表的查询:

1，还活着，1

2，拥有最大生命周期，1

5，呼吸空气，2

8、冷血、4

13，有四条腿，9

21，HasSkin，9

Answer 1

SELECT a.*
FROM   inheritance_tree  t
  JOIN inheritance_paths p ON p.descendant     = t.it_id
  JOIN attributes        a ON a.attribute_type = p.ancestor
WHERE  t.name = 'Lizard'

查看 sqlfiddle .

请注意，您的示例输出包括 a_id = 5 (BreathesAir)，它是 it_id = 2 (Mammal) 的属性，但不属于蜥蜴的祖先。