PHP-MySQL-入门教程-十-PHP MySQL 入门教程（十）三十、MySQL 触发器一个触发器是一个任务，它

PHP MySQL 入门教程（十）

原文：Beginning PHP and MySQL

协议：CC BY-NC-SA 4.0

三十、MySQL 触发器

一个触发器是一个任务，它响应一些预定义的数据库事件而执行，比如在一个新的行被添加到一个特定的表之后。具体来说，该事件涉及插入、修改或删除表数据，并且该任务可以在任何此类事件之前或之后立即发生。本章首先提供了一些通用示例，说明如何使用触发器来执行一些任务，如实施参照完整性和业务规则、收集统计信息以及防止无效事务。然后我将讨论 MySQL 的触发器实现，向您展示如何创建、执行和管理触发器。最后，您将了解如何将触发器特性整合到 PHP 驱动的 web 应用中。

介绍触发器

作为开发人员，我们必须记住实现大量的细节，以使应用正常运行。这一挑战在很大程度上与管理数据有关，包括以下任务:

防止由于数据格式错误而导致的损坏。
强制实施业务规则，比如确保将关于产品的信息插入到product表中的尝试包括制造商的标识符，该制造商的信息已经存在于manufacturer表中。
通过在整个数据库中级联更改来确保数据库的完整性，例如删除与您希望从系统中删除的制造商相关联的所有产品。

如果您已经构建了一个简单的应用，那么您可能已经花了一些时间编写代码来执行至少其中的一些任务。如果可能的话，最好在服务器端自动执行其中的一些任务，不管哪种类型的应用正在与数据库进行交互。数据库触发器为您提供了这种选择。

为什么要使用触发器？

触发器有许多用途，包括:

审计跟踪 : 假设您正在使用 MySQL 记录 Apache 流量(可能使用 Apache mod_log_sql 模块),但是您还想创建一个额外的特殊日志记录表，让您可以快速制表并将结果显示给不耐烦的主管。执行这个额外的插入可以通过触发器自动完成。
验证 : 您可以在更新数据库之前使用触发器来验证数据，例如确保满足最小订单阈值。
参照完整性实施 : 良好的数据库管理实践表明，表关系在项目的整个生命周期中保持稳定。偶尔使用触发器来确保这些任务自动发生可能是有意义的，而不是试图以编程方式合并所有完整性约束。支持外键约束的数据库无需触发器即可处理完整性实施。维护引用完整性意味着，如果删除了另一个(或同一个)表中的记录，确保没有引用指向该记录。外键是用于标识另一个表中的键的列的术语，从而将两个表链接在一起。

触发器的用途远远超出了这些目的。假设您希望在达到每月 100 万美元的收入目标时更新公司网站。或者假设您想给一周内缺勤超过两天的任何员工发送电子邮件。或者，您可能希望在特定产品的库存不足时通知制造商。所有这些任务都可以由触发器来处理。

为了让您更好地理解触发器的效用，让我们考虑两个场景:第一个场景涉及在触发器之前的*，一个在事件之前发生的触发器；第二个涉及触发后的，该触发发生在事件之后。*

在事件发生前采取行动

假设食品经销商要求在处理交易之前至少购买 10 美元的咖啡。如果客户试图添加少于该金额的商品到购物车，该金额将自动四舍五入为 10 美元。这个过程可以通过 before 触发器轻松完成，在本例中，该触发器评估任何将产品插入购物车的尝试，并将任何不可接受的低咖啡购买金额增加到$10。一般过程如下所示:

Shopping cart insertion request submitted.

    If product identifier set to "coffee":
        If dollar amount < $10:
            Set dollar amount = $10;
        End If
    End If

Process insertion 

request.

事件发生后采取行动

大多数帮助台支持软件都基于票证分配和解决的概念。票证由负责记录票证信息的帮助台技术人员分配和解决。然而，偶尔甚至技术人员也被允许离开他们的工作间去度假或养病。在此类缺席期间，客户不能指望等待技术人员回来，因此技术人员的票证应放回池中，由经理重新分配。

这个过程应该是自动的，这样未完成的票据就不会被忽略。这是一个很好的使用触发器的场景。

出于示例的目的，假设technicians表如下所示:

+--------+---------+--------------------------+------------+
| id     | name    | email                    | available  |
+--------+---------+--------------------------+------------+
| 1      | Jason   | jason@example.com        | 1          |
| 2      | Robert  | robert@example.com       | 1          |
| 3      | Matt    | matt@example.com         | 1          |
+--------+---------+--------------------------+------------+

tickets表看起来像这样:

+------+-----------+-----------------+---------------------+----------------+
| id   | username  | title           | description         |  technician_id |
+------+-----------+-----------------+---------------------+----------------+
| 1    | smith22   | disk drive      | Disk stuck in drive |       1        |
| 2    | gilroy4   | broken keyboard | Enter key is stuck  |       1        |
| 3    | cornell15 | login problems  | Forgot password     |       3        |
| 4    | mills443  | login problems  | forgot username     |       2        |
+------+-----------+-----------------+---------------------+----------------+

因此，要将技术人员指定为不在办公室，需要在technicians表中相应地设置available标志(0表示不在办公室，1表示在办公室)。如果对给定的技术人员执行查询，将该列设置为0,那么他的票据应该全部放回通用池中，以便最终重新分配。后触发过程如下所示:

Technician table update request submitted.
    If available column set to 0:
        Update tickets table, setting any flag assigned
        to the technician back to the general pool.
    End If

在本章的后面，你将学习如何实现这个触发器并把它合并到一个 web 应用中。

前触发器与后触发器

您可能想知道如何得出使用 before 触发器代替 after 触发器的结论。例如，在上一节的触发后情形中，为什么票证重新分配不能在技术人员的可用性状态更改之前发生？标准实践表明，在验证或修改要插入或更新的数据时，应该使用 before 触发器。before 触发器不应用于强制传播或引用完整性(确保所有键都指向其他表中的现有记录)，因为其他 before 触发器可能会在它之后执行，这意味着正在执行的触发器可能正在处理即将无效的数据。

另一方面，当要根据其他表传播或验证数据以及执行计算时，应该使用 after 触发器，因为您可以确保触发器使用的是数据的最终版本。

在下面几节中，您将学习如何最有效地创建、管理和执行 MySQL 触发器。还提供了许多 PHP/MySQL 驱动的应用中触发器使用的例子。

MySQL 的触发器支持

MySQL 版本 5.0.2 增加了对触发器的支持，但有一些限制。例如，在撰写本文时，存在以下缺陷:

不支持TEMPORARY 表**:**触发器不能与TEMPORARY表一起使用。
**不支持视图:**触发器不能与视图一起使用(将在下一章介绍)。
**MySQL 数据库不允许触发器:**在 MySQL 数据库中创建的表不允许创建触发器。
**触发器不能返回结果集:**只能在触发器内执行插入、更新和删除查询。然而，您可以在触发器中执行存储的例程，只要它们不返回结果集，以及SET命令。
**触发器必须是唯一的:**不能创建共享同一个表、事件(INSERT、UPDATE、DELETE)和提示(之前、之后)的多个触发器。但是，因为可以在一个查询的范围内执行多个命令(您很快就会知道)，所以这应该不是什么问题。
错误处理 **和报告支持不成熟:**尽管正如所料，如果 before 或 after 触发器失败，MySQL 会阻止操作的执行，但目前还没有合适的方法来导致触发器失败并向用户返回有用的信息。

这可能看起来有局限性，但是触发器仍然提供了一种实现业务逻辑的强大方法。如果有多个用户/系统直接与数据库交互，并且不希望每个用户/系统都实现特定的业务逻辑，那么可以使用触发器。解决这个问题的另一种方法是创建实现逻辑的 API，并且只允许用户与 API 交互，而不是直接与数据库交互。这种方法的一个优点是，只要您的 API 继续以同样的方式工作，您就可以在需要时自由地更改模式。

创建触发器

MySQL 触发器是使用一个相当简单的 SQL 语句创建的。语法原型如下:

CREATE
   [DEFINER = { USER | CURRENT_USER }]
   TRIGGER <trigger name>
   { BEFORE | AFTER }
   { INSERT | UPDATE | DELETE }
   ON <table name>
   FOR EACH ROW
   [{ FOLLOWS | PRECEDES } <other_trigger_name>]
   <triggered SQL statement>

如您所见，可以指定触发器应该在查询之前还是之后执行；是否应该在行插入、修改或删除时发生；以及触发器适用于哪个表。

DEFINER子句确定将咨询哪个用户帐户，以确定是否有适当的特权来执行触发器中定义的查询。如果已定义，您需要使用'user@host'语法指定用户名和主机名(例如，'jason@localhost')。如果使用了CURRENT_USER(默认)，那么将查询导致触发器执行的任何帐户的权限。只有拥有SUPER权限的用户才能将DEFINER分配给其他用户。

以下示例实现了本章前面介绍的帮助台触发器:

DELIMITER //
CREATE TRIGGER au_reassign_ticket
AFTER UPDATE ON technicians
FOR EACH ROW
BEGIN
   IF NEW.available = 0 THEN
      UPDATE tickets SET  technician_id=null WHERE  technician_id=NEW.id;
   END IF;
END;//

注意

您可能想知道触发器标题中的前缀au。关于这个前缀和类似前缀的更多信息，请参见侧栏“触发器命名约定”。

对于受technicians表更新影响的每一行，触发器将更新tickets表，只要在UPDATE查询中指定的technician_id值存在，就将tickets.technician_id设置为null。您知道正在使用查询值，因为别名NEW是列名的前缀。还可以通过在列前加上别名OLD来使用列的初始值。

一旦创建了触发器，继续进行测试，在tickets表中插入几行，并执行一个UPDATE查询，将技术人员的availability列设置为0:

UPDATE technicians SET available=0 WHERE id =1;

现在检查tickets表，您将看到分配给 Jason 的两张票不再被分配。

触发器命名约定

虽然不是必需的，但为触发器设计某种命名约定是个好主意，这样您可以快速确定每个触发器的用途。例如，您可以考虑在每个触发器标题前添加以下字符串之一，正如在触发器创建示例中所做的那样:

ad:在DELETE查询发生后执行触发器
ai:在INSERT查询发生后执行触发器
au:在UPDATE查询发生后执行触发器
bd:在DELETE查询发生之前执行触发器
bi:在INSERT查询发生之前执行触发器
bu:在UPDATE查询发生之前执行触发器

查看现有触发器

有两种方法可以查看现有的触发器:使用SHOW TRIGGERS命令或者使用信息模式。本节将介绍这两种解决方案。

显示触发器命令

SHOW TRIGGERS 命令为一个触发器或一组触发器生成几个属性。其原型如下:

SHOW TRIGGERS [FROM db_name] [LIKE expr | WHERE expr]

因为输出有溢出到下一行的趋势，使得难以读取，所以使用\G标志执行SHOW TRIGGERS是有用的，如下所示:

mysql>SHOW TRIGGERS\G

假设当前数据库中只存在先前创建的au_reassign_ticket触发器，输出如下:

*************************** 1\. row ***************************
         Trigger: au_reassign_ticket
           Event: UPDATE
           Table: technicians
       Statement: begin
if NEW.available = 0 THEN
UPDATE tickets SET  technician_id=0 WHERE  technician_id=NEW.id;
END IF;
END
          Timing: AFTER
         Created: NULL
        sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
         Definer: root@localhost
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

您可能希望查看触发器创建语句。要查看触发器创建语法，请使用SHOW CREATE TRIGGER语句，如下所示:

mysql>SHOW CREATE TRIGGER au_reassign_ticket\G
*************************** 1\. row ***************************
               Trigger: au_reassign_ticket
              sql_mode:
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER au_reassign_ticket
AFTER UPDATE ON technicians
FOR EACH ROW
BEGIN
   IF NEW.available = 0 THEN
      UPDATE tickets SET  technician_id=null WHERE  technician_id=NEW.id;
   END IF;
END
  character_set_client: latin1
  collation_connection: latin1_swedish_ci
    Database Collation: latin1_swedish_ci

了解触发器更多信息的另一种方法是查询INFORMATION_SCHEMA数据库。

信息模式

对在INFORMATION_SCHEMA数据库中找到的TRIGGERS表执行SELECT查询会显示关于触发器的信息。这个数据库在第二十八章中首次介绍。

