MySQL的10件事—它們也許和你預想的不一樣

BKJIA經典譯文】#10. 搜索一個“NULL”值 

SELECT  *  
FROM    a  
WHERE   a.column = NULL 

在SQL中，NULL什麼也不等於，而且NULL也不等於NULL。這個查詢不會返回任何結果的，實際上，當構建那個plan的時候，優化器會把這樣的語句優化掉。

當搜索NULL值的時候，應該使用這樣的查詢：

SELECT  *  
FROM    a  
WHERE   a.column IS NULL 

#9. 使用附加條件的LEFT JOIN

SELECT  *  
FROM    a  
LEFT JOIN 
        b  
ON      b.a = a.id  
WHERE   b.column = 'something' 

除了從a返回每個記錄至少一次），當沒有真正匹配的記錄的時候，用NULL值代替缺失的字段之外，LEFT JOIN和INNER JOIN都是一樣的。

但是，在LEFT JOIN之後才會檢查WHERE條件，所以，上面這個查詢在連接之後才會檢查column。就像我們剛才了解到的那樣，非NULL值才可以滿足相等條件，所以，在a的記錄中，那些在b中沒有對應的條目的記錄不可避免地要被過濾掉。

從本質上來說，這個查詢是一個INNER JOIN，只是效率要低一些。

為了真正地匹配滿足b.column = 'something'條件的記錄這時要返回a中的全部記錄，也就是說，不過濾掉那些在b中沒有對應的條目的記錄），這個條件應該放在ON子句中：

SELECT  *  
FROM    a  
LEFT JOIN 
        b  
ON      b.a = a.id  
        AND b.column = 'something' 

#8. 小於一個值，但是不為NULL

我經常看到這樣的查詢：

SELECT  *  
FROM    b  
WHERE   b.column < 'something' 
       AND b.column IS NOT NULL 

實際上，這並不是一個錯誤：這個查詢是有效的，是故意這樣做的。但是，這裡的IS NOT NULL是冗余的。

如果b.column是NULL，那麼無法滿足b.column < 'something'這個條件，因為任何一個和NULL進行的比較都會被判定為布爾NULL，是不會通過過濾器的。

有趣的是，這個附加的NULL檢查不能和“大於”查詢例如：b.column > 'something'）一起使用。

這是因為，在MySQL中，在ORDER BY的時候，NULL會排在前面，因此，一些人錯誤地認為NULL比任何其他的值都要小。

這個查詢可以被簡化：

SELECT  *  
FROM    b  
WHERE   b.column < 'something' 

在b.column中，不可能返回NULL

#7. 按照NULL來進行連接

SELECT  *  
FROM    a  
JOIN    b  
ON      a.column = b.column 

在兩個表中，當column是nullable的時候，這個查詢不會返回兩個字段都是NULL的記錄，原因如上所述：兩個NULL並不相等。

這個查詢應該這樣來寫：

SELECT  *  
FROM    a  
JOIN    b  
ON      a.column = b.column 
        OR (a.column IS NULL AND b.column IS NULL) 

MySQL的優化器會把這個查詢當成一個“等值連接”，然後提供一個特殊的連接條件：ref_or_null

#6. NOT IN和NULL值

SELECT  a.*  
FROM    a  
WHERE   a.column NOT IN 
        (  
        SELECT column 
        FROM    b  
        ) 

如果在b.column中有一個NULL值，那麼這個查詢是不會返回任何結果的。和其他謂詞一樣，IN  和 NOT IN 遇到NULL也會被判定為NULL。

你應該使用NOT EXISTS重寫這個查詢：

SELECT  a.*  
FROM    a  
WHERE   NOT EXISTS  
        (  
        SELECT NULL 
        FROM    b  
       WHERE   b.column = a.column 
       ) 

不像IN，EXISTS總是被判定為true或false的。

#5. 對隨機的樣本進行排序

SELECT  *  
FROM    a  
ORDER BY 
        RAND(), column 
LIMIT 10 

這個查詢試圖選出10個隨機的記錄，按照column來排序。

ORDER BY會按照自然順序來對輸出結果進行排序：這就是說，當第一個表達式的值相等的時候，這些記錄才會按照第二個表達式來排序。

但是，RAND()的結果是隨機的。要讓RAND()的值相等是行不通的，所以，按照RAND()排序以後，再按照column來排序也是沒有意義的。

要對隨機的樣本記錄進行排序，可以使用這個查詢：

SELECT  *  
FROM    (  
        SELECT  *  
        FROM    mytable  
        ORDER BY 
                RAND()  
        LIMIT 10  
       ) q  
ORDER BY 
       column 

#4. 通過一個組來選取任意的記錄

這個查詢打算通過某個組定義為grouper來）來選出一些記錄

