第19章 隐式的列

一个PHP开发人员向我寻求帮助，他的图书馆数据库在执行一个看上去很简单的SQL查询时的行为让人疑惑不解。

Implicit-Columns/intro/join-wildcard.sql
SELECT * FROM Books b JOIN Authors a ON (b.author_id = a.author_id);

这个查询返回的所有的书名都是NULL。更奇怪的是，当他执行一个不带Authors表的查询时，返回的结果又是和预期的一样，包含了正确的书名。

我帮他找到了问题的根源：他所使用的PHP数据库扩展将从SQL查询返回的每条记录都表示成一个关联数组。比如，使用 $row["isbn"] 直接获得Books.isbn的值。在他所设计的表中，Books（书）和Authors（作者）两张表里都有一个title（有“标题”和“称呼”两个意思）列。由于结果数组中的单个条目 $row["title"] 仅能存储一个值，在这个例子中，Authors.title占据了这个数组条目。而在数据库中，大多数作者的title这一列都没有值，因此 $row["title"] 的值就等于NULL。当这个查询不包含Accounts表时，列名之间没有冲突，书名这一列就如同我们预期的那样占据了这个数组条目。

我告诉这个程序员，解决方案就是给其中的一个title声明一个别名，这样不同title就会使用数组中不同的条目。

Implicit-Columns/intro/join-alias.sql
SELECT b.title, a.title AS salutation FROM Books b JOIN Authors a ON (b.author_id = a.author_id);

随后他问了我第二个问题：“我要怎么只给一个列定义别名，同时还要获取其余的所有列？”他想要继续使用通配符（SELECT *），又要给通配符所包含的某一列定义别名。

软件开发人员似乎不太愿意打字，这在某种程度上是对他们选择的这个职业的讽刺，就像欧亨利的小说中那对双胞胎的结局。

程序员通常用下面这个需要写出所有查询列的例子来说明要打的字太多了：

Implicit-Columns/obj/select-explicit.sql
SELECT bug_id, date_reported, summary, description, resolution, reported_by, assigned_to, verified_by, status, priority, hours FROM Bugs;

程序员喜欢使用SQL通配符，我一点也不觉得惊讶。符号*意味着所有的列，因此列的列表是隐式定义的，而不是显式的。这让查询代码变得更清晰。

Implicit-Columns/obj/select-implicit.sql
SELECT * FROM Bugs;

同样地，当使用INSERT时，使用默认的方案似乎更好：输入的数据会按照列在表中定义的顺序应用到所有的列上。

Implicit-Columns/obj/insert-explicit.sql
INSERT INTO Accounts (account_name, first_name, last_name, email, password, portrait_image, hourly_rate) VALUES ('bkarwin', 'Bill', 'Karwin', 'bill@example.com', SHA2('xyzzy'), NULL, 49.95);

不需要列出列名让SQL语句变得更短。

Implicit-Columns/obj/insert-implicit.sql
INSERT INTO Accounts VALUES (DEFAULT,   'bkarwin', 'Bill', 'Karwin', 'bill@example.com', SHA2('xyzzy'), NULL, 49.95);

尽管使用通配符和未命名列能够达到减少输入的目的，但这个习惯也会带来一些危害。

假设你要向Bugs表里增加一列，比如说用来安排日程的date_due列。

Implicit-Columns/anti/add-column.sql
ALTER TABLE Bugs ADD COLUMN date_due DATE;

INSERT语句现在会报错，因为现在这张表需要12个传入参数，而你只有11个。

Implicit-Columns/anti/insert-mismatched.sql
INSERT INTO Bugs VALUES (DEFAULT, CURDATE(), 'New bug', 'Test T987 fails...', NULL, 123, NULL, NULL, DEFAULT, 'Medium', NULL);  -- SQLSTATE 21S01: Column count doesn't match
value count at row 1

在使用隐式模式执行INSERT时，输入必须严格按照定义表时的那些列的顺序。如果列变了，这条语句就会抛出一个错误，甚至有可能把数据写到错误的列里面去。

假设你要执行一个SELECT *的查询，由于不知道具体的列名，所以你只能按照最开始设计表的顺序来使用结果：

Implicit-Columns/anti/ordinal.php
<?php $stmt = $pdo->query("SELECT * FROM Bugs WHERE bug_id = 1234");
      $row = $stmt->fetch();
      $hours = $row[10];

但是在你不知道的情况下，有人删掉了一列：

Implicit-Columns/anti/drop-column.sql
ALTER TABLE Bugs DROP COLUMN verified_by;

hours这一列已经不是第十列了，于是程序错误地使用了另一列的数据。在重命名、添加、删除列的时候，程序代码并不能适应查询结果的改变。如果使用了通配符，就无法预测这个查询会返回多少行。

这些错误可能会隐藏得很深，当你在程序的输出中发现问题的时候，很难回溯并定位到出问题的那行代码。

在查询中使用通配符可能会影响性能和扩展性。一次查询所获取的列越多，客户端程序和数据库之间的网络传输的字节数也越多。