mysql>SELECT * FROM INFORMATION_SCHEMA.triggers
    ->WHERE trigger_name="au_reassign_ticket"\G

执行此查询会检索到比上一个示例中显示的更多信息:

*************************** 1\. row ***************************
           TRIGGER_CATALOG: NULL
            TRIGGER_SCHEMA: chapter33
              TRIGGER_NAME: au_reassign_ticket
        EVENT_MANIPULATION: UPDATE
      EVENT_OBJECT_CATALOG: NULL
       EVENT_OBJECT_SCHEMA: chapter33
        EVENT_OBJECT_TABLE: technicians
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: begin
if NEW.available = 0 THEN
UPDATE tickets SET  technician_id=0 WHERE  technician_id=NEW.id;
END IF;
END
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
          CREATED: NULL
         SQL_MODE: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
          DEFINER: root@localhost
      CHARACTER_SET_CLIENT: latin1
      COLLATION_CONNECTION: latin1_swedish_ci
        DATABASE_COLLATION: latin1_swedish_ci

如您所见，查询INFORMATION_SCHEMA数据库的美妙之处在于它比使用SHOW TRIGGERS灵活得多。例如，假设您正在管理许多触发器，并想知道哪些触发器是在一条语句之后触发的:

SELECT trigger_name FROM INFORMATION_SCHEMA.triggers WHERE action_timing="AFTER"

或者，您可能想知道当 technologies 表是一个INSERT、UPDATE或DELETE查询的目标时，执行了哪些触发器:

mysql>SELECT trigger_name FROM INFORMATION_SCHEMA.triggers WHERE
    ->event_object_table="technicians"

修改触发器

在撰写本文时，没有支持的命令或 GUI 应用可用于修改现有的触发器。因此，修改触发器最简单的策略可能是删除并随后重新创建它。

删除触发器

可以想象，特别是在开发阶段，如果不再需要某个动作，您会想要删除某个触发器。这是通过使用DROP TRIGGER语句完成的，其原型如下:

DROP TRIGGER [IF EXISTS] table_name.trigger_name

例如，要删除au_reassign_ticket触发器，执行以下命令:

DROP TRIGGER au_reassign_ticket;

您需要TRIGGER或SUPER权限才能成功执行DROP TRIGGER。

警告

当数据库或表被删除时，所有相应的触发器也被删除。

在前面的章节中，我们已经讨论了触发器的创建和删除。这可以很容易地从 PHP 完成，而不是从命令行或 GUI 工具。这是因为 SQL 的本质。如前所述，有两种类型的 SQL 命令。第一个处理模式对象，第二个处理表中的数据。由于其性质，发出创建表或触发器的命令与发出在表中插入、更新或删除行的命令没有区别。清单 30-1 展示了如何使用 PHP 创建触发器。

<?php

   // Connect to the MySQL database
   $mysqli = new mysqli("localhost", "websiteuser", "secret", "helpdesk");

// Create a trigger
$query = <<<HEREDOC
DELIMITER //
CREATE TRIGGER au_reassign_ticket
AFTER UPDATE ON technicians
FOR EACH ROW
BEGIN
   IF NEW.available = 0 THEN
      UPDATE tickets SET  technician_id=null WHERE  technician_id=NEW.id;
   END IF;
END;//
HEREDOC;
$mysqli->query(($query);

?>

Listing 30-1Create trigger

将触发器集成到 Web 应用中

因为触发器透明地发生，所以您真的不需要做任何特殊的事情来将它们的操作集成到您的 web 应用中。尽管如此，还是有必要提供一个例子来证明这个特性在减少 PHP 代码量和进一步简化应用逻辑方面是多么有用。在本节中，您将学习如何实现先前在“事件后采取行动”一节中首次描述的帮助台应用。

首先，如果您还没有这样做，那么继续创建前面部分描述的两个表(technicians和tickets)。为每一行添加几个适当的行，确保每个tickets.technician_id匹配一个有效的technicians.technician_id。接下来，如前所述创建au_reassign_ticket触发器。

概括一下这个场景，提交的帮助台票证是通过将每个票证分配给技术人员来解决的。如果技术人员长时间不在办公室，他应该通过更改其可用性状态来更新其配置文件。profile manager 界面类似于图 30-1 所示。

img/314623_5_En_30_Fig1_HTML.jpg

图 30-1

帮助台帐户界面

当技术人员对该界面进行任何更改并提交表格时，清单 30-2 中显示的代码被激活。

<?php

   // Connect to the MySQL database
   $mysqli = new mysqli("localhost", "websiteuser", "secret", "helpdesk");

   // Assign the POSTed values for convenience
   $options = array('min_range' => 0, 'max_range' => 1);
   $email = filter_var($_POST['email'], FILTER_VALIDATE_EMAIL);
   $available = filter_var($_POST['available'], FILTER_VALIDATE_INT, $options);

   // Create the UPDATE query
   $stmt = $mysqli->prepare("UPDATE technicians SET available=? WHERE email=?");

   $stmt->bind_param('is', $available, $email);

   // Execute query and offer user output
   if ($stmt->execute()) {

      echo "<p>Thank you for updating your profile.</p>";

      if ($available == 0) {
         echo "<p>Your tickets will be reassigned to another technician.</p>";
      }

   } else {
      echo "<p>There was a problem updating your profile.</p>";
   }

?>

Listing 30-2Updating the Technician Profile

执行完这段代码后，返回到tickets表，您会看到相关的票已经被取消分配。

摘要

触发器可以大大减少为确保数据库的引用完整性和业务规则而需要编写的代码量。您了解了不同的触发器类型以及它们执行的条件。首先介绍了 MySQL 的触发器实现，然后介绍了如何将这些触发器集成到 PHP 应用中。

下一章介绍视图，这是一个强大的特性，允许您为长而复杂的 SQL 语句创建易于记忆的别名。

三十一、MySQL 视图

即使相对简单的数据驱动应用也依赖于涉及多个表的查询。例如，假设您负责创建一个人力资源应用，并希望创建一个界面来显示每个员工的姓名、电子邮件地址、缺勤总数和奖金。该查询可能如下所示:

SELECT emp.employee_id, emp.firstname, emp.lastname, emp.email,
       COUNT(att.absence) AS absences, COUNT(att.vacation) AS vacation,
       SUM(comp.bonus) AS bonus
FROM employees emp, attendance att, compensation comp
WHERE emp.employee_id = att.employee_id
AND emp.employee_id = comp.employee_id
GROUP BY emp.employee_id ASC
ORDER BY emp.lastname;

在这个例子中，从三个表中选择列:employees、attendance,和compensation。为了更容易编写查询，每个表都有一个别名:emp、att,和comp。这不仅有助于缩短对每个表的所有引用，还可用于将一个表与其自身连接起来，如下所示:

select a.name man_name, b.name emp_name from employee a, employee b where a.id = b.manager_id;

这里，我们在同一个表上创建了两个别名，允许您查找每个员工及其经理的姓名。我们还为两个 name 列引入了别名。因为它们来自同一个表，所以它们在模式中有相同的名称，但是添加一个别名可以区分它们。

这种性质的查询由于其大小足以让人不寒而栗，特别是当它们需要在整个应用的几个位置重复时。此类查询的另一个副作用是，它们可能会导致有人无意中泄露潜在的敏感信息。例如，如果您一时糊涂，不小心将列emp.ssn(员工的社会保险号，或 SSN)插入到这个查询中，该怎么办？这将导致每个员工的 SSN 显示给任何能够查看查询结果的人。此类查询的另一个副作用是，任何被指派创建类似界面的第三方承包商都有可能获得敏感数据的访问权限，从而开启身份盗窃和企业间谍活动的可能性。

有什么选择？毕竟，查询对于开发过程来说是必不可少的，除非你想纠结于管理列级权限(参见第二十六章)，否则你似乎只能苦笑着忍受了。

这就是视图变得有用的地方。视图提供了一种封装查询的方法，就像一个存储例程(见第二十九章)作为一组命令的别名一样。例如，您可以创建上述示例查询的视图，并按如下方式执行:

SELECT * FROM employee_attendance_bonus_view;

本章首先简要介绍了视图的概念以及将视图整合到您的开发策略中的各种优势。然后讨论 MySQL 的视图支持，向您展示如何创建、执行和管理视图。最后，您将学习如何将视图合并到 PHP 驱动的 web 应用中。

介绍视图

也称为虚拟表，视图由一组在执行特定查询时返回的行组成。视图不是查询表示的数据的副本，而是通过别名使查询可用，简化了检索数据的方式。其他数据库系统支持物化视图或复制数据的地方。MySQL 不支持，但可以用存储过程和表来实现。

视图非常有利，原因有很多:

**简单:**某些数据资源需要经常检索。例如，在客户关系管理应用中，将客户与特定的发票相关联是经常发生的。因此，创建一个名为get_client_name的视图可能会很方便，可以省去重复查询多个表来检索这些信息的麻烦。
**安全性:**如前所述，在某些情况下，您可能希望确保某些信息不被第三方获取，例如员工的社会保障号和工资。视图提供了实现这种保护的实用解决方案。这要求视图不是用 select *操作创建的，并且禁止直接对原始表进行查询访问。
可维护性:就像面向对象的类抽象底层数据和行为一样，视图抽象查询的血淋淋的细节。在查询必须随后更改以反映对模式修改的情况下，这种抽象非常有用。

现在，您已经更好地理解了视图如何成为您的开发策略的重要部分，是时候了解更多关于 MySQL 的视图支持了。

MySQL 的视图支持

在本节中，您将学习如何创建、执行、修改和删除视图。

创建和执行视图

创建一个视图是通过CREATE VIEW语句完成的。其原型如下:

CREATE
    [OR REPLACE]
    [ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED }]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

在本节的整个过程中，将介绍完整的CREATE VIEW语法；然而，现在让我们从一个简单的例子开始。假设您的数据库包含一个名为employees的表，其中包含每个雇员的信息。表创建语法如下所示:

CREATE TABLE employees (
   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   employee_id CHAR(8) NOT NULL,
   first_name VARCHAR(100) NOT NULL,
   last_name VARCHAR(100) NOT NULL,
   email VARCHAR(100) NOT NULL,
   phone CHAR(10) NOT NULL,
   salary DECIMAL(8,2) NOT NULL,
   PRIMARY KEY(id)
);

一名开发人员被分配了一项任务，即创建一个允许员工查找同事联系信息的应用。因为薪水是一个敏感的问题，所以要求数据库管理员创建一个只包含每个雇员的姓名、电子邮件地址和电话号码的视图。以下视图提供了该信息的界面，根据雇员的姓氏对结果进行排序:

CREATE VIEW employee_contact_info_view AS
  SELECT first_name, last_name, email, phone
  FROM employees ORDER BY last_name ASC;

这种观点可以这样称呼:

SELECT * FROM employee_contact_info_view;

这会产生类似于以下内容的结果:

+------------+-----------+-------------------+-------------+
| first_name | last_name | email             | phone       |
+------------+-----------+-------------------+-------------+
| Bob        | Connors   | bob@example.com   | 2125559945  |
| Jason      | Gilmore   | jason@example.com | 2125551212  |
| Matt       | Wade      | matt@example.com  | 2125559999  |
+------------+-----------+-------------------+-------------+

注意，在许多方面，MySQL 对待视图就像对待任何其他表一样。事实上，如果您在使用创建视图的数据库时执行SHOW TABLES(或者使用 phpMyAdmin 或另一个客户机执行一些类似的任务)，您会看到视图与其他表一起列出:

mysql>SHOW TABLES;

这会产生以下结果:

+-----------------------------+
| Tables_in_corporate         |
+-----------------------------+
| employees                   |
| employee_contact_info_view  |
+-----------------------------+

如果想知道哪些是表，哪些是视图，可以像这样查询 INFORMATION_SCHEMA:

SELECT table_name, table_type, engine
       FROM information_schema.tables
       WHERE table_schema = 'book'
       ORDER BY table_name;

输出如下所示:

+-----------------------------+------------+--------+
| table_name                  | table_type | engine |
+-----------------------------+------------+--------+
| employees                   | BASE TABLE | InnoDB |
| employee_contact_info_view  | View       | InnoDB |
+-----------------------------+------------+--------+

