3.7 加注释

　　允许在SQL 代码中使用注释。这对于说明存放在文件中的查询很有用处。可用两个方式编写注释。以“ #”号开头直到行尾的所有内容都认为是注释。另一种为C 风格的注释。即，以“/ *”开始，以“* /”结束的所有内容都认为是注释。C 风格的注释可跨多行，如：

　　自MySQL3.23 版以来，可在C 风格的注释中“隐藏” MySQL特有的关键字，注释以“/ * !”而不是以“ / *”起头。MySQL查看这种特殊类型注释的内部并使用这些关键字，但其他将这些关键字作为注释的一部分忽略。这样有助于编写由MySQL执行时利用MySQL特有功能的代码，而且该代码也可以不用修改就用于其他数据库服务器。下面的两条语句对于非MySQL的数据库服务器是等价的，但如果是MySQL服务器，将在第二条语句中执行一个INSERT DELAYED 操作：

　　自MySQL3.23.3 以来，除了刚才介绍的注释风格外，还可以用两个短划线和一个空格（“-- ”）来开始注释；从这两个短划线到行的结束的所有内容都作为注释处理。有的数据库以双短划线作为注释的起始。MySQL也允许这样，但需要加一个空格以免产生混淆。例如，带有如像5--7 这样的表达式的语句有可能被认为包含一个注释，但不可能写5-- 7这样的表达式，因此，这是一个很有用的探索。然而，这仅仅是一个探索，最好不用这种风格的注释。

　　在这个事件序列结束时，您已经卖掉了5 件衬衫，但库存数目却是45 而不是4 2。是如果在一条语句中查看库存而在另一条语句中更新其值，这是一个多语句的事务处理。第二条语句中所进行的活动取决于第一条语句中检索出的值。但是如果在重叠的时间范围内出现独立的事务处理，则每个事务处理的语句会纠缠在一起，并且互相干扰。在事务处理型的中，每个销售人员的语句可作为一个事务处理执行，这样，销售人员2 的语句在销售人员1的语句完成之前不会被执行。在中，可用两种方法达到这个目的：

　　■ 方法1：作为一个整体执行一组语句。可利用LOCK TABLES 和UNLOCK TABLES将语句组织在一起，并将它们作为一个原子单元执行：锁住所需使用的表，发布查询，然后释放这些锁。这样阻止了其他人在您锁住这些表时使用它们。利用表同步，库存情况如下所示：

　　t1销售人员1卖出3件衬衫

　　t2 销售人员1请求一个锁并检索当前衬衫计数（47）

　　LOCK TABLES inventory WRITE

　　SELECT quantity FROM inventory WHERE item = "shirt"

　　t3 销售人员2卖出2件衬衫

　　t4 销售人员2试图取得一个锁：这被阻塞，因为销售人员1已经占住了锁：

　　LOCK TABLES inventory WRITE

　　t5 销售人员1计算库存的新数目为47 - 3 = 44 并设置衬衫计数为44，然后释放锁：

　　UPDATE inventory SET quantity = 44 WHERE item = "shirt"

　　UNLOCK TABLES

　　t6 现在销售人员2的锁请求成功。销售人员2检索当前衬衫计数（ 44）

　　SELECT quantity FROM inventory WHERE item = "shirt"

　　t7 销售人员2计算库存的新数目为44 - 2 = 42，设置衬衫计数为4 2，然后释放锁：

　　UPDATE inventory SET quantity = 42 WHERE item = "shirt"

　　UNLOCK TABLES

　　现在来自两个事务处理的语句不混淆了，并且库存衬衫数也正确进行了设置。我们在这里使用了一个WRITE 锁，因为我们需要修改inventory 表。如果只是读取表，可使用READ 锁。当您正在使用表时，这个锁允许其他客户机读取表。在刚才举的例子中，销售人员2大概不会注意到执行速度上的差异，因为其中的事务处理都很短，执行速度很快。但是，作为一个具有普遍意义的规则，那就是应该尽量避免长时间地锁住表。

　　如果您正在使用多个表，那么在您执行成组查询之前，必须锁住他们。如果只是从某个特定的表中读取数据，那么只需给该表加一个读出锁而不是写入锁。假如有一组查询，其中想对inventory 表作某些更改，而同时需要从customer 表中读取某些数据。在此情形下，inventory 表上需要一个写入锁，而customer 表上需要一个读出锁：

　　LOCK TABLES inventory WRITE,customer READ

　　...

　　UNLOCK TABLES

　　这里要求您自己对表进行加锁和解锁。支持事务处理的系统将会自动完成这些工作。但是，在作为一个整体执行的分组语句方面，无论在是否支持事务处理的数据库中都是相同的。

