《PHP精粹：编写高效PHP代码》——2.7节设计数据库

本节书摘来自华章社区《PHP精粹&＃xff1a;编写高效PHP代码》一书中的第2章&＃xff0c;第2.7节设计数据库&＃xff0c;作者&＃xff1a;&＃xff08;美&＃xff09;　　Davey Shafik&＃xff0c;更多章节内容可以访问云栖社区“华章社区”公众号查看

2.7　设计数据库
到目前为止&＃xff0c;我们已经创建了两个非常基本的表并且看到如何使用PDO操作简单的数据。现在我们要扩展这些例子&＃xff0c;添加一些另外的表&＃xff0c;探讨我们如何在真实的应用程序中使用这些数据。让我们从图2.2开始&＃xff0c;看看目前有什么。
图2.2显示了两张表以一对多的关系链接。这表明categories表中的每一个记录在食谱表中会有很多与之相关的记录&＃xff1b;也就是说&＃xff0c;一个类别会有很多食谱&＃xff0c;但一个食谱只能属于一个类别。

7dda8d5b474be367b5566e9f4508e4433784918f

2.7.1　主键与索引
我们已经为两张表添加了主键&＃xff0c;主键使我们提供的列在每个表中必定是唯一的&＃xff0c;我们可以轻松访问一个特定的记录。此外还有一个好处&＃xff0c;MySQL可以在该列放置一个索引&＃xff08;index&＃xff09;。添加一个索引至数据库的列和要求数据库保持对其中内容的跟踪一样。例如&＃xff0c;如果你在recipes.name列上添加了一个索引&＃xff0c;数据库使用该列会很容易找到项目&＃xff0c;因为索引会保持对这些记录在什么地方进行跟踪。

2.7.2　MySQL解析
我们应该学习的最后一个数据库方法是MySQL EXPLAIN命令。EXPLAIN详细描述了MySQL将如何运行查询。在SELECT查询之前&＃xff0c;我们通过迅速放置EXPLAIN术语来使用它&＃xff1a;

ea3087770d8f634b63b46ae9e73c9a8b152bb2a8

这表明我们的查询不得不搜索所有的5个行&＃xff0c;以便找到正在寻找的一行。五行并不是很多&＃xff0c;但在这种情况下它们就是表中的每一行&＃xff0c;并且总是带来坏消息&＃xff01;如果我们经常用食谱名查询食谱表中的行&＃xff0c;我们可以添加一个索引来提高性能。
如果要添加一个索引&＃xff0c;需使用ALTER TABLE语句。因此&＃xff0c;要在recipes.name列添加一个索引&＃xff0c;我们将输入&＃xff1a;

26055c2e237033029ca7215067b41df364572a4b

这表明如果我们试图在recipes表中插入一个id为4的记录&＃xff0c;我们将看到一个错误消息。
外键支持
请注意并非所有的数据库都支持外键。MySQL支持外键&＃xff0c;但只支持InnoDB表类型。使用MyISAM表类型&＃xff0c;你可以创建一个外键&＃xff0c;但是它会被忽略&＃xff01;在phpMyAdmin中&＃xff0c;当你创建一个表&＃xff0c;你会发现一个标题为Storage Engine的下拉菜单&＃xff0c;你可以通过其中的选项来选择一个InnoDB表类型。

5e6b96e1478b82481847d927a14dc88201ea96dd

573a16ef0bc27381165be8b1ad578d1f441ee85e

这是一个内部连接&＃xff08;inner join&＃xff09;的例子&＃xff0c;这表明在这个查询中我们只能看到所有表中有匹配行的数据。我们在recipes表中还有其他的条目&＃xff0c;但是因为我们仍需将一些配料和它们连接&＃xff0c;所以这些条目不会在这个查询结果中出现。为了看到所有的食谱&＃xff0c;无论它们有没有和配料连接&＃xff0c;我们都将使用一个外部连接。
加入&＃61;内部连接
有时我们会看到只对自己使用JOIN关键字的查询&＃xff0c;这些都是隐含的内部连接。这个例子使用了INNER关键字&＃xff0c;让我们更清楚地看到发生了什么。我们很快将看到其他连接类型。