现在在视图上执行DESCRIBE语句:

mysql>DESCRIBE employee_contact_info_view;

这会产生:

+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| first_name | varchar(100) | NO   |     |         |       |
| last_name  | varchar(100) | NO   |     |         |       |
| email      | varchar(100) | NO   |     |         |       |
| phone      | char(10)     | NO   |     |         |       |
+------------+--------------+------+-----+---------+-------+

您可能会惊讶地发现，您甚至可以创建可更新的视图。也就是说，您可以通过引用视图来插入甚至更新行，但会导致基础表被更新。该特性在“更新视图”一节中介绍。

自定义视图结果

视图没有被约束为返回用于创建视图的查询中定义的每一行。例如，可以只返回雇员的姓氏和电子邮件地址:

SELECT last_name, email FROM employee_contact_info_view;

该返回结果类似于以下内容:

+-----------+-------------------+
| last_name | email             |
+-----------+-------------------+
| Connors   | bob@example.com   |
| Gilmore   | jason@example.com |
| Wade      | matt@example.com  |
+-----------+-------------------+

您还可以在调用视图时覆盖任何默认的排序子句。例如，employee_contact_info_view视图定义指定信息应该根据姓氏排序。但是如果你想根据电话号码来排序结果呢？只要改变从句，就像这样:

SELECT * FROM employee_contact_info_view ORDER BY phone;

这会产生以下输出:

+------------+------------+-------------------+------------+
| first_name | last_name  | email             | phone      |
+------------+------------+-------------------+------------+
| Jason      | Gilmore    | jason@example.com | 2125551212 |
| Bob        | Connors    | bob@example.com   | 2125559945 |
| Matt       | Wade       | matt@example.com  | 2125559999 |
+------------+------------+-------------------+------------+

就此而言，视图可以与所有子句和函数结合使用，这意味着您可以使用SUM()、LOWER()、ORDER BY、GROUP BY，或者任何其他您喜欢的子句或函数。

传入参数

正如您可以通过使用子句和函数来操纵视图结果一样，您也可以通过传递参数来实现这一点。例如，假设您对检索特定员工的联系信息感兴趣，但您只记得他的名字:

SELECT * FROM employee_contact_info_view WHERE first_name="Jason";

这将返回:

+------------+-----------+-------------------+------------+
| first_name | last_name | email             | phone      |
+------------+-----------+-------------------+------------+
| Jason      | Gilmore   | jason@example.com | 2125551212 |
+------------+-----------+-------------------+------------+

修改返回的列名

表列命名约定通常是程序员方便的产物，当呈现给最终用户时，偶尔会导致晦涩难懂的阅读。使用视图时，可以通过可选的 column_list 参数传递列名来改进这些名称。下面的例子是对employee_contact_info_view视图的修改，用一些更友好的名字替换了默认的列名:

CREATE VIEW employee_contact_info_view
  (`First Name`, `Last Name`, `Email Address`, `Telephone`) AS
  SELECT first_name, last_name, email, phone
  FROM employees ORDER BY last_name ASC;

现在执行以下查询:

SELECT * FROM employee_contact_info_view;

这将返回:

+------------+-----------+-------------------+-------------+
| First Name | Last Name | Email Address     | Telephone   |
+------------+-----------+-------------------+-------------+
| Bob        | Connors   | bob@example.com   | 2125559945  |
| Jason      | Gilmore   | jason@example.com | 2125551212  |
| Matt       | Wade      | matt@example.com  | 2125559999  |
+------------+-----------+-------------------+-------------+

创建视图时，使用反勾字符创建带空格的列名。最初的名字使用下划线。为了访问这些值，您必须以数组的形式获取数据。

使用算法属性

ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}

使用这个 MySQL 特有的属性，您可以通过三个设置来优化 MySQL 对视图的执行，下面将介绍这三个设置。

合并

MERGE算法使 MySQL 在执行视图时将视图的查询定义与传入的任何其他子句结合起来。例如，假设使用以下查询定义了名为employee_contact_info_view的视图:

SELECT * FROM employees ORDER BY first_name;

但是，以下语句用于执行视图:

SELECT first_name, last_name FROM employee_contact_info_view;

MERGE算法实际上会执行下面的语句:

SELECT first_name, last_name FROM employee_contact_info_view ORDER by first_name;

换句话说，视图的定义和SELECT查询已经被合并。

时间表

如果视图的基础表中的数据发生了变化，那么下次通过视图访问表时，这些变化将立即通过视图反映出来。但是，当处理特别大或频繁更新的表时，您可能会首先考虑将视图数据转储到一个临时表中，以便更快地释放视图的表锁。

当一个视图被分配了TEMPTABLE算法时，一个相应的临时表在创建视图的同时被创建。

不明确的

当一个视图被分配了UNDEFINED算法(默认)时，MySQL 会尝试确定应该使用两种算法中的哪一种(MERGE或TEMPTABLE)。虽然在一些特定的场景中，TEMPTABLE算法是首选(比如在查询中使用聚合函数时)，但MERGE算法通常更有效。因此，除非查询条件规定一种算法优于另一种算法，否则应该使用UNDEFINED。

如果将UNDEFINED算法分配给视图，如果查询表示其结果和视图中的结果之间存在一对一的关系，MySQL 将选择TEMPTABLE。

使用安全选项

[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]

在 MySQL 5.1.2 中，CREATE VIEW命令增加了额外的安全特性，有助于控制每次执行视图时如何确定特权。

DEFINER子句确定在视图执行时将检查哪个用户帐户的特权，以确定特权是否足以正确执行视图。如果设置为默认值CURRENT_USER，则检查执行用户的权限；否则，DEFINER可以被设置为一个特定的用户，使用语法‘user @ host’(例如，‘Jason @ localhost’)来标识用户。只有拥有SUPER权限的用户才能将DEFINER条款设置给其他用户。

SQL_SECURITY子句决定了当视图被执行时，是否应该检查视图创建者(DEFINER，然后查看前面提到的DEFINER子句的设置)或调用者(INVOKER)的特权。

使用 WITH CHECK OPTION 子句

WITH [CASCADED | LOCAL] CHECK OPTION

因为可以基于其他视图创建视图(不推荐)，所以必须有一种方法来确保更新嵌套视图的尝试不会违反其定义的约束。此外，尽管有些视图是可更新的，但在某些情况下，以违反视图基础查询所施加的某些约束的方式修改列值是不符合逻辑的。例如，如果查询只检索那些包含city = "Columbus"的行，那么创建一个包含WITH CHECK OPTION子句的视图将会阻止任何后续的视图更新将列中的任何值更改为除Columbus之外的任何值。

这个概念和修改 MySQL 在这方面的行为的选项可能最好用一个例子来说明。假设名为experienced_age_view的视图是用LOCAL CHECK OPTION选项定义的，并且包含以下查询:

SELECT first_name, last_name, age, years_experience
   FROM experienced_view WHERE age > 65;

注意，这个查询引用了另一个名为experienced_view的视图。假设这个视图是这样定义的:

SELECT first_name, last_name, age, years_experience
   FROM employees WHERE years_experience > 5;

如果用CASCADED CHECK OPTION选项定义了experienced_age_view，那么尝试执行下面的INSERT查询将会失败:

INSERT INTO experienced_age_view SET
   first_name = 'Jason', last_name = 'Gilmore', age = '89', years_experience = '3';

失败的原因是3的years_experience值将违反experienced_age_view的约束，该约束要求years_experience至少为 5 年。相反，如果experienced_age_view视图被定义为LOCAL，那么INSERT查询将是有效的，因为只有age值会大于 65。但是，如果年龄被设置为 65 以下的任何值，比如 42，查询将会失败，因为LOCAL会检查查询中引用的视图，在本例中是experienced_age_view。

查看视图信息

MySQL 提供了三种方法来更多地了解您现有的视图:DESCRIBE命令、SHOW CREATE VIEW命令或INFORMATION_SCHEMA数据库。

使用描述命令

因为视图类似于虚拟表，所以您可以使用DESCRIBE语句来了解视图所表示的列的更多信息。例如，要查看名为employee_contact_info_view的视图，请执行以下命令:

DESCRIBE employee_contact_info_view;

这会产生以下输出:

+----------------+--------------+------+-----+-------------+----------+
| Field          | Type         | Null | Key | Default     | Extra    |
+----------------+--------------+------+-----+-------------+----------+
| First Name     | varchar(100) | NO   |     |             |          |
| Last Name      | varchar(100) | NO   |     |             |          |
| Email Address  | varchar(100) | NO   |     |             |          |
| Telephone      | char(10)     | NO   |     |             |          |
+----------------+--------------+------+-----+-------------+----------+

使用显示创建视图命令

您可以使用SHOW CREATE VIEW命令查看视图的语法。其原型如下:

SHOW CREATE VIEW view_name;

例如，要查看employee_contact_info_view视图语法，请执行以下命令:

SHOW CREATE VIEW employee_contact_info_view\G

这将产生以下输出(为了可读性，略有修改):