　　■ 方法2：使用相对更新而不是绝对更新。要解决来自多个事务处理的语句混淆，应消除语句之间的依赖性。虽然这样做并不都总是可能的，它只针对我们的库存例子可行。对于方法1中所用的库存更新方法，其中事务处理需要查看当前库存数目，并依据销售衬衫的数目计算新值，然后更新衬衫的数目。有可能通过相对于当前衬衫数目进行计数更新，在一个步骤中完成工作。如下所示：

　　t1销售人员1卖出3件衬衫

　　t2 销售人员1将衬衫计数减3：

　　UPDATE inventory SET quantity = quantity - 3 WHERE item = "shirt"

　　t3 销售人员2卖出2件衬衫

　　t4 销售人员2将衬衫计数减2：

　　UPDATE inventory SET quantity = quantity - 2 WHERE item = "shirt"

　　因此，这里根本不需要多条语句的事务处理，从而也不需要锁住表以模拟事务处理功能。如果所使用的事务处理类型与这里类似，那么就可以不用事务处理也能完成工作。上面的例子说明了在特殊情形下怎样避免对事务处理功能的需求。但这并不是说不存在那种确实需要事务处理功能的场合。典型的例子是财务转账，其中钱从一个账户转到另一个账户。假如Bill 给Bob 开了一张$100 的支票，Bob 兑现了这张支票。Bill 的户头上应该减掉$100 而Bob 的户头上应该增加相同数量的钱：

　　UPDATE account SET balance = balance -100 WHERE name = "Bill"

　　UPDATE account SET balance = balance +100 WHERE name = "Bob"

　　如果在这两条语句执行中，系统发生了崩溃，此事务处理就不完整了。具有真正事务处理和提交/回退功能的数据库系统能够处理这种情况（至少从理论上能够处理。您可能仍然必须判断遇到了哪些事务处理并重新发布它们，但至少不会担心事务只处理了一半）。在中，系统崩溃时可通过检查更新日志来判断事务处理的状态，虽然这可能需要对日志进行某种手工检查。

　　■ 外部键和引用完整性。外部键允许定义一个表中的键与另一个表中的键相关，而引用完整性允许放置对包含外部键的表可以做什么的约束。例如， samp_db 样例数据库的score 表中包含一个student_id 列，我们用它来将学分记录关联到student 表中的学生。score.student_id 将定义为支持此概念的数据库中的一个外部键，我们将在其上加上一条约束，使不能为student 表中不存在的学生输入学分记录。此外，应该允许级联删除，以便如果某个学生从student 表中被删除后，该学生的任何学分记录将会自动地从

　　score 表中删除。

　　外部键有助于保持数据的一致性，而且还提供了某种方便的手段。不支持外部键的原因主要是由于它对的实现与维护有负作用。（MySQL参考指南详细列出了这些原因。）注意，对于外部键的这种看法与其他数据库文献中的看法有些不同，有的数据库文献通常将它们描述成“基本的”。MySQL的开发者并不赞同这个观点。如果您赞成，那么最好是考虑采用其他提供外部键支持的数据库。如果数据具有特别复杂的关系，您可能不希望担负在应用程序中实现这些相关性的工作。（即使这样做的工作量要比增加几个额外的DELETE 语句的工作量要稍少一些。）除了在一定程度上能够在CREATE TABLE 语句中分析FOREIGN KEY 子句外，MySQL不支持外部键。（这有助于使从其他数据库移植代码到MySQL更为容易。）MySQL不强制让外部键作为一种约束，也不提供级联删除功能。

　　外部键强制实施的约束一般不难用程序逻辑来实现。有时，它只是一个怎样进行数据录入处理的。例如，为了将一个新记录插入score 表，不太可能插入不存在的学生的学分。显然，输入一组学分的方法应该是根据从student 表得出的学生名单，对每个学生，取其学分并利用该学生的ID 号产生一个score 表的记录。对于这个过程，不存在录入一个不存在的学生的记录的可能。您不会凭空造出一个学分记录来插入score 表。要实现DELETE 的级联效果，必须用自己的应用程序逻辑来完成。假如想要删除13 号学生。这也隐含表示需要删除该学生的学分记录。在支持级联删除的数据库中，只需要用如下的语句就可以删除student 表的记录和相应的score 表的记录：

　　DELETE FROM student WHERE student_id = 13

　　而在MySQL中，必须明确地用DELETE 语句自己进行第二个删除语句：

　　DELETE FROM student WHERE student_id = 13

　　DELETE FROM score WHERE student_id = 13

　　■ 存储过程和触发器。存储过程是编译和存放在中的SQL 代码。它可在以后调用而无需从客户机发送并分析。可以对一个过程进行更改以影响使用它的任何客户机应用程序。触发器功能使一个过程在某个事件发生时被激活，如从表中删除某个记录时，激活相应的过程。例如，某个作为累计成分的记录被删除时，应该重新进行累计，使累计数反映最新情况。存储过程语言已列入了MySQL准备实现的计划。