SELECT  DISTINCT(grouper), a.*  
FROM    a 

DISTINCT不是一個函數，它是SELECT子句的一部分。它會應用到SELECT列表中的所有列，實際上，這裡的括號是可以省略的。所以，這個查詢可能會選出grouper中的值都相同的記錄如果在其他列中，至少有一個列的值是不同的）。

有時，這個查詢可以正常地使用( 這主要依賴於MySQL對GROUP BY的擴展）：

SELECT  a.*  
FROM    a  
GROUP BY 
        grouper 

在某個組中返回的非聚合的列可以被任意地使用。

首先，這似乎是一個很好的解決方案，但是，它存在著一個很嚴重的缺陷。它依賴於這樣一個假設：雖然可以通過組來任意地獲取，但是返回的所有值都要屬於一條記錄。

雖然當前的實現似乎就是這樣的，但是它並沒有文檔化，無論何時，它都有可能被改變尤其是，當MySQL學會了在GROUP BY的後面使用index_union的時候）。所以依賴於這個行為並不安全。

如果MySQL支持分析函數的話，這個查詢可以很容易地用另一種更清晰的方式來重寫。但是，如果這張表擁有一個PRIMARY KEY的話，即使不使用分析函數，也可以做到這一點：

SELECT  a.*  
FROM    (  
        SELECT  DISTINCT grouper  
        FROM    a  
        ) ao  
JOIN    a  
ON      a.id =  
        (  
        SELECT  id  
       FROM    a ai  
        WHERE   ai.grouper = ao.grouper  
        LIMIT 1  
        ) 

#3. 通過一個組來選取第一條記錄

把前面那個查詢稍微變化一下：

SELECT  a.*  
FROM    a  
GROUP BY 
        grouper  
ORDER BY 
        MIN(id) DESC 

和前面那個查詢不同，這個查詢試圖選出id值最小的記錄。

同樣：無法保證通過a.*返回的非聚合的值都屬於id值最小的那條記錄或者任意一條記錄）

這樣做會更清晰一些：

SELECT  a.*  
FROM    (  
        SELECT  DISTINCT grouper  
       FROM    a  
        ) ao  
JOIN    a  
ON      a.id =  
        (  
        SELECT  id  
        FROM    a ai  
        WHERE   ai.grouper = ao.grouper  
        ORDER BY 
                ai.grouper, ai.id  
        LIMIT 1  
        ) 

這個查詢和前面那個查詢類似，但是使用額外的ORDER BY可以確保按id來排序的第一條記錄會被返回。

#2. IN和‘,’——值的分隔列表

這個查詢試圖讓column的值匹配用‘,’分隔的字符串中的任意一個值：

SELECT  *  
FROM    a  
WHERE   column IN ('1, 2, 3') 

這不會正常發揮作用的，因為在IN列表中，那個字符串並不會被展開。

如果列column是一個VARCHAR，那麼它作為一個字符串）會和整個列表也作為一個字符串）進行比較，當然，這不可能匹配。如果 column是某個數值類型，那麼這個列表會被強制轉換為那種數值類型在最好的情況下，只有第一項會匹配）

處理這個查詢的正確方法應該是使用合適的IN列表來重寫它：

SELECT  *  
FROM    a  
WHERE   column IN (1, 2, 3) 

或者，也可以使用內聯：

SELECT  *  
FROM    (  
        SELECT  1 AS id  
        UNION ALL 
        SELECT  2 AS id  
        UNION ALL 
        SELECT  3 AS id  
        ) q  
JOIN    a  
ON      a.column = q.id 

但是，有時這是不可能的。

如果不想改變那個查詢的參數，可以使用FIND_IN_SET：

SELECT  *  
FROM    a  
WHERE   FIND_IN_SET(column, '1,2,3') 

但是，這個函數不可以利用索引從表中檢索行，會在a上執行全表掃描。

#1. LEFT JOIN和COUNT(*)

SELECT  a.id, COUNT(*)  
FROM    a  
LEFT JOIN 
        b  
ON      b.a = a.id  
GROUP BY 
        a.id 

這個查詢試圖統計出對於a中的每條記錄來說，在b中匹配的記錄的數目。

問題是,在這樣一個查詢中，COUNT(*)永遠不會返回一個0。對於a中某條記錄來說，如果沒有匹配的記錄，那麼那條記錄還是會被返回和計數。

只有需要統計b中的記錄數目的時候才應該使用COUNT。既然可以使用COUNT(*)，那麼我們也可以使用一個參數來調用它忽略掉NULL），我們可以把b.a傳遞給它。在這個例子中，作為一個連接主鍵，它不可以為空，但是如果不想匹配，它也可以為空。

原文標題：10 things in MySQL (that won’t work as expected)