*************************** 1\. row ***************************
                View: employee_contact_info_view
                Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`
                SQL SECURITY DEFINER VIEW `employee_contact_info_view`
                AS select `employees`.`first_name`
                AS `first_name`,`employees`.`last_name`
                AS `last_name`,`employees`.`email`
                AS `email`,`employees`.`phone`
                AS `phone` from `employees`
                order by `employees`.`last_name`
                character_set_client: latin1
                collation_connection: latin1_swedish_ci

虽然很有用，但是您可以通过使用INFORMATION_SCHEMA数据库来查看代码语法和更多内容。

使用信息模式数据库

IN FORMATION_SCHEMA 数据库包括一个views表，该表包含以下内容:

SELECT * FROM INFORMATION_SCHEMA.views\G

假设employee_contact_info_view是唯一存在的视图，执行该语句会产生以下输出:

*************************** 1\. row ***************************
             TABLE_CATALOG: NULL
             TABLE_SCHEMA: chapter31
             TABLE_NAME: employee_contact_info_view
             VIEW_DEFINITION: select first_name, last_name, email, phone from employees
             CHECK_OPTION: NONE
             IS_UPDATABLE: YES
             DEFINER: root@localhost
             SECURITY_TYPE: DEFINER
             CHARACTER_SET_CLIENT: latin1
             COLLATION_CONNECTION: latin1_swedish_ci

当然，使用信息模式的美妙之处在于能够查询视图的任何方面，而不是被迫整理堆积如山的信息。例如，如果您只想检索为chapter31数据库定义的视图的名称，您可以使用以下查询:

SELECT table_name FROM INFORMATION_SCHEMA.views WHERE table_schema="chapter31"\G

修改视图

可以使用ALTER VIEW语句修改现有视图。其原型如下:

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
   [DEFINER = { user | CURRENT_USER }]
   [SQL SECURITY { DEFINER | INVOKER }]
   VIEW view_name [(column_list)]
   AS select_statement
   [WITH [CASCADED | LOCAL] CHECK OPTION]

例如，要修改employee_contact_info_view by，将SELECT语句改为只检索名字、姓氏和电话号码，只需执行以下命令:

ALTER VIEW employee_contact_info_view
  (`First Name`, `Last Name`, `Telephone`) AS
  SELECT first_name, last_name, phone
  FROM employees ORDER BY last_name ASC;

删除视图

删除一个现有的视图是通过DROP VIEW语句完成的。它的原型是这样的:

DROP VIEW [IF EXISTS]
   view_name [, view_name]...
   [RESTRICT | CASCADE]

例如，要删除employee_contact_info_view视图，执行以下命令:

DROP VIEW employee_contact_info_view;

如果试图删除一个不存在的视图，包含IF EXISTS关键字将导致 MySQL 隐藏一个错误。在发布时，RESTRICT和CASCADE关键字被忽略，但允许从其他数据库系统移植 SQL 代码。

更新视图

视图的效用不仅限于抽象用户可以执行 SELECT 语句的查询。视图也可以作为一个接口，通过它可以更新底层的表。例如，假设一名办公室助理负责更新由员工联系信息组成的表中的关键列。助理应该只能查看和修改员工的名字、姓氏、电子邮件地址和电话号码；当然应该防止他们查看或操纵 SSN 和工资。本章前面创建的视图employee_contact_info_view将通过充当可更新和可选择的视图来满足这两个条件。如果视图的查询满足以下任一条件，则该视图不可更新:

它包含一个聚合函数，如SUM()。
其算法设置为TEMPTABLE。
它包含DISTINCT、GROUP BY、HAVING、UNION或UNION ALL。
它包含一个外部联接。
它在FROM子句中包含一个不可更新的视图。
它在SELECT或FROM子句中包含一个子查询，在WHERE子句中包含一个子查询，该子查询引用FROM子句中的一个表。
它仅指文字值，意味着没有要更新的表。

例如，要修改雇员 Bob Connors 的电话号码，可以对视图执行UPDATE查询，如下所示:

UPDATE employee_contact_info_view
       SET phone="2125558989" WHERE `Email Address`='bob@example.com';

术语“可更新视图”不仅限于UPDATE查询；如果视图满足一些约束条件，还可以通过视图插入新行:

该视图必须包含基础表中未分配默认值的所有列。
视图列不能包含表达式。例如，视图列CEILING(salary)将使视图不可测试。

因此，基于当前的视图定义，不能使用employee_contact_info_view视图添加新雇员，因为没有分配默认值的表列，例如salary和ssn，对于视图是不可用的。

与任何其他模式对象一样，可以直接从 PHP 创建、更新和删除这些对象。它们就像任何其他 SQl 查询一样被处理。

将视图合并到 Web 应用中

与前两章中介绍的存储过程和触发器示例一样，将视图合并到 web 应用中是一件相当简单的事情。毕竟，视图是虚拟表，可以像典型的 MySQL 表一样管理，使用SELECT、UPDATE和DELETE来检索和操作它们表示的内容。例如，执行本章前面创建的employee_contact_info_view视图。为了省去您查阅本章开头的麻烦，这里重复了视图创建语法:

CREATE VIEW employee_contact_info_view
  (`First Name`, `Last Name`, `E-mail Address`, `Telephone`) AS
  SELECT first_name, last_name, email, phone
  FROM employees ORDER BY last_name ASC;

以下 PHP 脚本执行视图并以 HTML 格式输出结果:

<?php

    // Connect to the MySQL database
    $mysqli = new mysqli("localhost", "websiteuser", "secret", "chapter34");

    // Create the query
    $query = "SELECT * FROM employee_contact_info_view";

    // Execute the query
    if ($result = $mysqli->query($query)) {

        printf("<table border="1">");
        printf("<tr>");

        // Output the headers
        $fields = $result->fetch_fields();
        foreach ($fields as $field)
            printf("<th>%s</th>", $field->name);

        printf("</tr>");

        // Output the results

        while ($employee = $result->fetch_assoc()) {
            // Format the phone number
            $phone = preg_replace("/([0-9]{3})([0-9]{3})([0-9]{4})/",
                                  "(\\1) \\2-\\3", $employee['Telephone']);

            printf("<tr>");
            printf("<td>%s</td><td>%s</td>", $employee['First Name'], $employee['Last Name']);
            printf("<td>%s</td><td>%s</td>", $employee['Email Address'], $phone);
            printf("</tr>");

      }

   }
?>

执行这段代码会产生如图 31-1 所示的输出。

img/314623_5_En_31_Fig1_HTML.jpg

图 31-1

从视图中检索结果

摘要

本章介绍了 MySQL 中的视图。视图可以减少应用中的重复查询，同时增强安全性和可维护性。在本章中，您学习了如何创建、执行、修改和删除 MySQL 视图；以及如何将它们整合到 PHP 驱动的应用中。

下一章深入探讨了查询的主题，涵盖了在构建数据驱动网站时必然会反复遇到的许多概念。

三十二、实用数据库查询

最后几章介绍了许多关于结合使用 PHP 和 MySQL 来检索和操作数据的概念。本章扩展了您的知识，展示了您在创建数据库驱动的 web 应用时必然会反复遇到的几个挑战。特别是，您将了解到以下概念的更多信息:

**表格输出:**以易读的格式列出查询结果是构建数据库驱动的应用时最常见的任务之一。本章解释了如何以编程方式创建这些列表。
**排序表格输出:**通常，查询结果以默认方式排序，例如按产品名称排序。但是，如果用户希望使用其他标准(比如价格)对结果进行重新排序，该怎么办呢？您将了解如何提供表排序机制，让用户可以对任何列进行排序。
**子查询:**即使简单的数据驱动应用也经常需要查询来处理多个表，通常使用连接。然而，正如您将了解到的那样，这些操作中的许多也可以通过更直观的子查询来完成。
**游标:**游标的操作方式类似于数组指针，它使您能够快速浏览数据库结果集。在这一章中，你将学习如何使用光标来简化你的代码。
**分页结果:**数据库表可以包含数千甚至数百万条记录。当检索大型结果集时，将这些结果分隔在几个页面上并为用户提供在这些页面之间来回导航的机制通常是有意义的。本章解释了如何做到这一点。

抽样资料

本章大部分内容中的许多示例都基于products和sales表，如下所示:

CREATE TABLE products (
   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   product_id VARCHAR(8) NOT NULL,
   name VARCHAR(25) NOT NULL,
   price DECIMAL(5,2) NOT NULL,
   description MEDIUMTEXT NOT NULL
);
CREATE TABLE sales (
   id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   client_id INT UNSIGNED NOT NULL,
   order_time TIMESTAMP NOT NULL,
   sub_total DECIMAL(8,2) NOT NULL,
   shipping_cost DECIMAL(8,2) NOT NULL,
   total_cost DECIMAL(8,2) NOT NULL
);

创建表格输出

无论是旅行选项、产品摘要还是电影放映时间，以表格或网格的形式显示信息是当今使用的最常见的表示范例之一 web 开发人员已经将 HTML 表格的初衷延伸到了他们的边界。令人高兴的是，XHTML 和 CSS 的引入使得基于 web 的表格显示比以往任何时候都更易于管理。在本节中，您将学习如何使用 PHP、MySQL 和一个名为HTML_Table的 PEAR 包构建数据驱动的表。

PEAR 组件的使用不是本节的重要部分。尽管许多 PEAR 类仍然提供有用的功能，但它们不再被积极维护。您应该编写自己的格式化类，或者找到支持您特别需要的特性集的开放源代码版本，这些版本由一个活跃的社区维护，或者您可以使用商业产品。本节旨在让您了解解决该问题的一种方法。

虽然通过在 PHP 代码中对表标记元素和属性进行硬编码，当然可以将数据库数据输出到 HTML 表中，但是这样做很快就会变得乏味且容易出错。考虑到表格驱动的输出在简单网站上的流行，以这种方式混合设计和逻辑的问题会很快变得复杂。那么，有什么解决办法呢？毫不奇怪，通过 PEAR 已经有一个供您使用，它叫做HTML_Table。

除了大大减少您需要处理的特定于设计的代码数量之外，HTML_Table包还提供了一种将 CSS 格式属性合并到输出中的简单方法。在本节中，您将学习如何安装HTML_T able 并使用它快速构建表格数据输出。请注意，本节的目的不是向您介绍每一个HTML_Table特性，而是强调一些您最有可能经常使用的关键特性。查看 PEAR 网站了解HTML_Table功能的完整分类。

安装 HTML_Table

为了利用HTML_Table的特性，你需要从 PEAR 安装它。启动 PEAR，向它传递以下参数:

%>pear install -o HTML_Table

因为HTML_Table依赖于另一个包HTML_Common，如果目标系统上目前没有这个包，那么传递–o选项也会安装这个包。执行这个命令，您将看到类似如下的输出:

WARNING: "pear/HTML_Common" is deprecated in favor of "pear/HTML_Common2"
downloading HTML_Table-1.8.4.tgz ...
Starting to download HTML_Table-1.8.4.tgz (16,440 bytes)
......done: 16,440 bytes
downloading HTML_Common-1.2.5.tgz ...
Starting to download HTML_Common-1.2.5.tgz (4,617 bytes)
...done: 4,617 bytes
install ok: channel://pear.php.net/HTML_Common-1.2.5
install ok: channel://pear.php.net/HTML_Table-1.8.4

一旦安装完毕，您就可以开始利用HTML_Table的功能了。让我们看几个例子，每个例子都建立在前面的基础上，以创建更好看、更有用的表。

创建简单的表格

在最基本的层面上，HTML_Table只需要几个命令就可以创建一个表。例如，假设您想将一组数据显示为 HTML 表格。清单 32-1 提供了一个介绍性的例子，它使用一个简单的 CSS 样式表(由于篇幅限制，这里没有列出)结合HTML_TABLE来格式化$salesreport数组中的销售数据。

<?php

    // Include the HTML_Table package
    require_once "HTML/Table.php";

    // Assemble the data in an array

    $salesreport = array(
    '0' => ["12309","45633","2010-12-19 01:13:42","$22.04","$5.67","$27.71"],
    '1' => ["12310","942","2010-12-19 01:15:12","$11.50","$3.40","$14.90"],
    '2' => ["12311","7879","2010-12-19 01:15:22","$95.99","$15.00","$110.99"],
    '3' => ["12312","55521","2010-12-19 01:30:45","$10.75","$3.00","$13.75"]
    );

    // Create an array of table attributes
    $attributes = array('border' => '1');

    // Create the table object

    $table = new HTML_Table($attributes);

    // Set the headers

    $table->setHeaderContents(0, 0, "Order ID");
    $table->setHeaderContents(0, 1, "Client ID");
    $table->setHeaderContents(0, 2, "Order Time");
    $table->setHeaderContents(0, 3, "Sub Total");
    $table->setHeaderContents(0, 4, "Shipping Cost");
    $table->setHeaderContents(0, 5, "Total Cost");

    // Cycle through the array to produce the table data

    for($rownum = 0; $rownum < count($salesreport); $rownum++) {
        for($colnum = 0; $colnum < 6; $colnum++) {
            $table->setCellContents($rownum+1, $colnum,
                                     $salesreport[$rownum][$colnum]);
        }
    }

    // Output the data

    echo $table->toHTML();

?>

Listing 32-1Formatting Sales Data with HTML_Table

列表 32-1 的结果如图 32-1 所示。

img/314623_5_En_32_Fig1_HTML.jpg

图 32-1

用 HTML_Table 创建表格

用 CSS 和 Html_Table 调整表格样式

从逻辑上讲，您会希望将 CSS 样式应用到您的表格中。幸运的是，HTML_Table还支持通过传递表格、标题、行和特定于单元格的属性来调整表格。这是通过针对表格属性的HTML_Table()构造函数、针对标题和行的setRowAttributes()方法以及针对单元格特定属性的setCellAttributes()方法来实现的。对于每一个，您只需传入一个关联的属性数组。例如，假设您想用一个名为 salesdata 的 id 属性来标记该表。您可以像这样实例化该表:

$table = new HTML_Table("id"=>"salesdata");

在“创建更可读的行输出”一节中，您将学习如何使用这个特性来进一步标记清单 32-1 。

创建可读性更强的行输出

虽然图 32-1 中的数据很容易理解，但是输出大量的数据很快就会变得乏味。为了减轻一些困难，设计者通常每隔一行就涂上颜色，以提供视觉上的突破。用HTML_Table这样做是微不足道的。例如，将包含以下样式的样式表与脚本相关联:

td.alt {
   background: #CCCC99;
}

现在，在清单 32-1 中的for循环完成后，直接添加下面一行:

$table->altRowAttributes(1, null, array("class"=>"alt"));

执行修改后的脚本会产生类似于图 32-2 中的输出。

img/314623_5_En_32_Fig2_HTML.jpg

图 32-2

用 HTML_Table 替换行样式

从数据库数据创建表格

虽然使用数组作为数据源来创建表对于介绍HTML_Table的基本原理来说是很好的，但是您很可能要从数据库中检索这些信息。因此，让我们以前面的例子为基础，从 MySQL 数据库中检索销售数据，并以表格形式呈现给用户。

一般的过程与清单 32-1 中给出的没有太大的不同，除了这一次您将通过一个结果集而不是一个标准数组进行导航。清单 32-2 包含代码。

<?php

    // Include the HTML_Table package
    require_once "HTML/Table.php";

    // Connect to the MySQL database
    $mysqli = new mysqli("localhost", "websiteuser", "secret", "corporate");

    // Create an array of table attributes
    $attributes = array('border' => '1');

    // Create the table object
    $table = new HTML_Table($attributes);

    // Set the headers

    $table->setHeaderContents(0, 0, "Order ID");
    $table->setHeaderContents(0, 1, "Client ID");
    $table->setHeaderContents(0, 2, "Order Time");
    $table->setHeaderContents(0, 3, "Sub Total");
    $table->setHeaderContents(0, 4, "Shipping Cost");
    $table->setHeaderContents(0, 5, "Total Cost");

    // Cycle through the array to produce the table data

    // Create and execute the query
    $query = "SELECT id AS `Order ID`, client_id AS `Client ID`,
                     order_time AS `Order Time`,
                     CONCAT('$', sub_total) AS `Sub Total`,
                     CONCAT('$', shipping_cost) AS `Shipping Cost`,
                     CONCAT('$', total_cost) AS `Total Cost`
                     FROM sales ORDER BY id";

    $stmt = $mysqli->prepare($query);

    $stmt->execute();

    $stmt->bind_result($orderID, $clientID, $time, $subtotal, $shipping, $total);

    // Begin at row 1 so don't overwrite the header
    $rownum = 1;

    // Format each row

    while ($stmt->fetch()) {

        $table->setCellContents($rownum, 0, $orderID);
        $table->setCellContents($rownum, 1, $clientID);
        $table->setCellContents($rownum, 2, $time);
        $table->setCellContents($rownum, 3, $subtotal);
        $table->setCellContents($rownum, 4, $shipping);
        $table->setCellContents($rownum, 5, $total);

        $rownum++;

    }

    // Output the data
    echo $table->toHTML();

    // Close the MySQL connection
    $mysqli->close();

?>

Listing 32-2Displaying MySQL Data in Tabular Format

执行清单 32-2 产生的输出与之前在图 32-1 中发现的输出相同。

分类输出

当显示查询结果时，使用方便用户的标准对信息进行排序是有意义的。例如，如果用户想要查看products表中所有产品的列表，按字母升序排序可能就足够了。但是，有些用户可能希望使用其他标准(如价格)来订购信息。通常这种机制是通过链接列表头来实现的，比如前面例子中使用的表格头。单击这些链接中的任何一个都将导致使用该标题作为标准对表数据进行排序。

要对数据进行排序，您需要创建一种机制，使查询根据所需的列对查询的数据进行排序。通常的方法是链接表格标题中的每一列。下面是如何创建这种链接的一个例子:

$orderID = "<a href='".$_SERVER['PHP_SELF']."?sort=id'>Order ID</a>";
$table->setHeaderContents(0, 0, $orderID);

按照每个标题的模式，呈现的 OrderID 链接将如下所示:

<a href='viewsales.php?sort=id'>Order ID</a>

接下来，修改查询以更改ORDER BY目标。让我们检索 GET 参数，并将其传递给上一节中找到的查询:

<?php
$columns = array('id','order_time','sub_total','shipping_cost','total_cost');

$sort = (isset($_GET['sort'])) ? $_GET['sort']: "id";
if (in_array($sort, $columns)) {
   $query = $mysqli->prepare("SELECT id AS `Order ID`, client_id AS `Client ID`,
          order_time AS `Order Time`,
          CONCAT('$', sub_total) AS `Sub Total`,
          CONCAT('$', shipping_cost) AS `Shipping Cost`,
          CONCAT('$', total_cost) AS `Total Cost`
          FROM sales ORDER BY {$sort} ASC");
}

//...
?>

重要的是不要接受排序列的任何值。这可能会在执行查询时导致错误，或者如果参数用于选择特定的列，则可能会公开不适合用户的数据。这就是上面的代码示例根据预定义的有效列列表检查排序参数的原因。不支持将绑定变量用作 order by 子句的一部分。这就是为什么通过将$sort变量直接插入查询字符串来创建语句的原因。

第一次加载脚本会导致输出按 id 排序。输出示例如图 32-3 所示。

img/314623_5_En_32_Fig3_HTML.jpg

图 32-3

按默认 id 排序的销售表输出

点击Client ID标题对输出进行重新排序。排序后的输出如图 32-4 所示。

img/314623_5_En_32_Fig4_HTML.jpg

图 32-4

按 client_id 排序的销售表输出

尽管使用服务器创建不同排序顺序的新查询很容易，但这也毫无理由地给服务器增加了额外的负载。客户端已经有了所有需要的数据。使用 JavaScript 创建本地排序系统将允许用户对表内容进行排序，而无需向服务器请求任何数据。使用 JavaScript 有许多表排序的实现。这里可以找到一个简单的: https://www.w3schools.com/howto/howto_js_sort_table.asp 。

创建分页输出

跨多个页面分离查询结果已经成为电子商务目录和搜索引擎的常见功能。这个特性不仅可以方便地增强可读性，还可以进一步优化页面加载。你可能会惊讶地发现，在你的网站上添加这个功能是一件小事。这一节演示了它是如何实现的。

这个特性部分依赖于 MySQL 的LIMIT子句。LIMIT子句用于指定起点和从SELECT查询返回的行数。它的一般语法如下所示:

LIMIT [offset,] number_rows

例如，要将返回的查询结果限制在前五行，请构造以下查询:

SELECT name, price FROM products ORDER BY name ASC LIMIT 5;

这与以下内容相同:

SELECT name, price FROM products ORDER BY name ASC LIMIT 0,5;

但是，要从结果集的第五行开始，可以使用以下查询:

SELECT name, price FROM products ORDER BY name ASC LIMIT 5,5;

因为这种语法非常方便，所以您只需要确定三个变量来创建结果分页机制:

**每页条目数:**这个值完全由你决定。或者，您可以轻松地为用户提供定制该变量的能力。这个值被传递到LIMIT子句的number_rows组件中。
**行偏移量:**该值取决于当前加载的页面。这个值是通过 URL 传递的，因此它可以被传递给LIMIT子句的offset组件。您将在下面的代码中看到如何计算这个值。
**结果集中的总行数:**您必须指定该值，因为它用于确定页面是否需要包含下一个链接。

首先，连接到 MySQL 数据库，设置每页显示的条目数，如下所示:

<?php
   $mysqli = new mysqli("localhost", "websiteuser", "secret", "corporate");
   $pagesize = 4;

接下来，一个三元运算符确定是否已经通过 URL 传递了 $_GET['recordstart']* 参数。此参数确定结果集应该开始的偏移量。如果这个参数存在，它被分配给*$ recordstart；否则， $recordstart 被设置为0。

$recordstart = (int) $_GET['recordstart'];
$recordstart = (isset($_GET['recordstart'])) ? (int)$recordstart: 0;

接下来，执行数据库查询，并使用上一节中创建的tabular_output()方法输出数据。注意，记录偏移量被设置为 $recordstart* ，要检索的条目数被设置为 *$ pagesize 。

$stmt = $mysqli->prepare("SELECT id AS `Order ID`, client_id AS `Client ID`,
          order_time AS `Order Time`,
          CONCAT('$', sub_total) AS `Sub Total`,
          CONCAT('$', shipping_cost) AS `Shipping Cost`,
          CONCAT('$', total_cost) AS `Total Cost`
          FROM sales ORDER BY id LIMIT ?, ?");

$stmt->bind_param("ii", $recordstart, $pagesize);

接下来，您必须确定可用的总行数，这可以通过从原始查询中删除LIMIT子句来实现。但是，为了优化查询，请使用count()函数，而不是检索完整的结果集:

$result = $mysqli->query("SELECT count(client_id) AS count FROM sales");
list($totalrows) = $result->fetch_row();

最后，创建上一个和下一个链接。只有当记录偏移量 $recordstart* 大于`0`时，才会创建前一个链接。只有当还有一些记录需要检索时，才会创建下一个链接，这意味着*$ recordstart+ $pagesize*必须小于 *$ totalrows 。

   // Create the 'previous' link
   if ($recordstart > 0) {
      $prev = $recordstart - $pagesize;
      $url = $_SERVER['PHP_SELF']."?recordstart=$prev";
      printf("<a href='%s'>Previous Page</a>", $url);
   }

   // Create the 'next' link
   if ($totalrows > ($recordstart + $pagesize)) {
      $next = $recordstart + $pagesize;
      $url = $_SERVER['PHP_SELF']."?recordstart=$next";
      printf("<a href='%s'>Next Page</a>", $url);
   }

样本输出如图 32-5 所示。

img/314623_5_En_32_Fig5_HTML.jpg

图 32-5

创建分页结果(每页四个结果)

如果在从一个页面导航到下一个页面期间，其他用户或进程正在更新这些表，用户可能会遇到奇怪的结果。这是因为 limit 子句使用行数，如果行数改变，结果也会改变。

列出页码

如果您有几页结果，用户可能希望以非线性顺序遍历它们。例如，用户可以选择从第一页跳到第三页，然后跳到第六页，然后再跳回第一页。令人高兴的是，为用户提供一个页码链表非常容易。在前一个例子的基础上，首先确定总页数，并将该值赋给 $totalpages 。通过将总结果行除以选择的页面大小来确定总页数，并使用ceil()函数向上舍入:

     $totalpages = ceil($totalrows / $pagesize);

接下来，确定当前页码，并将其分配给 $currentpage* 。您通过将当前记录偏移量( *$ recordstart )除以所选页面大小( $pagesize )并加 1 来确定当前页面，以说明LIMIT偏移量从0开始:

     $currentpage = ($recordstart / $pagesize ) + 1;

接下来，创建一个名为pageLinks() ,的函数，并向其传递以下四个参数:

$totalpages:结果页面总数，存储在$totalpages变量中。
$currentpage:当前页面，存储在$currentpage变量中。
$pagesize:选择的页面尺寸，存储在$pagesize变量中。
$parameter:用于通过 URL 传递记录偏移量的参数名。到目前为止，已经使用了recordstart,所以下面的例子坚持使用那个参数。

pageLinks()方法如下:

function pageLinks($totalpages, $currentpage, $pagesize, $parameter) {

   // Start at page one
   $page = 1;

   // Start at record zero
   $recordstart = 0;

   // Initialize $pageLinks
   $pageLinks = "";

   while ($page <= $totalpages) {
      // Link the page if it isn't the current one
      if ($page != $currentpage) {
         $pageLinks .= "<a href=\"{$_SERVER['PHP_SELF']}
                        ?$parameter=$recordstart\">$page</a> ";
      // If the current page, just list the number
      } else {
         $pageLinks .= "{$page} ";
      }
         // Move to the next record delimiter
         $recordstart += $pagesize;
         $page++;
   }
   return $pageLinks;
}

最后，您像这样调用函数:

echo "Pages: ".
pageLinks($totalpages, $currentpage, $pagesize, "recordstart");

图 32-6 显示了页面列表的示例输出，以及本章介绍的其他组件。

img/314623_5_En_32_Fig6_HTML.jpg

图 32-6

生成页面结果的编号列表

使用子查询查询多个表

将数据存储在多个表中是常见的做法。这使得维护数据变得容易，但是在提取数据时需要连接来自多个表的信息。考虑一个包含列、部门编号和姓名的雇员表。在这种情况下，多个雇员将具有相同的值，因为他们属于同一个部门。在这种情况下，创建一个包含 id、number 和 name 列的 department 表，然后在 employee 表中创建一个 department_id 列是有意义的。如果用新名称更新一个部门，就像更新部门表中的一行一样简单。如果所有内容都保存在 employee 表中，则必须更新具有旧部门编号和/或名称的所有行，才能进行这样的更改。将数据拆分到多个表中的概念称为规范化。它经常与 MySQL 等传统数据库系统一起使用，但当需要连接许多表时，可能会产生大型数据集的性能问题。

子查询为用户提供了查询多个表的辅助手段，使用的语法可以说比连接所需的语法更直观。本节介绍子查询，演示它们如何从应用中删除冗长的连接和繁琐的多个查询。请记住，这并不是对 MySQL 子查询功能的详尽论述；要获得完整的参考资料，请参阅 MySQL 手册。

简单地说，子查询是嵌入在另一个语句中的 SELECT 语句。例如，假设您想要创建一个支持空间功能的网站，通过向成员显示共享相同邮政编码的个人列表来鼓励拼车。members表的相关部分如下所示:

+-----+------------+-----------+--------------+-------+--------+
| id  | first_name | last_name | city         | state | zip    |
+-----+------------+-----------+--------------+-------+--------+
|   1 | Jason      | Gilmore   | Columbus     | OH    | 43201  |
|   2 | Matt       | Wade      | Jacksonville | FL    | 32257  |
|   3 | Sean       | Blum      | Columbus     | OH    | 43201  |
|   4 | Jodi       | Stiles    | Columbus     | OH    | 43201  |
+-----+------------+-----------+--------------+-------+--------+

如果没有子查询，您将需要执行两个查询或一个稍微复杂一点的查询，称为自连接。为了说明的目的，给出了执行两个查询的方法。首先，您需要检索成员的邮政编码:

$zip = SELECT zip FROM members WHERE id=1

接下来，您需要将邮政编码传递给第二个查询:

SELECT id, first_name, last_name FROM members WHERE zip='$zip'

子查询使您能够将这些任务合并到一个查询中，以便确定哪些成员与成员 Jason Gilmore 共享一个邮政编码，如下所示:

SELECT id, first_name, last_name FROM members
       WHERE zip = (SELECT zip FROM members WHERE id=1);

这将返回以下输出:

+----+------------+------------+
| id | first_name | last_name  |
+----+------------+--------- --+
|  1 | Jason      | Gilmore    |
|  3 | Sean       | Blum       |
|  4 | Jodi       | Stiles     |
+----+------------+------------+

与子查询进行比较

子查询对于执行比较也非常有用。例如，假设您在members表中添加了一个标题为daily_mileage的列，并提示成员将此信息添加到他们的个人资料中以供研究。您有兴趣知道哪些成员的旅行次数比网站上所有成员的平均次数多。以下查询做出了这一决定:

SELECT first_name, last_name FROM members WHERE
   daily_mileage > (SELECT AVG(daily_mileage) FROM members);

创建子查询时，您可以自由使用 MySQL 支持的任何比较运算符和聚合函数。

用子查询确定存在性

基于拼车主题，假设您的网站提示会员列出他们可以使用的车辆类型(例如，摩托车、货车或四门汽车)。因为一些成员可能拥有多辆车，所以创建了两个新表来映射这种关系。第一个表vehicles存储了车辆类型和描述的列表:

CREATE TABLE vehicles (
   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(25) NOT NULL,
   description VARCHAR(100),
   PRIMARY KEY(id));

第二个表member_to_vehicle，将成员 id 映射到车辆 id:

CREATE TABLE member_to_vehicle (
   member_id INT UNSIGNED NOT NULL,
   vehicle_id INT UNSIGNED NOT NULL,
   PRIMARY KEY(member_id, vehicle_id));

请记住，拼车的想法包括让没有车的会员有机会搭车，以此作为分担旅行费用的回报。因此，并非所有成员都出现在此表中，因为它只包括拥有汽车的成员。基于前面给出的members表数据，member_to_vehicle表如下所示:

+-----------+------------+
| member_id | vehicle_id |
+-----------+------------+
|    1      |      1     |
|    1      |      2     |
|    3      |      4     |
|    4      |      4     |
|    4      |      2     |
|    1      |      3     |
+-----------+------------+

现在，假设您想确定哪些成员至少拥有一辆车。将EXISTS子句与子查询结合使用，可以轻松地检索这些信息:

SELECT DISTINCT first_name, last_name FROM members WHERE EXISTS
   (SELECT member_id from member_to_vehicle WHERE
      member_to_vehicle.member_id = members.id);

这会产生以下结果:

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Jason      | Gilmore   |
| Sean       | Blum      |
| Jodi       | Stiles    |
+------------+-----------+

使用IN子句也可以产生相同的结果，如下所示:

SELECT first_name, last_name FROM members
   WHERE id IN (SELECT member_id FROM member_to_vehicle);

当子查询生成一个小数据集时，使用 IN 子句可能最快，而对于更大的结果，使用 EXISTS 最快。此外，IN 子句不能比较空值。

使用子查询执行数据库维护

子查询不仅限于选择数据；您也可以使用这个特性来管理您的数据库。例如，假设您扩展了拼车服务，为成员创建了一种方式，为其他成员的长途乘车提供金钱补偿。会员只能获得一定数量的积分，因此每次会员购买新游乐设备时，必须调整积分余额，具体如下:

UPDATE members SET credit_balance =
   credit_balance - (SELECT cost FROM sales WHERE sales_id=54);

在 PHP 中使用子查询

像前几章介绍的许多其他 MySQL 特性一样，在 PHP 应用中使用子查询是一个透明的过程；就像执行任何其他查询一样执行子查询。例如，以下示例检索与成员Jason共享相同邮政编码的个人列表:

<?php
   $mysqli = new mysqli("localhost", "websiteuser",
                                  "secret", "corporate");
   $stmt = $mysqli->prepare("SELECT id, first_name, last_name FROM members
             WHERE zip = (SELECT zip FROM members WHERE id=?)");

  $stmt->bind_param("ii", $recordstart, $pagesize);

$stmt->execute();

// Loop over data per usual

?>

用游标迭代结果集

如果你曾经使用 PHP 的fopen()函数打开过一个文件或者处理过一组数据，你会使用一个指针来执行这个任务。在前一种情况下，文件指针用于表示文件中的当前位置，在后一种情况下，指针用于遍历并可能操作每个数组值。

大多数数据库都提供了类似的遍历结果集的特性。称为游标，它允许您分别检索集合中的每一行，并对该行执行多个操作，而不用担心会影响集合中的其他行。这为什么有用？假设您的公司根据员工目前的工资和佣金率向他们提供假期奖金。然而，奖金的多少取决于多种因素，其比例安排如下:

如果工资> 60，000 美元，佣金> 5%，则奖金=工资×佣金
如果工资> 60，000 美元且佣金< = 5%，则奖金=工资× 3%
其他所有员工，奖金=工资× 7%

正如您将在本节中了解到的，使用光标可以轻松完成这项任务。

光标基础

在继续讨论如何创建和使用 MySQL 游标之前，先花点时间回顾一下关于这个特性的一些基础知识。一般来说，MySQL 游标的生命周期必须按照以下顺序进行:

用DECLARE语句声明游标。
用OPEN语句打开游标。
用FETCH语句从游标获取数据。
用CLOSE语句关闭光标。

此外，在使用游标时，您需要记住以下限制:

**服务器端:**一些数据库服务器可以同时运行服务器端和客户端游标。服务器端游标是从数据库内部管理的，而客户端游标可以由数据库外部的应用请求和控制。MySQL 只支持服务器端游标。
**只读:**光标可读写。只读游标可以从数据库中读取数据，而写游标可以更新游标指向的数据。MySQL 只支持只读游标。
**敏感:**游标可以敏感，也可以不敏感。敏感游标指的是在数据库中找到的实际数据，而不敏感游标指的是在创建游标时创建的数据的临时副本。MySQL 只支持敏感游标。
**仅向前:**高级游标实现可以向前和向后遍历数据集，跳过记录，并执行各种其他导航任务。目前，MySQL 游标是只进的，这意味着您只能向前遍历数据集。此外，MySQL 游标一次只能向前移动一条记录。

创建光标

在使用游标之前，必须使用DECLARE语句创建(声明)它。该声明指定了游标的名称以及它将处理的数据。其原型如下:

DECLARE cursor_name CURSOR FOR select_statement

例如，要声明本节前面讨论的奖金计算游标，请执行以下声明:

DECLARE calc_bonus CURSOR FOR SELECT id, salary, commission FROM employees;

声明游标后，必须将其打开才能使用。

打开光标

虽然游标的查询是在DECLARE语句中定义的，但是直到游标被打开，查询才真正执行。您可以使用OPEN语句来实现这一点:

OPEN cursor_name

例如，要打开本节前面创建的calc_bonus光标，请执行以下命令:

OPEN calc_bonus;

使用光标

使用光标指向的信息是通过FETCH语句完成的。其原型如下:

FETCH cursor_name INTO varname1 [, varname2...]

例如，下面的存储过程(存储过程在第二十九章中介绍过)，calculate_bonus()，获取光标指向的 id、salary 和 commission 列，执行必要的比较，最后插入适当的奖金:

DELIMITER //

CREATE PROCEDURE calculate_bonus()
BEGIN

   DECLARE emp_id INT;
   DECLARE sal DECIMAL(8,2);
   DECLARE comm DECIMAL(3,2);
   DECLARE done INT;

   DECLARE calc_bonus CURSOR FOR SELECT id, salary, commission FROM employees;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

   OPEN calc_bonus;

   BEGIN_calc: LOOP

      FETCH calc_bonus INTO emp_id, sal, comm;

      IF done THEN
         LEAVE begin_calc;
      END IF;

      IF sal > 60000.00 THEN
         IF comm > 0.05 THEN
            UPDATE employees SET bonus = sal * comm WHERE id=emp_id;
         ELSEIF comm <= 0.05 THEN
            UPDATE employees SET bonus = sal * 0.03 WHERE id=emp_id;
         END IF;
      ELSE
         UPDATE employees SET bonus = sal * 0.07 WHERE id=emp_id;
      END IF;

   END LOOP begin_calc;

   CLOSE calc_bonus;

END//

DELIMITER ;

关闭光标

使用完游标后，应该用CLOSE语句关闭它，以回收潜在的大量系统资源。要关闭本节前面打开的calc_bonus光标，执行以下操作:

   CLOSE calc_bonus;

关闭游标非常重要，MySQL 会在离开声明游标的语句块时自动关闭游标。然而，为了清楚起见，您应该努力使用CLOSE显式地关闭它。

在 PHP 中使用光标

像使用存储过程和触发器一样，在 PHP 中使用游标是一个相当简单的过程。执行之前创建的calculate_bonus()存储过程(包含calc_bonus光标):

<?php

  // Instantiate the mysqli class
  $db = new mysqli("localhost", "websiteuser", "secret", "corporate");

  // Execute the stored procedure
  $result = $db->query("CALL calculate_bonus()");

?>

PHP 也可以用来创建存储过程。数据库中的任何模式对象都可以用 SQL 语句创建。就像通过发出用于 select、insert、update 和 delete 的 SQL 语句与数据交互一样，您也可以使用 create 语句创建对象。当应用安装在系统上，使用 PHP 为应用创建初始数据库模式时，这很有用。

摘要

本章介绍了开发数据驱动应用时会遇到的许多常见任务。向您介绍了一种以表格格式输出数据结果的简便方法，然后您学习了如何为每个输出数据行添加可操作的选项。通过向您展示如何基于给定的表字段对输出进行排序，进一步扩展了这一策略。您还了解了如何通过创建链接页面列表将查询结果分布在多个页面上，使用户能够以非线性方式浏览结果。

下一章将介绍 MySQL 的数据库索引和全文搜索功能，并演示如何使用 PHP 执行基于 web 的数据库搜索。

三十三、索引和搜索

第二十五章介绍了PRIMARY和UNIQUE键的用途，定义了每个键的作用，并向你展示了如何将它们整合到你的表格结构中。然而，索引在数据库开发中起着如此重要的作用，如果不详细讨论这个主题，这本书将是非常不完整的。本章涵盖了以下主题:

**数据库索引:**本章的前半部分介绍了一般的数据库索引术语和概念，并讨论了主索引、唯一索引、普通索引和全文 MySQL 索引。
**基于表单的搜索:**本章的后半部分将向您展示如何创建支持 PHP 的搜索接口，用于查询新索引的 MySQL 表。

数据库索引

索引是表列的有序(或索引)子集，每个行条目指向其对应的表行。一般来说，在 MySQL 数据库开发策略中引入索引有三个好处:

**查询优化:**数据按照输入的顺序存储在表中。但是，这个顺序可能与您想要访问它的顺序不一致。例如，假设您批量插入根据 SKU 订购的产品列表。你的在线商店访问者可能会根据名称搜索这些产品。因为当目标数据被排序(在本例中是按字母顺序)时，数据库搜索可以最有效地执行，所以除了将被频繁搜索的任何其他列之外，索引产品的名称是有意义的。
**唯一性:**通常，需要一种方法来标识一个数据行，该方法基于某个值或一组已知对该行唯一的值。例如，考虑一个存储雇员信息的表。该表可能包括关于每个雇员的名和姓、电话号码和社会保险号的信息。虽然两个或两个以上的雇员可能共用同一个名字(例如，John Smith)或共用同一个电话号码(例如，如果他们共用一个办公室)，但是您知道没有两个人拥有相同的社会保险号，从而保证每一行的唯一性。
**文本搜索:**由于有了一个称为全文索引的功能，因此可以优化对位于任何索引字段中的大量文本的搜索。

这些优势的实现要归功于四种类型的索引:主索引、惟一索引、普通索引和全文索引。本节将介绍每种类型。

主键索引

主键索引是关系数据库中最常见的索引类型。由于主键的唯一性，它用于唯一地标识每一行。因此，键必须是由行表示的实体唯一拥有的值，或者是其他值，如数据库在插入行时创建的自动递增的整数值。因此，不管先前存在的行是否随后被删除，每一行都将有一个唯一的主索引。例如，假设您想为公司的 IT 部门创建一个有用的在线资源数据库。用于存储这些书签的表格可能如下所示:

CREATE TABLE bookmarks (
   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(75) NOT NULL,
   url VARCHAR(200) NOT NULL,
   description MEDIUMTEXT NOT NULL,
   PRIMARY KEY(id));

因为id列随着每次插入自动递增(从 1 开始),所以bookmarks表格不可能包含多个包含完全相同单元格的行。例如，考虑以下三个查询:

INSERT INTO bookmarks (name, url, description)
       VALUES("Apress", "www.apress.com", "Computer books");
INSERT INTO bookmarks (name, url, description)
       VALUES("Google", "www.google.com", "Search engine");
INSERT INTO bookmarks (name, url, description)
       VALUES("W. Jason Gilmore", "www.wjgilmore.com", "Jason's website");

执行这三个查询并检索表会产生以下输出:

+-------+------------------+-------------------+-----------------+
| id    | name             | url               | description     |
+------ +------------------+-------------------+-----------------+
|     1 | Apress           | www.apress.com    | Computer books  |
|     2 | Google           | www.google.com    | Search engine   |
|     3 | W. Jason Gilmore | www.wjgilmore.com | Jason's website |
+-------+------------------+-------------------+-----------------+

注意id列是如何随着每次插入而递增的，以确保行的唯一性。

注意

每个表只能有一个自动递增的列，并且该列必须被指定为主键。此外，任何被指定为主键的列都不能保存NULL值；即使没有明确声明为NOT NULL，MySQL 也会自动赋这个特质。主键不需要添加 NOT NULL 约束。

创建一个允许开发人员推测它所代表的行的一些信息的主索引通常是不明智的。并举例说明了原因。假设您决定使用 URL，而不是使用整数值作为bookmarks表的主索引。做出这样一个决定的影响应该是显而易见的。首先，如果 URL 由于商标问题或收购而改变，会发生什么？即使是社会安全号码，这个曾经被认为是独一无二的价值，也会因为身份盗窃的影响而改变。省去麻烦，总是使用主索引，它不提供对它所代表的数据的洞察；它应该是一种自主工具，唯一的目的是确保能够唯一地识别数据记录。主键不是必需的，但是如果您想引用其他表中的记录，主键是最好的方法。

唯一索引

像主索引一样，唯一索引可以防止创建重复值。但是，区别在于每个表只允许一个主索引，而支持多个唯一索引。考虑到这种可能性，再次考虑上一节的bookmarks表。虽然可以想象两个站点可以共享同一个名称——例如，“伟大的 PHP 资源”——但是重复 URL 是没有意义的。这听起来像是一个理想的唯一索引:

CREATE TABLE bookmarks (
   id INT UNSIGNED AUTO_INCREMENT,
   name VARCHAR(75) NOT NULL,
   url VARCHAR(200) NOT NULL UNIQUE,
   description MEDIUMTEXT NOT NULL,
   PRIMARY KEY(id));

如上所述，在给定的表中，可以将多个字段指定为惟一的。例如，假设您想要防止链接存储库的贡献者在插入新网站时重复指定非描述性名称(例如，“酷站点”)。再次返回到书签表，将 name 列定义为 unique:

CREATE TABLE bookmarks (
   id INT UNSIGNED AUTO_INCREMENT,
   name VARCHAR(75) NOT NULL UNIQUE,
   url VARCHAR(200) NOT NULL UNIQUE,
   description MEDIUMTEXT NOT NULL,
   PRIMARY KEY(id));

您还可以指定多列唯一索引。例如，假设您希望允许您的贡献者插入重复的 URL 值，甚至是重复的name值，但是您不希望出现重复的name和 URL 组合。您可以通过创建多列唯一索引来实施此类限制。重访最初的bookmarks表:

CREATE TABLE bookmarks (
   id INT UNSIGNED AUTO_INCREMENT,
   name VARCHAR(75) NOT NULL,
   url VARCHAR(200) NOT NULL,
   UNIQUE(name, url),
   description MEDIUMTEXT NOT NULL,
   PRIMARY KEY(id));

给定这种配置，下面的name和 URL 值对可以同时驻留在同一个表中:

Apress site, https://www.apress.com
Apress site, https://www.apress.com/us/blog
Blogs, https://www.apress.com
Apress blogs, https://www.apress.com/us/blog

然而，尝试多次插入这些组合将导致错误，因为重复的组合name和 URL 是非法的。

正常索引

您通常希望优化数据库的能力，使其能够根据列标准检索行，而不是那些被指定为主要或唯一的标准。最有效的方法是对列进行索引，使数据库能够以最快的方式查找值。这些指标通常被称为正常指标或普通指标。MySQL 为它们提供了“索引”类型。

单列普通索引

如果表中的某一列将成为大量选择查询的焦点，则应该使用单列普通索引。例如，假设一个包含雇员信息的表由四列组成:唯一的行 ID、名、姓和电子邮件地址。您知道大多数搜索都是针对员工的姓氏或电子邮件地址的。您应该为姓氏创建一个普通索引，为电子邮件地址创建一个唯一索引，如下所示:

CREATE TABLE employees (
   id INT UNSIGNED AUTO_INCREMENT,
   firstname VARCHAR(100) NOT NULL,
   lastname VARCHAR(100) NOT NULL,
   email VARCHAR(100) NOT NULL UNIQUE,
   INDEX (lastname),
   PRIMARY KEY(id));

基于这一思想，MySQL 提供了创建部分列索引的特性，其思想是给定列的前 N 个字符通常足以确保唯一性，其中 N 在索引创建语句中指定。创建部分列索引需要较少的磁盘空间，并且比索引整个列要快得多，尤其是在插入数据时。回顾前面的例子，您可以想象使用姓氏的前五个字符足以确保准确的检索:

CREATE TABLE employees (
   id INT UNSIGNED AUTO_INCREMENT,
   firstname VARCHAR(100) NOT NULL,
   lastname VARCHAR(100) NOT NULL,
   email VARCHAR(100) NOT NULL UNIQUE,
   INDEX (lastname(5)),
   PRIMARY KEY(id));

然而，选择查询通常是包含多个列的函数。毕竟，更复杂的表可能需要由几个列组成的查询才能检索到所需的数据。通过建立多列普通索引，可以大大减少这类查询的运行时间。

多列普通索引

当您知道在检索查询中将经常一起使用许多指定的列时，建议使用多列索引。MySQL 的多列索引方法是基于一种叫做最左边前缀的策略。最左边的前缀表示任何包含列 A、B 和 C 的多列索引都将提高涉及以下列组合的查询的性能:

甲、乙、丙
甲，乙
A

以下是创建多列 MySQL 索引的方法:

CREATE TABLE employees (
   id INT UNSIGNED AUTO_INCREMENT,
   lastname VARCHAR(100) NOT NULL,
   firstname VARCHAR(100) NOT NULL,
   email VARCHAR(100) NOT NULL UNIQUE,
   INDEX name (lastname, firstname),
   PRIMARY KEY(id));

这将创建两个索引(除了主键索引之外)。第一个是电子邮件地址的唯一索引。第二个是多列索引，由两列组成，lastname,和firstname。这很有用，因为当查询涉及以下任何列组合时，它可以提高搜索速度:

lastname，firstname
lastname

为了说明这一点，以下查询将受益于多列索引:

SELECT email FROM employees WHERE lastname="Geronimo" AND firstname="Ed";
SELECT lastname FROM employees WHERE lastname="Geronimo";

以下查询不会带来好处:

SELECT lastname FROM employees WHERE firstname="Ed";

为了提高后一个查询的性能，您需要为firstname列创建单独的索引。

全文索引

全文索引为搜索存储在CHAR、VARCHAR或TEXT数据类型中的文本提供了一种有效的方法。在深入研究示例之前，先了解一下 MySQL 对这个索引的特殊处理的背景。在 MySQL 5.6 之前，这个特性只有在使用 MyISAM 存储引擎时才可用。现在 Innodb 引擎也支持它。

因为 MySQL 假设将实现全文搜索来筛选大量的自然语言文本，所以它提供了一种检索数据的机制，这种机制可以产生最符合用户期望的结果。更具体地说，如果用户使用类似于 Apache 是世界上最受欢迎的 web 服务器这样的字符串进行搜索，那么是而是这两个词在确定结果相关性方面应该起不到什么作用。事实上，MySQL 将可搜索的文本分割成单词，默认情况下会删除少于四个字符的单词。在本节的后面，您将了解如何修改这种行为。

创建全文索引与创建其他类型的索引非常相似。例如，重新访问本章前面创建的bookmarks表，使用全文变体索引其description列:

CREATE TABLE bookmarks (
   id INT UNSIGNED AUTO_INCREMENT,
   name VARCHAR(75) NOT NULL,
   url VARCHAR(200) NOT NULL,
   description MEDIUMTEXT NOT NULL,
   FULLTEXT(description),
   PRIMARY KEY(id));

除了典型的主索引之外，这个示例还创建了一个由description列组成的全文索引。出于演示目的，表 33-1 给出了在bookmarks表中找到的数据。

表 33-1

示例表数据

编号

名字

全球资源定位器(Uniform Resource Locator)

描述

| | --- | --- | --- | --- | | one | Python.org | https://www.python.org | Python 官方网站 | | Two | MySQL 手动 | https://dev.mysql.com/doc | MySQL 参考手册 | | three | 阿帕奇网站 | https://httpd.apache.org | 包括 Apache 2 手册 | | four | PHP:超文本 | https://www.php.net | PHP 官方网站 | | five | 阿帕奇周 | http://www.apacheweek.com | 提供专门的 Apache 2 部分 |

创建全文索引与创建其他类型的索引非常相似，而基于全文索引的检索查询则不同。当基于全文索引检索数据时，SELECT查询使用两个特殊的 MySQL 函数，MATCH()和AGAINST()。使用这些函数，可以对全文索引执行自然语言搜索，如下所示:

SELECT name,url FROM bookmarks WHERE MATCH(description) AGAINST('Apache 2');

返回的结果如下所示:

+-------------+----------------------------+
| name        | url                        |
+------------------------------------------+
| Apache site | https://httpd.apache.org   |
| Apache Week | http://www.apacheweek.com  |
+-------------+----------------------------+

这列出了在description列中找到 Apache 的行，按照相关性最高的顺序排列。记住 2 因其长度而被忽略。为了说明这一点，您可以从第 3 行和/或第 5 行的 description 列中删除数字 2，然后再次运行相同的查询。你会得到同样的结果。当在WHERE子句中使用MATCH()时，根据返回的行与搜索字符串的匹配程度来定义相关性。或者，可以将函数合并到查询体中，返回匹配行的加权分数列表；分数越高，相关性越大。下面是一个例子:

SELECT MATCH(description) AGAINST('Apache 2') FROM bookmarks;

执行时，MySQL 将搜索bookmarks表中的每一行，计算每一行的相关性值，如下所示:

+----------------------------------------+
| match(description) against('Apache 2') |
+----------------------------------------+
|                                      0 |
|                                      0 |
|                       0.57014514171969 |
|                                      0 |
|                       0.38763393589171 |
+----------------------------------------+

您还可以利用一个称为查询扩展的特性，这在用户做出某些假设时特别有用，否则这些假设可能不需要内置到应用的搜索逻辑中。例如，假设用户正在搜索词语足球。从逻辑上来说，包括匹兹堡钢人队、俄亥俄州七叶树队和伍迪·海耶斯等术语的行也会引起他的兴趣。为了弥补这一点，您可以包含WITH QUERY EXPANSION子句，该子句将首先检索包含词语 football 的所有行，然后再次搜索所有行，这次检索包含在第一组结果的行中找到的任何单词的所有行。

因此，回到示例，如果在第一次搜索中找到的一行包含术语足球和匹兹堡，那么在第二次搜索中将检索到包含匹兹堡的一行，即使它不包含术语足球。虽然这肯定会导致更彻底的搜索，但它可能会产生意想不到的副作用，例如返回一行，因为其中包含术语 Pittsburgh ，但与足球毫无关系。

还可以执行面向布尔的全文搜索。本节稍后将介绍该功能。

停止言语

如前所述，默认情况下，MySQL 会忽略任何少于四个字符的关键字。这些词，以及那些在 MySQL 服务器内置的预定义列表中找到的词，被称为停用词，或者应该被忽略的词。通过修改以下 MySQL 变量，您可以对停用词行为进行很好的控制:

不符合特定长度的单词可以作为停用词。您可以使用此参数指定所需的最小长度。如果更改这个参数，您需要重启 MySQL 服务器守护进程并重建索引。
ft_max_word_len:你也可以将停用词定义为任何超过特定长度的词。您可以使用此参数指定该长度。如果更改这个参数，您需要重启 MySQL 服务器守护进程并重建索引。
ft_stopword_file:分配给该参数的文件包含一个 544 个英文单词的列表，这些单词会自动从任何搜索关键字中过滤出来。通过将该参数设置为所请求列表的路径和名称，可以将其更改为指向另一个列表。或者，如果您可以选择重新编译 MySQL 源代码，您可以通过打开myisam/ft_static.c并编辑预定义的列表来修改这个列表。在第一种情况下，您需要重启 MySQL 并重建索引，而在第二种情况下，您需要根据您的规范重新编译 MySQL 并重建索引。

可以使用以下命令显示这些和其他与停用字相关的变量的默认值:

show variables where variable_name like 'ft_%';
+--------------------------+----------------+
| Variable_name            | Value          |
+--------------------------+----------------+
| ft_boolean_syntax        | + -><()~*:""&| |
| ft_max_word_len          | 84             |
| ft_min_word_len          | 4              |
| ft_query_expansion_limit | 20             |
| ft_stopword_file         | (built-in)     |
+--------------------------+----------------+

注意

MySQL 索引的重建是通过命令 REPAIR TABLE table_name QUICK完成的，其中table_name表示您想要重建的表的名称。

默认情况下，停用词被忽略的原因是，它们在普通语言中出现得过于频繁，可能被认为是不相关的。这可能会产生意想不到的效果，因为 MySQL 还会自动过滤掉在超过 50%的记录中存在的任何关键字。例如，考虑一下如果所有贡献者都添加了一个与 Apache Web 服务器相关的 URL，并且都在描述中包含单词Apache会发生什么。执行全文搜索来查找术语Apache将会产生意想不到的结果:没有找到记录。如果您正在处理一个小的结果集，或者由于其他原因需要忽略这种默认行为，请使用 MySQL 的布尔全文搜索功能。

布尔全文搜索

布尔全文搜索对搜索查询提供了更细粒度的控制，允许您明确地确定哪些词应该出现在候选结果中，哪些词不应该出现在候选结果中(但是，在执行布尔全文搜索时，停用词列表仍然适用)。例如，布尔全文搜索可以检索包含单词 Apache 的行，但不能检索包含单词 Navajo 、 Woodland 或 Shawnee 的行。同样，您可以确保结果至少包含一个关键字、所有关键字或不包含关键字；您可以自由地对返回的结果进行大量的过滤控制。这种控制是通过许多公认的布尔运算符来维护的。表 33-2 中列出了其中几个操作符。

表 33-2

全文搜索布尔运算符

操作员

描述

| | --- | --- | | + | 前导加号确保每个结果行中都出现随后的单词。 | | – | 前导减号确保随后的单词不会出现在返回的任何行中。 | | * | 尾部星号允许关键字变化，前提是变化以前面单词指定的字符串开始。 | | " " | 双引号可以确保结果行包含括起来的字符串，与输入的字符串完全一样。 | | < > | 前面的大于号和小于号分别用于减少和增加后续单词与搜索排名的相关性。 | | ( ) | 括号用于将单词分组为子表达式。 |

考虑几个例子。第一个例子返回包含 Apache 的行，但不包含 manual :

SELECT name,url FROM bookmarks WHERE MATCH(description)
   AGAINST('+Apache -manual' in boolean mode);

下一个示例返回包含单词 Apache 的行，但不包含 Shawnee 或 Navajo 的行:

SELECT name, url FROM bookmarks WHERE MATCH(description)
   AGAINST('+Apache -Shawnee -Navajo' in boolean mode);

最后一个示例返回包含 web 和脚本，或者 php 和脚本的行，但是 web 脚本的排名低于 php 脚本:

SELECT name, url FROM bookmarks WHERE MATCH(description)
   AGAINST('+(<web >php) +scripting');

请注意，只有当您将ft_min_word_len变量降低到3时，最后一个示例才会起作用。

只要数据集大小合理，在关系数据库上执行的搜索操作就可以工作，而关系数据库从来不是为搜索而设计或优化的。其他系统如 ElasticSearch 更适合搜索大量结构化或非结构化数据。

索引最佳实践

下面的列表提供了一些在将索引合并到数据库开发策略中时应该始终牢记的提示:

仅索引那些在WHERE和ORDER BY子句中需要的列。大量索引列只会导致不必要的硬盘空间消耗，并且在更改表信息时实际上会降低性能。索引表上的性能会下降，因为每次记录更改时，索引都必须更新。
如果你创建了一个像INDEX(firstname, lastname)这样的索引，不要创建INDEX(firstname)，因为 MySQL 能够搜索索引前缀。但是，请记住，只有前缀是相关的；这种多列索引不适用于仅针对lastname的搜索。
使用--log-long-format选项记录不使用索引的查询。然后，您可以检查该日志文件，并相应地调整您的查询。
EXPLAIN语句帮助您确定 MySQL 将如何执行查询，向您展示如何以及以什么顺序连接表。这对于确定如何编写优化的查询以及是否应该添加索引非常有用。请查阅 MySQL 手册，了解关于EXPLAIN语句的更多信息。

基于表单的搜索

使用超链接轻松深入网站的能力是使 Web 成为如此受欢迎的媒体的行为之一。然而，随着网站和 Web 的规模呈指数级增长，基于用户提供的关键字执行搜索的能力从便利发展为必要。本节提供了几个例子，展示了构建搜索 MySQL 数据库的搜索界面是多么容易。

执行简单搜索

许多有效的搜索界面只包含一个文本字段。例如，假设您希望为人力资源部门提供按姓氏查找员工联系信息的功能。为了实现这个任务，查询将检查在employees表中找到的lastname列。图 33-1 显示了这样做的示例界面。

img/314623_5_En_33_Fig1_HTML.jpg

图 33-1

一个简单的搜索界面

清单 33-1 实现了这个接口，将请求的姓氏传递给搜索查询。如果返回的行数大于零，则输出每一行；否则，会提供适当的消息。

<p>
Search the employee database:<br />
<form action="search.php" method="post">
   Last name:<br>
   <input type="text" name="lastname" size="20" maxlength="40" value=""><br>
   <input type="submit" value="Search!">
</form>
</p>

<?php

   // If the form has been submitted with a supplied last name
   if (isset($_POST['lastname'])) {

      // Connect to server and select database

      $db = new mysqli("localhost", "websiteuser", "secret", "chapter36");

      // Query the employees table
      $stmt = $db->prepare("SELECT firstname, lastname, email FROM employees
                            WHERE lastname like ?");

      $stmt->bind_param('s', $_POST['lastname']);

      $stmt->execute();

      $stmt->store_result();

      // If records found, output them
      if ($stmt->num_rows > 0) {

        $stmt->bind_result($firstName, $lastName, $email);

        while ($stmt->fetch())
          printf("%s, %s (%s)<br />", $lastName, $firstName, $email);
      } else {
         echo "No results found.";
      }

   }
?>

Listing 33-1Searching the Employee Table (search.php

)

因此，在搜索界面中输入Gilmore将会返回如下结果:

Gilmore, Jason (gilmore@example.com)

扩展搜索功能

虽然这个简单的搜索界面是有效的，但是如果用户不知道员工的姓氏会怎么样呢？如果用户知道另一条信息，比如电子邮件地址，该怎么办？清单 33-2 修改了原来的例子，这样它就可以处理来自图 33-2 中描述的表单的输入。

img/314623_5_En_33_Fig2_HTML.jpg

图 33-2

修订后的搜索表单

<p>
Search the employee database:<br>
<form action="search2.php" method="post">
   Keyword:<br>
   <input type="text" name="keyword" size="20" maxlength="40" value=""><br>
   Field:<br>
   <select name="field">
      <option value="">Choose field:</option>
      <option value="lastname">Last Name</option>
      <option value="email">E-mail Address</option>
      </select>
   <input type="submit" value="Search!" />
</form>
</p>

<?php
   // If the form has been submitted with a supplied keyword
   if (isset($_POST['field'])) {

      // Connect to server and select database
      $db = new mysqli("localhost", "websiteuser", "secret", "chapter36");

      // Create the query
      if ($_POST['field'] == "lastname") {
         $stmt = $db->prepare("SELECT firstname, lastname, email
                               FROM employees WHERE lastname like ?");
      } elseif ($_POST['field'] == "email") {
         $stmt = $db->prepare("SELECT firstname, lastname, email
                               FROM employees WHERE email like ?");
      }

      $stmt->bind_param('s', $_POST['keyword']);

      $stmt->execute();

      $stmt->store_result();

      // If records found, output them
      if ($stmt->num_rows > 0) {

        $stmt->bind_result($firstName, $lastName, $email);

        while ($stmt->fetch())
          printf("%s, %s (%s)<br>", $lastName, $firstName, $email);

      } else {
        echo "No results found.";
      }
   }
?>

Listing 33-2Extending the Search Capabilities (searchextended.php

)

因此，将该字段设置为E-mail Address并输入gilmore@example.com作为关键字，将会返回类似如下的结果:

Gilmore, Jason (gilmore@example.com)

当然，在这两个示例中，您需要放置额外的控件来净化数据，并确保用户在提供无效输入时收到详细的响应。然而，基本的搜索过程应该是显而易见的。

执行全文搜索

执行全文搜索实际上与执行任何其他选择查询没有什么不同；只有查询看起来不同，这个细节对用户是隐藏的。例如，清单 33-3 实现了图 33-3 中描述的搜索接口，演示了如何搜索bookmarks表的description列。

img/314623_5_En_33_Fig3_HTML.jpg

图 33-3

全文搜索界面

<p>
Search the online resources database:<br>
<form action="fulltextsearch.php" method="post">
   Keywords:<br>
   <input type="text" name="keywords" size="20" maxlength="40" value=""><br>
   <input type="submit" value="Search!">
</form>
</p>

<?php

   // If the form has been submitted with supplied keywords
   if (isset($_POST['keywords'])) {

      // Connect to server and select database
      $db = new mysqli("localhost", "websiteuser", "secret", "chapter36");

      // Create the query
      $stmt = $db->prepare("SELECT name, url FROM bookmarks
                          WHERE MATCH(description) AGAINST(?)");

      $stmt->bind_param('s', $_POST['keywords']);

      $stmt->execute();

      $stmt->store_result();

      // Output retrieved rows or display appropriate message
      if ($stmt->num_rows > 0) {

        $stmt->bind_result($url, $name);

        while ($result->fetch)
          printf("<a href='%s'>%s</a><br />", $url, $name);
      } else {
          printf("No results found.");
      }
   }
?>

Listing 33-3Implementing Full-Text Search

为了扩展用户的全文搜索能力，可以考虑提供一个演示 MySQL 布尔搜索特性的帮助页面。

摘要

表索引是优化查询的可靠方法。本章介绍了表索引，并向您展示了如何创建主索引、唯一索引、普通索引和全文索引。然后，您了解到创建支持 PHP 的搜索界面来查询 MySQL 表是多么容易。

下一章将介绍 MySQL 的事务处理特性，并向您展示如何将事务整合到您的 web 应用中。