MySQL:类别和子类别的交叉表引用(主、主、名称)？

Question

我正在创建一个标记为类别的表，其中主类别(父列)包含 0，子类别包含父类别的 ID。我听说这叫引用。我的问题:这张表的结构正确吗？或者是否有更好的方法，例如实现遍历树或类似方法？

CREATE TABLE `categories` (
`primary_id` int(11) NOT NULL auto_increment,
`master_id` int(11) NOT NULL default '0',
`name` varchar(50) NOT NULL default '',
PRIMARY KEY (`c_id`)
)

INSERT INTO `categories` (`primary_id`, `master_id`, `name`) VALUES 
(1, '0', 'Games'),
(2, '0', 'Technology'),
(3, '0', 'Science'),
(4, '0', 'Pop Culture'),
(5, '0', 'Jobs/Services'),
(6, '0', 'Blogs'), 
(7, '1', 'Computer'),
(8, '1', 'Game Console'),
(9, '2', 'Cellphone'),
(10, '2', 'Audio/Video/Photos'),
(11, '2', 'Laptop'),
(12, '2', 'Others >'),
(13, '3', 'Human Body'),
(14, '3', 'Ocean Life'),
(15, '3', 'Plant Life'),
(16, '3', 'Animal Life'),
(17, '4', 'Artist'),
(18, '4', 'Upcoming'),
(19, '5', 'Jobs'),
(20, '5', 'Tutoring'),
(21, '5', 'Seminars'),
(22, '5', 'Haircuts'),
(23, '9', 'Iphone'),
(24, '9', 'Android'),
(25, '9', 'Windows Mobile'),
(26, '11', 'Toshiba'),
(27, '11', 'HP'),
(28, '11', 'Apple'),

Answer 1

如果您使用 InnoDB 引擎，则可以强制表的引用完整性。这样，您可以确保永远不会插入没有匹配父 ID 的类别，删除父类别时级联删除子类别等。将创建语句更改为:

CREATE TABLE `categories` (
`primary_id` int(11) NOT NULL auto_increment,
`master_id` int(11) NOT NULL default '0',
`name` varchar(50) NOT NULL default '',
PRIMARY KEY (`c_id`),
KEY `fk_master_id1` (`master_id`),
CONSTRAINT `fk_master_id1` FOREIGN KEY (`master_id`) REFERENCES `master` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

虽然此策略在大多数情况下都足够有效，但您可能还想了解实现树状结构的其他策略以及有效查询它们所带来的挑战。请参阅this有关在 MySQL 中存储树状结构的替代设计的更多信息，请回答 SO。