　　■ 视图。视图是一个逻辑概念，其功能像表但本身不是表。它提供了一种查看不同表中的列的途径，在查看时好像这些列属于同一个表一样。视图有时也称为虚表。MySQL也准备实现视图功能。

　　■ 记录级权限和锁定。支持各种权限，从全局权限到、表、列的权限。但它不支持记录级的权限。不过，可在应用程序中利用GET_LOCK( ) 和R E L E A S E _LOCK( ) 函数来实现协同记录锁。这个过程在附录C“运行算符和函数参考”中相应的项目下介绍。

　　■ “- -”作为注释的开始。MySQL不支持这种注释风格，因为它是一个有歧义的结构，虽然自MySQL3.23 以来，注释可用两个短划线加一个空格开始。更详细的信息，请参阅3 . 7节“加注释”。

　　现在让我们来考虑一种更为困难的情况，“缺了哪些值”。对于第1章中提到的学分保存方案中，有一个列出学生的student 表，一个列出已经出现过的学分事件的event 表，以及列出每个学生的每次学分事件学分的一个score 表。但是，如果一个学生在某个测试或测验的同一天病了，那么score 表中将不会有这个学生的该事件的学分，因此，要进行测验或测试的补考。我们怎样查找这些缺少了的记录，以便能保证让这些学生进行补考？是要对所有的学分事件确定哪些学生没有某个学分事件的学分。换个说法，就是我们希望知道学生和事件的哪些组合不出现在学分表中。这就是我们希望LEFT JOIN 所做的事。这个连接不像前例中那样简单，因为我们不仅仅要查找不出现在单列中的值；还需要查找两列的组合。

　　我们想要的这种组合是所有学生/事件的组合，它们由student 表与event 表的叉积产生：

　　FROM student, event

　　然后我们取出此连接的结果，与score 表执行一个LEFT JOIN 语句找出匹配者：

　　FROM student, event

　　LEFT JOIN score ON student.student_id = score.student.id

　　AND event.event_id = score.event_id

　　请注意，ON 子句使得score 表中的行根据不同表中的匹配者进行连接。这是解决本问题的关键。LEFT JOIN 强制为由student 和event 表的叉连接生成的每行产生一个行，即使没有相应的score 表记录也是这样。这些缺少的学分记录的结果行可通过一个事实来识别，就是来自score 表的列将全是NULL 的。我们可在WHERE 子句中选出这些记录。来自score 表的任何列都是这样，但因为我们查找的是缺少的学分，测试score 列从概念上可能最为清晰：

　　WHERE score.score IS NULL

　　可利用ORDER BY 子句对结果进行排序。两种最合理的排序分别是按学生和按事件进行，我们选择第一种：

　　ORDER BY student.student_id, event.event_id

　　现在需要做的就是命名我们希望在输出结果中看到的列。最终的查询如下：

　　SELECT

　　student.name, student.student_id,

　　event.date, event,event_id, event.type

　　FROM

　　student,event

　　LEFT JOIN score ON student.student_id = score.student_id

　　AND event.event_id = score.event_id

　　WHERE

　　score.score IS NULL

　　ORDER BY

　　student.student_id, event.event_id

　　运行此查询得出如下结果：

　　这里有一个要引起注意。此输出列出了学生的ID 和事件的ID。student_id 列出现在student 和score 表中，因此，开始您可能会认为选择列表可以给出student.student_id 或score . student _ id。但实际不是这样，因为能够找到感兴趣记录的是所有学分表字段返回NULL。选择score.student_id 将只在输出中产生NULL 值的列。类似的推理可应用到event_id 列，它也出现在event 和score 表中。

　　3.8.3 执行UNION 操作

　　如果想通过从具有相同结构的多个表中建立一个结果集，可在某些系统中使用某种UNION 语句来实现。没有UNION（至少直到3 . 2 4版还没有），但有许多办法来解决这个问题，下面是两种可行的方案：

　　■ 执行多个SELECT 查询，每个表执行一个。如果不关心所选出行的次序，这样做就行了。

　　■ 将每个表中的行选入一个临时存储表，然后选择该表的内容。这样可对行按所需的次序进行排序。在MySQL3.23版及以后的版本中，可通过允许创建存储表来解决这个问题。而且，还可以使该表为临时表，以便在您与服务器的会话结束时，自动删除该表。

　　在下面的代码中，我们明确地删除该表使服务器释放与其有关的资源。如果客户机会话将继续执行进一步的查询，这样做很有好处。为了取到更好的性能，还可以利用HEAP（在中）表。

　　对于3 . 2 3版本，除了必须自己明确定义hold_tbl 表中的列外，其想法是类似的，而且结尾处的DROP TABLE 是强制性的，用来防止在以下客户机会话生命周期之后继续存在：

