破釜沉舟 http://www.7880.com
MySQL數據庫技術(shù)(18)文章類(lèi)別：MySQL 發(fā)表日期：2005-09-27
閱讀次數: 129
3.8 解決方案隨筆
本節內容相當雜；介紹了怎樣編寫(xiě)解決各種問(wèn)題的查詢(xún)。多數內容是在郵件清單上看到的解決問(wèn)題的方案（謝謝清單上的那些朋友，他們?yōu)榻鉀Q方案作了很多工作）。
3.8.1 將子選擇編寫(xiě)為連接
MySQL自3.24版本以來(lái)才具有子選擇功能。這項功能的缺少是MySQL 中一件常常令人惋惜的事，但有一件事很多人似乎沒(méi)有認識到，那就是用子選擇編寫(xiě)的查詢(xún)通?？梢杂眠B接來(lái)編寫(xiě)。實(shí)事上，即使MySQL 具有了子查詢(xún)，檢查用子選擇編寫(xiě)的查詢(xún)也是一件苦差事；用連接而不是用子選擇來(lái)編寫(xiě)會(huì )更為有效。
1. 重新編寫(xiě)選擇匹配值的子選擇
下面是一個(gè)包含一個(gè)子選擇查詢(xún)的樣例，它從score 表中選擇所有測試的學(xué)分（即，忽略測驗的學(xué)分）：
可通過(guò)將其轉換為一個(gè)簡(jiǎn)單的連接，不用子選擇也可以編寫(xiě)出相同的查詢(xún)，如下所示：
下面的例子為選擇女學(xué)生的學(xué)分：
可將其轉換為連接，如下所示：
這里是一個(gè)模式，子選擇查詢(xún)如下形式：
這樣的查詢(xún)可轉換為如下形式的連接：
2. 重新編寫(xiě)選擇非匹配值的子選擇查詢(xún)
另一種常用的子選擇查詢(xún)是查找一個(gè)表中有的而另一個(gè)表中沒(méi)有的值。正如以前所看到的那樣，“那些未給出的值”這一類(lèi)的問(wèn)題是LEFT JOIN 可能有用的一個(gè)線(xiàn)索。下面的查詢(xún)包含一個(gè)子選擇（它尋找那些全勤的學(xué)生）：
3.8.2 檢查表中未給出的值
我們已經(jīng)在3 . 6節“檢索記錄”中看到，在要想知道一個(gè)表中哪些值不出現在另一表中時(shí)，可對兩個(gè)表使用LEFT JOIN 并查找那些從第二個(gè)表中選中NULL 的行。并用下列兩個(gè)表舉例：
現在讓我們來(lái)考慮一種更為困難的情況，“缺了哪些值”。對于第1 章中提到的學(xué)分保存方案中，有一個(gè)列出學(xué)生的student 表，一個(gè)列出已經(jīng)出現過(guò)的學(xué)分事件的event 表，以及列出每個(gè)學(xué)生的每次學(xué)分事件學(xué)分的一個(gè)score 表。但是，如果一個(gè)學(xué)生在某個(gè)測試或測驗的同一天病了，那么score 表中將不會(huì )有這個(gè)學(xué)生的該事件的學(xué)分，因此，要進(jìn)行測驗或測試的補考。我們怎樣查找這些缺少了的記錄，以便能保證讓這些學(xué)生進(jìn)行補考？問(wèn)題是要對所有的學(xué)分事件確定哪些學(xué)生沒(méi)有某個(gè)學(xué)分事件的學(xué)分。換個(gè)說(shuō)法，就是我們希望知道學(xué)生和事件的哪些組合不出現在學(xué)分表中。這就是我們希望LEFT JOIN 所做的事。這個(gè)連接不像前例中那樣簡(jiǎn)單，因為我們不僅僅要查找不出現在單列中的值；還需要查找兩列的組合。
我們想要的這種組合是所有學(xué)生/事件的組合，它們由student 表與event 表的叉積產(chǎn)生：
FROM student, event
然后我們取出此連接的結果，與score 表執行一個(gè)LEFT JOIN 語(yǔ)句找出匹配者：
FROM student, event
LEFT JOIN score ON student.student_id = score.student.id
AND event.event_id = score.event_id
請注意，ON 子句使得score 表中的行根據不同表中的匹配者進(jìn)行連接。這是解決本問(wèn)題的關(guān)鍵。LEFT JOIN 強制為由student 和event 表的叉連接生成的每行產(chǎn)生一個(gè)行，即使沒(méi)有相應的score 表記錄也是這樣。這些缺少的學(xué)分記錄的結果行可通過(guò)一個(gè)事實(shí)來(lái)識別，就是來(lái)自score 表的列將全是NULL 的。我們可在WHERE 子句中選出這些記錄。來(lái)自score 表的任何列都是這樣，但因為我們查找的是缺少的學(xué)分，測試score 列從概念上可能最為清晰：
WHERE score.score IS NULL
可利用ORDER BY 子句對結果進(jìn)行排序。兩種最合理的排序分別是按學(xué)生和按事件進(jìn)行，我們選擇第一種：
ORDER BY student.student_id, event.event_id
現在需要做的就是命名我們希望在輸出結果中看到的列。最終的查詢(xún)如下：
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
運行此查詢(xún)得出如下結果：
這里有一個(gè)問(wèn)題要引起注意。此輸出列出了學(xué)生的ID 和事件的I D。student_id 列出現在student 和score 表中，因此，開(kāi)始您可能會(huì )認為選擇列表可以給出student.student_id 或score . student _ id。但實(shí)際不是這樣，因為能夠找到感興趣記錄的基礎是所有學(xué)分表字段返回N U L L。選擇score.student_id 將只在輸出中產(chǎn)生NULL 值的列。類(lèi)似的推理可應用到event_id 列，它也出現在event 和score 表中。
3.8.3 執行UNION 操作
如果想通過(guò)從具有相同結構的多個(gè)表中建立一個(gè)結果集，可在某些數據庫系統中使用某種UNION 語(yǔ)句來(lái)實(shí)現。MySQL 沒(méi)有UNION（至少直到3 . 2 4版還沒(méi)有），但有許多辦法來(lái)解決這個(gè)問(wèn)題，下面是兩種可行的方案：
■ 執行多個(gè)SELECT 查詢(xún)，每個(gè)表執行一個(gè)。如果不關(guān)心所選出行的次序，這樣做就行了。
■ 將每個(gè)表中的行選入一個(gè)臨時(shí)存儲表，然后選擇該表的內容。這樣可對行按所需的次序進(jìn)行排序。在MySQL 3.23版及以后的版本中，可通過(guò)允許服務(wù)器創(chuàng )建存儲表來(lái)解決這個(gè)問(wèn)題。而且，還可以使該表為臨時(shí)表，以便在您與服務(wù)器的會(huì )話(huà)結束時(shí)，自動(dòng)刪除該表。
在下面的代碼中，我們明確地刪除該表使服務(wù)器釋放與其有關(guān)的資源。如果客戶(hù)機會(huì )話(huà)將繼續執行進(jìn)一步的查詢(xún)，這樣做很有好處。為了取到更好的性能，還可以利用HEAP（在內存中）表。
對于3 . 2 3版本，除了必須自己明確定義hold_tbl 表中的列外，其想法是類(lèi)似的，而且結尾處的DROP TABLE 是強制性的，用來(lái)防止在以下客戶(hù)機會(huì )話(huà)生命周期之后繼續存在：
3.8.4 增加序列號列
如果用A LTER TABLE 增加AUTO_INCREMENT 列，則該列用序列號自動(dòng)地填充。下面這組mysql 會(huì )話(huà)中的語(yǔ)句示出了怎樣創(chuàng )建一個(gè)表，在其中存放數據，然后增加一個(gè)AUTO_INCREMENT 列：
3.8.5 對某個(gè)已有的列進(jìn)行排序
如果有一個(gè)數值列，可對其按如下進(jìn)行排序（或對其重排序，如果已對其排過(guò)序，但刪除了行并且想要對值重新排序使其連續）：
ALTER TABLE t MODIFY i INT NULL
UPDATE t SET i = NULL
ALTER TABLE t MODIFY i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY
但是有一種更容易的方法，那就是刪除該列，然后再作為一個(gè)A U TO_INCREMENT 列追加它。A LTER TABLE 允許指定多個(gè)活動(dòng)，因此，上述工作可在單個(gè)語(yǔ)句中完成：
ALTER TABLE t
DROP i,
ADD i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY
3.8.6 非正常次序的串
假如有一個(gè)表示體育機構人員的表，如橄欖球隊，如果按人員職位進(jìn)行排序，以便以特殊的順序表示它，如：教練、教練助理、四分衛、流動(dòng)后衛、接球員、巡邏員等?？蓪⒘卸x為ENUM 并按希望出現的順序定義枚舉元素。對該列的排序將會(huì )以所指定的順序自動(dòng)進(jìn)行。
3.8.7 建立計數表
在第2章的“使用序列”小節中，我們介紹了怎樣利用L A S T _ I N S E RT_ID(expr) 生成一個(gè)序列。那個(gè)例子說(shuō)明了怎樣利用單列的表進(jìn)行計數。那樣做對于只需要單個(gè)計數器的情形能夠滿(mǎn)足需要，但是，如果需要幾個(gè)計數器，該方法將會(huì )引起不必要的表重復。假如有一個(gè)Web 站點(diǎn)并且想要在幾個(gè)頁(yè)面上放置“此頁(yè)面已經(jīng)被訪(fǎng)問(wèn)nnn 次”這樣的計數器。那么為每個(gè)具有一個(gè)計數器的頁(yè)面建立一個(gè)單獨的表就有些多余了。避免創(chuàng )建多個(gè)計數器表的一種方法是建立一個(gè)兩列的表。其中一列存放計數值；另一列存放計數器名。這時(shí)仍然可以使用LAST _ INSERT_ID( ) 函數，但可用計數器名來(lái)決定用哪一行。這個(gè)表如下所示：
CREATE TABLE counter
(
count INT UNSIGNED,
name varchar(255) NOT NULL PRIMARY KEY
)
其中計數器名為一個(gè)串，從而可以調用任何想要的計數器，我們將其定義為PRIMARY KEY 以免名稱(chēng)重復。這里假定使用這個(gè)表的應用程序知道他們將使用的名稱(chēng)。對于前面所說(shuō)的Web 計數器，可通過(guò)利用文件樹(shù)中每個(gè)頁(yè)面的路徑名作為其計數器名的方法，保證計數器名的唯一性。例如，要為站點(diǎn)的主頁(yè)建立一個(gè)新計數器，可執行下列語(yǔ)句：
INSERT INTO counter(name) VALUES("index.html")
它用零值初始化稱(chēng)為“ index.html”的計數器。為了生成序列中的下一個(gè)值，增加表中相應行的計數值，然后用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+1 WHERE name = "index.html"
SELECT count FROM counter WHERE name = "index.html"
然而，如果另一個(gè)客戶(hù)在您發(fā)布U P D ATE 語(yǔ)句與SELECT 語(yǔ)句之間增加了該計數器的值，則這種方法工作不正常。不過(guò)可在此兩條語(yǔ)句的前后分別放置LOCK TABLES 和U N L O C KTABLES，在您使用該計數器時(shí)阻塞其他客戶(hù)，以解決上述問(wèn)題。但用L A S T _ I N S E RT_ID( )方法完成同樣的工作更為容易一些。因為它的值是客戶(hù)專(zhuān)用的，您總能得到自己插入的值，而不是其他客戶(hù)插入的值，而且不必阻塞其他客戶(hù)使代碼復雜化。
3.8.8 檢查表是否存在
在應用程序內部知道一個(gè)表是否存在有時(shí)很有用。為了做到這一點(diǎn)，可使用下列任一條語(yǔ)句：
SELECT COUNT(*) FROM tb1_name
SELECT * FROM tb1_name WHERE 1=0
如果指定的表存在，則上述兩條語(yǔ)句都將執行成功，如果不存在，則都失敗。它們是這種測試的很好的查詢(xún)。它們執行速度快，所以不會(huì )費太多的時(shí)間。這種方法最適合您自己編寫(xiě)的應用程序，因為您可以測試查詢(xún)的成功與失敗并采取相應的措施。但在從mysql 運行的批量腳本中不特別有用，因為發(fā)生錯誤時(shí)除了終止運行外不可能做任何事（或者可以忽略相應的錯誤，但是顯然無(wú)法再運行該查詢(xún)了）。
破釜沉舟 http://www.7880.com
