MySQL外键约束应用与优化指南

日期：2024-12-22 01:30:27 作者：创始人浏览：0

MySQL数据库中的外键约束详解

任何使用MySQL开发过小型数据库驱动的Web应用程序的人都知道，在关系数据库中创建、检索、更新和删除表是相对简单的过程。
理论上，只要你熟练使用最常用的SQL语句，熟悉你选择使用的服务器端脚本语言，就足以处理MySQL表上所需的各种操作，尤其是当你使用快速的MyISAM时。
数据库引擎时。
但即使在最简单的情况下，事情也比你想象的要复杂。
下面我们用一个典型的例子来说明。
假设您运营一个几乎每天都会更新的博客网站，并且该网站允许访问者对您的帖子发表评论。
在这种情况下，我们的数据库模式必须至少包含两个MyISAM表，一个用于存储您的博客文章，另一个用于管理访问者评论。
显然这两个表之间存在一对多的关系，因此我们需要在第二个表中定义一个外键，以便在更新或删除数据行时可以保持数据库的完整性。
对于像上面这样的应用程序来说，维护两个表的完整性不仅是一个很大的挑战，更大的困难是我们需要在应用程序级别维护它们的完整性。
这是大多数不需要使用事务的Web项目在开发过程中采用的方法，因为MyISAM表提供了出色的性能。
当然，这也是有代价的。
正如我之前所说，应用程序必须维护数据库的完整性和一致性，这涉及实现更复杂的编程逻辑来管理不同表之间的关系。
尽管可以通过使用抽象层和ORM模块来简化数据库访问，但随着应用程序所需的数据表数量的增加，管理它们所需的逻辑无疑会变得更加复杂。
那么对于MySQL来说，有没有一种在数据库层面处理外键的方法来帮助维护数据库的完整性呢？幸运的是答案是肯定的，MySQL还可以支持InnoDB表，这使得我们可以用一种非常简单的方式来处理外键约束？此功能允许我们触发某些操作，例如更新和删除表中的某些数据行以维护预定义的关系。
凡事都有优点和缺点，使用InnoDB表的主要缺点是比MyISAM慢，尤其是在需要查询很多表的大型应用程序中。
幸运的是，新版本MySQL中的MyISAM表也支持外键约束。
本文介绍如何对InnoDB表应用外键约束。
此外，我们将使用一个简单的基于PHP的MySQL抽象类来创建相关的示例代码。
当然，您也可以使用您喜欢的其他服务器端语言；现在我们开始介绍如何在MySQL中应用外键约束。
何时使用外键约束说实话，在MySQL中使用InnoDB表时，没有必要使用外键约束。
然而，为了理解外键约束在某些情况下的作用，我们将使用前面提到的例子。
。
代码有详细解释。
它包括两个MyISAM表，用于存储博客文章和评论。
在定义数据库模式时，我们需要通过在存储评论的表中创建外键来建立两个表之间的一对多关系，以将数据行（即评论）映射到特定的博客文章。
以下是创建示例MyISAM表的基本SQL代码：DROPTABLEIFEXISTS`test`.`blogs`;CREATETABLE`test`.`blogs`(`id`INT(10)UNSIGNEDAUTO_INCRMENT,`title`TEXT，`内容`TEXT，`作者`VARCHAR(45)DEFAULTNULL,PRIROSEKEY(`id`))ENGINE=MyISAMDEFAULTCHARSET=utf8;DROPTABLEIFEXISTS`test`.`评论`;CREATETABLE`测试`.`评论`(`id`INT(10)UNSIGNEDAUTO_INCRMENT,`blog_id`INT(10)UNSIGNEDDEFAULTNULL,`评论`TEXT,`作者`VARCHAR(45)DEFAULTNULL,PRIROSEKEY(`id`))ENGINE=MyISAMDEFAULTCHARSET=utf8上面我们刚刚定义了两个MyISAM表，构成博客应用程序的数据层。
如您所见，第一个表称为博客。
它由几个明显的字段组成，用于存储每篇博客文章的ID、标题和内容，最后是作者。
第二个表名为comments，用于存储与每篇博文相关的评论，以的ID作为外键，从而建立一对多关系。
到目前为止，我们的工作相对简单，因为我们只创建了两个简单的MyISAM表。
接下来，我们要做的是用一些记录填充这些表，以进一步演示当第一个表中的条目被删除时需要在另一个表中执行哪些操作。
更新和维护数据库完整性在上一节中，我们创建了两个MyISAM表作为博客应用程序的数据层。
当然，上面的介绍还是很简单，我们还需要进一步讨论。
为此，我们将使用如下SQL命令在这些表中填充一些记录：INSERTINTOblogs(id,title,content,author)VALUES(NULL,'Titleofthefirstblogentry','Contentofthefirstblogentry','Ian')INSERTINTOcomments(id,blog_id,comment,author)VALUES(NULL,1,'Commentingfirstblogentry','SusanNorton'),(NULL,1,'Commentingfirstblogentry','RoseWilson')上面的代码实际上模拟了读者Susan和Rose对我们的响应第一篇发表评论的博客文章。
现在假设我们想用另一篇文章更新第一个博客。
当然，这种情况也是有可能的。
在这种情况下，为了保持数据库一致性，评论表也必须相应更新，可以手动更新，也可以由处理数据层的应用程序更新。
对于本例，我们将使用SQL命令来完成更新，如下：UPDATEblogsSETid=2,title='Titleofthefirstblogentry',content='Contentofthefirstblogentry',author='JohnDoe'WHEREid=1UPDATEcommentsSETblog_id=2WHEREblod_id=1如前所述，第一篇博客的数据内容已更新，表格评论也应该反映这一变化。
当然，实际上这个更新操作需要在应用层完成，而不是手动完成，这意味着这个逻辑需要使用服务器端语言来实现。
为了实现这个操作，PHP可以使用一个简单的子进程，但实际上，如果key约束使用外文，更新评论表的操作可以委托给数据库。
正如本文前面提到的，InnoDBMySQL表为此功能提供了无缝支持。
因此，在最后一部分中，我们将使用外键约束来重新创建前面的代码示例。
级联数据库更新接下来，我们将使用外键约束和InnoDB表（而不是默认的MyISAM类型）重构前面的代码示例。
为此，首先重新定义两个示例表，以便它们可以使用特定的数据库引擎。
为此，您可以使用如下SQL代码：DROPTABLEIFEXISTS`test`.`blogs`;CREATETABLE`test`.`blogs`(`id`INT(10)UNSIGNEDAUTO_INCRMENT,`title`TEXT,`content`TEXT,`作者`VARCHAR(45)DEFAULTNULL,PRIROSEKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;DROPTABLEIFEXISTS`test`.`comments`;CREATETABLE`test`.`comments`(`id`INT(10)UNSIGNEDAUTO_INCRMENT,`blog_id`INT(10)UNSIGNEDDEFAULTNULL,`评论`TEXT,`作者`VARCHAR(45)DEFAULTNULL,PRIROSEKEY(`id`),KEY`blog_ind`(`blog_id`),CONSTRAINT`comments_ibfk_1`FOREIGNKEY(`blog_id`)REFERENCES`blogs`(`id`)ONUPDATECASCADE)ENGINE=InnoDBDEFAULTCHARSET=utf8;与之前的代码相比，这里的代码和之前的代码之间一个明显的区别是两个表现在都使用InnoDB存储引擎，使其可以支持外键约束。
另外，我们还需要注意定义评论表的代码：CONSTRAINT`comments_ibfk_1`FOREIGNKEY(`blog_id`)REFERENCES`blogs`(`id`)ONUPDATECASCADE事实上，这条语句告诉MySQL，当博客。
表更新后，也会更新comments表中的blog_id外键值。
也就是说，这里所做的就是让MySQL保持级联数据库的完整性。
这意味着当博客更新时，与其相关的评论也应立即反映此更改。
这不是在应用程序层完成的。
两个示例MySQL表均已定义。
现在，更新这两个表就像运行UPDATE语句一样简单，如下所示："UPDATEblogsSETid=2,title='Titleofthefirstblogentry',content='Contentofthefirstblogentry',author='JohnDoe'WHEREid.=1“如前所述，我们不需要更新评论表。
”因为MySQL会自动处理所有这些。
此外，当您尝试更新blogs表中的行时，您可以通过删除查询的“ONUPDATE”部分或指定“NOACTION”和“RESTRICT”来告诉MySQL不执行任何操作。
当然，你也可以让MySQL做其他事情，这将在后续文章中讨论。
通过上面的介绍，想必大家都清楚地了解了如何结合MySQL中的InnoDB表来使用外键约束。
当然，您也可以立即编写一些代码来进一步加深对这个方便的数据库功能的理解。