生产环境中的程序可能会有很多并发的查询请求。它们都共享同一个网络带宽，即使一个千兆网络环境也可能由于上百个客户端同时查询返回上千条记录而造成阻塞。

诸如Active Record这类的对象关系映射（ORM）技术通常默认使用SELECT *取得的数据来填充一个表示数据库行的对象。即使ORM提供了一些修改默认行为方式的接口，大多数程序员也懒得去改。

我所遇到的程序员使用SQL通配符时问得最多的问题是：“有没有选择除了几个我不想要的列之外所有列的方法？”可能这些程序员想要避免获取庞大的TEXT类型的列的开销，但他们同时也想要获得通配符所带来的书写上的便利。

答案是“没有”。SQL不支持这种“除了我不想要的，其他都要”的语法。你只能使用通配符获取一张表的所有列，或者一个个显式地列出所有你想要的列。

如下的情形可能预示着你的项目在使用隐式列的时候处理得不恰当，并且造成了一定的麻烦。

在你只是为了快速地写几个脚本对一个解决方案进行测试，或者写临时SQL查询对当前数据进行校验时，使用通配符是很合情合理的。只执行一次的查询对可维护性没有任何要求。

本书中的例子用到了通配符，一来是为了节省空间，二来是为了避免分散读者对例子中那些更重要部分的注意力。在实际的工作代码中，我很少使用通配符。

如果你的程序需要在增加、删除、重命名或者重新配置列时依旧能自动适应及调整，那最好还是使用通配符，但要确认对之前描述的那些陷阱有充分的准备。

你可以对一个联结查询中的每个独立的表使用通配符。在通配符之前加上表名或者别名作为前缀。这么做可以让你在从一张表中获取所有列的同时，从另一张表中获取少量你所指定的列。如下例：

Implicit-Columns/legit/wildcard-one-table.sql
SELECT b.*, a.first_name, a.email FROM Bugs b JOIN Accounts a ON (b.reported_by = a.account_id);

输入一个很长的列的列表是很耗时的。对某些人来说，开发效率比程序执行效率更重要。类似地，你可能会把“写更短更可读的查询语句”的优先级提高。使用通配符确实能减少输入的量，得到一个更简短的查询，因此，如果你确实注重这方面的需求，那就用通配符吧。

我听过一个开发人员抱怨说，从程序传递到SQL查询请求的包太大而使得网络负载加剧，在某些情况下查询语句的长度的确会造成影响。但更常见的情况是，返回的数据所使用的带宽比查询语句本身要多得多。你需要自己判断这些特殊的情况，别纠结于这些小问题。

每次查询时都列出所有你需要的列，而不是使用通配符或者隐式列的列表。

Implicit-Columns/soln/select-explicit.sql
SELECT bug_id, date_reported, summary, description, resolution, reported_by, assigned_to, verified_by, status, priority, hours FROM Bugs;
Implicit-Columns/soln/insert-explicit.sql
INSERT INTO Accounts (account_name, first_name, last_name, email, password_hash, portrait_image, hourly_rate) VALUES ('bkarwin', 'Bill', 'Karwin', 'bill@example.com',   SHA2('xyzzy'), NULL, 49.95);

所有这些输入看上去都是很繁重的工作，但非常值得。

还记得poka-yoke1吗？你在查询时指明所需要选择的列，这能让SQL查询更好地应付错误以及更早地暴露问题。

如果指定了列名，在使用INSERT语句时也能得到类似的好处。你所定义的插入列的顺序会覆盖原始表的定义，并且插入的值会分配到你想要插入的列里。没有在列表里出现的新加入的那列，会自动获得一个默认值或者直接等于NULL。如果你引用了一个已经被删除的列，就会得到一个错误信息，这能更早地发现并解决问题。

这是一个尽早出错原则的例子。

如果必须关心软件的可扩展性和程序的吞吐量，你应该检查一下在网络传输过程中可能造成的浪费。在开发和测试环境中，SQL查询所造成的流量上的问题可以忽略不计，但在生产环境中每秒上千次的SQL查询就会造成严重的问题。

一旦你禁止了SQL通配符，就很自然地有针对性地去除那些你不需要的列，同时也意味着更少的输入。这也能使得网络带宽的使用更加有效率。

Implicit-Columns/soln/yagni.sql
SELECT date_reported, summary, description, resolution, status, priority FROM Bugs;

你从自动售货机买了一包M&M’s，包装袋很有用，它能让你很简单地就把这些糖带回办公桌。一旦你打开了包装袋，就需要将M&M’s的每一粒糖都视为独立的个体。它们会滚得到处都是。如果你不小心，一些糖还会掉在桌子下面引来臭虫。但是，你想吃到它们就只有打开包装袋。

在SQL查询中，一旦你想要对某一列进行一些表达式计算，或者使用一个列别名，或者由于性能原因排除某一列，你就打开了通配符这个“包装袋”。你不再享有将所有列的集合当成一个包处理所带来的便利，但能够访问到这个包里面的所有内容。

你不可避免地要在查询中引入列别名、函数，或者从列表中排除某列。如果你从一开始就不使用通配符，那之后要对查询进行修改就会变得更加方便。

随便拿，但是拿了就必须吃掉。