　　3.8.4 增加序列号列

　　如果用ALTER TABLE 增加AUTO_INCREMENT 列，则该列用序列号自动地填充。下面这组mysql会话中的语句示出了怎样创建一个表，在其中存放数据，然后增加一个AUTO_INCREMENT 列：

　　 　3.8.5 对某个已有的列进行排序

　　 　如果有一个数值列，可对其按如下进行排序（或对其重排序，如果已对其排过序，但删除了行并且想要对值重新排序使其连续）：

　　 　ALTER TABLE t MODIFY i INT NULL

　　 　UPDATE t SET i = NULL

　　 　ALTER TABLE t MODIFY i INT UNSIGNEDAUTO_INCREMENT NOT NULL PRIMARY KEY

　　 但是有一种更容易的方法，那就是删除该列，然后再作为一个AUTO_INCREMENT 列追加它。ALTER TABLE 允许指定多个活动，因此，上述工作可在单个语句中完成：

　　ALTER TABLE t

　　DROP i,

　　ADD i INT UNSIGNEDAUTO_INCREMENT NOT NULL PRIMARY KEY

　　3.8.6 非正常次序的串

　　假如有一个表示体育机构人员的表，如橄榄球队，如果按人员职位进行排序，以便以特殊的顺序表示它，如：教练、教练助理、四分卫、流动后卫、接球员、巡逻员等。可将列定义为ENUM 并按希望出现的顺序定义枚举元素。对该列的排序将会以所指定的顺序自动进行。

　　3.8.7 建立计数表

　　在第2章的“使用序列”小节中，我们介绍了怎样利用L A S T _ INSERT_ID(expr) 生成一个序列。那个例子说明了怎样利用单列的表进行计数。那样做对于只需要单个计数器的情形能够满足需要，但是，如果需要几个计数器，该方法将会引起不必要的表重复。假如有一个 站点并且想要在几个页面上放置“此页面已经被访问nnn 次”这样的计数器。那么为每个具有一个计数器的页面建立一个单独的表就有些多余了。避免创建多个计数器表的一种方法是建立一个两列的表。其中一列存放计数值；另一列存放计数器名。这时仍然可以使用LAST _ INSERT_ID( ) 函数，但可用计数器名来决定用哪一行。这个表如下所示：

　　CREATE TABLE counter

　　(

　　count INT UNSIGNED,

　　name varchar(255) NOT NULL PRIMARY KEY

　　)

　　其中计数器名为一个串，从而可以调用任何想要的计数器，我们将其定义为PRIMARY KEY 以免名称重复。这里假定使用这个表的应用程序知道他们将使用的名称。对于前面所说的Web 计数器，可通过利用文件树中每个页面的路径名作为其计数器名的方法，保证计数器名的唯一性。例如，要为站点的主页建立一个新计数器，可执行下列语句：

　　INSERT INTO counter(name) VALUES("index.html")

　　它用零值初始化称为“ index.html”的计数器。为了生成序列中的下一个值，增加表中相应行的计数值，然后用LAST _ INSERT_ID( ) 检索它：

　　UPDATE counter

　　SET count = LAST_INSERT_ID(count+1)

　　WHERE name = "index.html"

　　SELECT LAST_INSERT_ID()

　　另一种方法是不用LAST _ INSERT_ID( ) 增加计数器的值，如下所示：

　　UPDATE counter SET count = count+1WHERE name = "index.html"

　　SELECT count FROM counter WHERE name = "index.html"

　　然而，如果另一个客户在您发布UPDATE语句与SELECT 语句之间增加了该计数器的值，则这种方法工作不正常。不过可在此两条语句的前后分别放置LOCK TABLES 和U N L O C KTABLES，在您使用该计数器时阻塞其他客户，以解决上述。但用L A S T _ INSERT_ID( )方法完成同样的工作更为容易一些。因为它的值是客户专用的，您总能得到自己插入的值，而不是其他客户插入的值，而且不必阻塞其他客户使代码复杂化。

　　3.8.8 检查表是否存在

　　在应用程序内部知道一个表是否存在有时很有用。为了做到这一点，可使用下列任一条语句：

　　SELECT COUNT(*) FROM tb1_name

　　SELECT * FROM tb1_name WHERE 1=0

　　如果指定的表存在，则上述两条语句都将执行成功，如果不存在，则都失败。它们是这种测试的很好的查询。它们执行速度快，所以不会费太多的时间。这种方法最适合您自己编写的应用程序，因为您可以测试查询的成功与失败并采取相应的措施。但在从运行的批量脚本中不特别有用，因为发生错误时除了终止运行外不可能做任何事（或者可以忽略相应的错误，但是显然无法再运行该查询了）。