四、数据库
。。。总的来说，数据库设计仍然主要是一种艺术努力，而不是科学努力。。。
—C. J .日期，数据库上的日期
联合通常没有得到很好的优化。。。。如果可能，请改用 UNION ALL。
—乔·塞尔科的 SQL 编程风格
永远不要指定全部。
—C. J. Date， SQL 和关系理论
似乎数据库设计是一门艺术，而 SQL 技术则是见仁见智。在这一章中，我试图给你一些指导方针，帮助你掌握这门艺术。你也会得到我对 SQL 的看法。正如您将了解到的，我不像 Joe Celko 那样是 SQL 爱好者，也不像 Chris Date 那样是理论家。不过，我确实知道如何构建应用。
除了两个例外，所有的应用都操纵现实 的局部模型。例如，当您运行一个应用来平衡您的支票簿时，您实际上并没有改变您的支票簿，甚至没有改变银行对存款和已结清支票的想法。你只是在操作一个计算机化的支票登记簿，它是应用对你的财务状况的模型，准确与否。或者，当您到 Amazon 的网站寻找要购买的书时，您只看到每本书的模型，由封面、书名、作者姓名、价格、评论等图像表示。这本书本身——现实——就在亚马逊的一个仓库里。(电子书让我的例子有点偏离，我知道。)
这两个例外都与 PHP/MySQL 应用没有太大关系，它们是操纵现实世界的实时控制应用和操纵非现实模型的程序(例如，发生在某个虚构的遥远星球上的视频游戏)。前几天，我在用一个实时应用给我和女儿组装的乐高思维风暴机器人编程。我犯了一个错误，差点把它从桌子上弄下来，把放它进来的大盒子撞到了地板上。那真的发生了——不仅仅是我在电脑屏幕上看到的！但是 PHP 既不适合实时控制，也不适合视频游戏，所以我将讨论的唯一应用是那些操纵现实模型的应用。
如果模型值得保留，那么它需要存储在某个地方，通常是在运行应用的计算机的磁盘上。对于许多应用来说，比如一个电子表格或者一个字处理器，就像我现在正在使用的，一个文件就可以了。但是如果模型有任何结构，可以变得很大，并且可以被几个用户同时访问，那么最好使用数据库。
这就引出了数据库的定义:*数据库是现实的一个持久的局部模型，可以被应用操纵。*应用不是直接操作数据库，这很复杂，必须恰到好处地避免损坏数据，并且必须相当快，而是处理一个称为数据库管理系统(DBMS)的中间程序。在本书中，数据库管理系统是 MySQL。
这一章是关于如何设计一个适合应用需求的逻辑数据库，如何在 MySQL 中物理地实现它，以及如何用 SQL 连接它。在第五章中，你会发现如何从 PHP 连接到它的机制。
关系数据库
数据库可以使用几种方法来排列构成模型的数据，其中最流行的方法被称为关系型，因为数据保存在数据库理论中称为关系的表中。这种表格的每一列构成一个属性，或字段。每一行都是一个字段的集合，我们认为是一个记录。例如，employee 表中有雇员编号、姓氏、名字、电话号码等列，每个雇员对应一行(或一条记录)。类似地，可能有一个部门表，其中包含部门编号和名称等列，每个部门占一行。
在关系方法中，每个表都是完全独立的；没有从一行数据到另一行数据的指针，这在计算机程序内部使用的数据结构中是常见的，例如树和链表。但是，这并不意味着您不能将数据项存储在引用另一个表中的行的行中，例如存储在雇员行中的部门号，它指示雇员所在的部门。但是，在关系方法中，这样的引用不是指针——它只是两个表碰巧共有的数据项。
使用关系方法的 DBMS 称为关系 DBMS (RDBMS)，这就是 MySQL。PostgreSQL 也是如此，它是另一种流行的开源 DBMS，还有大家伙:SQL Server、Oracle 和 DB2。甚至最流行的嵌入式 DBMS SQLite 也是关系型的。
情况并非总是如此。当我在 20 世纪 70 年代开始接触数据库时，关系数据库非常新，效率低得令人绝望，难以使用，而且大多数是研究项目。IBM 的旗舰商业数据库 IMS 使用了层次化的方法，至少在贝尔实验室，数据库专家们迷恋于网络数据库，它比 IMS 强大得多。随着时间的推移，关系型方法取而代之，层次型和网络型方法现在只存在于历史书和大公司运营的遗留系统中。
对于非常频繁使用的数据库应用，如亚马逊、脸书和网飞，即使是运行在最快硬件上的最成熟的 RDBMS 也太慢，而且几乎不具备足够的可伸缩性来满足这些网站的需求。用第三章中的术语来说，他们已经尽可能扩大了*(更大的服务器)，任何进一步的增长都必须来自于向外扩展*(更多的服务器)，但是关系数据库很难跨服务器分布。他们的答案是放弃最常访问的数据的关系方法，使用更简单、更容易分发的方法。这些被称为 NoSQL 数据库，但我不会在本书中涉及它们。**
**结构化查询语言
您可能已经知道，SQL 是用于定义、查询和修改关系数据库的语言。如果您想在 PHP 中做这些事情，您可以通过从 PHP 向数据库发送 SQL 语句来完成。我将在这里解释 SQL，可能你已经知道了，但是你可能喜欢阅读我的方法，因为它不同于 SQL 的典型解释方式。
一些历史
最初，关系数据库的发明者 E. F. Codd 提出了关系代数和关系演算来处理关系。Codd 工作过的 IBM 的其他研究人员提出了一种基于关系演算的语言，在较小程度上也基于关系代数，现在被称为 SQL。(发音是 S-Q-L，不是“续集”。)SQL 与关系数据库的联系如此紧密，以至于它们有时被称为 SQL 数据库，而且，在许多情况下，如 MySQL，这些字母甚至出现在名称中。
关于术语:SQL 所谓的表、列和行被称为“关系”(关系数据库由此得名)、“属性”和“元组”这种对应不准确；例如，关系必须有一个主键，必须对属性或元组(它们是集合)没有顺序，并且不能有重复的行。这些事情对表格来说都不是真的，尽管在设计和思考表格时把它们当成真的是个好主意。也就是说，不要假定列和行的顺序，并且总是有一个主键。在本书中，我主要使用 SQL 术语。从应用的角度来看，列就像一个字段，行就像一个记录，但是这些术语并不在 SQL 中使用。
我将在这里提供 SQL 的概念性概述，目的是解释它的重要思想，而不是它的所有子句、操作符和函数。我会推荐一些书和其他资源，你可以去那里了解所有的细节。
SQL 语句
SQL 有几种语句。
- 用于检索数据的
select语句，有许多子条款和选项， - 修改数据的语句:
insert、update、delete， - 用于定义数据的语句，所谓的数据定义语言(DDL) ，如
create table和alter table， - 用于控制访问的语句，如
grant和revoke，称为数据控制语言(DCL) ，以及 - 其他杂项声明。
数据修改语句加上select被称为数据操作语言 (DML)。所有的 SQL 语句都很容易学习和使用，除了select，这两个都很难。
Select 语句的作用
一个select语句定义了一个虚拟表。该声明有以下四个部分:
- 所需的列，可以是所有可用的列，也可以是您指定的特定列(也称为投影)，
- 要处理哪些表来为虚拟表提供数据，
- 要选择哪些行，以及
- 如何对行进行排序。
(在我的解释中，我将跳过与结果中的行分组相关的两个子句，group by和having。)
各部分按照我列出的顺序出现在一个select语句中，但这不是思考它们的最佳方式。图 4-1 是更好的图片，显示select更像一个工厂。
图 4-1 。选择语句工厂
正如图 4-1 所暗示的，连接(从漏斗中出来)产生的虚拟表中的任何列都可以用于过滤行(where子句)，但是只有选择的列(列在单词select之后)可以用在order by子句中。一些 SQL 实现允许在order by子句中使用表达式，但这不是一个好主意，因为无论结果如何排序，都应该显示为数据。
下面是一个select陈述的印刷外观，斜体字短语对应于四个部分。
select column-specification from table-specification where conditional-specification order by order-specification
不需要的话可以省略where和order by子句。如果您不想指定列，而只想全部指定，那么您可以使用一个*来指定列。因此，键入到mysql命令中的最简单的select语句的例子是
mysql> select * from department;
+---------------+------------+
| department_id | name |
+---------------+------------+
| 1 | Accounting |
| 2 | Shipping |
| 3 | Sales |
+---------------+------------+
这里的结果与实际的表相同，但是如果我只指定了name列，那么它就是虚拟的，因为它是由select factory捏造的，并不真正存在于数据库中。
mysql> select name from department;
+------------+
| name |
+------------+
| Accounting |
| Shipping |
| Sales |
+------------+
这里有一个where子句，它在一个简单的正则表达式(模式)中使用了like操作符。
mysql> select name from department where name like 's%';
+----------+
| name |
+----------+
| Shipping |
| Sales |
+----------+
同样，它是一个比计算它的基表更窄(更少的列)和更短(更少的行)的虚拟表。
添加一个order by子句，就可以对结果行进行排序。
mysql> select name from department where name like 's%' order by name;
+----------+
| name |
+----------+
| Sales |
| Shipping |
+----------+
带有order by子句的select语句的结果是有序的，因此它显然不是数学集合，而所有关系都是。这就是 SQL 处理表而不是关系的一个原因。
连接表格
像department一样，select的表规范部分比仅仅是表名要复杂得多。假设我们也有一个employee表。
mysql> select * from employee;
+-------------+---------------+----------+-------+
| employee_id | department_id | last | first |
+-------------+---------------+----------+-------+
| 1 | 2 | Smith | John |
| 2 | 2 | Jones | Mary |
| 3 | 1 | Gonzalez | Ivan |
| 4 | NULL | Chu | Nancy |
+-------------+---------------+----------+-------+
我们可以组合来自department和employee表的数据，这样它们就可以出现在一个虚拟表中，利用它们的公共列department_id。这称为连接，执行连接的 SQL 操作符称为join。如果不加入，关系数据库比文件集合好不了多少。
有不同类型的连接。我先从一个交叉连接开始，它是两个表的叉积；也就是说，生成的虚拟表包含两个表的所有列，第一个表的每一行都在第二个表的每一行旁边。如果第一个表有 15 列 100 行，第二个表有 20 列 600 行，结果将有 35 列(15 + 20)和 60，000 行(100 * 600)。清单 4-1 显示了一个小得多的结果，来自于department和employee表的交叉连接。
清单 4-1 。部门和员工表的交叉连接
mysql> select * from department cross join employee;
+---------------+------------+-------------+---------------+----------+-------+
| department_id | name | employee_id | department_id | last | first |
+---------------+------------+-------------+---------------+----------+-------+
| 1 | Accounting | 1 | 2 | Smith | John |
| 2 | Shipping | 1 | 2 | Smith | John |
| 3 | Sales | 1 | 2 | Smith | John |
| 1 | Accounting | 2 | 2 | Jones | Mary |
| 2 | Shipping | 2 | 2 | Jones | Mary |
| 3 | Sales | 2 | 2 | Jones | Mary |
| 1 | Accounting | 3 | 1 | Gonzalez | Ivan |
| 2 | Shipping | 3 | 1 | Gonzalez | Ivan |
| 3 | Sales | 3 | 1 | Gonzalez | Ivan |
| 1 | Accounting | 4 | NULL | Chu | Nancy |
| 2 | Shipping | 4 | NULL | Chu | Nancy |
| 3 | Sales | 4 | NULL | Chu | Nancy |
+---------------+------------+-------------+---------------+----------+-------+
重要的是要认识到这种连接是无用的，甚至是误导的，因为盲目地将行配对是没有意义的。然而，请看第二行，其中两个department_id值(来自department和employee表)恰好是同一个数字(以粗体显示)。该行包含一个事实:John Smith 从事运输工作。事实上，所有这两列相等的行都是有用的，其他的行都是无用的。我们应该做的是一个内部连接，其中我们从每个表中指定一个在结果表中相等的列，导致其他列被跳过。即如下:
mysql> select * from department inner join employee
-> using (department_id);
+---------------+------------+-------------+----------+-------+
| department_id | name | employee_id | last | first |
+---------------+------------+-------------+----------+-------+
| 2 | Shipping | 1 | Smith | John |
| 2 | Shipping | 2 | Jones | Mary |
| 1 | Accounting | 3 | Gonzalez | Ivan |
+---------------+------------+-------------+----------+-------+
(如果你有一个using或on从句，你可以跳过inner这个词，我会一直这样做。除了本章中的例子，我从不使用交叉连接。我将很快介绍一些其他有用的连接类型。)
现在我们有了一个事实信息表，至少在模型反映真实世界的范围内。记住，数据库只是模型。
我们在本书中做的每个连接，以及我在任何应用中做过的每个连接，都将是一个等价连接，这意味着连接列是相等的，尽管连接条件可以是一个更复杂的表达式(例如，使用像<这样的操作符)。
如果没有雇员和部门编号的混乱，更容易阅读的结果将是以下按姓氏排序的结果:
mysql> select last, first, name from department join employee
-> using (department_id) order by last;
+----------+-------+------------+
| last | first | name |
+----------+-------+------------+
| Gonzalez | Ivan | Accounting |
| Jones | Mary | Shipping |
| Smith | John | Shipping |
+----------+-------+------------+
回到清单 4-1 中的交叉连接，查看最后三行，可以看到 Nancy Chu 不在任何部门(可能她是新员工，或者退休员工，或者大老板)。但是在前面的两个内部连接示例中都没有看到这个事实，原因是任何包含空值的条件都不会为真。然而，可以通过指定一个右外连接来获得这些行，之所以这样叫是因为它保留了右边表中的所有行，即使左边表中没有匹配的行，在这种情况下会提供空值。单词“outer”可以省略，所以它也称为右连接。在这里。
mysql> select last, first, name from department
-> right join employee using (department_id) order by last;
+----------+-------+------------+
| last | first | name |
+----------+-------+------------+
| Chu | Nancy | NULL |
| Gonzalez | Ivan | Accounting |
| Jones | Mary | Shipping |
| Smith | John | Shipping |
+----------+-------+------------+
如果我们将employee表放在连接操作符的左边，将department表放在右边，我们将会做一个左连接，因为左边的表将会被保留。
有时会有两个以上的连接。假设部门被分组为分部，并且您有一个定义了两个分部的division表。
mysql> select * from division;
+-------------+------------+
| division_id | name |
+-------------+------------+
| 1 | Operations |
| 2 | Product |
+-------------+------------+
为了扩展示例，我将 Jane Doe 添加到销售部门，并将一个division_id添加到department表。
mysql> select * from employee;
+-------------+---------------+----------+-------+
| employee_id | department_id | last | first |
+-------------+---------------+----------+-------+
| 1 | 2 | Smith | John |
| 2 | 2 | Jones | Mary |
| 3 | 1 | Gonzalez | Ivan |
| 4 | NULL | Chu | Nancy |
| 5 | 3 | Doe | Jane |
+-------------+---------------+----------+-------+
mysql> select * from department;
+---------------+------------+-------------+
| department_id | name | division_id |
+---------------+------------+-------------+
| 1 | Accounting | 1 |
| 2 | Shipping | 1 |
| 3 | Sales | 2 |
+---------------+------------+-------------+
为了获得一个显示每个人所在部门的虚拟表，我可以像以前一样连接employee和department表，然后将中间结果与division表连接，如清单 4-2 所示。department和division表都有一个name列，这很好，因为它对两者(组织名称)有相同的含义，但是我必须在 SQL 中用它们的表名限定它们。否则，我会得到一个错误，因为单独的name是不明确的。
清单 4-2 。员工、部门和分部查询
mysql> select last, first,
-> department.name,
-> division.name
-> from employee
-> join department using (department_id)
-> join division using (division_id);
+----------+-------+------------+------------+
| last | first | name | name |
+----------+-------+------------+------------+
| Smith | John | Shipping | Operations |
| Jones | Mary | Shipping | Operations |
| Gonzalez | Ivan | Accounting | Operations |
| Doe | Jane | Sales | Product |
+----------+-------+------------+------------+
可以对该查询进行两项改进。
- 为了让 Nancy Chu 出现，她没有部门，我需要使用左连接(以前，因为我有
department在先，所以它是右连接)。 - 我可以使用一个列别名来区分结果集中带有单词
as的两个name列。注意限定列本身(例如，department.name)是不够的。
清单 4-3 展示了改进后的查询，其中我还添加了一个order by子句。
清单 4-3 。改进的员工、部门和分部查询
mysql> select last, first,
-> department.name as 'Dept. Name',
-> division.name as 'Div. Name'
-> from employee
-> left join department using (department_id)
-> left join division using (division_id)
-> order by last;
+----------+-------+------------+------------+
| last | first | Dept. Name | Div. Name |
+----------+-------+------------+------------+
| Chu | Nancy | NULL | NULL |
| Doe | Jane | Sales | Product |
| Gonzalez | Ivan | Accounting | Operations |
| Jones | Mary | Shipping | Operations |
| Smith | John | Shipping | Operations |
+----------+-------+------------+------------+
在 PHP/MySQL 程序中，我不会使用类似于'Dept. Name'的别名，因为查询的结果会发送给程序，而不是运行mysql命令的终端，别名将作为数组下标，而不是像清单 4-3 中那样作为人类可读的标题。像$row['Dept. Name']这样的 PHP 表达式很难编写。我改为使用别名department_name，它反映了出现在select语句中的限定名department.name，所以表达式变成了$row['department_name']。这样就很容易将数组下标与选择列关联起来。
为了加深你对这些 SQL 查询的理解，这里回顾一下清单 4-3 中的一个查询，按照图 4-1 所示的 select 语句工厂。
employee、department和division表被组合成一个由所有列和行组成的虚拟表。left join操作符和它们的using子句用于将第 1 步中虚拟表中的行限制为那些department_id列和division_id列匹配或者这些列为空的行。- 我没有过滤步骤 2 中虚拟表的行，所以它们都保留了下来。
- 步骤 3 中的虚拟表被缩小到只包含四列，其中两列被重命名。
- 对步骤 4 中的虚拟表进行了排序。
MySQL 并不完全像我的工厂那样进行处理——它的效率要高得多——但这无关紧要，因为 SQL 是非过程化的。您指定了想要的结果集，但没有指定如何获得它。
顺便说一下，我刚刚介绍了 99%，甚至 100%的应用开发所需的所有连接:连接(内部连接)、左连接(左外部连接)和右连接(右外部连接)。我对表的看法是，我似乎从来不使用右连接。所以，如果你通读我写过的所有应用代码，你真正看到的只是 join 和 left join。(大约十年前，当我在 Oracle 为德克萨斯州的 Richardson 学区工作时，我做过一次完整的外部连接。现在想起来还会发麻。)
表达式和存储过程
与其他编程语言一样，SQL 包含大量的数字、字符串、日期和各种运算符和函数。表达式可以出现在 SQL 语句的几个地方，比如列列表(列值可以是计算的结果)、where子句和update语句。但是在 PHP/MySQL 程序中，你通常只会在where子句中使用它们。与用 SQL 计算列值相比，更简单的方法是将值返回给 PHP 程序并在那里进行任何计算。(这是一个共性；我相信你会发现偶尔的例外。)类似地，对于要放入行中的值，您需要在 PHP 中进行任何需要的计算，然后将答案传递给 SQL。因此，您会发现 SQL 书籍或课程告诉您的大部分内容都没有任何用处。在学习 SQL 时，我会专注于真正强大的连接和子查询(稍后解释)，而忽略传统的编程语言表达式。
SQL 的发展从最早的时候就没有停止过表达式。它现在包括一个完整的编程语言，所以你可以把程序放在数据库中，由数据库执行。对于 MySQL，这对于用于数据验证的触发器很重要，我将在本章的“约束”一节中介绍。否则，我不使用它们。
我想说的是，你对 SQL 的使用要保守。把它用在关系数据库最擅长的地方，用 PHP 完成大部分计算。
关于 SQL 的进一步阅读
这就是我现在要解释的关于 SQL 的全部内容，因为我完全跳过了我一直使用的employee、department和division表的来源；也就是说，数据库是如何设计的，这一点更重要。我将在本书的剩余部分解释我使用的 SQL。
我的书架上没有一本介绍 SQL 的书。用了这么久，都不记得什么时候怎么学的了。我的 SQL 书籍都是参考书或者进阶书。我发现很难找到可以推荐的辅导书。他们都有一个或更多的缺陷:他们只是给出例子而没有真正解释发生了什么(就像我对我的select语句工厂所做的)，他们有错误的信息，或者他们太复杂和先进而不能作为介绍。但是我确实找到了一本技术上准确、易于阅读，并且解释了原理和理论的书:Clare Churcher (Apress，2008)的《SQL 查询入门》,这是迄今为止我遇到的最好的 SQL 入门书籍。她只关注查询，而不是任何更新或 SQL 的其他部分，但这没关系，因为 99%的能力和复杂性都在查询中；其他陈述很简单。
一旦你读过丘奇的书，或者如果你已经知道 SQL，我会去找高级书籍，其中最好的是乔·塞尔科(摩根·考夫曼，2011 年)的《聪明人的 SQL》,这本书真的很好，应该被任何打算专业使用 SQL 的人学习。(他的 SQL 编程风格(摩根·考夫曼，2005)也不错。)您可能想了解 MySQL 的 SQL 文档，您可以在 MySQL 网站dev.mysql.com/doc找到这些文档。
如果你倾向于理论，C. J. Date (O’Reilly Media，2009)的《SQL 和关系理论》是一本很棒的书，绝对值得花时间去读。(事实证明，SQL 不是一种关系型语言，所谓的 RDBMS 也不是关系型数据库，你永远不需要使用空值。我是说理论上。)
实体关系建模
现在该说说表是从哪里来的了；也就是如何设计一个关系数据库。
ER 图 s
还记得我说过关系数据库中的表是独立的吗？我没有撒谎——他们在撒谎。但是，你不应该独立地思考 ??。您应该设计它们，以便可以使用select语句创建虚拟表，以有用的方式呈现数据。雇员和部门的例子非常简单，很容易理解如何设置这些表来实现连接。然而，在实践中，有几十个表，每个表可能有十列或更多列，很难预料如何用 SQL 处理它们。我们需要一种在比独立表更高的抽象层次上工作的设计方法，这就是实体关系建模 (ER 建模)。
ER 建模并不是唯一的建模方法。许多程序员更喜欢使用面向对象的建模，使用统一建模语言(UML) 。如果您想这样做，那就去做吧，但是请记住，您不是要用面向对象的编程语言来实现这个模型，而是要用不支持继承的关系数据库来实现这个模型。正如我在“子类型”一节中解释的，有一种方法可以完成继承的一部分，但这不是一回事，所以不要过分使用深继承树。在这本书里，我不会涉及面向对象的数据库；与他们的继承情况不同。
ER 建模提供了比一组表格更具表达力的符号。在department和employee表中使用department_id列意味着包括零个雇员在内的许多雇员可以在一个部门中，并且一个雇员可以在一个部门中，但不是必须在一个部门中(如果department_id列包含 null)。那需要太多的思考。真想像图 4-2 中的图画一样说出来。
图 4-2 。许多员工可以在一个部门工作
图 4-2 是一个部门排和五个员工排的图。最好绘制表，这意味着所有雇员只有一个框，因为每个雇员只是雇员表中的一行。行尾的一些符号可以表示可能有许多雇员。其实我还在概念阶段的时候，连一个绘图应用都懒得做。我只是素描，如图图 4-3 。
图 4-3 。一个部门中许多员工的更简洁的绘图
像这样的图片有一个正式的名字:实体关系图 (ER 图)。与关系表不同，我们可以在表之间画线。那些线条代表关系，圆圈或圆角矩形代表实体。这些图片中没有显示实体的属性，比如名称和部门编号。(ER 图中显示的“关系”和“关系”数据库之间没有联系。那是巧合。)
其思想是，根据实体、它们的属性以及它们之间的关系，将整个数据库设计成一个 ER 图，然后，在根据需求(尤其是用例)对其进行验证之后，将它转换成 RDBMS 的一组表。顺便说一下，这种翻译基本上是机械的。
每当我使用“实体”这个词时，我实际上是指一个实体集(或实体类型)。从技术上讲，实体是集合中的一个成员，例如特定的雇员或特定的部门。面向对象的程序员知道这是类和实例之间的区别。但是，我从来不用“实体”这个词来指代一个实例；为此，我总是使用“元组”、“行”或“记录”这样的词我使用“实体”一词来指代那些元组、行或记录所在的表或关系。
ER 设计工具和 MySQL 工作台
我不会在纸上勾画整个数据库。在我画出大部分草图之后，当然是所有困难的部分，我使用 er 设计工具重新绘制图表，添加属性(即命名列)，并让工具生成将创建数据库的 SQL 语句。如果我以后需要更改数据库，我会更改绘图并使数据库与之同步。您可以将 ER 设计工具想象成一个具有关系数据库智能的绘图程序。
我非正式地做建模，不关心关系的精确符号和盒子的正确形状(圆形或圆形，虚线或实线等)。).(你已经读到第四章了，现在你知道我几乎非正式地做了所有的设计；我只有在编码的时候才会紧张。)如果你想了解更多关于如何以正确的方式进行 ER 建模的知识，两本最好的书已经绝版，但仍然很容易从亚马逊的二手书店买到:案例研究:理查德·巴克(Addison-Wesley，1990)的《实体关系建模》，以及史蒂夫·霍伯曼(Technics Publications，LLC，2005)的《数据建模变得简单。也有更厚的书，比如 Graeme Simsion 和 Graham Witt (Morgan Kaufmann，2004)的数据建模基础，但是我没有发现所有额外的材料值得一读。
ER 设计工具曾经非常昂贵，但现在 MySQL 开发人员有一个免费的工具，叫做 MySQL Workbench，你可以从dev.mysql.com/downloads/tools/workbench开始为 Mac OS、Windows 或 Linux 下载。它不仅仅用于 ER 建模，它还处理数据库管理、备份和恢复、表定义和更改、数据编辑和查询。我认为这对任何 MySQL 开发都是必不可少的。
我将展示如何用 MySQL Workbench 构建employee、department和division表。首先，在初始屏幕上，我单击了“创建新的 EER 模型”按钮来启动一个新的模型，然后双击了“添加图表”按钮。这给了我一个空白的绘图画布，如图图 4-4 。
图 4-4 。空白画布
接下来我点击桌子图标，在图 4-4 中圈出，并点击画布放置一张桌子。我重复了两次，得到了如图图 4-5 所示的三个表格。
图 4-5 。三张桌子
然后我双击table1进入列编辑器，在那里我将表名改为department并输入列，如图图 4-6 所示。
图 4-6 。为部门表输入的列
我对employee和division表格做了同样的操作，并稍微重新排列了一下表格的位置，如图图 4-7 所示。
图 4-7 。完成的员工、部门和分部表
现在是有趣的部分。我点击图 4-7 中圈出的关系图标，从employee表的department_id列到department表的department_id列画一条线，在这两个表之间建立一对多的关系。(你从“多”的一面开始。)然后我从department表的division_id列到division表的division_id列做了同样的操作。这就完成了 ER 图，如图图 4-8 所示。
图 4-8 。完整的 ER 图
你觉得那很有趣吗？听听这个:MySQL Workbench 不仅仅是绘图——它知道如何将 ER 图转换为 SQL 来创建数据库，如果图发生变化，它甚至可以在以后同步它。为此，我从数据库菜单中选择同步模型，点击几个已经设置好默认值的对话框(MySQL Workbench 之前连接到我的开发平台数据库服务器)，然后进入 SQL 屏幕，如图 4-9 所示。
图 4-9 。生成 SQL 来创建表格
我单击了 Execute 按钮，表就创建好了。为了将一些测试数据输入到division表格中，我使用了 MySQL Workbench 的另一部分，表格数据编辑器，如图图 4-10 所示。我还将测试数据输入到department和employee表格中。
图 4-10 。输入到分部表中的数据
注意，我必须按顺序输入数据:division、department和employee。这是因为数据库强制的外键约束，要求输入到department表中的division_id必须已经存在于division表中。否则，将会有一个悬空的引用，一个不存在的division_id。这种强制是参照完整性的一个方面，这是极其重要的，因为它确保了 ER 图所定义的数据库模型保持一致。
正如我提到的，当我添加新的表或列，或者修改现有的列时，我可以使用 ER 图，然后将更改同步回数据库，就像我创建初始表一样。MySQL Workbench 不需要编写 SQL DDL 语句。你必须用 PHP 程序编写代码，但是我很少这样做。我的 PHP 应用只处理数据，从不修改数据模型。
ER 设计流程
在他 1976 年介绍实体关系模型的论文(“实体关系模型——走向数据的统一视图”)中，陈品山给出了设计数据库的四个步骤，这些步骤在今天仍然有意义。
- “识别感兴趣的实体集和关系集”
- “识别关系集合中的语义信息，例如某个关系集合是否是[a] 1:n 映射”
- “定义值集和属性”
- “将数据组织成实体/关系关系并决定主键”
这些是我在这里遵循的步骤，现在我已经展示了 MySQL Workbench 中的 ER 设计工具是如何工作的。
识别实体
使用 ER 设计工具听起来很容易，事实也的确如此。这是因为该工具不能帮助您解决困难的部分，即决定实体应该是什么。在图 4-8 的例子中，我很容易地画出了实体和它们的关系，因为我已经用一张纸画出了我想要的东西。我思考的时候你没在看。你会很无聊的。
一旦有了实体，事情就变得简单了，因为需求会告诉你关系是什么。例如，一个部门可以有多名员工吗？是的，当然，否则为什么有一个部门。员工可以不在任何部门吗？是的，听起来很合理。一名员工可以在多个部门工作吗？不，那是不允许的。一个部门可以没有员工吗？是的，他们就是这样开始的。所以我的结论是，部门和员工是一对多的关系，从员工端来说是可选的。
当你确定关系时，应该向你的团队成员和客户提出这样的问题。这是引出更多需求的好方法。(回想一下第二章中的内容，需求开始时范围很广，但是非常缺乏细节。)如果你问一群学校管理人员，一个学生是否可以入学，但没有课程，接着就是 45 分钟的讨论，不要感到惊讶。你会提出一些你从未想过要问的问题。
属性也是由需求决定的。您需要足够的属性来生成每个报告和屏幕，为每个业务逻辑算法提供数据输入，并保存所有转换后的数据。
但是需求不会告诉你实体应该是什么，尽管它们肯定会建议很多实体。如果是人事系统，需求中会提到部门和员工。他们会满足所有的要求。绩效评估、经理、工资等级、电话号码和其他数百种东西也是如此。这是显而易见的。但是，这些东西是实体还是属性呢？电话号码本身是员工的属性还是实体的属性？如果是属性，是部门的属性还是部门经理的属性？
无论这些问题如何回答，都有可能让系统运行并满足所有需求。既然如此，一套答案要比另一套好得多。一个好的数据库设计可以决定一个容易实现的应用和一个难以忍受的半工作应用。数据库是应用设计中最重要的部分，而实体是数据库设计中最重要的部分。
实体到底是什么？实体是有意义的事物，可能是抽象的，数据库需要存储关于它的信息，并且它与一个或多个其他实体相关。细说，
- 一个实体需要是重要的，对数据库所代表的模型来说是重要的。对于世界事务会议(CWA)来说，很明显，专门小组成员、专家小组、主持人和捐款是非常重要的。电子邮件地址和航班到达时间不是——它们只是用来完成重要的事情。换句话说，成千上万的与会者在那里聆听专家小组成员参与小组讨论。他们根本不在乎他们的航班什么时候到达。他们可能会在意自己的电子邮件地址，但不是出于正当理由，而且 99%的人都不在乎。在所有数百个潜在的实体中，只有少数几个需要任何艰难的思考来决定他们是否值得这一崇高的荣誉。
- 如果这个东西有属性，它可能是一个实体。电话号码、电子邮件地址和航班时间没有属性；它们本身就是单一的价值观。在数据库设计中，我们不关心将电话号码、电子邮件地址和时间分解成它们的组成部分。如果需要，应用可以这样做。事实上，在数据库术语中，这些被称为原子值。(回想一下物理学，甚至原子也可以分裂，但是元素周期表——延伸这个类比——有原子的盒子，而不是电子、质子和中子的盒子。)
- 如果有一系列的东西，它们可能是实体。例如，即使你不认为小组是重要的，一个小组成员通常是其中的五个或十个，这意味着他或她所在的小组的列表，这暗示了实体。正如我们将看到的，属性列表不是一个好主意。
- 稍微改变一下前面的观点，如果事物是一个集合(例如，部门和分部)，它可能是一个实体。
- 当你开始绘制关系时，你会发现一些实体需要被创建、合并或者调整。
- 好的数据库设计遵循规范化规则，你可能已经听说过(第一范式，第二范式，等等。).应用这些规则会强制将一个实体的某些属性移动到另一个必须创建的实体中。如果你的 ER 图是精心构建的，这种情况即使有，也会很少，但这是可能的。如果你试图设计没有 ER 图的表，由于标准化会有很多麻烦。
注意我所有的含糊其词，比如“可能”和“暗示”这是因为选择实体不是一成不变的。这涉及到一些艺术。
所以，记住这些要点，开始吧。需求摆在你面前，开始在一些纸上画代表实体的圆形或矩形。如果有助于你搞清楚事情的话，在一些属性上涂写，但是不要费心去得到所有的属性(忽略中间名、尊称、邮政信箱号码和其他琐事)。在人际关系中也画素描。如果你不喜欢乱涂乱画，你可以直接使用你的 ER 设计工具，但那可能会迫使你过早地进入太多的细节，而你仍然在为大图而奋斗。
经过几个小时的需求和你的草图，你会到达一个点，事情真正开始凝胶，er 模型开始有意义。您将愉快地发现，您理解您正在建模的组织或流程实际上是如何工作的。当您停止与同事谈论部门拥有员工，并开始谈论部门和员工表处于可选的一对多关系时，您将知道您已经到了。
然后是 ER 图表工具的时间。您必须更精确地确定关系，决定主键和外键，键入所有属性，并决定每个属性的物理数据类型。哦，别忘了，所有的表和属性都必须命名。那都是大量的工作，一小时又一小时。但是，主要是打字。你已经得到了实体！
识别关系及其语义信息
我已经解释并举例说明了一对多关系，这是目前最常见和最有用的一种关系。还有另外两个你会用到的。
- 一对一。这意味着一个表中的一行与另一个表中的一行相关。例如，一个面板(面板表的一行)可能有一个记录(记录表的一行)。一个面板不能有两个记录，一个记录只能属于一个面板。(这是 CWA 的政策。)如果面板不一定要有录音(可能面板还没发生)，那就是可选的一对一关系。否则，这是强制性的。
- 多对多。一个小组成员可以在几个小组中，每个小组有几个小组成员。它在任何一端都可以是可选的:某个专门小组成员可能还没有被安排到任何专门小组中，或者可能生病了而没有到达，不得不从所有专门小组中被删除，但仍然是(失踪的)专门小组成员。或者，一个专门小组可能只是凭空想象出来的，CWA 委员会还没有将任何成员列入其中。
有认同和非认同关系。如果一个表与另一个表有标识关系，则第一个表中的行如果不与第二个表相关就不能存在。例如，没有面板就不可能有录音(CWA 只记录面板)，所以一对一的关系也是可识别的(面板识别录音)。但是可以有一个没有部门的员工，所以这是不确定的。
在模型中表示标识关系的方法是将外键作为主键的一部分或全部。(我还没有正式介绍这两个术语，但是你可以理解我在这里所说的。)例如，如果面板表的主键是panel_id，我们可以使它也成为记录表的主键。这既创造了一对一的关系，又使其具有识别性。这是一对一的，因为每个主键都必须是唯一的，所以panel_id在记录表中只能出现一次。它是可识别的，因为每一行都必须有一个主键，除非有一个与之相关的面板，否则不可能有记录行。
如果是一对多的关系，因为一个面板可以有几个录音(可能一个音频一个视频)，那么panel_id只是录音主键的一部分；整个键可能是(panel_id, type)，其中类型是音频或视频。出于同样的原因，它仍然是可识别的:没有panel_id，因此没有面板，就没有主键，没有主键就没有行。
有趣的是，尽管多对多关系很重要，但是没有办法在关系数据库中直接表示它们。您必须创建另一个实体，有时称为交集(或关联)实体，您可以对其构建两个一对多关系。交集实体通常只有两列，每一列对应一个外键，这些外键引用表示多对多关系的表。
例如，图 4-11 显示了一个 MySQL 工作台画布，我在上面画了专门小组成员和小组表格。
图 4-11 。小组成员和小组成员表
现在，如果我单击图 4-11 中圈出的多对多关系图标，单击 panel 表一次，单击 panel 表一次，MySQL Workbench 不会绘制多对多关系。相反，它发明了一个新的实体，并为其构建了两个一对多的关系。老实说，图 4-12 中所示的panel_has_panelist表不是我画的，也不是我命名的，更不是我输入的栏目。这个工具自己完成了所有这些工作。
图 4-12 。表示多对多关系的综合交集表
如果你仔细观察图 4-12 中的图，你可以看到两个外键panel_panel_id和panelist_panelist_id一起构成了panel_has_panelist表的复合主键。这正是我想要的。我当然不想为那个表创建一个新的键，因为两个外键可以完成这项工作。
我不喜欢的是panel_panel_id这样的名字。简单的panel_id，准确地匹配panel表的主键名，要好得多。这不仅使外键引用的内容更清晰，而且使 SQL 连接更简单，因为您可以说
select * from panelist join panel_has_panelist using (panelist_id)
join panel using (panel_id)
而不是罗嗦
select * from panelist join panel_has_panelist
on panelist_id = panelist_panelist_id
join panel on panel_panel_id = panel_id
合成表的名称panel_has_panelist是可以的，但是我通常会为关系想一个更自然的名称，比如participation，或者干脆选择更简洁的panel_panelist。
交集表拥有附加属性是完全合理的。例如，假设小组成员有两个角色:演讲者和讨论者。这应该是panel_has_panelist表的一个属性，因为同一个小组成员在不同的小组中有不同的角色。添加了这个属性后，panel_has_panelist这个名字看起来更别扭。它现在是一个真正的实体，应该有一个像样的名字，比如participation。当你设计一个数据库时，当你练习你的艺术时，这些是你要考虑的事情。
定义属性
如果一个数据项不值得作为一个实体，或者如果设计考虑，如规范化或处理多对多关系的需要，不要强迫它成为一个实体，它是一个属性(即列)。当您筛选需求时，您将得到一个初始的属性列表，并随着开发的进行或在初始部署后添加新特性时引入更多的属性。添加属性很少影响数据库或应用的其余部分，除了数据库进行更改时的一些开销。除此之外，MySQL 和大多数其他数据库可以动态添加属性。他们可以更改名称和类型，也可以删除它们，但是这些更改可能会影响正在运行的应用。
就数据库而言，您希望保持属性的原子性。没错，使用 SQL 字符串函数，您可以解析电子邮件地址或分解日期的各个部分，但这对于编码来说有些麻烦，而且您知道您正在尝试拆分原子。“电子邮件地址”和“日期”是常用的名称，表明它们是单个单元，因此适合用于数据库列。另一方面，如果您有一个名为name的列，并输入像“Smith，John”或更糟的“John Smith”这样的值，它甚至看起来像是将两个字段打包成一个。实际上，有很多地方需要将名字和姓氏分开，当名字是单个字段时，试图解析名字会很麻烦，而且容易出错，尤其是当如此多的数据库在国际上使用时。本着组合比分解容易得多的原则，名字应该分解。没有人需要把电子邮件地址分开，所以它可以被认为是原子的。
如果这个属性有一个标准代码，试着使用它。美国邮政局为每个州和地区定义了代码，所以使用它们，而不是编造自己的代码或允许在表单中键入任何缩写。这同样适用于性别:一个名为 ISO/IEC 5218 的标准为未知、男性、女性和不适用(例如，公司)定义了代码 0、1、2 和 9，所以继续将列定义为整数并使用代码。(代码 0 避免了允许字段可为空，这具有其他优点；在“空值”一节中会有更多的介绍。)
我已经讨论了如何决定一个属性是否应该是一个实体，我会在“规范化”一节中详细介绍
决定主键
关系数据库不使用指针。相反，它们使用键来进行连接。此外，主键是标识要更新或删除的特定行的主要方式。
一个键是唯一标识表中一行的一列或多列。如果有不止一个，它们被称为候选键，你必须选择一个作为主键。大多数表只有一个键，所以别无选择。
每个关系都必须有一个主键，但是 SQL 表没有，MySQL 也是如此。但是，不要创建没有主键的表。在 MySQL Workbench 的 ER 图表部分，如果您有一个没有主键的表，您将不能用任何关系工具连接它。如果你想知道为什么当你点击鼠标时没有任何反应，这可能就是原因。我想这是一件好事，但是它确实迫使你在你的 er 设计中比你想要的更早地定义至少一个临时主键。
就关系理论而言，任何键都适合作为主键，即使它由几列(组合键)组成，并且它们是相当长的字符串。然而，实际上，组合键不方便用 SQL 编码(太多的输入)，长组合键对于数据库处理来说效率很低。较短的键更好。最方便有效的键是单个整数列，或者，如果没有类似的东西，也可以是相当短的单个列，比如州代码。
如果数据中根本没有键，实体可能设计得不好。或者，可能只是数据没有合适的内容。CWA 人的桌子就是这种情况。我们不会像音像店或保险公司那样给小组成员或捐赠者分配号码。有时两个小组成员有相同的名字，当人们改变他们的名字或纠正拼写错误时，名字经常会被修改。您不希望您的主键经常被编辑。因此，我创建了一个代理键 :一个在插入一行时自动生成的整数，它保证是唯一的。它作为一个键工作得很好，但是它是实现的一个工件，在现实世界中没有任何意义。
我总是用表名加上后缀_id来命名我的代理键。不要使用普通的id，因为这样你就不能合理地使用相同的名称作为外键，因为id太不明确了，如果已经有一个列使用了这个名称，它甚至是不被允许的。对于像department_id这样的名字，我对外键使用相同的名字，除非包含该外键的表需要多个外键，可能一个外键用于雇员所在的部门，另一个外键用于他或她进行代码评审的部门。然后你需要去类似reporting_department_id和code_review_department_id的地方，或者reporting_id和review_id的地方。你的电话。
顺便说一句，千万不要在一个地方用“dept”而在另一个地方用“dept”。每样东西都应该只有一个名字，通常它不应该是一个缩写，除非它是一个广泛使用的标准名称。像“rprtng_dept_id”这样的词太难听了。
由实际数据形成的密钥称为自然密钥。其中一个应该永远是你的首选，但是如果没有可用的，或者有但是太笨拙，继续创建一个代理键。这在 MySQL 中很容易做到:只需将列设置为不可空且自动递增的整数。我已经在图 4-6 中展示了其中的一个，我在图 4-13 中放大了其中的一部分，这样你可以看得更清楚。请注意，PK(主键)、NN(不可为空)和 AI(自动递增)被选中。
图 4-13 。定义代理主键
代理键有三个主要缺点。
- 如果有另一个候选键，您可能需要对它指定一个惟一的约束，以确保不会输入重复的数据，因为代理键会使相同的行变得惟一。如果没有 unique 约束，一个重复的行将获得自己的代理键(记住，它是自动递增的)，所以数据库会很高兴地插入它，并且错误不会被检测到。缺点是这需要一个额外的索引，因为这是唯一性的实现方式。如果自然键是主键，那么只需要一个索引。
- 当我们开始编写更多的 PHP 代码时，您会看到，如果您插入一个带有代理键的行，您必须在单独的数据库调用中询问这个键是什么，因为它是在插入时计算的，这可能有点棘手。如果钥匙是天然的，你就不用问了。
- 有时代理键会导致额外的连接。
要了解为什么会有额外的连接，假设您有一个包含列last、first、street、city和state的person表。您还有一个包含列city、state、population和mayor的city表。图 4-14 显示了模型。
图 4-14 。与自然键的一对多关系
city 表的主键是(city, state)，所以 person 表中的那两列是外键。请注意，这些是自然键。现在，如果您想要的只是一个包含城市和州的人员目录，那么您可以非常简单地做到，因为您想要的所有列都在person表中。
select * from person
然而，(city, state)正是我说过不喜欢的那种键:复合的、长的(例如，“马里兰州塞文河畔温彻斯特”)。因此，我将使 city 表的主键成为代理键city_id，并使用它作为person列中的单个外键，替换city和state列。(我们绝不会想把这些列留在那里，因为那样我们会在两个地方有城市和州，它们可能是不一致的。这就是正常化的意义所在。)
这些都没问题，但是现在要显示目录，我需要说
select * from person
join city using (city_id)
它有一个我以前不需要的连接，还显示了city_id字段，对于不在数据库中的人来说，这完全没有意义。回到我真正需要做的地方
select last, first, street, city, state from person
join city using (city_id)
我不是说你不应该使用代理键。我喜欢它们，使用它们的次数可能比大多数数据库设计人员都多。我是说他们不自由。
嗯，有时候他们是免费的。如果您希望目录列出人口和/或市长，不仅不会有额外的连接，因为在两种情况下都需要连接，而且使用代理键的连接会更有效，因为键要短得多。
没有什么是直截了当的。你必须继续思考。
外键
表中的外键是另一个表(也可能是同一个表)中的主键，它在两个表之间建立关系。外键的唯一目的是参与联接。事实上，如果有一个外键与 ER 图上画出的关系不对应，那么一定有问题。外键不仅仅是为了被聪明的 SQL 程序员发现。它们总是被故意放在那里。是的，您当然可以连接两个表，一个表中有高尔夫差点属性，另一个表中有部门编号，但是这样做是没有意义的，即使您这样做了，也不会使高尔夫差点成为外键。
重要的是外键不能引用不存在的主键。这将意味着，例如，一个专门小组成员在特定的小组上，但是该小组行已经被删除。如果 panelist 表中的外键是用外键约束声明的，这种删除可能会被数据库阻止，MySQL Workbench 会自动这样做。如果您仔细查看图 4-9 中雇员表的 SQL，您会看到以下内容:
CONSTRAINT `fk_employee_department`
FOREIGN KEY (`department_id`)
REFERENCES `department` (`department_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
这个约束意味着列department_id是对表department中同名列的引用，它引用的行不能被删除，除非首先删除该行，或者外键被更改，可能是 NULL。on delete子句表示没有动作，但是有一个选项是cascade，这意味着如果删除了department表中被引用的行，MySQL 数据库也应该自动删除该行(在employee表中被引用的行)。如果有另一个表的外键引用了 employee 表，并且也指定了cascade，这可能会导致另一个级联。等等。
我从不使用cascade，因为我害怕如果我没有完全考虑清楚事情以及数据库中的连锁反应可能导致的破坏。如果试图删除被引用的行，我宁愿得到错误消息。然后，我将把它翻译成用户能够理解的术语，比如“只要员工还在，就不能删除部门。”对我来说，这听起来安全多了；毕竟，用户可能试图错误地删除该部门，打算删除一个空部门。
正如我在讨论主键的命名时提到的，您总是希望外键具有相同的名称，除非在同一个表中有多个这样的外键，在这种情况下，它们必须具有不同的名称。无论如何，您都希望这样，因为外键显然服务于不同的目的。
正如我所说的，外键可以引用同一个表的主键。图 4-15 显示，还有两个不同角色的外键。建模的现实是经理和助理都是雇员，经理可以管理一个或多个雇员，助理可以协助一个或多个雇员(其中一些可能是经理)。MySQL Workbench 有点混淆了界限，但是如果您发挥想象力，您可以看到从主键(employee_id)到manager列的一对多关系，以及从主键到assistant列的类似但完全独立的关系。为了澄清这种关系，这在您看来可能是颠倒的:一个经理(“一”方)管理几个员工(“多”方)，每个人都有他或她的manager列引用该经理的主键。(不是经理用manager栏目；被管理的是员工。)助手同上。
图 4-15 。雇员表中的经理和助理外键列
在这里，我向雇员表添加了一些数据，以显示 Nancy Chu 是三名雇员的经理(他们的manager列中有她的employee_id):
mysql> select * from employee;
+-------------+---------------+----------+-------+---------+-----------+
| employee_id | department_id | last | first | manager | assistant |
+-------------+---------------+----------+-------+---------+-----------+
| 1 | 2 | Smith | John | 4 | NULL |
| 2 | 2 | Jones | Mary | 4 | NULL |
| 3 | 1 | Gonzalez | Ivan | 4 | NULL |
| 4 | NULL | Chu | Nancy | NULL | 2 |
| 5 | 3 | Doe | Jane | NULL | 2 |
+-------------+---------------+----------+-------+---------+-----------+
现在假设我想要一个显示每个雇员及其经理姓名的查询。在前面的例子中，当我有一个引用另一个表的外键时，我使用公共列名(例如，department_id)来连接这两个表。这一次外键(manager)引用了它所在的同一个表，所以我将把 employee 表与其自身连接起来。因为我将不得不两次提到employee表，所以我将使用别名来保持它们的正确性。第一次提到的是我要寻找其经理的员工，所以我将使用别名e。第二次提到的是经理，所以我用m。(使用什么别名完全由你决定，只要它们是不同的。)好了，准备好这个:
mysql> select e.last, e.first,
-> m.last as manager_last, m.first as manager_first
-> from employee as e join employee as m
-> on e.manager = m.employee_id;
+----------+-------+--------------+---------------+
| last | first | manager_last | manager_first |
+----------+-------+--------------+---------------+
| Smith | John | Chu | Nancy |
| Jones | Mary | Chu | Nancy |
| Gonzalez | Ivan | Chu | Nancy |
+----------+-------+--------------+---------------+
注意，我还在列列表中使用了别名，因为有两个姓和两个名。如果没有别名，MySQL 会抱怨不明确的列名。理解这个查询的方法是首先查看表表达式。它是employee表与其自身的连接，连接表达式根据主键测试manager外键。完成后，我们只想挑选出给出这两个名字的四列。
如果您仍然感到困惑，请回到我最初对内部连接的解释，当时我取了一个叉积，然后注意到一些行有匹配的键。这里你也可以产生一个叉积，如图图 4-16 所示。
图 4-16 。员工表与其自身的交叉连接
由第一个employee表(别名e)提供的前六列是这样标记的，来自第二个employee表(别名m)的后六列也是这样标记的。第一个表中的列manager是外键，第二个表中的列employee_id是与之匹配的主键。回想一下，交叉连接显示了许多毫无意义的行；它所做的只是将第一个表的每一行与第二个表的每一行配对。然而，有些行是有意义的，我已经画了阴影。他们是那些
e.manager = m.employee_id
如果您回头看一下查询，这正是内部连接条件。内部连接意味着“取叉积，只给我满足条件的行。”现在我希望一个表和它本身的连接是清楚的。
在 CWA 应用中，一个人可以是一个人的住房主人、一个人的委员会联系人、一个配偶/伴侣、一个中间人，以及其他一些东西。很多人和其他人联系在一起！我的 SQL 充满了自连接，有时在同一个查询中有三四个。提示:如果您发现自己正在这样做，请明智地使用表别名来命名表，以便它们在查询的其余部分看起来像不同的表。
子类型
有时你有一个有点一般的实体，比如person，以及该实体的几个更具体的子类型，比如moderator、donor和panelist。一个拥有像person这样的类的面向对象程序员可能会对它进行子类化来创建三个子类型，这样它们就继承了超类的公共属性，比如说，名和姓。仅与子类型密切相关的属性，例如是否要求匿名，将进入子类(比如donor)。
但是关系数据库不是这样工作的。没有继承，也没有类似“子表”的东西。我们只有表，尽管如您所知，我们当然可以从一个表引用另一个表。有三种方法来处理子类型。
- 将所有子类型(
moderator、donor和panelist)的所有属性放在person表中，不要使用不需要的属性。这种方法有时被称为卷起。 - 把所有的属性，普通的和只有仲裁者的，放在一个
moderator表中，对donor和panelist表做同样的事情。保留person桌子，如果你需要它给任何不是主持人、捐赠者或小组成员的人；否则，扔掉它。这有时被称为下降。 - 仅将公共属性放入
person表中，并为每个子类型创建一个单独的表(在本例中为moderator、donor和panelist表)。用一对一的标识关系将子类型行连接到其在person表中的行。你可以称这种方法为多表。
汇总方法很容易思考和使用，因为每个人，不管是什么角色，都是person表中的一行，这看起来很简单。有些列有默认值，甚至空值，但那又怎么样呢？即使不涉及子类型，这也并不罕见。
向下滚动的方法听起来很笨拙，因为人们分布在四张或者更多的桌子上。比方说，将所有这些表与panel表连接起来，以得出小组成员(主持人和小组成员)的列表，这需要额外的工作，并且 SQL 可能很快失控。(由子类型表的联合组成的视图会有所帮助。)
多表方法很简洁，应该会吸引面向对象的程序员。它确实需要一个 join 来获取版主、捐赠者或小组成员的所有属性，但这并不太坏，而且您可以创建一个隐藏 join 的视图，因此，实际上，您可以假装它是与查询相关联的。但是，根据 MySQL 对可更新视图非常严格的规则，这样的视图是不可更新的，因此任何插入、删除或更新都必须针对基表。这比只有一张宽大的卷起来的桌子要多得多。
这种简化的方法根本不能很好地处理一个人是两个或更多子类型的成员(例如，既是仲裁者又是施主)。两个不同表中的两行数据几乎相同，这可能不违反任何范式，但这非常糟糕。相比之下，多表方法很好地处理了这一点，但是您必须确保连接(或者封装它们的视图)是精心设计的。
如果这一切看起来太复杂了，我会说直接把所有东西都放在一个表中。我对 CWA 数据库这样做了，虽然这个表非常宽(超过 100 列)，但是使用它没有任何问题。所有这些未使用的列都浪费了空间，但这是一个非常小的应用，所以没关系。此外，我经常发现一个我认为只适用于捐赠者的属性(比如do_not_call)实际上也适用于版主和小组成员，在这种情况下，我除了开始使用一个已经准备好并在等待的专栏之外，什么都不需要做。如果我使用多表方法，我可能会希望将列移动到person表中，然后修改假设它在donor表中的代码。
也就是说，大多数看我的person表的人会说它格式不好，应该分开。不是因为他们熟悉应用和所有列的含义，而是因为超过 100 列的表很难闻。他们不会错的。
物理设计
首先，我将解释如何从 ER 图生成物理设计。然后我将讨论两个比逻辑设计更影响物理设计的复杂问题:空值和规范化。
从 ER 图到物理设计
我将列出将 ER 图转换为物理设计(表格、列等)的步骤。)，尽管 MySQL Workbench 将两者混合在一起，在绘制 ER 图时就完成了大部分物理设计。比如画一个实体其实就是画一个表，你至少要有一个主键才能画任何连接线(关系)，这就暴露了列编辑器。您可以决定推迟输入类型，直到 ER(逻辑)设计完成，您准备好进入物理设计，但是 MySQL Workbench 无论如何都会坚持提供默认类型。
尽管如此，即使 ER 设计工具在进行过程中创建了物理设计，解释一下如何从纸上绘制的纯 ER 图生成物理设计也是有用的。这些步骤不会是一个惊喜。我将简洁地陈述它们，没有例子；大多数复杂性(例如，代理键、外键和唯一约束)已经解释过了。
- 每个实体变成一个表。
- 每个属性成为一列。
- 对于多对多关系，构建一个新的实体来表示该关系，并将该关系重绘为两个一对多关系。(正如我所展示的，MySQL Workbench 坚持马上做这一步。)
- 如果您还没有为每个实体确定一个主键，那么请这样做。如果有必要，或者看起来合适的话，使用代理键。对于在步骤 3 中添加的任何实体，在步骤 5 之后决定主键，因为您将使用两个外键。
- 对于每个一对多关系，向“多”方添加一个外键，该外键引用“一”方的主键。除非这样做会导致重复的列名，否则将外键列命名为与其引用的主键相同。
- 为每个外键添加一个外键约束。
- 如果在第 4 步中引入了任何代理主键，则为其他候选键添加一个唯一约束，以避免除代理主键之外都相同的行。
- 检查除可选外键之外的每一列，看它是否可以声明为 not null，因为缺省的可为 null 是危险的。(参见“空值”一节)
- 检查每一张表，确保它处于第一、第二和第三范式。如有必要，重复步骤 1 到 8，以纠正任何规范化违规，除了您认为正常的第一范式违规。(参见“规范化”一节，我在那里解释了这个异常。)
- 添加检查约束，以确保所有数据都符合模型，并尽可能符合实际情况。(更多信息在“约束”一节中。))
- 根据需要添加索引以加快处理速度。所有主键和唯一约束都将被索引，但您可能需要更多的主键和唯一约束。最好推迟这一步，直到开发完应用并用真实数据加载数据库(可能来自转换)，否则很难知道要索引什么。(索引除了提高性能之外，没有任何其他用途。)
空值
不幸的是，默认情况下，每个非键列都允许用 NULL 代替值，如果不插入值，NULL 也是默认的，所以大多数数据库到处都是 NULL。这是一个问题，因为在条件表达式中使用时它们的行为很奇怪:任何包含 NULL 的条件表达式都会产生未知值，这是第三个真值，还有 TRUE 和 FALSE。也就是说，SQL 中的条件是三值的，而不是像大多数编程语言那样是二值的。
当我说“任何条件表达式”时，我指的是任何。甚至表情
NULL = NULL
不是真的；未知。所有其他条件操作符也是如此。
一个常见的错误是假设 NULL 与 FALSE 相同，这在其他编程语言中很常见。但不是在 SQL 中，如下例所示:
select * from employee where salary < 5000
结果不会包括salary列为空的任何人(可能工资还没有设置，或者员工是志愿者，或者输入员工数据的人不知道工资，打算以后再输入)。无论 NULL 的原因是什么，都不会包含该员工，因为NULL < 5000不为真，这是包含在结果中的条件。
当我在 CWA 数据库工作时，那条空蛇咬了我。我有一些 BOOL 列来表示一个人是版主、捐赠者还是小组成员，并且我没有禁止空值。如果条目表单上没有选中这些复选框，我的 PHP 代码会将值默认为 NULL。我编写了一个select语句来查找不是版主、捐赠者或参与者的人，如下:
select * from person where
not moderator and not donor and not panelist
结果集只有几行，这些行中的复选框已经被选中，然后又被取消选中，在这种情况下，我的程序确实为每一列输入了 0。但是从来没有任何值的列是空的，所以很多行都丢失了，包括所有的委员会成员、制作人和职员。
有几种方法可以解决这个问题。首先，可以修复 SQL。最直接的方法是使用coalesce函数，它返回第一个非空的参数。在这里，我使用它实际上使 NULL 的行为类似于 FALSE:
mysql> select * from person where
-> not coalesce(moderator, false) and
-> not coalesce(donor, false) and
-> not coalesce(panelist, false);
+-----------+-------+-----------+-------+----------+
| person_id | last | moderator | donor | panelist |
+-----------+-------+-----------+-------+----------+
| 1 | Smith | NULL | NULL | NULL |
| 2 | Jones | 0 | 0 | 0 |
| 3 | Doe | NULL | NULL | NULL |
+-----------+-------+-----------+-------+----------+
这个结果是正确的:Smith、Jones 和 Doe 是三个不是主持人、捐赠者或小组成员的人。
但是最好通过使这些列不可为空来修复数据库，这是我在“从 er 图到物理设计”一节的步骤 8 中应该做的 NULL 表示“没有值”moderator列可以解释为“已知是一个版主”，在这种情况下，如果不知道这个人是否是版主，那么 FALSE 是可以的。也就是说，代替create table语句的是
moderator bool default null,
它应该说
moderator bool default 0 not null,
考虑 NULL 对于像middle_name这样的列可能意味着的所有事情:值未知、值尚未输入、值不适用或者没有中间名。实际上，如果类型是varchar，长度为零的字符串就和 NULL 一样好，没有 NULL 的任何问题。
数字列可能需要空值，因为0不是一个好的占位符(它通常是一个有效值)，类似于-1的东西会造成混乱。
一个绝对需要 NULL 的地方是外键列为空，因为该行没有这种关系(例如，雇员不在任何部门)。由于外键约束，您不能输入像零这样的特殊值。数据库将只允许 NULL 或与约束中给定的主键匹配的值。
除了这些情况之外，空值是不需要的，并且应该被消除。不能将它设置为缺省值是很糟糕的，但是对数据库运行一个查询来报告所有可为 all 的列也差不多。我将向您展示如何做到这一点，主要是因为它给了我一个讨论information_schema和子查询的借口。
每个 MySQL 安装(从版本 5 开始)的一部分information_schema，保存每个表的结构数据。它在 MySQL 网站上有完整的文档，但是通过使用 MySQL Workbench 浏览，您可以很容易地找到它。您将很快看到columns表，它保存了每一列的数据。特别是，columns表的is_nullable列告诉我们哪些列可以为空，如清单 4-4 所示。
清单 4-4 。mydb 架构中所有可空的列
mysql> select table_name, column_name from columns
-> where is_nullable = 'YES' and table_schema = 'mydb';
+------------+---------------+
| table_name | column_name |
+------------+---------------+
| employee | department_id |
| employee | first |
| employee | manager |
| employee | assistant |
| person | first |
| person | street |
| person | city |
| person | state |
| person | moderator |
| person | donor |
| person | panelist |
+------------+---------------+
为了改进查询，可以跳过外键但需要更复杂查询的列。作为外键的列可以通过连接table_constraints和key_column表来显示，如清单 4-5 所示。
清单 4-5 。mydb 模式中的外键列
mysql> select u.table_name, u.column_name
-> from table_constraints
-> join key_column_usage as u using(constraint_name)
-> where u.table_schema = 'mydb' and
-> constraint_type = 'foreign key';
+--------------------+---------------+
| table_name | column_name |
+--------------------+---------------+
| department | division_id |
| employee | manager |
| employee | assistant |
| employee | department_id |
| panel_has_panelist | panel_id |
| panel_has_panelist | panelist_id |
| person | city |
| person | state |
+--------------------+---------------+
现在，通过使外键查询成为可空列查询的相关子查询，可以将两个查询组合起来，如清单 4-6 所示，其中相关名称用粗体显示。
清单 4-6 。mydb 模式中非外键的可空列
mysql> select table_name, column_name from columns as col
-> where is_nullable = 'YES' and table_schema = 'mydb'
-> and column_name not in (
-> select u.column_name
-> from table_constraints
-> join key_column_usage as u using(constraint_name)
-> where u.table_schema = 'mydb' and
-> u.table_name = col .table_name and
-> constraint_type = 'foreign key'
-> );
+------------+-------------+
| table_name | column_name |
+------------+-------------+
| employee | first |
| person | first |
| person | street |
| person | moderator |
| person | donor |
| person | panelist |
+------------+-------------+
事情是这样的:我只想要外部查询中不在外键集中的列名(在columns表上)。该集合由内部查询在in函数中动态生成。它与清单 4-5 中的查询几乎相同，除了只包含列名，不包含表名，并且在where子句中添加了另一个条件，以根据columns表中的表名测试key_column_usage表中的表名。
u.table_name = col .table_name
columns表不直接参与内部查询(它不是连接的表之一)，但是它的别名col仍然可以在条件中使用。这就是为什么它被称为相关子查询:从外部查询中引用别名将两个查询相关联。
我之前建议你阅读 Clare Churcher (Apress，2008)的开始 SQL 查询。当你阅读她对嵌套查询的看法时，你可能已经有了清单 4-6 中所示的查询。大多数其他 SQL 入门书籍也讨论了它们。(值得学习一下。难道您不想和您的朋友一起喝一杯，并以某种方式参与到您那天下午编写了相关子查询的对话中吗？我知道我会的。)
但是我已经偏离了本节的要点，即找出不是外键的可空列，并尽可能使它们成为not null。对于清单 4-6 中的六个可空列，这很容易做到。我已经说过moderator、donor和panelist应该是not null，因为假和空一样有效。这同样适用于varchar列、两个first列和street列:空字符串也可以。
从数据库中删除可空列，你会更开心。
归一化
每本关于数据库的书都至少列出了前三种范式，有些列出了一种称为 Boyce-Codd 范式的无编号范式，有些提到了第五种范式，至少有一本提到了新的第六种范式。如果您遵循这些规则，所有这些规则将使您的数据库设计更好。
我在这里做的是给出我自己的第一范式的版本(你会明白为什么我不确定)。我一起讨论第二范式和第三范式，因为它们在意义上非常接近，我甚至通过解释第四范式超越了大多数数据库书籍。
其他的我就不多说了，但是如果你想的话，你可以看看。你的第一选择可能是一篇很容易在网上找到的论文，威廉·肯特的《关系数据库理论中五种范式的简单指南》。它既易于阅读，又数学精确。
第一范式(1NF)
与其他范式不同，1NF 不是为了改进设计，而是关于关系数据库的一般陈述:一列中的所有值必须包含原子值，而不是例如数组、集合或关系。或者，换句话说，所有行必须有相同的列数。
既然创建一个不在 1NF 中的表是不可能的，为什么它被谈论得这么多？这是因为自从 1NF 被引入以来，它已经发展了几十年，意思是数学上的，如果不是实际上的，一个不同的问题:*表格的列不应该形成一个水平列表。*要了解为什么会这样，请查看这个表，从技术上讲，它在 1NF 中。
+---------------+------------+-----------+-----------+-----------+
| department_id | name | employee1 | employee2 | employee3 |
+---------------+------------+-----------+-----------+-----------+
| 1 | Accounting | 1 | 4 | 5 |
| 2 | Shipping | 2 | NULL | NULL |
| 3 | Sales | 3 | 6 | NULL |
+---------------+------------+-----------+-----------+-----------+
很明显，这里的想法是通过在每一行中列出员工来表示部门中的员工，但是这样做的问题是显而易见的。
- 只允许三名员工。由于可能没有固定的上限，随着部门的扩大，必须添加更多的列。当添加更多数据时修改模式是一个糟糕的主意。
- 在 SQL 中处理雇员是很尴尬的，因为必须明确地提到列(现在是三列，但是还在增加)。例如，对于每个雇员列，您必须将 department 表与 employee 表连接一次，以得到一个非常宽、非常混乱的结果表，其中包含所有的雇员数据。
如果您仔细构建了您的 ER 模型，就永远不会创建这样的表，因为您会看到 employee 是一个实体，并且您会在部门和雇员之间建立一对多的关系，如我在前面的示例中所示。即使您最终得到了一个包含多个雇员列的表，您也会很快发现这有多尴尬并解决它。所以 1NF 不是你应该担心的事情。当你不跟随它的时候你会知道它。
或者，也许不是。下面这张表呢:
+-------+-------+--------------+--------------+--------------+
| last | first | home phone | work phone | mobile phone |
+-------+-------+--------------+--------------+--------------+
| Smith | John | 303-111-2222 | 303-888-4321 | 303-987-1234 |
+-------+-------+--------------+--------------+--------------+
| Jones | Mary | 303-456-9876 | NULL | NULL |
+-------+-------+--------------+--------------+--------------+
| Doe | Joe | 303-098-3456 | 720-234-1122 | NULL |
+-------+-------+--------------+--------------+--------------+
这三个电话号码组成一个列表吗？看起来是这样，但不同的是，这些数字有不同的作用(家庭、工作、移动)，而以前员工列表只是一个列表，哪个员工是哪个并不重要。但是，不同的角色不会妨碍将电话号码移动到它们自己的表中，因为该表中的一列可以用于该角色。
将电话号码移动到它们自己的表中使得phone成为一个实体，并且在需要电话号码的任何时候都需要加入。此外，这样的连接可能会为每个人创建多达三个结果集行，每个数字一行，这在应用中处理起来比每个人一行要复杂得多，而现在表中只有一行。
消除电话号码列表值得吗？没有放之四海而皆准的答案，但在大多数情况下我不会这么做。如果列表中的元素数量很少(在本例中只有三个)并且稳定(不太可能超过三个)，并且电话号码不参与连接或where子句，那么我很想让电话号码保持原样。但是如果你认为他们应该在他们自己的桌子上，你没有错。
只是延伸一下这个论点，尽可能的烦人，前两栏呢？他们不也是名单吗？我没有在这里展示它，但是中间名的列是很常见的。然而，几乎没有设计师会认为名字的三列是一个需要解决的问题。这是真的，尽管列名(名字、中间名、姓氏)比电话列更像是一个列表。正如 Chris Date 在本章开头的引言中所说，数据库设计“主要是一种艺术努力”
明确一点:如果您对 1NF 的理解是它意味着“没有重复列”，那么您将是大多数人。这不是最初的提法，但这就是它的含义。
第二和第三范式(2NF 和 3NF)
这两条规则几乎一样，所以我将它们放在一起讨论。他们所说的本质上是这样的:*所有的列都应该依赖于整个主键，而不是其他。*否则，表格可能包含冗余数据，这可能导致不一致。与 1NF 不同，2NF 和 3NF 不是您应该违反的规则。
以下表为例:
+----------+-------+-------------+----------+
| city | state | mayor | governor |
+----------+-------+-------------+----------+
| Akron | OH | Plusquellic | Kasich |
| Columbus | IN | Brown | Pence |
| Columbus | OH | Coleman | Kasich |
+----------+-------+-------------+----------+
主键是(city, state)。调控器的名称只取决于州，而州只是键的一部分，所以表不在 2NF 中。事实上，一项快速研究表明了问题所在:卡西奇是俄亥俄州州长的事实被重复了一遍。如果选举了新的调控器，有两行需要更新以保持表的一致性。如果不一致，获得显示谁是调控者的结果将取决于查询是如何形成的，一些查询甚至可能同时产生两个不同的答案。不好。
这里还有另一个问题:如果我们删除哥伦布所在的行，比如说，因为我们关闭了那里的分支机构，我们还会丢失 Pence 是 IN 的州长这一事实。
要修复这些问题，必须将governor列移动到一个表中，其中只有state是主键。因为主键只能出现一次，所以在那个表中 Kasich 只能出现一次。
这两种范式放在一起讨论，因为 3NF 几乎是相同的，除了它是关于一个非键列，如在这个表中，显示了服务于一个城市的主要航空公司以及预订号码(不要打电话，这些号码是真实的)。
+----------+-------+---------+--------------+
| city | state | airline | phone |
+----------+-------+---------+--------------+
| Akron | OH | United | 800-864-8331 |
| Columbus | IN | Delta | 800-221-1212 |
| Columbus | OH | United | 800-864-8331 |
+----------+-------+---------+--------------+
这个问题类似于前面的问题:电话号码取决于航空公司，而不是主键，主键也是(city, state)。如果数量发生变化，有两个地方需要更新。如果哥伦布去了，达美航空的电话号码也没了。
我从来不会去区分 2NF 和 3NF。实际上，由于我在一个表中几乎从来没有一个带有其他属性的组合键，所以违反 2NF 在我的数据库中不是问题。
这里有一个避免 2NF/3NF 麻烦的更简单的方法:确保每个事实只被表示一次。
顺便说一下，解决航空公司问题的方法是将电话号码移到airline表中，如果还没有这个表，就创建这个表。回想一下，我之前说过选择实体是数据库设计最重要的方面。一旦有了正确的实体，确定将每个事实(即每列)放在哪里就很容易了:它会进入与它相关的实体的表中。
第四范式(4NF)
第四范式认为一个表不应该包含两个或更多独立的多值事实。和往常一样，最好用一个例子来说明。假设您有一个主键为employee_id的employee表，并且您想要记录每个员工使用过的所有操作系统以及他或她知道什么编程语言。由于一个员工可能有不止一个操作系统和不止一种语言，这些事实是多值的。此外，操作系统和语言是独立于 ?? 的。所以 4NF 说，你不能把操作系统和语言放在同一个表中。真的吗？这看起来确实是个好主意，有一个像清单 4-7 中所示的表格。
清单 4-7 。具有两个多值独立列的技能表
+-------------+---------+----------+
| employee_id | os | language |
+-------------+---------+----------+
| 1 | Linux | SQL |
| 1 | MacOS | PHP |
| 1 | Windows | |
| 2 | Linux | C++ |
| 2 | Windows | Java |
| 2 | | Lua |
| 2 | | SQL |
| 2 | | PHP |
| 2 | | Python |
+-------------+---------+----------+
注意os和language列是独立的；不是说员工 1 懂 Linux 上的 SQL，只是说他(她)懂 SQL，用过 Linux。这两个数据项在同一行上只是为了填充表格。主键是(employee_id, os, language)，按照要求，它在各行中是唯一的。
那么，有什么问题吗？而是有很多方法可以把同样的事实摆在桌面上。例如，清单 4-8 中的变体非常不同，但包含完全相同的冗余信息。
清单 4-8 。具有两个多值独立列的技能表
+-------------+---------+----------+
| employee_id | os | language |
+-------------+---------+----------+
| 1 | Linux | SQL |
| 1 | MacOS | PHP |
| 1 | Windows | SQL |
| 2 | | C++ |
| 2 | Windows | SQL |
| 2 | Linux | Java |
| 2 | | Lua |
| 2 | | SQL |
| 2 | Windows | PHP |
| 2 | Windows | Python |
+-------------+---------+----------+
正如《精神病黑仔》中的“会说话的人”所唱的:“说一次，为什么要说第二次？”
在清单 4-8 中，与清单 4-7 相比，OS 与语言的巧合配对不同，有些 OS 重复，有些语言重复。因为所有列都是主键的一部分，并且没有水平列表，所以表的格式是 1NF、2NF 和 3NF。然而，清单 4-8 有冗余。如果雇员 2 忘记了他或她的所有 SQL，则有两行需要更新。如果他或她开始使用 Mac OS，不清楚是将该事实放入现有行，还是放入两个现有行，或者添加一个新行。所有这些都是有效的，表仍然在 1NF、2NF 和 3NF 中。更糟糕的是，不完全熟悉这个表格的人可能会认为它说雇员 2 懂 Windows 上的 PHP，但不懂 Linux。或者他或她知道 Windows 上的 SQL 或者根本不知道 OS 上的 SQL，但是不知道 Linux 上的 SQL，这是愚蠢的。
简而言之，表中充满了怪异的问题，不能认为是良构的。由于多值列是独立的，它们需要在自己的表中，一个叫做os，一个叫做language，如清单 4-9 所示。
清单 4-9 。独立表中的多值独立列
+-------------+---------+
| employee_id | os |
+-------------+---------+
| 1 | Linux |
| 1 | Mac OS |
| 1 | Windows |
| 2 | Linux |
| 2 | Windows |
+-------------+---------+
+-------------+----------+
| employee_id | language |
+-------------+----------+
| 1 | SQL |
| 1 | PHP |
| 2 | C++ |
| 2 | Java |
| 2 | Lua |
| 2 | SQL |
| 2 | PHP |
| 2 | Python |
+-------------+----------+
现在，事实只能放在一个地方，没有冗余，因为在每个表中，两列都构成主键，主键总是唯一的。
很少有数据库设计人员关心 4NF，您也没有理由这样做。如果您曾经创建了一个不在 4NF 的表，您可能会意识到更新它涉及到数据应该去哪里的一些不确定性，然后您可以修复这个问题。
限制
默认情况下，一个 MySQL 表根本不需要有任何关于什么数据进入其中的规则，只要每个值适合列的类型，如果列有字符类型，几乎任何东西都可以。您不必有主键或任何键，并且可以有重复的行。你有一张桌子，但是你没有亲戚。(所有关系都有一个主键，这意味着不能有重复的行。)不过，对于你的数据库来说，你并不想生活在蛮荒的西部。你想要一些法律和秩序。
MySQL 约束
从用 MySQL Workbench 绘制的 ER 图构造的表确实有一些规则，称为*约束:*有一个主键，它是唯一的，也有外键约束，它防止被引用行被删除，直到引用行被首先删除(或引用被更改)，以防止悬空引用。
此外，我说过您可以声明一个或多个列是惟一的，如果有一个您不想作为主键的自然键，您会这样做。在这种情况下，您可能希望将候选(自然)键约束为惟一的，在create table语句中有如下内容:
unique index unique_name (last, first)
我还强调了制作尽可能多的专栏的重要性。
所以，我已经提到的约束是
- 主关键字
- 外键(参照完整性)
- 独一无二的
- 不为空
SQL 定义了一个检查约束，它允许您为列指定一个条件，以更广泛地检查输入的数据，而不仅仅是不为空、唯一或引用一个主键。例如，如果一个表有一个office列，您可以编写如下代码:
check (office in ('DALLAS', 'BOSTON', 'PARIS', 'TOKYO'))
不幸的是，MySQL 没有检查约束。但是在版本 5 中，它确实有触发器，而且它们几乎可以同样有效地用来验证数据。
您可能想知道为什么要在数据库中检查数据，因为所有输入的数据都要通过您的 PHP 应用，您可以在那里检查数据。我的想法是，数据库不仅应该负责存储数据模型，还应该负责确保其完整性。这样，无论数据是如何进入的，即使是直接通过 MySQL Workbench 或其他实用程序，检查都会进行。甚至一个 PHP 应用也可能有多种方式将数据放入数据库:表单、转换程序或来自另一个系统的数据提要，可能是另一个应用或像 UPC 扫描仪这样的设备(杂货店收银台用来读取条形码的设备)。如果您将验证放在数据库中，您知道没有任何无效数据可以进入。
将约束放在数据库中的另一个优点是组织性的:它将更多的工作交给团队中负责数据库的任何人，因为一旦设计好了，除了随着需求的增加做一些修改之外，就没有更多的事情要做了。添加约束使工作变得更大，减轻了其他忙于完成应用的开发人员的工作。集中约束也使得它们更有可能被强制执行，而不是依赖于每个团队成员理解与他或她项目部分相关的所有约束。
MySQL 触发器的约束
MySQL 触发器是在对表进行插入、更新或删除之前或之后执行的操作，这意味着一个表最多可以有六个触发器。您用 SQL 编写操作代码。
例如，假设您有一个包含几列的manager表，其中包括一个名为office的列，并且您希望记录每次插入。这里有一个你可以使用的触发器。
delimiter @
create trigger manager_trigger
before insert on manager
for each row begin
insert into log
set msg = concat('insert ', new.office);
end;
@
delimiter语句不是创建触发器的 SQL 的一部分，但它对mysql命令和 MySQL Workbench 的脚本部分很重要，因为create trigger语句包含一个分号(在倒数第三行的末尾)，这是默认的语句分隔符。所以改成了@，允许分号作为普通字符处理。
另一个有趣的语法是concat函数的第二个参数中的限定符new。如果触发器是为了更新，那么对于office,将会有两个感兴趣的值:旧值和新值；old和new限定符表示您想要哪个。对于插入触发器，只有新值，但仍然需要限定符。
该触发器在对manager表的任何插入之前执行，它导致对log表的插入。通过执行插入，您可以看到这一点。
insert into manager
(last, first, office)
values ('Smith', 'John', 'TOKYO');
经理表现在包含
+-------+-------+--------+
| last | first | office |
+-------+-------+--------+
| Smith | John | TOKYO |
+-------+-------+--------+
并且插入导致日志表包含
+--------+---------------------+--------------+
| log_id | datetime | msg |
+--------+---------------------+--------------+
| 2 | 2013-05-08 12:55:21 | insert TOKYO |
+--------+---------------------+--------------+
我想指出一些奇怪的行为，因为几个月前的大部分时间里，它让我相当困惑。假设您忘记在office前输入new，导致触发器出错。
delimiter @
create trigger manager_trigger
before insert on manager
for each row begin
insert into log
set msg = concat('insert ', office);
end;
@
MySQL 将允许您创建触发器，但在触发器执行之前不会检查错误的引用。现在假设您尝试一个完全有效的插入。
insert into manager
(last, first, office)
values ('Jones', 'Mary', 'PARIS');
您会得到以下错误消息:
Error Code: 1054\. Unknown column 'office' in 'field list'
花尽可能多的时间盯着insert语句，看看为什么office是未知的，看看表定义，尝试用其他方式来表达它，改变数据，无论如何，你永远不会找到错误的来源，因为它在触发器中，而被引用的字段列表在触发器中，而不是在你正在看的插入中。就我而言，我在几周前就已经创建了触发器，并且已经忘记了它们。(明年 4 月 1 日，你可能会在同事身上试用，但请不要说你是在这里读到的。)
像这样设置自动日志记录实际上是有用的，您可能想要这样做，但是我们感兴趣的是约束。为此，你用 MySQL 的过程语言写一些代码，它没有名字，但是基于 ANSI 标准的 SQL/PSM(持久存储模块)规范。关于这种语言最好的入门书籍是 Guy Harrison 和 Steven Feuerstein 的《MySQL 存储过程编程》( O’Reilly Media，2006)。
这种语言有编程语言通常会有的条件和流控制语句，但是数据验证只需要很少的一部分。下面是如何更改触发器来完成上一节中 check 约束所做的事情。
delimiter @
create trigger manager_trigger
before insert on manager
for each row begin
if new.office not in
('DALLAS', 'BOSTON', 'PARIS', 'TOKYO') then
-- generate an error
end if;
end;
@
但是我们如何产生一个错误呢？MySQL 程序员过去常常通过执行非法操作来生成一个错误，例如更新一个不存在的表，该表的名称是由错误消息形成的，从而得到如下错误:
Error Code: 1146\. Table 'mydb.ERROR: bad office' doesn't exist
然后，他们会对错误消息进行一些模式匹配，解析出“错误:坏办公室”部分。
但是现在，在 5.5 版本中，MySQL 有了signal语句，因此触发器可以编码如下:
delimiter @
create trigger manager_trigger
before insert on manager
for each row begin
if new.office not in
('DALLAS', 'BOSTON', 'PARIS', 'TOKYO') then
signal SQLSTATE value 'CK001'
set MESSAGE_TEXT = 'Invalid OFFICE value.';
end if;
end;
@
SQLSTATE 可以保存五个字符的字符串。所有内置的都是数字，所以如果你让你的以非数字开始，就不会有任何冲突。
关于 PHP 和 MySQL 的接口我还没有说太多(更多在第五章，但是我现在将给出一个例子来说明不仅信号会导致 MySQL 错误，而且 MySQL 的 PDO 接口会抛出一个异常，所以这个错误很容易被 PHP 捕获。清单 4-10 显示了一个试图插入无效数据的程序(office列中的“巨石”)。insert以粗体显示。
清单 4-10 。插入无效数据会触发错误
define('DB_HOST', 'localhost');
define('DB_PORT', '3306');
define('DB_NAME', 'mydb');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '...');
try {
$dsn = 'mysql:host=' . DB_HOST . ';port=' . DB_PORT .
';dbname=' . DB_NAME . ';charset=utf8';
$pdo = new PDO($dsn, DB_USERNAME, DB_PASSWORD);
$pdo->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
$pdo->query("insert into manager set office = 'BOULDER'");
}
catch (PDOException $e) {
die(htmlspecialchars($e->getMessage()));
}
注意，我设置了 PDO 属性PDO::ERRMODE_EXCEPTION,这样任何错误都会引发异常。这是你应该经常使用的 PDO 界面的一个有价值的特性，但是，不幸的是，它在默认情况下是禁用的。还要注意异常处理程序中的函数htmlspecialchars，因为 MySQL 错误消息往往包含尖括号和其他特殊字符。
当我运行该程序时，我在屏幕上看到以下内容:
SQLSTATE[CK001]: <<Unknown error>>: 1644 Invalid OFFICE value.
我喜欢将这个约束实现为触发器，因为它在数据库内部，所以这个或任何 PHP 程序都会自动得到错误。正如我之前所说，数据模型约束属于数据库，而不是应用。
当然，对更新进行同样的约束也很重要。这可以通过另一个触发器来完成(注意，它现在显示的是before update)。
delimiter @
create trigger manager_trigger
before update on manager
for each row begin
if new.office not in
('DALLAS', 'BOSTON', 'PARIS', 'TOKYO') then
signal SQLSTATE value 'CK001'
set MESSAGE_TEXT = 'Invalid OFFICE value.';
end if;
end;
@
但是，正如拥有冗余数据不是一个好主意一样，对约束进行两次编码也不是一个好主意。不幸的是，没有 MySQL 语法像
before update or insert on manager
所以你需要两个完全独立的触发器。
好吧，程序员怎么把常用代码合并，这样就不用写两遍了？有了程序，就是这样。MySQL 代码也是如此——我将定义一个过程并从两个触发器中调用它，如清单 4-11 所示。
清单 4-11 。两个触发器调用同一个过程(不起作用)
delimiter @
create procedure check_manager() begin
if new.office not in
('DALLAS', 'BOSTON', 'PARIS', 'TOKYO') then
signal SQLSTATE value 'CK001'
set MESSAGE_TEXT = 'Invalid OFFICE value.';
end if;
end;
@
create trigger manager_trigger_update
before update on manager
for each row call check_manager;
@
create trigger manager_trigger_insert
before insert on manager
for each row call check_manager;
@
当我试图更新经理表时，我得到以下结果:
Error Code: 1109\. Unknown table 'new' in field list
问题是限定符old和new允许在触发器中使用，但不允许在过程中使用，即使这些过程是从触发器中调用的。因此，有必要传入new.office的值，因为该过程没有其他方法来获取列数据。事实上，所有的列都应该被传入，这样过程就可以访问整行，允许在一个过程中对表的所有约束进行编码。
清单 4-12 显示了修改后的代码，现在可以工作了。
清单 4-12 。两个触发器调用同一个过程(有效)
delimiter @
create procedure check_manager(last varchar(45),
first varchar(45), office varchar(45))
begin
if office not in
('DALLAS', 'BOSTON', 'PARIS', 'TOKYO') then
signal SQLSTATE value 'CK001'
set MESSAGE_TEXT = 'Invalid OFFICE value.';
end if;
end;
@
create trigger manager_trigger_update
before update on manager
for each row call check_manager(new.last, new.first, new.office);
@
create trigger manager_trigger_insert
before insert on manager
for each row call check_manager(new.last, new.first, new.office);
@
我喜欢将每个表的所有约束代码放入它自己的过程中，但我不喜欢的是必须将参数列表写出三次，包括将所有类型写对。这意味着每当我修改一个表时，我都必须调整触发器和约束过程。此外，我的一些 CWA 表有很多列，甚至第一次写出参数列表也很痛苦。真的，我宁愿花四个小时写代码，也不愿花四分钟打一些无聊的东西。
所以，我就是我，本质上是一个工具铁匠，我决定自动写出参数列表，因为它都在information_schema中，我已经在“Nulls”一节中展示过了。
我将一点一点地构建 PHP 程序。清单 4-13 显示了依赖函数add_triggers来创建插入和更新触发器以及它们调用的过程的主要部分。
清单 4-13 。代码调用add_triggers 来添加触发器和过程
define('DB_HOST', 'localhost');
define('DB_PORT', '3306');
define('DB_NAME', 'mydb');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '...');
try {
$dsn = 'mysql:host=' . DB_HOST . ';port=' . DB_PORT .
';dbname=' . DB_NAME . ';charset=utf8';
$pdo = new PDO($dsn, DB_USERNAME, DB_PASSWORD);
$pdo->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
add_triggers($pdo, 'manager', "
if office not in
('DALLAS', 'BOSTON', 'PARIS', 'TOKYO') then
signal SQLSTATE value 'CK001'
set MESSAGE_TEXT = 'Invalid OFFICE value.';
end if;
");
}
catch (PDOException $e) {
die(htmlspecialchars($e->getMessage()));
}
注意，add_triggers的第三个参数是将在过程内部结束的检查约束。它是从清单 4-12 中的代码复制而来的。
使用information_schema总是需要一些研究和实验，但是之后我想出了这个查询来列出一个表的列和它们的类型。
mysql> select column_name, column_type
-> from information_schema.columns
-> where table_schema = 'mydb' and
-> table_name = 'manager';
+-------------+-------------+
| column_name | column_type |
+-------------+-------------+
| last | varchar(45) |
| first | varchar(45) |
| office | varchar(45) |
+-------------+-------------+
清单 4-14 显示了初始的add_triggers函数，它只显示了列的逗号分隔列表($cols)和另一个列及其类型的逗号分隔列表($parms)。
清单 4-14 。初始add_triggers功能
function add_triggers($pdo, $table, $sql) {
$stmt = $pdo->prepare('select column_name, column_type
from information_schema.columns
where table_schema = :dbname and table_name = :table');
$stmt->execute(array('dbname' => DB_NAME, 'table' => $table));
$cols = $parms = '';
while ($row = $stmt->fetch()) {
$cols .= ", new.{$row['column_name']}";
$parms .= ", {$row['column_name']} {$row['column_type']}";
}
$cols = substr($cols, 2); // extra ", " at front
$parms = substr($parms, 2);
echo "<p>$cols";
echo "<p>$parms";
}
这是输出。
new.last, new.first, new.office
last varchar(45), first varchar(45), office varchar(45)
下一步是获取这两个列表($cols和$parms)并构建create trigger和create procedure字符串。清单 4-15 中的显示了这么多，它显示了清单 4-14 中最后一个echo之后的内容。
清单 4-15 。add_triggers函数的更多代码
$trigger1_name = "table_{$table}_trigger1";
$trigger2_name = "table_{$table}_trigger2";
$proc_name = "check_table_{$table}";
$trigger1_create = "create trigger $trigger1_name
before insert on $table for each row begin
call check_table_{$table}($cols); end";
$trigger2_create = "create trigger $trigger2_name
before update on $table for each row begin
call check_table_{$table}($cols); end";
$proc_create = "create procedure $proc_name($parms)
begin $sql end";
echo "<p>$trigger1_create";
echo "<p>$trigger2_create";
echo "<p>$proc_create";
现在我得到了清单 4-16 中所示的输出，添加了一些换行符和空格以使其更具可读性。
清单 4-16 。添加清单 4-15 中的代码时的输出
new.last, new.first, new.office
last varchar(45), first varchar(45), office varchar(45)
create trigger table_manager_trigger1 before insert on manager
for each row begin
call check_table_manager(new.last, new.first, new.office);
end
create trigger table_manager_trigger2 before update on manager
for each row begin
call check_table_manager(new.last, new.first, new.office);
end
create procedure check_table_manager
(last varchar(45), first varchar(45), office varchar(45))
begin
if office not in ('DALLAS', 'BOSTON', 'PARIS', 'TOKYO') then
signal SQLSTATE value 'CK001'
set MESSAGE_TEXT = 'Invalid OFFICE value.';
end if;
end
SQL 构建完成后，剩下的工作就是添加代码来删除现有的触发器和过程，并创建新的触发器和过程。清单 4-17 显示了完整的功能。
清单 4-17 。最终add_triggers功能
function add_triggers($pdo, $table, $sql) {
$stmt = $pdo->prepare('select column_name, column_type
from information_schema.columns
where table_schema = :dbname and table_name = :table');
$stmt->execute(array('dbname' => DB_NAME, 'table' => $table));
$cols = $parms = '';
while ($row = $stmt->fetch()) {
$cols .= ", new.{$row['column_name']}";
$parms .= ", {$row['column_name']} {$row['column_type']}";
}
$cols = substr($cols, 2); // extra ", " at front
$parms = substr($parms, 2);
echo "<p>$cols";
echo "<p>$parms";
$trigger1_name = "table_{$table}_trigger1";
$trigger2_name = "table_{$table}_trigger2";
$proc_name = "check_table_{$table}";
$trigger1_create = "create trigger $trigger1_name
before insert on $table for each row begin
call check_table_{$table}($cols); end";
$trigger2_create = "create trigger $trigger2_name
before update on $table for each row begin
call check_table_{$table}($cols); end";
$proc_create = "create procedure $proc_name($parms)
begin $sql end";
echo "<p>$trigger1_create";
echo "<p>$trigger2_create";
echo "<p>$proc_create";
$pdo->exec("drop procedure if exists $proc_name");
$pdo->exec("drop trigger if exists $trigger1_name");
$pdo->exec("drop trigger if exists $trigger2_name");
$pdo->exec($trigger1_create);
$pdo->exec($trigger2_create);
$pdo->exec($proc_create);
echo "<p>Success!";
}
回顾一下我刚才展示的内容:为了避免编辑列和类型的列表，我通过对information_schema的查询生成了它们。然后，我构建 SQL 语句来创建触发器和过程，并将这些语句发送到 MySQL 进行处理。我首先删除已创建的对象(如果它们存在的话)，这样程序就可以在每次模式改变时运行。实际上，针对单个表传递给add_triggers的 SQL 会更长，因为通常会有许多检查需要进行。您还可以为每张桌子调用add_triggers。但是add_triggers功能本身不必改变。机械地生成参数列表是值得的，因为它们可能很长，可能有很多表，并且它们必须随着模式的改变而保持最新。
嗯，用 MySQL 编码检查约束不像用 Oracle 这样更完整的 DBMS 那么容易，但也不错。但是，还有一个更严重的问题:带有 check 约束的触发器的存在并不意味着数据都检查正常，因为触发器可能在数据输入后被添加或更改。请记住，它只在插入或更新时触发。这是在输入任何数据之前对数据库设置所有约束是个好主意的原因之一。
交易 s
一个事务 是一个与数据库交互的短序列，由查询和/或更新组成，它们一起形成一个有意义的活动单元。实际上，您应该将一个事务看作是一组必须完全完成或者根本不完成的 SQL 语句。
例如，清单 4-9 中显示的两个表os和language，以及它们引用的employee表。假设您想要删除一个雇员，这需要删除所有三个表中该雇员的所有行，这将需要执行三个单独的 SQL delete语句(在没有级联的情况下)。这些构成了一个事务，因为如果由于某种原因它们都无法完成，您不希望任何数据被删除。删除员工完全失败是可以的；在这种情况下，用户将被简单地告知失败，他或她可以再试一次。比如说，删除语言，留下主雇员行和操作系统行是不行的。如果数据库处于这种状态，它将包含错误的信息。尽管这种不一致可能会很快得到解决，但仍然会有时间让另一个用户生成一个包含错误信息的报告。也许员工刚刚辞职，所以虚假信息对他或她来说无关紧要。但是报告的目的可能是判断经理的表现，他会因为有一个不懂编程语言的员工而受到批评。
事务的这种属性，要么完全完成，要么根本不做，被称为原子属性。有四个基本属性共同构成了首字母缩略词 ACID 。
- 正如我刚才解释的，A 代表原子。
- C 代表一致性，这意味着当事务完成时，所有一致性约束(例如，外键和非空)必须为真。
- I 表示被隔离，这意味着该事务的影响对于与数据库交互的任何其他进程都是不可见的。
- D 代表耐久性，这意味着一旦完成，该事务的效果不能丢失，即使出现操作系统错误、硬件故障或断电。
Theo Haerder 和 Andreas Reuter 在 1983 年的一篇调查论文“面向事务的数据库恢复原则”(www.minet.uni-jena.de/dbis/lehre/ws2005/dbs1/HaerderReuter83.pdf)中创造了一个聪明的缩写词 ACID，意思是高质量的数据库必须通过“ACID 测试”，非常值得一读。
这些属性是数据库的责任，而不是您的应用的责任，前提是您已经指明了事务开始和结束的时间。通过 MySQL 的 PDO 接口，您可以通过调用PDO::beginTransaction和PDO::commit来实现。您还必须使用 InnoDB 存储引擎，无论如何您都应该这样做。其他存储引擎(有很多)可能也支持事务，但是对于大多数目的来说，InnoDB 是最好的。
在您的 PHP 代码中，一个事务看起来如下:
$pdo->beginTransaction();
// ... several SQL statements ...
$pdo->commit();
如果执行在到达commit之前被中断，那么从beginTransaction开始的所有操作都将回滚，就像从未发生过一样——所有的删除、插入和更新。因为隔离，没有其他事务可以看到你的任何部分工作；如果有，并且您回滚了，它可能会看到看似存在但实际不存在的幻影数据。
如果您想强制回滚，因为可能有一个 SQL 错误，或者您已经检测到一些错误，或者用户取消了您的应用正在做的任何事情，您调用PDO::rollback函数。如果 SQL 语句失败，这不会自动完成；你必须自己捕捉异常并调用rollback。
如果不启动事务，每个 SQL 删除、插入或更新都是在 MySQL 所谓的自动提交模式下执行的，这意味着每个语句都是自己的事务。也就是说，你一边走一边提交。这是默认模式——如果您愿意，可以关闭它，如果有一长串不一定在事务中的更新，并且如果单个更新被排队等待以后处理，可以更有效地处理，那么您可能会这样做。
MySQL 的一致性属性不言而喻，因为没有办法像其他系统那样推迟约束。有了它们，任何延迟的约束都会作为提交的一部分得到处理；在 MySQL 中，约束(和触发器)逐个语句地操作。
默认情况下，MySQL(实际上是 InnoDB 引擎)提供的隔离赋予了可重复读取，这意味着事务中的任何普通(非锁定)select语句都可以看到一致的数据，即使某处的另一个事务试图更改该数据。如果需要，您可以用一个set transaction isolation level语句来改变它；选项有read committed、read uncommitted或serializable。我不会在这里讨论细节，但是你可以在 MySQL 文档的dev.mysql.com/doc中读到。
在我编写的应用中，几乎我所有的代码都使用了自动提交。偶尔我会做一些必须是原子的更新，所以我建立了一个事务。您希望避免让事务变得太大——例如，几十个，甚至几百个更新——因为整个事情可能必须回滚，并且许多行可能必须被锁定。如果您有那种批量更新，并且它确实需要是原子性的(可能不是——仔细想想),那么最好在数据库不被使用时运行它，如果有这样的时间的话。
当我有一个事务时，我将rollback调用放在错误处理代码中，如清单 4-18 中的所示。注意，我测试以确保设置了$pdo,因为如果构造函数失败就会抛出一个异常，在这种情况下它没有被设置。此外，在调用rollback之前，我会测试自己是否在一个事务中。(通常，我的应用不是这样组织的，因为$pdo设置远离事务代码。在第五章中会有更多的介绍。)
清单 4-18 。调用错误处理程序中的rollback
try {
$dsn = 'mysql:host=' . DB_HOST . ';port=' . DB_PORT .
';dbname=' . DB_NAME . ';charset=utf8';
$pdo = new PDO($dsn, DB_USERNAME, DB_PASSWORD);
$pdo->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
$pdo->beginTransaction();
// ... several SQL statements ...
$pdo->commit();
}
catch (PDOException $e) {
if (isset($pdo) && $pdo->inTransaction())
$pdo->rollBack();
die(htmlentities($e->getMessage()));
}
数据库安全
数据库安全包括
- 备份和恢复:保护数据不因错误、设备故障或破坏而丢失，
- 网络安全:防止对 MySQL 服务器的未授权访问，以及
- 访问控制:防止未经授权的 SQL 操作，比如删除表。
我简要地讨论了其中的每一个，要了解更多信息，您可以查看位于dev.mysql.com/doc的 MySQL 文档。
备份和恢复
无论谁运行生产服务器，无疑都有某种备份和恢复系统，但问题是您是否能信任它。例如，您可以询问备份是否异地存储，多久异地移动一次，保留多少代等等，您会得到答案，但这些都将基于书面政策。当夜班操作员在凌晨 2 点打盹、给他或她的朋友发短信或者在外面抽烟时，实际上发生了什么，谁也说不准。
因此，除非数据库太大而不实用，否则您应该自己制作备份并将它们存储在本地计算机上。您可以从 MySQL Workbench 或使用*nix shell 脚本来完成。另一个想法是使用一个名为s3cmd的命令，用一个每天晚上自动运行的脚本将备份存储在亚马逊 S3(云存储)上。你可以在gist.github.com/oodavid/2206527找到这方面的文章。
注意，我这里说的备份和恢复是针对整个数据库的。它与回退事务(回滚)无关，回退事务在 MySQL 内部处理。
网络安全
如果运行在 web 服务器上的 PHP 程序和 MySQL 在同一台计算机上，那么您只需要从“localhost”默认访问 MySQL。它是安全的，因为没有任何其他计算机的访问。如果 MySQL 在它自己的计算机上，你需要从应用计算机上访问它，但是你可以把它限制在固定的 IP 地址上，这仍然是安全的，特别是如果它们在本地网络上。只要你设置正确，在公共互联网上进行更广泛的访问是安全的，不要过于慷慨地给出允许访问的 IP 地址。
您可以在 MySQL Workbench 中设置网络安全性。你需要的技术信息在dev.mysql.com/doc的 MySQL 文档的第六章中。
访问控制
MySQL 的访问控制允许您创建拥有自己密码的用户，然后控制他们拥有什么特权来操作数据库、表和行。为应用的每个用户创建一个 MySQL 用户是不切实际的，因为 MySQL 管理员必须创建 MySQL 用户，而且权限不能映射到应用功能中。
一般来说，对于本书主题的各种 PHP/MySQL 应用所使用的任何数据库，您只需要两个用户:一个拥有所有权限，用于管理数据库，另一个用于普通应用用户。第一个会在你安装 MySQL 的时候自动设置，也就是本书(root)的例子中出现的那个。你必须自己设置一个更有限的，用 MySQL Workbench 很容易做到。首先设置用户，这里称为app，如图图 4-17 所示。
图 4-17 。MySQL Workbench 中的用户应用设置
接下来，在“管理角色”选项卡上，您将该用户限制为只能进行一些 DML 操作，因此它可以读取和修改数据，但不能更改模式，如图 4-18 所示。
图 4-18 。用户应用的管理角色
在第五章和第六章中，我展示了如何让用户登录到你的应用中。每个人都以 MySQL 用户app的身份运行，除了管理员，他们以root或者你所称的管理用户的身份运行。在第七章的中，我会对面向应用的角色(基于角色的访问控制，或者 RBAC)有更多的介绍。
性能优化
关于数据库性能优化的第一条也是最重要的一条规则是，除非有证据表明你需要，否则你不应该这么做。即使你认为你有，你也不能做任何测量，直到有一些现实生活中的数据，所以你至少要等到那个时候。
如果有问题，尽量本地化。如何对查询进行编码会对它的运行时间产生巨大的影响。找到一个检索兆字节数据的查询，结果却是大海捞针，丢掉了干草，这是很常见的。一个更好的查询可能会运行得更快。
索引可能有助于加快连接或where子句的速度。每个主键和唯一约束都有一个，但更多可能会有帮助。它们会降低更新速度，但会大大加快查询速度。
如果您认为模式和 SQL 已经尽您所能做到最好，那么接下来的事情就是通过增加内存来扩大规模，如果这不起作用，就增加更多的 CPU/内核和更快的磁盘，甚至可能增加固态硬盘。如果必须向外扩展，有时可以逻辑地拆分数据库，例如，将每个销售区域放在自己的服务器上。这使得一些报告变得复杂，但是更新可能仍然很简单。最后一个办法是将一些数据转移到 NoSQL 数据库。
我没有足够的空间来详细讨论 MySQL 的性能，但是有一本关于这个主题的非常出色的书，由巴伦·施瓦茨、彼得·扎依采夫和瓦迪姆·特卡琴科合著的《高性能 MySQL 》( 2012 年，奥赖利媒体出版公司)。如果你有 MySQL 性能问题，这是必不可少的读物。
你有好的数据库吗？
现在您已经读到了本章的末尾，您会想知道您的数据库设计是否是一个好的设计。如果以下条件都为真，则为真:
- ER 图是可以理解的，所有的关系不仅有意义而且符合要求。
- 所有的表和列都有很好的名称，具有一致的命名方案。外键列与其相关的主键同名，除非表中有多个这样的外键列。
- 您已经完成了“从 ER 图到物理设计”一节中列出的所有步骤这意味着每个外键都有一个外键约束，所有候选键都有唯一的约束，它是第三范式(如果不是第四范式)，并且您已经建立了所有合理的完整性约束，包括触发器形式的检查条件。
- 您已经用一个执行适当连接的查询测试了 ER 图上的每个关系。
- 所有要从现有系统转换的数据都可以加载到数据库中。
- 可以满足所有报告要求，这可以通过编写测试 SQL 查询来确定。(不需要自己创建报告。)
- 可以满足所有 CRUD 要求。
- 您已经浏览了每一个用例，并且您需要的数据库中的所有东西都在那里。
在测试关系(步骤 4)之前，您必须加载一些数据，这样您就有东西可以使用了。最好的选择是开发并运行你无论如何都需要的转换程序(第八章)，但是，因为这需要一些时间，你可能想用假数据来代替。关系(表示为外键)应该从 PHP 程序中插入，因为手工插入太繁琐了。原始数据(假名、地址等)的良好来源。)是网站generatedata.com。或者，用 PHP 编造你自己的假东西是相当容易的。如果你一直想认识名叫斯图·皮德利或丹·塔弗洛斯的人，现在你的机会来了。
如果您满足了这些条件，那么您就拥有了一个好的数据库！你应该祝贺自己，因为现在你的项目一定会成功。您有需求，这是成功的最重要的标准，还有数据库，这是实现的最关键的部分。完成转换，写报告，现在你需要的是一个像样的用户界面。这确实很难，但如果你第一次没有做对，你可以继续尝试，直到你做对为止，而不会影响系统的任何其他部分。
等等。。。。把失败从胜利的虎口中夺走还不算太晚。阅读下一节了解如何做到这一点。
开发对象关系映射层
如果您的团队中有任何面向对象的程序员，您几乎肯定会这样做，他们会希望用面向对象的模型包装关系模型，这样他们就可以假装他们真的在使用对象数据库。对象关系映射(ORM) 提倡用他们的编程语言，通常是 Java 或 C#，根据对象进行真正的设计；数据库，在某种程度上，他们想考虑它，只是一种方法，使这些对象持久化。
这种方法的好处是
- 应用与关系模型是分离的，所以如果模型改变，只有 ORM 的内部必须改变。
- 整个应用只有一个对象模型，而不是一个用于程序，另一个用于数据库(由实体和关系组成)。
- 只有在 ORM 层工作的程序员(或者程序员，如果是一个大项目的话)必须处理 SQL 和事务(ACID 属性)。
- 跟踪数据库的使用情况很简单，因为所有的访问都通过同一个界面。
我觉得很棒！但是，一如既往地，也有一些缺点。
- ORM 将会有数百甚至数千行额外的代码，如果应用的程序员愿意并且能够直接处理 SQL，所有这些都是不必要的。换句话说，没有一行 ORM 代码与任何需求直接相关，因此也没有任何客户利益。它的唯一目的是实现一种特定的实施方法。
- ORM 是数据不匹配、一致性故障、崩溃、错误查询和 ACID 故障的新来源。它必须配备人员、调试、测试、记录、移植和维护。
- 如果 ORM 要保护大多数程序员不受 SQL 的影响，那么这将是一个巨大的开发瓶颈，在一两个从事 ORM 工作的人实现他们需要的对象和方法之前，任何人都无法取得很大的进展。以数据库为中心的架构所提供的开发并行性被破坏了。
- ORM 程序员的工作非常困难。将每个实体映射到一个对象很容易，但是应用的许多部分都要利用关系，这意味着实体之间的连接。这些都需要发明新的物体。事务也必须在 ORM 中实现。
- 应用开发人员被剥夺了 SQL 的好处，主要是将数据作为集合处理的能力，使用非过程的基于集合论的查询代替过程代码。
- 应用程序员对新的 ORM 特性的每一个请求都需要协商，如果我自己的经验有指导意义的话，还需要争论。这使得工作变得非常不愉快和低效。
- 由于前一点，程序员将通过已经实现的对象将查询编码为一系列循环，而不是让数据库做它擅长的事情，即以高度优化的方式运行
select语句工厂(图 4-1 )，这可能需要对 ORM 进行更改。
我还应该提到一点，ORM 并没有而不是保护其余的应用代码免受数据模型变化的影响，因为大多数这样的变化都会改变 ORM 模型。您已经拥有了逻辑和物理模型之间的数据独立性，这是 Codd 最初对关系方法的证明，您不需要它两次。
啊哦！我可能暴露了我的位置！当然，我见过。为什么，有了一个强大的团队(第一章)、手头的需求或进展良好的需求(第二章)、建立了正确的平台(第三章)以及良好的数据库设计(第四章)，你会想用 ORM 把它搞砸呢？在这一点上，没有 ORM，你几乎不可能失败。编程转换(如果你还没有)，CRUD 和报告，你就完成了。事实上，在您构建 ORM 之前，您就已经完成了！
构建一个高效可靠的 PHP/MySQL 应用的最快方法是按照它本来的用途使用 MySQL，作为一个关系数据库。也许如果你使用一个对象数据库，你也可以做得很好(也许)，但是假装这就是 MySQL 将会是一个巨大的错误。
尽管如此，面向对象的狂热分子会要求 ORM，他们会无情地攻击任何不同意他们的人。尽你所能击退他们。他们的想法在一个长长的Stackoverflow.com帖子(stackoverflow.com/questions/760834/question-about-the-benefit-of-using-an-orm)的评论中被很好地捕捉到了。
当你让数据库决定你的应用设计时，你就失去了正确建模面向对象设计的能力，并开始让数据库决定你如何设计一个荒谬的应用。我不关心数据库。它是一个持久的数据存储，仅此而已。在应用中使用数据库仅仅是拥有可以以某种方式查询的持久数据存储的一种方式。
这是一个很棒的评论，因为它非常清楚地表达了我一直建议的完全相反的观点:数据库应该决定设计，你必须非常关心它，因为它建立了应用操纵的模型。
这让我想起我曾经看过的一篇关于摇滚唱片的评论，大意是音乐似乎来自讨厌摇滚的音乐家。我认为 ORM 的人讨厌数据库。
Ted Neward 在他的博客(blogs.tedneward.com)上称 ORM 为“计算机科学的越南”。这个比喻不错。用纽沃德的话说，ORM“代表了一个泥潭，开始很好，变得更复杂久而久之，不久就让用户陷入一个没有明确分界点、没有明确胜利条件、没有明确退出战略的承诺中。”还有这个:“。。。早期的成功产生了在成功变得更加难以捉摸的地方使用 O/R-M 的承诺，随着时间的推移，由于通过所有可能的用例来支持它所需的时间和精力开销，根本就不是成功。”
我将以一个真实的故事来结束这篇长篇大论:你还记得我在第一章中描述的超级任务项目吗？该项目的程序员为了开发一个 Windows 版本，耗尽了所有的风险投资，却一无所获。当我在他们工作了一年后接手工程时(50 万美元)，他们有一个看起来很像 Windows 文件资源管理器的外壳，一个非常好的数据库设计和一个 ORM。仅此而已。正如我提到的，我们得到了另外六个月的资金。我保留了数据库，扔掉了 shell 和 ORM。所有程序员都用 SQL 编写代码，有时被查询对象保护得很薄，这些查询对象是微软用于 Windows 应用的 MFC 类库的一部分，有时直接编写代码。这使得我们可以并行工作。我们完成了这个应用，把它安装在一些大型食品连锁店里，并通过把公司卖给一个更大的机构，让投资者和创始人都满意了。ORM 呆在它该呆的垃圾桶里。
正如爱丽丝·温的民权老歌所唱的，“把你的眼睛放在奖品上。”一只 ORM 不是。
章节总结
- SQL 允许您以集合(元组/行)的集合(关系/表)的形式非过程地处理数据。
- SQL 的重要部分是连接，主要是内部连接和左/右外部连接。
- 一种有效的高级建模符号是 er 建模，尽管还有其他符号。
- 识别实体是 ER 建模中最重要的部分，其次是关系，再次是属性。一旦建立了实体，关系和属性就由需求决定了。
- 物理上，关系由主键和外键表示。
- 第二和第三范式是必不可少的；第一范式主要是为了避免笨拙的编码和频繁的模式更改。
- 数据库的约束越多越好(unique、foreign-key、not null 和用触发器实现的 check 约束)。
- 将对数据库的访问保持在应用所需的最低限度，只有两个用户具有有限的网络访问权限，或者没有网络访问权限。
- 自己备份数据库，不管你的主机提供商声称在做什么。
- 根据您当时的需求，继续进行数据库设计，直到它是正确的。随着需求的发展，数据库当然也应该发展。
- 不要构建 ORM 层，如果你知道有人不这么认为，可以考虑进行干预。**
五、应用结构
从前有一个来自莱姆的年轻人
谁不能让他的打油诗押韵
当被问及“为什么不呢？”
据说他认为
它们可能太长，结构不良，一点也不好笑。
匿名的
本章和下一章主要关注 PHP 主题。一般来说，这一章涵盖了结构问题:MySQL 和 PHP 之间的接口，HTML 页面和生成它们的 PHP 程序应该如何组织，以及如何维护会话，以便独立的 PHP 程序可以组成一个应用。下一章将讨论更详细的主题。
如您所知，我的目的不是要深入了解 PHP 编程的所有细节，我假设您已经知道其中的大部分内容，或者可以在现有的大量 PHP 书籍中轻松找到。相反，我会尽量把你的时间花在那些很少在任何书中讨论的事情上。
我按照以下顺序介绍了本章中的主题:
- 如何通过 PDO 接口从 PHP 访问 MySQL？
- PHP 如何与表单交互，以及如何将表单域连接到数据库列。
- PHP 会话，允许应用页面组成一个应用。
- 一个可以用来编码标准化页面的框架。
- 如何处理与表单的一对多和多对多关系？
从 PHP 访问 MySQL
作为一等公民，已经有一些尝试将数据库访问合并到编程语言中，但是这不是你在 PHP 中使用 SQL 的方式。该接口具有传统的函数调用。您将 SQL 以字符串的形式传递给数据库驱动程序，如果有结果集返回，您将以 PHP 数组的形式接收它。这是一种完全令人满意的工作方式，而且我从来不觉得这种语言需要被额外的语法搞得乱七八糟。
有时，您会在运行mysql命令的终端会话中、在 MySQL Workbench 查询窗口中或以其他方式直接执行 SQL，但本章专门讨论在 PHP 程序中使用 SQL。
连接 PDO
自从 PHP 和 MySQL 出现以来，它们就一直在一起，并且在过去的几年中，已经引入了几个应用接口(API)。有最初的 API，简称 mysql，改进的 API，称为 mysqli，以及最近的 PDO (PHP 数据对象)。
出于三个原因，PDO 是你想要的(除非在极少数情况下，它不支持一些模糊的 MySQL 特性)。
- 你可以设置一个
PDO::ERRMODE_EXCEPTION选项，让每个错误抛出一个异常，就像我在实例化 PDO 对象之后，在清单 4-10 的中所做的那样。(实例化失败总是会引发异常。)这意味着你不会无意中忽略一个错误，也不必检查每个 PDO 函数调用的返回。 - PDO 为参数化查询提供了方便的支持。mysqli API 对它们的支持就不那么方便了。
- PDO 适用于任何数据库，不仅仅是 MySQL，所以，一旦你学会了，你就万事俱备了。
我已经在第四章中演示了 PDO 的一些用法，但是没有一个显示结果集(一个虚拟表)被返回给 PHP。清单 5-1 ，基于清单 3-1 的测试程序，就是这样一个。事实上，它以二维数组的形式一次性获得整个结果集。第一个整数维是行，第二个整数维给出列值，按列名索引。工作台没有被加工，只是被转储出去，如图图 5-1 所示。
清单 5-1 。以 PHP 数组的形式检索结果集
define('DB_HOST', 'localhost');
define('DB_PORT', '3306');
define('DB_NAME', 'mydb');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '...');
try {
$dsn = 'mysql:host=' . DB_HOST . ';port=' . DB_PORT .
';dbname=' . DB_NAME;
$pdo = new PDO($dsn, DB_USERNAME, DB_PASSWORD);
$pdo->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,
PDO::FETCH_ASSOC);
$pdo->exec('set session sql_mode = traditional');
$pdo->exec('set session innodb_strict_mode = on');
$stmt = $pdo->prepare('select * from department');
$stmt->execute();
$result = $stmt->fetchAll();
echo '<pre>';
print_r($result);
echo '</pre>';
}
catch (PDOException $e) {
die(htmlspecialchars ($e->getMessage()));
}
图 5-1 。清单 5-1 中运行程序的屏幕截图
除了PDO::ERRMODE_EXCEPTION之外，我一直启用的另一个选项是PDO::FETCH_ASSOC，正如你在清单 5-1 中看到的，这样数组中返回的任何结果都只按列名进行索引。否则，默认情况下会有第二组按列号索引的元素，这是多余的。我还将sql_mode设置为traditional，将innodb_strict_mode设置为on，以强制对数据值进行更严格的检查。
我调用htmlspecial用 HTML 实体替换错误消息中的特殊字符(在catch块中)。这是一个方便的函数，我定义为
function htmlspecial($s) {
return htmlspecialchars($s, ENT_QUOTES, 'UTF-8');
}
我没有在每次访问数据库时重复 PDO 设置代码，而是将它放入一个函数中，在需要时可以调用，如清单 5-2 所示。这个函数在一个DbAccess类中，我将在接下来的几节中添加这个类。注意凭证的定义(DB_HOST等)。)都不见了；我马上告诉你他们去了哪里。
清单 5-2 。设置 PDO 的常用getPDO功能
class DbAccess {
function getPDO() {
static $pdo;
if (!isset($pdo)) {
$dsn = 'mysql:host=' . DB_HOST . ';port=' . DB_PORT .
';dbname=' . DB_NAME;
$pdo = new PDO($dsn, DB_USERNAME, DB_PASSWORD);
$pdo->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,
PDO::FETCH_ASSOC);
$pdo->exec('set session sql_mode = traditional');
$pdo->exec('set session innodb_strict_mode = on');
}
return $pdo;
}
}
该函数将 PDO 对象保存在一个静态变量中，以防它在 PHP 程序中被多次调用。在getPDO中根本没有检错。实例化失败(new操作符)总是会导致异常，并且，一旦PDO::ERRMODE_EXCEPTION被设置，所有其他失败的 PDO 调用也会如此。
我通常不希望一次得到整个结果集，这是函数PDO::fetchALL给我的。我几乎总是想一次处理一行，如清单 5-3 所示，它调用了PDO::fetch。注意，它使用了DbAccess类和getPDO方法。它生成 HTML 来格式化结果，而不仅仅是将结果转储出来，如图 5-2 所示。
清单 5-3 。按行检索结果集
try {
$db = new DbAccess();
$pdo = $db->getPDO();
$stmt = $pdo->prepare('select * from department');
$stmt->execute();
echo '<table border=1>';
$first = true;
while ($row = $stmt->fetch()) {
if ($first) {
echo '<tr>';
foreach ($row as $attr => $val)
echo "<th>$attr";
$first = false;
}
echo '<tr>';
foreach ($row as $attr => $val)
echo "<td>$val";
}
echo '</table>';
}
catch (PDOException $e) {
die(htmlspecialchars($e->getMessage()));
}
图 5-2 。检索到的 HTML 格式的结果集
关于清单 5-3 中的 HTML，我已经跳过了周围的 HTML ( <!doctype ...>、<html>、<head>等)。).这是草率的，不是我在产品代码中会做的事情，但是我在测试代码和小例子中一直这样做。我还跳过了像<tr>和<th>这样的元素的结束标记，但这是完全合法的，省去了试图让所有内容正确匹配的麻烦。我也大多省略段落的结尾</p>，因为它们也是可选的。(一些 HTML 编码人员出于风格考虑，更喜欢以段落结尾。)我通常也会省略 HTML 属性值周围的可选引号(例如<table border=1>)，因为 PHP 程序已经被单引号和双引号搞得过于混乱了。
数据库凭证
询问任何 web 程序员数据库凭证应该放在哪里，您会被告知永远不要将它们放在使用它们的数据库访问代码所在的文件中。不过，这一建议的理由并不充分。关于 web 服务器意外地以纯文本的形式提供 PHP 程序，或者在 web 站点上访问源代码的人能够看到用户名和密码，或者如果您发布或以其他方式分发源代码时意外地发布了它们。
只有最后一点对我真正有意义。因为凭证必须放在服务器上的某个地方，任何人都可以访问服务器上的所有文件。Web 服务器不以文本形式提供 PHP 程序，而是将它们传递给 PHP 处理器执行。断开的 web 服务器可能会显示文本，但是，一旦设置正确，web 服务器就不会中断。
也许将数据库凭证放在它们自己的文件中并放在一个标准位置的最好理由是，服务器上所有相关的应用都可以访问它们。这就是我所做的，尽管，正如我所说的，给我增加的安全是最小的。
文件应该有一个“.php”扩展名，以防它被直接访问。使用类似于“.”的扩展名。include”是危险的，因为如果它在服务器的文档树中，它将作为纯文本提供。你通常可以把它放在树之外，但并不总是这样，因为主机服务可能不允许你访问任何其他目录。
无论如何，为了谨慎起见，我将凭证放在一个名为credentials.php的文件中，并在可能的时候将它们放在文档树之外。(更好的名字可能更难猜，比如X56-2345-QR77-J654.php，但这真的太离谱了。)
清单 5-4 显示了我在DbAccess类前面执行的代码，它在几个地方寻找凭证文件。如果它没有找到它们，它会尝试环境，这就是亚马逊的弹性豆茎放它们的地方。如果这不起作用，它将它们定义为伪值，以防我想测试该路径(例如，用 PHPUnit)。
清单 5-4 。正在搜索credentials.php文件
foreach (array(
"/.config/credentials.php",
"{$_SERVER['DOCUMENT_ROOT']}/../.config/credentials.php",
"{$_SERVER['DOCUMENT_ROOT']}/.config/credentials.php",
"../.config/credentials.php",
"../../.config/credentials.php"
) as $f)
if (file_exists($f)) {
require_once $f;
break;
}
if (!defined('DB_HOST')) {
if (isset($_SERVER['RDS_HOSTNAME'])) {
// Amazon Elastic Beanstalk
define('DB_HOST', $_SERVER['RDS_HOSTNAME']);
define('DB_PORT', $_SERVER['RDS_PORT']);
define('DB_NAME', $_SERVER['RDS_DB_NAME']);
define('DB_USERNAME', $_SERVER['RDS_USERNAME']);
define('DB_PASSWORD', $_SERVER['RDS_PASSWORD']);
}
else { // force an error, mostly for PHPUnit
define('DB_HOST', 'no host');
define('DB_PORT', 0);
define('DB_NAME', 'no db');
define('DB_USERNAME', 'no user');
define('DB_PASSWORD', 'no password');
}
}
我把清单 5-4 中的代码和其他公共代码，比如DbAccess的require_once语句，放在一个名为common.php的文件中，我的每个 PHP 应用文件都包含这个文件。对于这本书，所有内容都放入 EPMADD 名称空间，所以我的 PHP 文件如下开始:
namespace EPMADD;
require_once 'lib/common.php';
并不是每个文件都需要common.php中的所有内容，但是我不会费心把它们分开。如果您认为自己的common.php文件已经变得太大，并且开始影响执行时间，那么您可以这样做。
为了完成这个故事，我的credentials.php文件包含了你所期望的内容。
define('DB_HOST', 'localhost');
define('DB_PORT', '3306');
define('DB_NAME', 'mydb');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '...');
每台服务器(开发平台，亚马逊 EC2，A2 托管等。)有自己的credentials.php文件，如果有几个应用的话，有时会有几个。
还有一件事:即使有人得到了我的 MySQL 密码，他们也不能从服务器以外的任何地方访问数据库本身，因为只允许从localhost访问。您可以通过 SSH 连接 MySQL Workbench 或 UNIX shell 来解决这个问题，但是 SSH 比 MySQL 安全得多，所以这不是问题。
使用 PDO 执行 SQL 语句
在清单 4-17 中，当我添加触发器时，我展示了用于执行不返回结果集的 SQL 语句的PDO::exec方法，比如drop trigger。在这一章中，在清单 5-1 和 5-3 中，我展示了另外两个一起使用的方法:PDO::prepare，准备一个 SQL 语句，和PDOStatement::execute，执行它。你可以用PDO::query同时做这两个步骤，我还没有展示。
为了解释为什么做同样的事情有三种方法，我将从两步方法开始，然后再到其他方法。
PDO::prepare将一条 SQL 语句作为参数，尽可能多地处理它，而不实际执行它。这意味着编译它，分析它，想出一个执行它的计划，尽可能的聪明。它可能会对连接进行重新排序以提高处理效率，重新安排部分where子句，使用索引，以及其他任何它足够聪明可以解决的问题。完成这些后，它返回一个名为PDOStatement的 PDO 对象，这就是为什么在清单 5-1 和 5-3 中我将返回值赋给了一个名为$stmt的变量。
如果需要多次执行那个语句，可以只准备一次，然后调用PDOStatement::execute来执行。这样可以节省时间，但是，根据我的经验，这种情况很少发生。不过，这是值得记住的。
使用 prepare + execute 的真正动机是完全不同的:您可以使用参数化查询，这一点非常重要，就像本书中的任何内容一样，它几乎是一条绝对的规则。如果 SQL 语句的任何部分包含运行时提供的值，请始终使用参数化查询。我这样做有两个原因:
- 这使得处理字符串更加容易，因为您不必担心使用
PDO::quote方法引用。事实上，如果你的代码中有PDO::quote，你就没有遵守我的规则。 - 它阻止了 SQL 注入。
SQL 注入是一个聪明的技巧，用户将 SQL 片段放入表单字段，试图改变传递给数据库的 SQL，从而破坏或危及安全性。数据库编程书籍的作者对此大加赞赏，大多数人建议使用PDO:quote或mysqli::real_escape_string，如果你正在使用那个 API 的话。使用这些功能很笨拙，而且很容易忘记这样做，所以 SQL 注入是一个问题。
编造 SQL 注入的例子很有趣。一个常见的是把
'; drop person; --
在名称字段中，比如说。假设提交了 PHP 表单，该字段的值以 PHP 变量$name结束，程序员编写了
$pdo->query("update person set name = '$name'");
当 PHP 代入$name的值时，查询结果是
$pdo->query("update person set name = ''; drop person; --'");
您应该感到害怕，因为这个恶意用户已经知道如何删除person表。这是一个有趣的例子，但它不会起作用，因为PDO::query只处理一条语句，大多数其他 SQL APIs 也是如此。
好吧，那么用下面的方法来检查密码是否有效呢:
$pdo->query("select userid from user where
password = '$password'");
(我暂时不考虑密码应该被散列。)用户可以在密码字段中键入以下内容:
' or 'x' = 'x
变量替换后得出
$pdo->query("select userid from user where
password = '' or 'x' = 'x'");
答对了。条件为真，检索到userid，用户已经闯入。
你可能会说用户不知道包含用户信息的表的名称，不知道程序内部的 SQL 是如何编写的，等等。然而，今天的许多应用都是开源的(例如，用于博客的 WordPress)，所以在许多情况下，用户确实拥有这些信息。做大量的尝试来找出合适的东西也是有效的，甚至有破解程序可以自动做到这一点。
嗯，我可以继续下去，但是你可以用一个简单的规则 100%排除 SQL 注入的所有可能性:永远不要把任何包含数据值的 PHP 变量放在传递给任何采用 SQL 语句的方法的字符串中。(分别是PDO::exec、PDO::prepare、PDO::query)。)
为了帮助您遵守规则，请始终将 SQL 语句放在单引号中。你仍然可以用串联操作符(.)来绊倒自己，但是这需要足够的工作，所以也许你会意识到你违反了规则。
那么，如何动态构建 SQL 语句，使其包含用户输入的值呢？用参数化查询。每当您想要替换一个值时，请在标识符前加一个冒号，不带引号，如下所示:
$stmt = $pdo->prepare('select userid from user where
password = :pw');
注意，我使用了prepare，而不是query，因为只有prepare接受参数。另外，请注意我使用了单引号。
您用PDOStatement::execute执行语句:
$stmt->execute(array('pw’ => $password));
数组中的元素数量必须与参数数量完全相同，并且所有名称必须匹配。
我所做的是剥夺 PHP 进行字符串替换的机会。参数pw的替换由 PDO 接口执行。如果有人输入密码
' or 'x' = 'x
嗯，你知道，这其实是一个很好的密码。这就是它的全部—只是数据，而不是 SQL。
我的建议是，不要问自己一个 SQL 语句是否会涉及字符串替换，因此需要防范 SQL 注入。那只是那种浪费时间、消耗大脑能量、导致错误的额外思考。总是将 SQL 语句放在单引号中，永远不要在 SQL 语句中使用 PHP 变量，并且总是使用PDO::prepare后跟PDOStatement::execute。假以时日，它将成为第二天性。
事实上，我并没有直接调用这些 PDO 方法，因为在我的DbAccess类中有一个方法为我完成了这项工作。我称之为query，尽管它适用于所有 SQL 语句，而不仅仅是查询。它在清单 5-5 中。
清单 5-5 。DbAccess::query方法
function query($sql, $input_parameters = null, &$insert_id = null) {
$pdo = $this->getPDO();
$insert_id = null;
if (is_null($input_parameters))
$stmt = $pdo->query($sql);
else {
$stmt = $pdo->prepare($sql);
$stmt->execute($input_parameters);
}
if (stripos($sql, 'insert ') === 0)
$insert_id = $pdo->lastInsertId();
return $stmt;
}
第一个参数$sql是您提供的 SQL 语句，当然，其中没有任何 PHP 变量。然后是$input_parameters，这是将被传递给PDOStatement::execute的同一个数组。第三个参数是一个自动增量值，我稍后会解释。
该方法每次都为自己调用DbAccess :: getPDO，但是回想一下清单 5-2 中的内容，如果已经建立了数据库连接，那么该函数是即时的。
因为这个方法将由计算机执行，如果没有输入参数，我就调用PDO::query来节省一点时间。(回想一下我的建议，如果您自己直接编写 PDO 调用，就不要进行这种区分。)不然我叫prepare + execute。
如果 SQL 语句是一个insert，如果其中一个列(假设是一个代理键)是自动递增的，那么可能会有一个自动递增值。如果是这样，它将通过函数的第三个参数$insert_id返回，因为调用者可能想知道主键是什么，以防新插入的行需要被其他 SQL 操作引用。(注意，比较使用的是===，而不是==，因为如果没有匹配，它将返回false，当使用==时，它等于零。)
在我的应用代码中，我将DbAccess::query用于 100%的 SQL 语句。我从来不直接叫PDO::exec、PDO::query或PDO::prepare。
这里有一个对DbAccess::query的调用，取自我在“页面框架使用”一节中介绍的一个例子(它做什么现在并不重要)。
$stmt = $this->db->query('delete from member where
member_id = :member_id',
array('member_id' => $_REQUEST['pk']));
有了参数化查询提供的所有保护，我可以直接将表单数据从 PHP 存放表单数据的$_ REQUEST数组发送到DbAccess::query，而不需要使用引号。它干净、高效、绝对安全。
处理数据库插入和更新
假设你有一个类似于图 5-3 中的表单，取自我在“页面框架使用”一节中展示的一个示例应用
图 5-3 。示例表单
要输入新记录，用户单击 new 按钮得到一个空表单。填写完毕后，单击 Save 按钮将其插入数据库。要更新一条记录，有一种方法(图中未显示)可以将其数据输入表单，此时用户更改一些数据并单击 Save。这里的要点是，保存可能是插入一个新记录或更新一个现有记录。
您看不到它，但是，当检索现有记录时，它的主键被放入表单中的隐藏字段。在点击 submit 按钮(这里是 Save 按钮)时 PHP 放置表单数据的$_POST数组中，隐藏字段中主键数据的存在表示应该进行更新，不存在表示插入。
因此，很容易将 PHP 代码写到insert或update，它们是单独的 SQL 语句。如果主键是一个代理键，这在我的应用中很常见，那么在insert之后找出它是什么就很方便了，这是由DbAccess::query提供的，如我在清单 5-5 中所示。例如，如果您想在另一个表中插入一行，而第一个表与该表有关系，需要引用一个外键(您刚刚得到的主键)，那么您可能需要该键。
与其决定你是需要一个insert还是一个update，不如使用一个叫做 upsert 的语句。您提供数据，语句更新现有的行，或者，如果没有行，插入一个新行。MySQL 提供了一个 upsert 特性，在它的insert语句后面附加了一个on duplicate key子句，如下所示，其中person_id是代理主键:
insert into person (person_id, last, first)
values (1234, 'Smith', 'John')
on duplicate key update
last = 'Smith', first = 'John'
因为主键 1234 是已知的，所以这一定是由从现有记录填充的表单产生的。因为已经有一行具有该键，所以它是重复的，并且执行了update子句。
如果表单最初为空，则没有预先存在的主键，因此upsert可能如下所示:
insert into person (person_id, last, first)
values (NULL, 'Smith', 'John')
on duplicate key update
last = 'Smith', first = 'John'
这里没有重复的键，所以执行insert。
看起来 MySQL 的 upsert 做了我们想要的，但是有一个问题。回想一下，在第四章的中，我说过如果你在已经有一个自然候选键的情况下定义了一个代理主键，你应该在候选键上放置一个唯一的约束来防止重复。假设您已经为自然键(last, first)完成了这一步。如果用户试图为 John Smith 插入新记录，而那里已经有一个 John Smith，那么即使您没有提供代理键，也将执行更新。为什么呢？因为该子句显示“无重复键”，而不是“无重复主键”更糟糕的是，如果你试图获取代理键，因为它应该是一个新行，你需要代理键，方法PDO::lastInsertId(出现在清单 5-5 )不会给你，因为没有插入。等等，还有更多:如果重复键是一个意外，你不会从 MySQL 得到一个违反约束的错误，所以你不能警告用户。简而言之，除了主键以外，任何键的存在都可能造成严重破坏。
如果主键是自然的，情况似乎会更好。我不认为(last, first)是一个非常好的自然键，因为相同的名和姓太常见了。尽管如此，如果这是自然键，当键是新的时插入一行并更新一个现有的行是正确的行为，并且upsert语句会自动这样做。
然而，仍然有一个问题:如果有一个名字的改变——约翰·史密斯结婚了，并决定使用他新丈夫的名字——它将被视为一个插入，现在新婚的约翰·多伊在数据库中有两个记录。但这确实是自然键选择不当的问题，而不是 upsert 语句本身的问题。这是我通常更喜欢代理键的一个原因。有了代理键，对任何(自然)列的任何更改都很简单。
所以，我希望我已经说服了您，MySQL upsert 语句充满了特殊情况和奇怪的副作用，除了您有一个简单、自然的键之外，它不值得使用。取而代之的是，当行是新的时候使用普通的insert，当你正在更新的时候使用update。听起来很简单，事实也确实如此，尤其是与我花了六段时间来解释upserts的危害相比。
如你所料，我不会到处都编码inserts和updates的序列。我有一个单一的方法，DbAccess::update，，来为我做这个工作。与DbAccess::query不同，它不使用 SQL 语句，而是使用一个规范来构造和执行适当的语句。这是它的声明。
DbAccess::update($table, $pkfield, $fields, $data,
&$row_count = null)
第一个参数是要更新的表的名称，第二个参数是它的主键列，假设它是一个单列，对我来说总是这样。然后是要插入或更新的列的数组，然后是包含列值的另一个数组。为$data参数提供$_POST、$_GET或$_REQUEST是很常见的，尽管您也可以为调用合成一个数组。最后一个可选参数是受影响行数的计数，因此您可以通过检查其值是否为 1 来验证insert或update是否正常工作。(如果主键与任何记录都不匹配，那么update不会做任何事情，所以它不是会抛出异常的错误。)
假设您有一个俱乐部成员的表单，其中可能包含新成员的数据(开始时为空，隐藏字段中没有存储主键)，或者可能包含现有成员的更新数据。然后，当收到提交的表单时(例如，单击了 Save 按钮)，您将执行这条语句。
$pk = $this->db->update('member', 'member_id',
array('last', 'first', 'street', 'city', 'state',
'specialty_id'), $_POST);
表是member，主键是member_id，要插入或更新六个字段(不包括代理键，member_id)，这些字段的值可能在$_POST数组中。我说“可能”是因为如果member_id不在表单中，它的值就不会出现。该方法判断是否需要一个insert或update，执行它，然后如果它是一个insert并且主键是一个代理，则返回主键的值。
看起来可能是一个奇怪的方法，但是它准确地反映了我是如何处理保存按钮的，并且我一直都在使用它。
清单 5-6 显示了DbAccess::update的代码。
清单 5-6 。DbAccess::update方法
function update($table, $pkfield, $fields, $data,
&$row_count = null) {
$input_parameters = array();
$upd = '';
foreach ($fields as $f) {
if (!isset($data[$f]) || is_null($data[$f]))
$v = 'NULL';
else {
$v = ":$f";
$input_parameters[$f] = $data[$f];
}
$upd .= ", $f=$v";
}
$upd = substr($upd, 2);
if (empty($data[$pkfield]))
$sql = "insert $table set $upd";
else {
$input_parameters[$pkfield] = $data[$pkfield];
$sql = "update $table set $upd
where $pkfield = :$pkfield";
}
$stmt = $this->query($sql, $input_parameters, $insert_id);
$row_count = $stmt->rowCount();
return $insert_id;
}
foreach循环建立了一个参数赋值列表——而不是实际值！—添加到$fields参数中的列。也就是说，如果列last被列出，如果在$data数组中有一个非空值，赋值将是last=:last，否则赋值为last=NULL。它还从$data获取值，并将其放入$input_parameters。这是必需的，因为$input_parameters的元素必须与 SQL 语句中的命名参数完全匹配。对于为$data传递的任何内容来说，都有可能包含额外的元素，尤其是当它是$_REQUEST时，这种情况甚至很常见。这个函数做了更多的工作，所以使用它更容易。
构建列表的方式在前面添加了额外的逗号和空格，因此对substr的调用会删除它们。
现在做出决定:如果在$data数组中没有指定主键，那么语句将是一个insert；否则，一个update。在后一种情况下，主键的元素被添加到$input_parameters，因为该参数在 update 语句的where子句中被引用。
注意，我利用了非标准的 MySQL insert语句，该语句允许赋值列表，就像update一样。这节省了标准形式的insert所需的几行代码，标准形式的【】需要一个列列表和一个带有值列表的values子句。
执行参数化语句的实际工作由DbAccess::query完成。剩下的就是设置行数并返回以插入 ID 的方式提供的任何内容。
您可能已经注意到 SQL 字符串包含 PHP 变量替换，这似乎违反了我的规则。然而，该规则是关于包含数据值的变量的。这些 PHP 变量都没有:它们是表名、列名和参数名，没有一个来自用户。值本身被隔离在$input_parameters数组中。
在我的应用中，DbAccess、query和update这两个主要方法处理了大约 99%的 MySQL 接口。从两个非常小的函数中可以看出很多！
PHP-浏览器交互
现在是时候研究一个运行在服务器上的 PHP 程序如何与运行在客户机上的浏览器交互了。(在开发系统的情况下，服务器是本地的，区分服务器和客户机仍然是有用的。)接下来的大部分内容对你来说都很熟悉，但无论如何，请听听我要说的话，即使是一篇综述，因为这将有助于理解用户如何与 PHP 应用交互。
HTTP 如何工作
基本上，HTTP 是这样工作的:当你在浏览器的 URL 字段中键入一个 URL 时，浏览器通过在一个名为 DNS(域名服务)的目录服务中查找其名称(例如basepath.com)来找到其 IP 地址，然后通过一个名为 TCP/IP 的通信协议连接到你指定的服务器。一旦建立了连接，浏览器和服务器就可以交换消息。最初，服务器在监听——浏览器应该先走。
通常，浏览器会向服务器发送一条GET消息。你可以自己尝试一下，不用浏览器，打开一个到服务器的telnet会话，自己输入GET命令。在一个*nix(类 UNIX)系统上，包括 Mac OS，从你运行的任何终端应用，你使用一个叫做telnet的命令。在 Windows 上，您可能必须从控制面板小程序程序和功能安装它；点击“打开或关闭 Windows 功能”，然后勾选 Telnet 客户端，如图图 5-4 所示。
图 5-4 。在 Windows 上安装 Telnet
这是我放在文件dump.php中basepath.com上的一个 PHP 程序。
<?php
print_r($_REQUEST);
?>
我通过telnet(当然是在客户端)执行它，如清单 5-7 所示；我打的是黑体字。
清单 5-7 。GET通过telnet输入的请求
$ telnet `basepath.com` 80
Trying 75.98.162.194...
Connected to basepath.com .
Escape character is '^]'.
GET /dump.php?p1=mustard & p2=pepper HTTP/1.1
Host: `basepath.com`
HTTP/1.1 200 OK
Date: Fri, 17 May 2013 15:43:09 GMT
Server: Apache
X-Powered-By: PHP/5.3.8
Transfer-Encoding: chunked
Content-Type: text/html
31
Array
(
[p1] => mustard
[p2] => pepper
)
0
Connection closed by foreign host.
$
注意，在输入了主机行之后，我必须按两次回车键。(由于分页符的原因，您看不到这两个空行。)服务器返回一个以HTTP/1.1 200 OK开始的响应，后面是一些被称为头的行。数字31是第一个块中的字符数，因为 Transfer-Encoding 头表示数据将分块存储。只有一大块，然后计数为零表示没有更多。你会认出第一个块是 PHP 程序写的。它只是print_r函数的输出；我没有用 HTML 把它包围起来。
注意，在我输入的GET行中，我提供了两个参数，p1和p2，它们被 PHP 自动放到全局$_REQUEST数组中。实际上，它们在$_GET数组中，但是$_REQUEST同时包含了$_GET和$_POST(我将对此进行解释)。
正如我所展示的，当浏览器发出一个GET时，参数与 URL 在同一行，这是你所熟悉的，因为你已经在浏览器的 URL 字段中多次看到这样的参数。这是将参数放在那里的一个缺点:用户可以看到它们。他们也进入历史和书签。
为了更加隐私，浏览器可以使用POST，而不是GET，并在发送给服务器的消息中输入参数。那么它们在浏览器 URL 字段、历史记录或书签中都不可见。清单 5-8 显示了与之前相同的两个参数，但是输入了一个POST请求。这一次 PHP 将它们放入$_POST数组和$_REQUEST数组。出于某种原因，这次来自服务器的响应没有被分块。不管怎样，这不是你关心的事情，因为处理服务器发送的内容是浏览器的工作。
清单 5-8 。POST通过telnet输入的请求
$ telnet `basepath.com` 80
Trying 75.98.162.194...
Connected to basepath.com .
Escape character is '^]'.
POST /dump.php HTTP/1.1
Host: `basepath.com`
Content-Type: application/x-www-form-urlencoded
Content-Length: 21
p1=mustard& p2=pepper
HTTP/1.1 200 OK
Date: Fri, 17 May 2013 15:40:48 GMT
Server: Apache
X-Powered-By: PHP/5.3.8
Content-Length: 50
Content-Type: text/html
Array
(
[p1] => mustard
[p2] => pepper
)
Connection closed by foreign host.
$
你应该总是让你的表单使用POST，你会发现不在表单中的按钮和锚点(HTML <a ...>元素)使用GET是最简单的。(也有可能让他们用POST。)
PHP 和表单
除了我刚才展示的例子，你不能通过telnet与 web 服务器交互。发生的情况是，PHP 程序，或运行在服务器上的其他东西，可能是一个静态的 HTML 页面，在浏览器显示的页面上放置一些交互元素，然后，当用户单击该元素时，一个GET或POST被发送。它调用 PHP 程序，PHP 程序做一些处理，然后发送一些 HTML 到浏览器。等等，等等。。。这就是应用的工作方式。
最常见的交互元素是表单，毫无疑问您已经使用过了。例如，在清单 5-9 中有一个，显示在屏幕上的图 5-5 中，其中有我输入的一些数据。(没有显示布局表单的一些 CSS。)
清单 5-9 。简单的形式
echo <<<EOT
<form action=dump.php method=post accept-charset=UTF-8>
<label for=p1>p1:</label>
<input type=text size=50 name=p1 id=p1>
<label for=p2>p2:</label>
<input type=text size=50 name=p2 id=p2>
<input type=submit name=button_name value='Click Me'>
</form>
EOT;
图 5-5 。带有输入数据的表单
注意，表单的动作被设置为dump.php，这个程序与telnet请求执行的程序相同，它将您期望的内容写入浏览器。
Array ( [p1] => hotdog [p2] => pickle [button_name] => Click Me )
当我编码时，我经常转储出$_REQUEST数组来查看什么进入了 PHP 程序，然后使用它作为发送了什么参数以及它们的名称的指南。当然，一旦我运行了这个程序，我就会删除转储代码。在本例中，您可以看到单击表单的提交按钮向$_REQUEST数组添加了一个元素button_name，这意味着该按钮被单击了；否则，元素就不会存在。我不在乎它的价值，只在乎它的名字。在我的代码中，每个按钮都有一个不同的名称，我用它来确定点击了什么。另一种方法是使用值，但是，由于值被用户视为按钮的标签，因此它会随着用户界面的调整而被修改，如果应用是本地化的，甚至会被翻译。所以，用这个名字。
通常最好将与一个表单相关的所有交互保存在同一个 PHP 文件中，而不是让表单的动作成为一个完全不同的文件，如清单 5-9 中的。将处理代码与表单保存在同一个文件中增加了内聚力；您最终会得到一堆非常独立的微型应用，每个都在自己的文件中，并且每个都只与数据库通信。正如我在第一章中提到的，数据库中心性允许并行开发，减少模块间的耦合，并且方便调试和测试。(凝聚力好；耦合不好。)
因此，大多数主 PHP 文件(不包括包含的类和其他公共代码)在顶部都有一些代码，以便在单击任何按钮时执行操作，或者在没有参数的情况下调用文件时生成一些输出。通常，即使采取了某个操作，也会生成该输出。例如，如果提交的表单导致新的一行被添加到表中，最好再次显示该表单，以防用户想要进行一些更改。
考虑到这一点，图 5-5 中的表单可以由清单 5-10 中所示的微型应用来处理，它是按照我的建议来组织的:一个处理任何按钮点击的动作部分，后面是一个显示部分。
清单 5-10 。表单显示后的动作处理
if (isset($_REQUEST['button_name'])) {
echo <<<EOT
Button was clicked.
<br>p1: {$_REQUEST['p1']}
<br>p2: {$_REQUEST['p2']}
EOT;
}
echo <<<EOT
<form action="{$_SERVER['PHP_SELF']}" method=post
accept-charset=UTF-8>
<label for=p1>p1:</label>
<input type=text size=50 name=p1 id=p1>
<label for=p2>p2:</label>
<input type=text size=50 name=p2 id=p2>
<input type=submit name=button_name value='Click Me'>
</form>
EOT;
注意，表单动作现在是$_SERVER['PHP_SELF']，它将提交的数据发送回同一个文件。顶部的代码截取一个按钮点击，进行处理——在本例中不太多——并重新显示表单。它是空白的，因为我没有为两个表单字段编写任何value属性来用提交的数据填充它们。图 5-6 显示了输出。
图 5-6 。空白表单后的动作处理输出
到目前为止，这个程序只有一个按钮还可以，但是随着按钮越来越多，文件顶部的if语句开始堆积，很快就会变得一团糟。图 5-7 显示了这样一种形式。
图 5-7 。有三个按钮的表单
处理几个按钮的一个简单方法是采用惯例，动作按钮的名字以action_开头。然后，文件顶部的代码可以简单地遍历$_REQUEST数组，查找以action_开头的名字，并以该名字调用函数。这允许每个按钮的动作被放入它自己的功能中，这比一系列的if语句要干净得多。就几行而已。
foreach ($_REQUEST as $name => $value)
if (strpos($name, 'action_') === 0)
$name();
例如，如果 Click Me 2 按钮的 name 属性是action_button2，那么它的动作代码将进入一个同名的函数中。
function action_button2() {
echo <<<EOT
<p>Button 2 was clicked.
<p>{$_REQUEST['p1']} -- {$_REQUEST['p2']}
EOT;
}
清单 5-11 显示了整个程序。注意，第三个按钮是独立的，不在表单中，所以在一个form元素上没有action属性来指示应该调用什么文件，而前两个按钮有。相反，onclick事件的 JavaScript 改变浏览器的window.location，这导致它请求指定的 URL。正如我所说，在这种情况下，将参数放在那里也是最容易的，所以它们将进入 PHP 程序的$_GET数组，而不是$_POST数组。但是，程序使用了$_REQUEST数组，所以它不关心。
清单 5-11 。一个有三个按钮的窗体程序
foreach ($_REQUEST as $name => $value)
if (strpos($name, 'action_') === 0)
$name();
echo <<<EOT
<form action="{$_SERVER['PHP_SELF']}" method=post
accept-charset=UTF-8>
<label for=p1>p1:</label>
<input type=text size=50 name=p1 id=p1>
<label for=p2>p2:</label>
<input type=text size=50 name=p2 id=p2>
<br>
<input type=submit name=action_button1 value='Click Me 1'>
<input type=submit name=action_button2 value='Click Me 2'>
</form>
<button οnclick='window.location="{$_SERVER['PHP_SELF']}\
?action_button3=1&p3=cake"'>
Click Me 3
</button>
EOT;
function action_button1() {
echo <<<EOT
Button 1 was clicked.
<br>{$_REQUEST['p1']} -- {$_REQUEST['p2']}
EOT;
}
function action_button2() {
echo <<<EOT
Button 2 was clicked.
<br>{$_REQUEST['p1']} -- {$_REQUEST['p2']}
EOT;
}
function action_button3() {
echo <<<EOT
Button 3 was clicked.
<br>
EOT;
print_r($_REQUEST);
}
图 5-8 和 5-9 显示了点击第一个和第三个按钮的输出。
图 5-8 。单击第一个按钮的输出
图 5-9 。单击第三个按钮的输出
如果不明显的话，因为第三个按钮不在表单中，它与表单无关，任何输入表单的东西都不会随之进入 PHP 程序。也就是说，它不是一个提交按钮；它是独立式的。通常，像这样的按钮会把用户带到一些其他的迷你应用，比如从会员页面到捐赠页面。
表单文本字段和按钮只是许多可能的交互元素中的两种。有像复选框和选择列表这样的标准选项，也有像日期选择器这样的自定义选项。我将在第六章中讨论这些，因为我们现在只需要文本字段和按钮。过早地把事情复杂化是没有意义的。
整合表单和数据库
现在是整合我在本章中讲述的两个主题的时候了，数据库访问和表单。我已经重新标记了两个表单字段“last”和“first”，它们将保存一个member表的姓和名，该表的代理主键是member_id。图 5-10 显示了表单。
图 5-10 。带有查找、新建和保存按钮的成员窗体
我将从一个显示表单的函数开始，该函数填充了来自参数数组$data的数据。它在清单 5-12 中。
清单 5-12 。函数来显示成员窗体
function show_form($data) {
$member_id = empty($data['member_id']) ? '' :
$data['member_id'];
$last = empty($data['last']) ? '' :
$data['last'];
$first = empty($data['first']) ? '' :
$data['first'];
echo <<<EOT
<form action='{$_SERVER['PHP_SELF']}' method=post
accept-charset=UTF-8>
<label for=last>Last:</label>
<input type=text size=50 name=last id=last
value='$last'>
<label for=first>First:</label>
<input type=text size=50 name=first id=first
value='$first'>
<input type=hidden name=member_id value='$member_id'
hidden>
<br>
<input type=submit name=action_find value='Find'>
<input type=submit name=action_new value='New'>
<input type=submit name=action_save value='Save'>
</form>
EOT;
}
关于这个函数有两点需要注意。
- 如果数组中不存在用于保存传入数组中的值的变量，则必须将这些变量设置为空字符串，以避免出现关于引用不存在的元素的 PHP 错误消息。
member_id在表单的隐藏字段中传递。
在文件的顶部，我包含了公共代码，它引入了DbAccess。我不希望这个文件在EPMADD名称空间中，因为那会干扰动作函数的动态调用。有一种方法可以解决这个问题，但我不会为此而烦恼，因为很快我将展示一种完全不同的处理表单的方法，这种方法将使问题消失。
调用适当动作函数的代码与我之前展示的类似，只是这次我传入了对DbAccess实例的引用，并取回数据数组，该数组被传入show _form。它在清单 5-13 中。
清单 5-13 。文件顶部，显示调用动作函数和show_form
require_once 'lib/common.php';
$db = new EPMADD\DbAccess();
$data = array();
foreach ($_REQUEST as $name => $value)
if (strpos($name, 'action_') === 0) {
$data = $name($db);
break;
}
show_form($data);
最后，清单 5-14 展示了三个动作函数，因为我在本章前面展示了DbAccess函数，所以没什么要做的。函数action_new特别有意思。
清单 5-14 。动作功能
function action_find($db) {
$last = empty($_REQUEST['last']) ? '' : $_REQUEST['last'];
$stmt = $db->query('select member_id, last, first from
member where last like :pat',
array('pat' => "$last%"));
if ($row = $stmt->fetch()) {
return $row;
}
echo "<p>Not found";
return array();
}
function action_new($db) {
return array();
}
function action_save($db) {
$db->update('member', 'member_id',
array('last', 'first'), $_REQUEST);
echo "saved";
return $_REQUEST;
}
如果你仔细听了我的介绍，你可能会注意到一些严重的缺陷。
- 所有的错误都会抛出异常，但是我不会处理它们。
- Find 按钮不使用名字字段，这对用户来说并不明显。
- 如果找到多行，则只显示第一行。
- 页面的外观可以得到实质性的改善。其中一些可以用 CSS 来完成，但是请注意，我甚至都懒得放入一些必需的 HTML (
DOCTYPE、html、head等等)。).
我不打算修复这些问题，因为整个程序只是一个例子，用来展示处理表单的 PHP 应用的一般结构。我保证在本章结束时，我会展示更好的代码，它们足够健壮，可以在您自己的应用中使用。
在获取和发布之间选择
在下一章，当我详细讨论安全性时，我会说你应该用POST而不是GET向网页传递参数。在表单中使用POST非常简单——您只需在form元素中指定POST——但是对于按钮和页面传输来说就复杂了，PHP 程序员通常使用header函数，参数在 URL 中指定。
我将在下一章中讨论所有这些复杂的问题，但是现在，在这一章中，我将使用GET,因为这可能是你所习惯的，尽管这不是最好的方法。最终都会解决的。
PHP 会话
从概念上讲，会话是用户和 web 服务器调用的一些 PHP 程序之间的一组相关交互。将它们联系起来的是，它们不仅可以通过数据库共享数据(任何可以访问数据库的代码都可以这样做)，还可以更直接地通过会话中的程序共享的一个名为$_SESSION的 PHP 数组共享数据。如果一个程序设置了一个元素$_SESSION，那么作为会话成员的任何其他程序也可以访问该元素。
让会话工作的诀窍是 PHP 将$_SESSION的内容存储在服务器上的一个私有文件中，这个文件有一个很难猜到的名字(例如，88734 ab 92 a 219031 C4 ACD 7 ea 6 ee 0 ff 83)，称为会话 ID 。每个会话都有一个唯一的名称。如果运行 web 浏览器的客户端知道该名称，它就可以访问会话，就像运行在同一服务器上的任何 PHP 程序一样。事实上，由于运行在任何服务器上的程序都可以模仿 web 浏览器，就像我在本章前面对telnet所做的那样，一旦名称被泄露，会话就被破坏了。而且，这个名字就是全部的保护——不需要密码或其他任何东西。
因此，要将会话限制为仅授权的客户端，即启动会话的实际人员，保持会话 ID 的私密性非常重要。当通过将 ID 存储在用户浏览器中的 cookie——一段命名数据——中来创建会话时，这是由 PHP 完成的。与大多数其他 cookie 不同，会话 cookie 保存在内存中，所以当浏览器退出时，cookie 和会话 ID 的记录也消失了。会话 ID 必须从服务器发送到浏览器一次，随着会话的进行，可能从浏览器发送到服务器很多次，因此，为了真正安全，整个会话应该加密，这意味着服务器应该设置为https访问，而不是普通的http。这被称为安全套接字层(SSL)会话。
想要作为会话运行的 PHP 应用应该命名会话，因此每个应用都有一个唯一的名称。这样，运行 Front Range Butterfly Club 应用的用户将与运行脸书的用户处于不同的会话中。您不必试图对会话名称保密，因为如果用户显示 cookies 列表，他或她就会看到它们。事实上，让名称具有描述性是一个好主意，这样用户就能知道这是哪个 cookie。在我的例子中，我使用了 EPMADD 这个名字(来自这本书的标题)。
因为浏览器执行时会有一个 cookie，所以每个应用(会话名)和用户都有一个会话。在数据库术语中，您可以将名称和用户视为复合主键，会话 ID 就是数据。如果你离开你的电脑一会儿，而我坐在它旁边，我可以访问你的 cookie 并得到你的会话 ID。但是，从我自己的电脑，你的饼干是完全无法访问的。即使我可以访问你的文件，那也是真的，因为正如我所说的，会话 cookies 保存在内存中。图 5-11 显示了一个真实的 cookie，由我的 Chrome 浏览器显示。
图 5-11 。Chrome 浏览器显示的 Cookie
很明显，会话 id 必须是秘密的，并且组成应用的 PHP 程序可以访问它。Cookies 和 SSL 连接实现了这一点。通过在每个 URL 中包含会话 ID，也可以在没有 cookies 的情况下运行会话，但这会使它出现在您的 web 浏览器的 URL 字段中，因此这是一个非常糟糕的主意。您可能会不经意地将该链接通过电子邮件发送给某人，或者张贴到脸书，这可能不会造成任何伤害，但这仍然是一个安全漏洞。在我将要展示的代码中，我特别禁止 PHP 将会话 id 存储在 cookies 之外的任何地方。如果用户禁用了 cookies，应用将拒绝运行。
会话数据本身保存在服务器上的一个临时文件中。在我的开发系统上，我在应用中执行了以下语句:
$_SESSION['somedata'] = 'I am some data';
然后，我找到了会话文件并查看了它的内容(cat是 UNIX 的 view-file 命令)。
$ cat /Applications/MAMP/tmp/php/sess_88734ab92a219031c4acd7ea6ee0ff83
userid|s:4:"marc";somedata|s:14:"I am some data";
所以你可以看到会话没有什么神秘的。(我把userid也放在那里了；我很快会解释原因。)
启动 PHP 会话非常简单。两行代码就够了，但四行更好。
ini_set('session.use_only_cookies', TRUE);
ini_set('session.use_trans_sid', FALSE);
session_name(SESSION_NAME);
session_start();
第一行强制只使用 cookies，另外，第二行阻止 PHP 在 URL 中包含会话 ID。然后我设置会话名，之前已经将常量SESSION_NAME定义为EPMADD。最后，session_start在浏览器发送的 cookiess 中寻找一个名为EPMADD的 cookie。如果找到了，它就有了会话 ID，并使用它从会话数据文件中设置$_SESSION，无论应用决定要共享什么。如果没有找到 cookie，就假设要创建一个新的会话，PHP 发送一个头以及 PHP 程序要发送给浏览器的任何其他内容，以创建 cookie。然后在下一次执行这个语句序列时使用它。
我将这四条语句放在一个名为start_session的Page类的方法中。(随着我们的进展，我会给Page类添加更多的东西。)
具有讽刺意味的是，销毁一个会话比创建一个会话要麻烦得多，例如，当用户注销时，您可能会想这么做。您必须做三件事:破坏$_SESSION数组，告诉浏览器处理 cookie，删除服务器上的临时数据文件。那是在方法destroy_session里。
private function destroy_session() {
$_SESSION = array();
if (ini_get("session.use_cookies")) {
$params = session_get_cookie_params();
setcookie(session_name(), '', time() - 42000,
$params["path"], $params["domain"],
$params["secure"], $params["httponly"]);
}
session_destroy();
}
要删除 cookie，它将在大约 11 小时前过期。你用什么时间并不重要，但是，众所周知，最好的数字是 42。(详见著《银河系漫游指南》(潘出版社，1979)；实际上，我从官方 PHP 文档中复制了这段代码，这些文档是由大概知道如何破坏会话的人编写的。)
我的应用很少在$_SESSION中存储太多。如果我想跟踪用户的面包屑(他或她的导航路径)，或者最近的搜索，或者类似的事情，我会使用它。不过，我确实在那里存储了一个非常重要的东西:用户登录时的userid。我想把它显示在每一页的底部(或者顶部——由你决定),但是，更重要的是，我想看看它是否在那里。如果是，则有一个$_SESSION数组，这意味着有一个会话，如果有userid，则用户成功登录。这样的用户有权运行应用，无论我选择给他或她什么特权。
我在Page中有一个方法告诉我用户是否登录。
protected function is_logged_in() {
return !empty($_SESSION['userid']);
}
有些页面，如登录页面本身，不在会话中运行。Page类变量$want_session指示页面是否在会话中运行。因此，每个页面都在任何应用代码之前执行这个序列。
if ($this->want_session && !$this->is_logged_in()) {
$this->message("Not logged in.");
echo '<div class=div-process></div>'; // to get spacing
$this->bottom();
exit();
}
方法bottom只是输出应用希望在页面底部显示的任何内容(比如用户标识)。
我提到过登录页面本身不在会话中运行。它验证用户的用户标识和密码，如果它们都是正确的，就启动会话。否则，它会报告一个无效登录，并给用户另一次机会，仍然没有会话。任何包含营销信息之类的页面也不必在会话中运行。但是应用本身可以。
只是为了完成Page中与相关的方法，这里有一个登录用户
protected function login($login) {
$this->start_session();
$_SESSION['userid'] = $login;
}
另一个用于注销用户
protected function logout() {
$this->start_session();
$this->destroy_session();
}
关于页面标题还有一件事，其中一个是在需要创建 cookie 时设置的:它们是标题，所以它们必须在浏览器的任何其他输出之前。如果你试图设置一个标题为时已晚，你会得到你一生中可能见过几次的信息。
Warning: Cannot modify header information - headers already sent
这只是一个警告，但很严重，因为标头没有发送。因此，您必须在页面上的任何其他代码之前执行Page::start_session。看一下Page::login，你会发现它也必须在任何输出之前被调用，因为它调用了Page::start_session。Page::logout同上。正如我将要展示的，这些限制将影响Page产生输出的方式。
页面框架
所有应用的 HTML 页面都应该有一致的外观，并以统一的方式进行处理，以确保所有需要的处理都正确执行，例如确保用户正确登录。为此，我总是使用一个通用的页面结构，并从同一个页面模板驱动我的处理。我将在这里展示一个简单的方法，尽管它足够完整，我已经在实际应用中使用过了，您也可以使用它。
页面结构
我的每一个应用页面都由相同的五个分部组成，如图图 5-12 所示。
图 5-12 。一页的分割
两个外部部分div-top和div-bottom，包含出现在每个页面上的标准项目，比如徽标、顶级菜单、版权声明、用户标识等等。有一个消息区，div-message，只用于 PHP 处理页面时生成的消息。我把这些放在顶部，这样用户可以很容易地发现它们。接下来是我称之为请求部分的部分，div-request，它用于页面最初显示的任何内容。通常它是一个搜索表单，用于请求用户想要处理的数据。最后，当页面后面的 PHP 执行一个请求时，它在div-process部分显示输出，通常是另一种形式。如何使用最后两个部分因页面而异，有些页面只使用其中一个。
由于划分的位置和外观是由 CSS 控制的，所以您可以更改它们的外观，而无需篡改应用代码本身。特别是，您可以对台式机和笔记本电脑使用一种布局，而对通常屏幕小得多的移动设备使用另一种布局。
图 5-13 显示了(虚构的)Front Range Butterfly Club 的会员页面。logo、俱乐部名称、菜单栏在div-top，底部的通知和链接在div-bottom。没有消息，所以div-message是隐藏的。查找会员记录的表单和查找、新建按钮在div-request中，div-process为空。
图 5-13 。初始成员页面
单击 Find 按钮会导致应用检索姓氏以“s”开头的成员的所有记录，并在div-process部门显示这些记录的摘要，如图图 5-14 所示。在那里，单击一个细节链接在一个表单中显示该成员的数据，同样在div-process区域——以此类推，直到成员微型应用运行。在某个时候，用户可能会从菜单中选择其他事情来做，然后这个微型应用就会以相同的页面结构运行。
图 5-14 。找到成员
这种僵硬的页面结构听起来可能有局限性，但是由于div-request和div-process部分的内容、位置和外观完全取决于每个应用，所以它实际上对几乎任何事情都足够通用。
页面框架用法
一个名为Page的类负责处理每个页面的标准处理，实现了统一的页面结构。子类是被实例化的东西，对于每个微型应用都是不同的，并且在页面处理过程中会调用该子类的方法。这些方法中的大部分相当于我在清单 5-11 中展示的动作函数。
文件member.php是成员微型应用，其结构如清单 5-15 所示。当在没有 URL 参数的情况下执行时，处理从request方法开始。然后，像以前一样，任何按钮单击都会导致对其中一个操作方法的调用。检查$_REQUEST数组以确定调用什么动作函数的代码在基类Page中，为页面输出整个 HTML 的代码也在基类中，包括五个标准分部(div-top等)。).
清单 5-15 。典型页面的结构
class MyPage extends Page {
protected function request() {
// ...
}
protected function action_find() {
// ...
}
protected function action_new() {
// ...
}
protected function action_detail() {
// ...
}
protected function action_delete() {
// ...
}
protected function action_save() {
// ...
}
}
$page = new MyPage('Member');
$page->go();
我将展示每个方法内部的代码，但是如果你已经学习过清单 5-14 的话，我所做的将会很熟悉。
首先是清单 5-16 中的request方法，它输出查找表单。请注意，这两个按钮的名称以action_ (in bold)开头，这就是当您单击它们时基类如何知道要调用什么方法。
清单 5-16 。request方法
protected function request() {
echo <<<EOT
<form action="{$_SERVER['PHP_SELF']}"
method=post accept-charset=UTF-8>
<label for=last>Last Name:</label>
<input type=text size=50 name=last id=last
placeholder='Last Name'>
<input class=button type=submit name= action_find value='Find'>
<br>
<input class=button type=submit name= action_new value='New'>
</form>
EOT;
}
点击 Find 按钮会导致您再次执行member.php，但是这次您调用的是action_find方法，如清单 5-17 所示。
清单 5-17 。action_find方法
protected function action_find() {
$url = $_SERVER['PHP_SELF'];
$stmt = $this->db->query('select member_id, last, first
from member where last like :pat',
array('pat' => "{$_POST['last']}%"));
if ($stmt->rowCount() == 0)
$this->message('No records found', true);
else {
echo '<p>';
while ($row = $stmt->fetch()) {
$name = "{$row['last']}, {$row['first']}";
echo <<<EOT
<p class=find-choice>
<a href=$url?action_detail&pk=$pk>Detail</a>
<a href=$url?action_delete&pk=$pk>Delete</a>
$name
EOT;
}
}
}
基类Page执行action_find，以及所有其他动作函数，因此它们的输出在div_process部分。在action_find中，我之前唯一没有展示的是基类方法Page::message，它在div_message部分显示了一条消息。在action_find执行之前，该部门的 HTML 已经输出，因为div-message在页面的顶部。因此，该方法不是输出直接的 HTML 来显示消息，而是输出 JavaScript 来修改页面的内容。这个 JavaScript 一到达浏览器就被执行，但是最好延迟实际的内容修改，直到浏览器处理完页面的所有 HTML。方法是使用 jQuery ready函数。稍后我会展示Page::message，但它所做的相当于向浏览器输出以下内容:
<script>
$(document).ready(function () {
$('#div-message').css('padding', '10px');
$('#message-error').html("No records found");
});
</script>
ready的参数是一个匿名函数，当执行时，它向div_message添加填充，然后将消息本身放入该部分的一个段落中。该函数已排队；它在浏览器处理完页面的所有 HTML 后执行。您可以直接输出消息，可能如下所示:
<p style='color:red;'>No records found
但是它会出现在页面的某个地方，这取决于正在编写的其他 HTML，用户可能会错过它。如果所有消息都出现在同一个地方会更好，这正是 JavaScript 方法所实现的。如我所说，我稍后将回到消息。
看到找到的成员列表，如图 5-14 中的所示，用户可以点击其中一个细节链接，这将执行action_detail方法，如清单 5-18 中的所示。
清单 5-18 。action_detail方法
protected function action_detail($pk = null) {
if (is_null($pk))
$pk = $_REQUEST['pk'];
$stmt = $this->db->query('select * from member
where member_id = :member_id',
array('member_id' => $pk));
if ($stmt->rowCount() == 0)
$this->message('Failed to retrieve record.');
$row = $stmt->fetch();
$this->show_form($row);
}
参数$pk用于函数被直接调用的情况，而不是被Page框架调用；稍后我会展示一个例子。如果省略参数，主键来自$_REQUEST数组；回想一下清单 5-17 ，在action_find中的细节链接将它作为 URL 参数提供。
清单 5-19 的中的函数show_form显示了一个相当实用的表单，使用列名作为表单标签，这通常不是你想要的，但是现在可以了。
清单 5-19 。show_form功能
protected function show_form($row) {
echo "<form action='{$_SERVER['PHP_SELF']}'
method=post accept-charset=UTF-8>";
foreach (array('member_id', 'last', 'first', 'street',
'city', 'state') as $col) {
if ($col == 'member_id')
$type = 'hidden';
else {
echo "<label for$col>$col:</label>";
$type = 'text';
}
$v = is_null($row) ? '' : $row[$col];
echo "<input type=$type id=$col size=50 name=$col
value='" . htmlspecial($v) . "'>";
}
echo "<br><input class=button type=submit
name=action_save value=Save></form>";
}
关于show_form，有三点需要注意。
- 主键必须在表单中，所以当单击保存按钮时,
action_save方法将拥有它，但它是一个隐藏字段。 - 如果
$row参数为空，则字段的值为空字符串。正如我们将看到的，这就是action_new用来输出一个空表单的东西。 - 我调用了函数
htmlspecial(本章前面已经介绍过了)来处理写入页面的任何值。
将 HTML 特殊字符替换为它们的实体(例如，<的<)是很重要的，以防有任何字符可能被解释为 HTML。这不仅是为了外观，也是为了防止有人试图通过将 HTML(尤其是 JavaScript)放入数据字段来劫持页面，这将导致页面做一些与设计目的不同的事情。这种利用的名称是跨站脚本(XSS)，我将在第六章的中详细讨论。
图 5-15 显示了在图 5-14 中点击妮娜·斯坦顿的细节按钮后，这个笨拙但可行的表单。
图 5-15 。通过 show_form 方法显示的表单
如果新按钮被点击，它的action_new方法利用了show_form输出空白表单的能力。在这种情况下，member_id隐藏字段将是空的，这就是为什么由保存按钮调用的action_save知道这是一个要插入的新记录。清单 5-20 展示了这些方法。
清单 5-20 。action_new和action_save方法
protected function action_new() {
$this->show_form(null);
}
protected function action_save() {
try {
$pk = $this->db->update('member', 'member_id',
array('member_id', 'last', 'first', 'street',
'city', 'state'), $_POST);
}
catch (\Exception $e) {
$this->show_form($_POST);
throw $e;
}
$this->action_detail($pk);
$this->message('Saved OK', true);
}
DbAccess::update方法自动处理插入和更新，所以这不是问题。我展示的所有其他代码都让异常向上下降，由Page基类处理，但是这里异常被捕获，这样表单可以在出现错误时再次显示，给用户一个修复问题的机会。(如第四章所述，该错误可能是由约束失败引起的。)
如果DbAccess::update起作用(没有抛出异常)，如果你插入一条新记录，$pk保存主键，如果你再次查看清单 5-18 ，你会看到action_detail检索那一行。如果执行更新，$pk为空，因此action_detail从$_REQUEST获取主键，也就是调用action_save时的主键。
换句话说，出错时显示相同的表单，成功时再次检索记录。还有其他的编码方法，但是这是最简单的方法，可以确保在插入新记录后主键在表单中，因为如果用户修改了刚刚插入的内容，那就需要更新，而不是再次插入。代码不算多，但是编排的很仔细。
顺便说一下，Page:: message的第二个参数表明这是一个成功的消息，而不是一个错误。稍后，当我展示Page::message的代码时，您会看到它做了什么。
我还没有谈到的唯一方法是action_delete，当你点击删除链接时调用，如图图 5-14 所示。它在清单 5-21 中。
清单 5-21 。action_delete方法
protected function action_delete() {
$stmt = $this->db->query('delete from member where
member_id = :member_id',
array('member_id' => $_REQUEST['pk']));
if ($stmt->rowCount() == 1)
$this->message('Deleted OK', true);
else
$this->message('Nothing deleted');
}
如果单击删除链接，该行会立即被删除，但最好要求用户确认。您可以通过让action_delete显示一个确认表单，然后让该表单上的一个按钮调用另一个执行实际删除的动作方法来做到这一点。但是我更喜欢用 JavaScript 请求确认，这可以通过将清单 5-17 中的删除链接改为如下:
<a href='' onclick="DeleteConfirm('$name', '$pk');">Delete</a>
JavaScript 函数DeleteConfirm是
function DeleteConfirm(name, pk) {
if (confirm('Delete ' + name + '?')) {
event.preventDefault();
window.location = document.URL +
'?action_delete=1&pk=' + pk;
}
}
图 5-16 显示了一个例子。如果用户同意，浏览器的 URL 被设置为类似
member.php?action_delete=1&pk=117
图 5-16 。删除确认
(实际上，document.URL返回了一个绝对 URL，但是我只显示了文件部分。)注意对preventDefault()的调用，以防止锚到它的href位置，这是不相关的。
由于DeleteConfirm独立于任何应用，它和其他普通的 JavaScript 一起位于一个名为page.js的文件中，这个文件是Page框架所包含的，我将很快展示。稍后会有其他 JavaScript 添加到该文件中。
页面框架文件
由于Page类有很多方法，当您阅读下一节时，您会发现按照本书的可下载代码(www.apress.com)进行学习是最容易的。如果手头没有代码，这里有一个Page类引用的文件树，以及本章中引用的三个应用页面(首先列出)。
login.php
member.php
specialty.php
Directory incl:
bottom.php
logo.png
page.css
page.js
top.php
Directory menu_assets:
...
Directory lib:
DbAccess.php
Page.php
Directory jquery:
...
在本章和本书的代码中，我没有在 PHP 文件的顶部显示一些设置包含路径的语句，但是你会在可下载的代码(www.apress.com)中看到它们。
页面框架实现
既然我已经展示了会话是如何工作的，以及如何使用Page框架来实现一个简单的应用，我将展示它的实现，这要简单得多。大部分代码在Page::go方法中，大部分我已经以某种形式展示过了。
首先，清单 5-22 显示了构造函数，它只是存储它的参数以备后用。第一个是页面的标题，第二个指示这个页面是否应该在会话中运行，第三个是包含文件的目录，比如div-top和div-bottom部分的内容。
清单 5-22 。Page构造器
class Page {
protected $title, $want_session, $db, $incl_dir;
function __construct($title, $want_session = true,
$incl_dir = 'incl') {
$this->title = $title;
$this->want_session = $want_session;
$this->db = new DbAccess();
$this->incl_dir = $incl_dir;
}
// ...
}
我已经把调用一个动作方法的代码(如清单 5-11 顶部所示)放入一个单独的方法中，在清单 5-23 的中，因为它在任何 HTML 被发送到浏览器之前都在顶部被调用，并且在div-process部分内部也被调用。正常的动作以action_为前缀，就像我们一直做的那样；在任何其他输出开始之前执行的那些以pre_action_开始。返回值指示是否调用了操作方法。
清单 5-23 。perform_action方法
private function perform_action($want_pre = false) {
if ($want_pre)
$pfx = 'pre_action_';
else
$pfx = 'action_';
foreach ($_REQUEST as $k => $v)
if (strpos($k, $pfx) === 0) {
$this->$k();
return true;
}
return false;
}
现在，正如我所说的，Page::go做了大部分的工作。它在清单 5-24 中，我将一部分一部分地介绍它。
清单 5-24 。Page::go方法
public function go() {
if ($this->want_session)
$this->start_session();
try {
if ($this->perform_action(true)) // actions before output
return;
$this->top();
}
catch (\Exception $e) {
$this->top();
echo '<p class=message-error>' .
$e->getMessage() . '</p>';
$this->bottom();
return;
}
echo <<<EOT
<div class=div-message id=div-message>
<p class=message-error id=message-error></p>
<p class=message-ok id=message-ok></p>
</div>
EOT;
if ($this->want_session && !$this->is_logged_in()) {
$this->message("Not logged in.");
echo '<div class=div-process></div>'; // to get spacing
$this->bottom();
exit();
}
try {
echo '<div id=div-request class=div-request>';
$this->request();
echo '</div>';
echo '<div class=div-process>';
$this->perform_action();
echo '</div>';
}
catch (\Exception $e) {
$this->message($e->getMessage());
}
$this->bottom();
}
正如我已经解释过的，首先，你要加入一个现有的会话或者开始一个新的会话。然后，在除了头之外的任何输出之前，执行任何pre_action_方法。大多数页面没有，但是，特别是登录页面有，当我到达它的时候我会展示。任何错误都会被捕获，但是由于您还没有编写div-message，它们会被直接输出到页面。这是可以的，因为这里的任何错误都来自内部处理，而不是因为用户做错了什么。
接下来，调用Page::top方法来输出div-top部分中应用开发人员想要的任何内容。通常情况下，Page::top只是
protected function top() {
require_once "{$this->incl_dir}/top.php";
}
但是Page的子类可以覆盖它。
接下来是div-message部分，有两段，一段用于错误，一段用于成功消息。拥有两个段落可以让它们在 CSS 中有不同的风格。最初，两个段落都是空的，填充和边距都为零，所以整个部分根本不占用空间。正如我所展示的，如果显示一条消息，这种情况将会改变。
接下来是我在“会话转换和登录页面”一节中展示的会话检查如果页面应该在一个会话中运行，但是没有运行，那么将会有一条消息排队等待，并且页面以对Page::bottom的调用结束，该调用将写入div-bottom部分的内容:
protected function bottom() {
require_once "{$this->incl_dir}/bottom.php";
}
和Page::top一样，子类可以覆盖它。
现在，所有这些预备工作都完成了，是时候编写应用代码了，这很简单。子类为Page::request定义的东西在div-request部分中被调用；基类中有一个存根，以防应用没有定义它。如果有一个动作函数被调用，它在div-process部门被调用。所有这些都在一个try块中，捕获代码显示错误消息。最后，Page::bottom被称为。
剩下要展示的是清单 5-25 中的Page::message，它的重要部分我已经展示过了。
清单 5-25 。Page::message方法
protected function message($s, $ok = false) {
if ($ok)
$id = 'message-ok';
else
$id = 'message-error';
$s = str_replace('"', "'", $s);
$s = str_replace("\r", '', $s);
$s = str_replace("\n", ' ', $s);
$s = htmlspecial($s);
echo <<<EOT
<script>
$(document).ready(function () {
$('#div-message').css('padding', '10px');
$('#$id').html("$s");
});
</script>
EOT;
}
传入的消息文本必须被替换成 JavaScript，所以我用单引号替换双引号，并去掉回车和换行符。(我本来可以使用转义双引号，但是我用了最简单的方法。)
这就是整个Page框架。我没有显示的是包含样本 HTML 的包含文件，主要是top.php，如清单 5-26 中的所示。(回想一下，它在incl目录中。)
清单 5-26 。页面顶部的 HTML
echo <<<EOT
<!doctype html>
<html lang=en>
<head>
<meta charset=utf-8>
<title>{$this->title}</title>
<link rel=stylesheet type=text/css
href="lib/jquery/css/dark-hive/jquery-ui-1.10.3.custom.min.css">
<link rel=stylesheet type=text/css
href="incl/menu_assets/styles.css">
<link rel=stylesheet type=text/css href="incl/page.css" />
<script src="lib/jquery/js/jquery-1.9.1.js"></script>
<script src="lib/jquery/js/jquery-ui-1.10.3.custom.min.js"></script>
<script src="incl/page.js"></script>
</head>
<body>
<div class=page>
<div class=div-top>
<table border=0 width=100%><tr>
<td class=logo><img src=incl/logo.png>
<td class=company>Front Range Butterfly Club
</table>
</div>
EOT;
注意title HTML 中的变量替换{$this->title}。因为这个文件是 PHP 代码，而不仅仅是 HTML，它可以做类似的事情。
有两个 CSS 文件:一个用于 jQuery，它是从jqueryui.com/themeroller下载的，在这里你可以从常用主题中选择或者创建自己的主题；另一个包含应用的 CSS，用于所有的页面元素(message-error、div_request等)。).
有三个 JavaScript 文件:两个用于 jQuery，一个用于应用，其中包含类似于DeleteConfirm go的函数，我已经展示过了。以后再补充。我喜欢下载 jQuery 文件并把它们放在服务器上，这就是我在这里所做的。这对开发平台来说很重要，因为您希望能够在没有互联网的情况下进行测试，但是我也是为了生产而这样做的，所以我可以控制正在执行的 JavaScript。如果需要，您也可以引用外部 URL。
<script
src=" //ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js ">
</script>
我听说使用 Google 的 URL 可以减少延迟、增加并行性和更好的缓存，所以你可能想这样做。
在正文中，整个页面处于一个page分割中；从这里开始，到bottom.php结束。接下来是div-top部分，包括网站的标志和名称。
清单 5-27 中的文件bottom.php更简单。
清单 5-27 。页面底部的 HTML
echo <<<EOT
<div class=div-bottom>
<p class=bottom>
All information in this database is private and is not to be disclosed.
EOT;
if ($this->is_logged_in())
echo <<<EOT
Logged in as {$_SESSION['userid']}
<a href='login.php?pre_action_logout=1'>Logout</a>
EOT;
else
echo <<<EOT
(Not logged in)
<a href='login.php'>Login</a>
EOT;
echo <<<EOT
</div>
</div>
</body>
</html>
EOT;
这里的大部分内容显示用户是否登录，如果登录，则显示用户 id。还有注销或登录的链接。我还没有展示login.php，但是你可以想象它可能会做什么。注意多出来的</div>；它结束了始于top.php的page分裂。
这几乎是所有的框架代码。我不打算展示 CSS，因为它太详细了，无法简洁地展示，此外，它超出了本书的范围。如果您想看的话，可以下载示例代码。
一大块 HTML 和一个关联的 CSS 文件处理菜单。我没有编码——我是从cssmenumaker.com下载的。如果你在那里制作一个菜单，把 HTML 代码放在top.php文件中，并在驱动菜单的 CSS 中添加一个link。(这类菜单完全由 CSS 运行，而不是 JavaScript。)
总结一下我带你去的地方:我们有一个Page框架，它将用于所有页面。它处理许多常见的处理，并确保像检查会话这样的关键事情得以完成。我解释了会话是如何工作的，尽管还没有解释如何处理登录。我展示了 Front Range Butterfly Club 的会员页面，并且我将展示它的一些增强功能。然而，我将继续使用Page框架。因为它实际上只是调用它的子类，所有的 HTML、CSS 和 JavaScript 都在单独的文件中，所以这个框架非常通用。它处理会话和页面结构的方式永远不会改变。
会话转换和登录页面
登录页面与普通页面member.php有些不同，因为它代表授权用户启动一个会话。它的对应物是注销页面，它的功能正好相反，这两个页面可以合并到同一个文件中，在我给出的例子中我称之为login.php。
login.php中的子类是MyPage，就像在member.php中一样，执行如下开始(你可能想回头看看清单 5-15 ):
$page = new MyPage('Login', false);
$page->go();
构造函数的第二个参数是false，表示不需要会话，因为这是登录页面的开始。
Page::go然后调用我的Page::request，如图清单 5-28 所示。我稍后将解释底部处理msg参数的部分。
清单 5-28 。MyPage::request方法
protected function request() {
echo <<<EOT
<form action="{$_SERVER['PHP_SELF']}" method=post
accept-charset=UTF-8>
<label for=userid>User ID:</label>
<input type=text size=50 name=userid id=userid
placeholder='User ID'>
<label for=password>Password:</label>
<input type=password size=50 name=password id=password
placeholder='Password'>
<br>
<input class=button type=submit name=pre_action_login value='Login'>
<input class=button type=submit name=action_forgot value='Forgot'>
</form>
EOT;
if (isset($_REQUEST['msg']))
$this->message($_REQUEST['msg']);
}
图 5-17 显示了表单。这是一个非常小的页面。通常一个网站的首页会有更多的内容，比如新闻、营销信息、如何登录的细节等等。您可以在您的request方法中输出任何您想要的 HTML。
图 5-17 。登录表单
有两个动作:登录和忘记，后者现在只是一个存根。
protected function action_forgot() {
$this->message('Not yet implemented');
}
第六章相当详细地讨论了忘记密码的问题。
如果你再次查看清单 5-28 ，你会看到登录按钮的动作方法是pre_action_login，而不是action_login，所以它是由Page::go在任何输出被写入之前执行的(清单 5-23 和 5-24)。这一点很重要，因为如果一切顺利，它会想要启动一个会话，这只能在任何输出之前完成，因为 PHP 必须编写一个头来创建 cookie。MyPage::pre_action_login在清单 5-29 中。
清单 5-29 。MyPage::pre_action_login方法
protected function pre_action_login() {
// Somewhat naive!
if ($_POST['password'] == 'cupcake') {
$this->login($_POST['userid']);
header('Location: member.php');
}
else
header('Location: login.php?msg=Wrong%20password');
}
我将在第六章中正确处理密码，所以你在这里看到的只是临时代码。(我保证！)然而，如果密码检查是真实的，那么会发生什么:一个userid元素被添加到会话中，这是所有会话中页面检查的内容。(检查在清单 5-24 的中的Page::go内)。)然后浏览器被重定向到member.php页面，这样用户就可以开始工作了。
如果登录失败，可以立即写入页面，这样会出现一条错误消息，但是这使得Page::go中的处理过于复杂。将浏览器直接重定向回登录页面更容易，只是这一次会显示一条消息，这就是清单 5-28 底部的代码所做的。(通常你不会说密码错了，而是说一些不太具体的话，这样就不会通过暗示用户 id 是有效的来给猜测者提供不必要的帮助。)
从我所展示的内容中得到的关键是，因为如此多的处理都涉及到编写头部，无论是启动会话还是重定向页面，处理都必须在Page::go或它调用的任何方法编写任何 HTML 之前发生。
注销，如我在清单 5-27 中所示，页面底部有 HTML，login.php用一个pre_action_logout参数执行，得到那个被调用的方法。在清单 5-30 里。
清单 5-30 。MyPage::pre_action_logout 方法
protected function pre_action_logout() {
$this->logout();
header('Location: login.php');
}
与MyPage::pre_action_login在出现错误时重定向浏览器的原因相同，一旦注销完成，浏览器将被重定向回登录页面。我在前面的“PHP 会话”一节中展示了Page::logout它启动会话，因为login.php总是从没有会话开始。然后它调用Page:: destroy_session。另一种方法是将日志放在它自己的文件中，logout.php，比如说，在一个会话中运行，但是我不认为仅仅为了保存这个小方法而创建一个单独的文件有什么意义。
处理人际关系
如果你有一个 cruddy 关系，我帮不上忙，但是我可以帮助编写 CRUD 页面来处理实体之间的关系。
最棘手的情况是当有一个外键时，就像一对多关系中的“多”方一样。例如，假设成员表单被增强为包括每个成员的专业，可以是刷脚、薄纱翼、金属标记、硫磺、燕尾或白色。(根据维基百科；我对蝴蝶的了解甚至比我对人际关系的了解还要少。)许多成员可以有相同的专长，但是一个成员只能有一个，所以这是一对多，在member表中有specialty表的外键——“多”方。
外键列只是一个列，尽管有一个约束，所以我们可以把它放在表单上，让用户在其中键入一个数字，比如 4738 表示燕尾，如果这就是代理键的话。显然这是一个糟糕的设计。最好是一个下拉列表，这对于蝴蝶专业来说可能是可以的，但是对于很长的列表来说就不行了。
如果可以使用下拉列表，那么可以在生成表单时动态填充它，方法是在表上执行select操作，为每一行获取一个描述性字符串及其相关的主键。字符串进入下拉框，主键存储在某个地方，可能在隐藏字段中。然后，当用户做出选择并提交表单时，可以将外键插入到引用列中。
如果外键引用一个更长的表，用户可能必须搜索该表以确定应该引用哪一行。因为这比下拉列表更复杂，所以我将详细展示它。然后我将展示如何处理多对多的关系。
带有外键的表单
在涉及代理键的一对多关系的“多”方的表单中，用户应该看到行的一些表示，比如名称，但是实际的外键应该是隐藏的。更新数据库时，只涉及外键列。举个例子会让事情变得更清楚。
图 5-18 显示了蝴蝶俱乐部会员形式，增加了一个新的领域，会员的专长(刷脚、游丝翼等)。)，以及两个按钮:选择和清除。在清单 5-31 中增强的show_form方法显示了那个字段，以及一个外键specialty_id的隐藏字段。
图 5-18 。增强的成员表单
清单 5-31 。增强的show_form方法
protected function show_form($row) {
echo "<form action='{$_SERVER['PHP_SELF']}'
method=post accept-charset=UTF-8>";
foreach (array('member_id', 'last', 'first', 'street',
'city', 'state', 'specialty_id', 'name') as $col) {
if ($col == 'name')
$readonly = 'readonly';
else
$readonly = '';
$id = $col == 'name' ? 'specialty_id_label' : $col;
if ($col == 'member_id' || $col == 'specialty_id')
$type = 'hidden';
else {
echo "<label for=$id>$col:</label>";
$type = 'text';
}
$v = is_null($row) ? '' : $row[$col];
echo "<input type=$type id=$id size=50 name=$col
value='" . htmlspecial($v) .
"' $readonly>";
if ($col == 'name') {
echo "<button class=button type=button
οnclick='ChooseSpecialty(\"specialty_id\");'>
Choose...</button>";
echo "<button class=button type=button
οnclick='ClearField(\"specialty_id\");'>
Clear</button>";
}
}
echo "<p class=label><input class=button type=submit
name=action_save value=Save></form>";
}
这些按钮是因为用户看到的字段name是只读的。(它应该有一个更好的标签，但这是另一个问题，我将在第六章中讨论。)选择按钮执行 JavaScript 函数ChooseSpecialty，清除按钮执行ClearField。两者都传递了外键字段的 id，即specialty_id(与列名相同)。此外，包含与外键字段配对的可见字段的字段具有相同的 id，但带有后缀_label。
适用于任何形式，非常简单:
function ClearField(id) {
$('#' + id).val('');
$('#' + id + '_label').val('');
}
像会员一样，专业也有自己的页面，叫做specialty.php，你可以从窗口顶部菜单上的专业按钮进入。(你可以在图 5-19 中看到那个按钮。)我不打算呈现那个页面的细节，因为它与成员表单如此相似，但是图 5-19 显示了它的样子。除了代理键之外，只有一个字段specialty_id(它是隐藏的)。
图 5-19 。专业表单
回到清单 5-31 ，由成员表单上的选择按钮调用的ChooseSpecialty 功能，打开一个执行specialty.php并显示查找表单的新窗口，允许用户找到他或她想要与成员关联的专业。(如果有几十或几百条记录可供选择，那么 Find 表单的动机会更明显。)然而，在这种情况下，specialty.php被赋予了参数choose，这告诉它它被执行只是为了提供一个选择，而不是为了一般的 CRUD 操作。
首先，这里是ChooseSpecialty JavaScript 函数。
function ChooseSpecialty(id) {
window.open("specialty.php?choose=yes&id=" + id, "_blank",
"height=600, width=800, top=100, left=100, tab=no, " +
"location=no, menubar=no, status=no, toolbar=no", false);
}
窗口的细节并不重要，你可以随意设置。重要的是传递给specialty.php : choose的两个参数和仍然显示成员表单的窗口中隐藏的specialty_id字段的 id。图 5-20 显示了点击专业窗口中的查找按钮显示所有专业后的两个窗口。
图 5-20 。带有弹出专业表单的成员表单
图 5-21 显示了点击金属标记的选择按钮时会发生什么。调用 specialty 窗口中的 JavaScript 函数MadeChoice，使用三个参数:member 窗口中隐藏的specialty_id字段的 id(在图中用虚线轮廓显示)、所选专业的主键以及该专业的名称(“metalmarks”)。MadeChoice然后用同样的三个参数调用成员窗口中的 JavaScript 函数HaveChoice ，HaveChoice将specialty_id和name插入表单。这就是一个窗口(专业)如何写入另一个窗口(成员)的表单。有了这个解释，你应该能够理解我给出的代码。
图 5-21 。专业窗口修改成员窗口
清单 5-32 展示了specialty.php中的action_find方法，除了处理choose参数的代码之外，它看起来很像清单 5-17 中用于成员表单的方法。
清单 5-32 。specialty.php中的action_find
protected function action_find() {
$url = $_SERVER['PHP_SELF'];
$stmt = $this->db->query('select specialty_id, name
from specialty where name like :pat',
array('pat' => "{$_POST['name']}%"));
if ($stmt->rowCount() == 0)
$this->message('No records found', true);
else {
echo '<p>';
while ($row = $stmt->fetch()) {
$name = $row['name'];
$pk = $row['specialty_id'];
echo '<p class=find-choice>';
if (isset($_REQUEST["choose"]))
echo "<button class=button
οnclick='MadeChoice(\"{$_REQUEST['id']}\",
\"$pk\", \"$name\");'>Choose</button>";
else {
echo <<<EOT
<p class=find-choice>
<a href=$url?action_detail&specialty_id=$pk>Detail</a>
<a href=''
οnclick="DeleteConfirm('$name', '$pk');">Delete</a>
EOT;
}
echo " $name";
}
}
}
它所做的是在每个专业名称旁边显示一个选择按钮(粗体)，而不是从菜单栏执行时显示的详细信息和删除链接。这就是你在图 5-20 中看到的。请注意，这些是按钮，而不是带下划线的链接；我展示了两种方法来说明这两种方法。为了保持一致性，您可能希望在自己的应用中使用按钮。
无论如何，当你点击一个选择按钮时，如图 5-21 中的所示，JavaScript 函数MadeChoice被执行，带有三个参数:成员表单中的字段 id(在成员窗口中)，所选专业的主键，以及专业的名称。如我所说，这个函数需要做的是将名称放入成员表单上的可见字段，并将主键放入成员表单上的隐藏外键字段。听起来很容易，但是这些字段在不同的窗口中。幸运的是，做起来和说起来一样容易。
function MadeChoice(id, result, label) { // executes in popup
window.opener.HaveChoice(id, result, label);
window.close();
}
原来你可以用window.opener引用打开这个窗口的窗口，显示成员窗体的那个。函数MadeChoice调用那个窗口中的函数HaveChoice。然后关闭弹出的专业窗口。
回到会员窗口，这里是HaveChoice。
function HaveChoice(id, result, label) { // executes in main window
$('#' + id).val(result);
$('#' + id + '_label').val(label);
}
它使用 jQuery 代码将主键(result参数)放入外键字段(其 id 被传入)，将专业名称(label参数)放入可见字段，其 id 相同，但带有一个_label后缀。
JavaScript 在一个完全不同的窗口中调用一个函数，这是一个来回的过程。我来回顾一下发生了什么，现在你可能想再读一遍这一节，研究一下图 5-21 ，因为你已经知道故事的结局了。
- 在成员窗口中单击了只读名称字段旁边的选择按钮。
- 弹出一个专业窗口，允许用户找到要选择的专业。它被传递了成员窗口中名称字段的 id。
- 在专业窗口中，点击名称旁边的选择按钮，导致
member窗口、specialty_id和name中名称字段的 id 被传递给专业窗口中的MadeChoice函数。 - 成员窗口中的
HaveChoice函数被调用，它将名称放入成员表单上的可见名称字段，并将外键放入隐藏字段。
此时，用户可以验证选择的专业是否正确，并点击保存按钮更新member表。
在您自己的应用中，您可能想要一个更光滑的用户界面，但是您仍然可以利用这种在表单之间传递数据的技术。
处理多对多关系
多对多关系比那些包含外键的表单更简单，因为你不需要像MadeChoice和HaveChoice函数中那样复杂的 JavaScript 来将数据从一个窗口复制到另一个窗口。当然，有一个表可以实现多对多关系，但是它不会以任何形式出现，并且可以在后台更新。
举个例子，假设 Butterfly Club 希望接纳拥有多项专长的会员，使member和specialty桌之间的关系成为多对多关系。
为了实现这一点，我添加了一个新表member_specialty，它有两列，共同构成主键:member_id和specialty_id。一排意味着那个成员有那个专长。具有相同member_id的多行意味着该成员具有多个专业。
为了显示成员的专长，我将窗口分成两半，并在列表中显示他或她的专长，以及两个按钮，删除选定和添加，如图图 5-22 所示，其中 Eleanor 有两个专长。
图 5-22 。显示专业的表格
为了展示特色，我修改了action_detail方法来输出一个 HTML 表，带有主表单，在左边显示member表的字段，在右边显示一个新表单。我不会展示左半部分的代码，因为它与我已经展示过的相似，但是清单 5-33 展示了显示右半部分的方法。
清单 5-33 。窗体右半部分的方法
protected function show_form_right($member) {
$member_id = $member['member_id'];
echo <<<EOT
Specialties
<form action='{$_SERVER['PHP_SELF']}'
method=post accept-charset=UTF-8>
EOT;
if (isset($member_id)) {
$stmt = $this->db->query('select specialty_id, name
from specialty
join member_specialty using (specialty_id)
where member_id = :member_id',
array('member_id' => $member_id));
echo '<select name=specialties size=10
style="min-width:100px;">';
while ($row = $stmt->fetch())
echo "<option
value={$row['specialty_id']}>{$row['name']}</option>";
echo '</select>';
}
echo <<<EOT
<br><input class=button type=submit
name=action_delete_specialty value='Delete Selected'>
<br><input class=button type=button
value='Add'
οnclick='ChooseSpecialty($member_id);'>
<input type=hidden name=member_id value=$member_id>
</form>
EOT;
}
参数是一个列值数组，这里称为$member，而不是通常的$row，以免与函数内部的查询结果混淆。
该查询在specialty和member_specialty表之间进行连接，以查找该成员的专长。他们每个人都成为一个select领域的option。出现的是专业名称，所以用户会看到它，但是主键specialty_id是值。
删除选中的按钮导致页面在action_delete_specialty方法被调用，这在清单 5-34 中。它捕捉并重新抛出一个异常，这样如果有错误，表单将被重新显示。注意，要删除的specialty_id(只允许一个)是select字段的值，其名称是specialties(粗体显示)。
清单 5-34 。action_delete_specialty方法
protected function action_delete_specialty() {
try {
if (isset($_POST['specialties'])) {
$this->db->query('delete from member_specialty
where member_id = :member_id and
specialty_id = :specialty_id',
array('member_id' => $_POST['member_id'],
'specialty_id' => $_POST['specialties'] ));
}
}
catch (\Exception $e) {
$exc = $e;
}
$this->action_detail();
if (isset($exc))
throw $exc;
}
右边的另一个按钮 Add 使用了我之前展示的相同的ChooseSpecialty JavaScript 函数，只是这次参数是主键member_id，它将是member_specialty表中的外键。您应该还记得，ChooseSpecialty用参数choose和id执行specialty.php程序。
由于specialty.php必须更新member_specialty表，而不是像上一节那样仅仅将一个specialty_id和name返回给成员表单，因此其action_find中的处理与我在清单 5-32 中展示的不同。为了使代码更清晰，它会调用另外两个方法中的一个，这取决于您是否定义了 choose 参数。
protected function action_find() {
if (isset($_REQUEST["choose"]))
$this->action_find_choices();
else
$this->action_find_normal();
}
当从菜单栏执行specialty.php时，方法action_find_normal用于正常的 CRUD 情况。这是我们关心的另一个方法，它显示了一个带有复选框的未选择的专业列表，如图图 5-23 所示。代码在清单 5-35 中。
图 5-23 。选择专业的表格
清单 5-35 。action_find_choose 选择专业
protected function action_find_choices() {
$url = $_SERVER['PHP_SELF'];
$member_id = $_REQUEST['id'];
$stmt = $this->db->query('select specialty.specialty_id, name
from specialty
left join member_specialty on
specialty.specialty_id = member_specialty.specialty_id and
:member_id = member_specialty.member_id
where name like :pat and member_id is null',
array('pat' => "{$_POST['name']}%",
'member_id' => $member_id));
if ($stmt->rowCount() == 0)
$this->message('No unchosen specialties found', true);
else {
echo <<<EOT
<p>Unchosen Specialties
<form action=$url method=post>
EOT;
while ($row = $stmt->fetch()) {
$name = $row['name'];
$pk = $row['specialty_id'];
echo <<<EOT
<p class=find-choice>
<input type='checkbox' name=specialty[$pk]>
$name
EOT;
}
echo <<<EOT
<p>
<input type=hidden name=member_id value=$member_id>
<input class=button type=submit
name=action_add value='Add Specialties'>
</form>
EOT;
}
}
这种方法有几个值得注意的地方。
- 为了获取那些尚未选择的专业，我将
specialty表与member_specialty表连接起来，并获取没有出现在后一个表中的专业。注意，member_id在连接条件中(粗体)，但是在where子句中有一个测试来证明它为空。(我本来可以使用子查询，但是我认为它是一个“左非连接”) name=specialty[$pk]复选框的属性导致 PHP 将$_REQUEST['specialty']变成一个数组，我将在清单 5-36 中展示。我只想要所选行的主键，我将把它们作为数组下标。
你可以在清单 5-36 (粗体)中的action_add方法中看到specialty_id值的数组是如何被访问的。
清单 5-36 。action_add专业方法
protected function action_add() {
if (isset($_REQUEST['specialty'])) {
foreach ($_REQUEST['specialty'] as $specialty_id => $v )
$this->db->query('insert into member_specialty
(member_id, specialty_id)
values (:member_id, :specialty_id)',
array('member_id' => $_REQUEST['member_id'],
'specialty_id' => $specialty_id));
$this->message('Added OK. Window may be closed.',
true);
}
else
$this->message('No specialties were added.');
}
在这个循环中，所有需要做的就是为member_id(在清单 5-35 中作为隐藏字段传递)和specialty_id插入一个新行。该行肯定不会出现(这将违反主键的唯一约束)，因为选择表单中只显示了尚未选择的专业。
我忘了一件重要的事情:会员窗口在添加专业之后没有显示任何新的东西；你必须手动重新加载。我让您添加必要的 JavaScript 来使specialty.php更新成员窗口。(提示:在成员窗口中调用一个 JavaScript 函数，通过window.opener引用它，就像我在“带有外键的表单”一节中对MadeChoice函数所做的那样)
诚然，从六个专业中选择一个需要很多代码，正如我所说的，会员表单上的下拉菜单也可以，如果不是更好的话。但是我想展示更难的情况，因为有时会有数百甚至数千个选择，用户会希望使用完整页面的所有功能来进行选择。
在这一节和前一节之间，您将发现为一对多关系和多对多关系的“多”方面开发自己的用户界面所需的所有编码技巧。基本上，这两种选择是“传递表单”和“更新关联表”
章节总结
- 使用 PDO 从 PHP 访问 MySQL，因为它在出错时抛出异常，容易处理参数化查询，并且独立于数据库。
- 将
sql_mode设置为traditional，将innodb_strict_mode设置为on。 - 将数据库凭证放在它们自己的文件中。
- 如果 SQL 语句的任何部分包含运行时提供的值，请始终使用参数化查询。不要将任何包含数据值的 PHP 变量放在传递给任何使用 SQL 语句的方法的字符串中。
- 大多数 MySQL 交互可以通过两种方法处理，
DbAccess::query和DbAccess::update。 - 一个通用的页面框架，在一个
Page类中，确保所有需要的处理在每个页面上执行，并且页面有一致的外观。 Page类中的代码使动作按钮调用按钮所在页面中的动作方法，从而提高了内聚力。- 除了登录页面之外，应用页面应该在会话中运行，并且会话 id 必须保密。
- 为了安全起见，应用应该对任何包含会话 cookie 或敏感数据(如用户 ID 或密码)的页面使用 SSL(以
https开头的 URL)。对于大多数应用，这意味着所有页面。 - 任何写入页面的用户提供的数据都应该由
htmlspecialchars处理。 - 如果用户成功登录，登录页面将启动会话。注销页面(或登录页面中的注销方法)会破坏会话。
- 一对多关系的“多”方可以通过一个弹出窗口来处理，该窗口修改父窗口(
window.opener)中的字段(通信表单)。 - 通过修改关联表，然后将结果反映在表单的列表中，可以处理多对多关系。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