2.7.4　外部连接
现在你知道什么是内部连接了&＃xff0c;你大概也猜到了什么是外部连接&＃xff08;outer join&＃xff09;。外部连接使我们能从一个表中检索所有行&＃xff0c;还有其他表中与之匹配的行。如果没有匹配的数据&＃xff0c;MySQL将对这些列返回NULL值。
由于外部连接包含的行来自于一个表及另一个随机的表&＃xff0c;因此我们需要指定哪个表连接哪个表。我们使用RIGHT JOIN和LEFT JOIN表达式做到以上这些。因为我们是从左到右读&＃xff0c;所以左边的表就是我们在SQL语句中遇到的第一个表。外部连接通常有助于勾勒出此时数据库的布局&＃xff0c;或者你可以参考前面的架构图。
让我们来看看外部连接的一个例子。我们要显示所有的食谱&＃xff0c;而不仅仅是带有配料的食谱。由于recipes表首先出现&＃xff0c;因此我们将使用LEFT JOIN来表明需要显示左表中的所有行。

0b97ad13bd9848e58101dcf2f0e1a4d9d2af648a

要是愿意&＃xff0c;我们可以从包含在查询中的任一表格提取很多列或几列。若在多个表中有相同名称的列&＃xff0c;我们必须为这些列加上它们所属表名的前缀&＃xff0c;否则MySQL会告诉我们它不知道所指的是哪一列。这是一个修饰所有列名的好办法&＃xff0c;可以帮助明确数据来源。当你想要添加另一个表到你的查询中时&＃xff0c;这可让你免于返回去重新修饰这些列名。

2.7.5　聚合函数和Group By
聚合函数&＃xff08;aggregate function&＃xff09;向我们提供了与查询相匹配的简要数据信息。我们用这种技术能达到各种理想的结果。这种精确的功能不同于平台到平台模式&＃xff0c;这里有一些常见的例子以及它们的MySQL函数名&＃xff1a;
对记录计数&＃xff08;COUNT&＃xff09;。
得到最大或最小的一个特定列的值&＃xff08;MAX或MIN&＃xff09;。
计算某一列的总和&＃xff08;SUM&＃xff09;。
计算某一列的平均值&＃xff08;AVG&＃xff09;。
例如&＃xff0c;若想知道在查询中有多少条记录&＃xff0c;可以使用MySQL中的COUNT()函数&＃xff0c;就像这样&＃xff1a;

3957702e11ab5fc9c8fddb4845764adb55a640fe

使用连接和聚合这两个函数的确非常棘手&＃xff0c;但如果我们走一步看一步&＃xff0c;对这些技术还是会慢慢理清头绪的。比起写一个庞大的SQL语句然后调试它&＃xff0c;分阶段建立这些东西显然更加容易。
第一步是从一个表中得到数据并按照需要进行过滤。一次加入一个到表中&＃xff0c;每次运行查询的时候&＃xff0c;检查结果是否如你预期。一旦你看到MySQL根据请求算出的所有数据行&＃xff0c;你就可以添加额外的格式化列、计算总数&＃xff0c;以及其他任何需要为你应用程序生成的正确数据。使用聚合函数远比在PHP中为汇总数据集或计算平均数使用循环效率更高&＃xff1b;数据库平台的确擅长处理数据&＃xff0c;因此最好是把这些任务交给专家完成。

2.7.6　规格化数据
数据规格化的主题通常本身就可以构成一整章&＃xff0c;但是&＃xff0c;简而言之&＃xff0c;使用这个方法的目标是&＃xff1a;
按实体来拆分它们&＃xff0c;并将拆分后的部分各自组成自己的表。
避免在一个列中有多个值。
在一个地方记录数据&＃xff0c;并将其与其他数据连接。
按照现在的情况我们可以改善数据库设计&＃xff0c;将chef列的数据移到一个单独的表中。每个厨师都有一个唯一的标识符&＃xff0c;并且记录在recipes表中。由于厨师是一个实体&＃xff0c;他应该有自己的表&＃xff0c;在这里我们可以集中记录厨师的信息并且维护这些信息&＃xff0c;而不是在每个recipe行中重复记录。
显而易见&＃xff0c;如果允许用户输入名字会导致食谱表中出现很多个“John”&＃xff0c;也许只有几个“John”&＃xff0c;其中一些名字可能就是同一个人&＃xff01;为了避免发生这种情况&＃xff0c;我们需要移动厨师到他们自己的表中&＃xff0c;就像下面这样&＃xff1a;