建站
咨詢
售后
建站咨詢
新聞中心
【經(jīng)典譯文】#10. 搜索一個“NULL”值
我們提供的服務(wù)有：網(wǎng)站建設(shè)、成都網(wǎng)站設(shè)計、微信公眾號開發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、耒陽ssl等。為千余家企事業(yè)單位解決了網(wǎng)站和推廣的問題。提供周到的售前咨詢和貼心的售后服務(wù)，是有科學(xué)管理、有技術(shù)的耒陽網(wǎng)站制作公司
- SELECT *
- FROM a
- WHERE a.column = NULL
在SQL中，NULL什么也不等于，而且NULL也不等于NULL。這個查詢不會返回任何結(jié)果的，實(shí)際上，當(dāng)構(gòu)建那個plan的時候，優(yōu)化器會把這樣的語句優(yōu)化掉。
當(dāng)搜索NULL值的時候，應(yīng)該使用這樣的查詢：
- 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返回每個記錄（至少一次），當(dāng)沒有真正匹配的記錄的時候，用NULL值代替缺失的字段之外，LEFT JOIN和INNER JOIN都是一樣的。
但是，在LEFT JOIN之后才會檢查WHERE條件，所以，上面這個查詢在連接之后才會檢查column。就像我們剛才了解到的那樣，非NULL值才可以滿足相等條件，所以，在a的記錄中，那些在b中沒有對應(yīng)的條目的記錄不可避免地要被過濾掉。
從本質(zhì)上來說，這個查詢是一個INNER JOIN，只是效率要低一些。
為了真正地匹配滿足b.column = 'something'條件的記錄（這時要返回a中的全部記錄，也就是說，不過濾掉那些在b中沒有對應(yīng)的條目的記錄），這個條件應(yīng)該放在ON子句中：
- SELECT *
- FROM a
- LEFT JOIN
- b
- ON b.a = a.id
- AND b.column = 'something'
#8. 小于一個值，但是不為NULL
我經(jīng)?？吹竭@樣的查詢：
- SELECT *
- FROM b
- WHERE b.column < 'something'
- AND b.column IS NOT NULL
實(shí)際上，這并不是一個錯誤：這個查詢是有效的，是故意這樣做的。但是，這里的IS NOT NULL是冗余的。
如果b.column是NULL，那么無法滿足b.column < 'something'這個條件，因?yàn)槿魏我粋€和NULL進(jìn)行的比較都會被判定為布爾NULL，是不會通過過濾器的。
有趣的是，這個附加的NULL檢查不能和“大于”查詢（例如：b.column > 'something'）一起使用。
這是因?yàn)?，在MySQL中，在ORDER BY的時候，NULL會排在前面，因此，一些人錯誤地認(rèn)為NULL比任何其他的值都要小。
這個查詢可以被簡化：
- SELECT *
- FROM b
- WHERE b.column < 'something'
在b.column中，不可能返回NULL
#7. 按照NULL來進(jìn)行連接
- SELECT *
- FROM a
- JOIN b
- ON a.column = b.column
在兩個表中，當(dāng)column是nullable的時候，這個查詢不會返回兩個字段都是NULL的記錄，原因如上所述：兩個NULL并不相等。
這個查詢應(yīng)該這樣來寫：
- SELECT *
- FROM a
- JOIN b
- ON a.column = b.column
- OR (a.column IS NULL AND b.column IS NULL)
MySQL的優(yōu)化器會把這個查詢當(dāng)成一個“等值連接”，然后提供一個特殊的連接條件：ref_or_null
#6. NOT IN和NULL值
- SELECT a.*
- FROM a
- WHERE a.column NOT IN
- (
- SELECT column
- FROM b
- )
如果在b.column中有一個NULL值，那么這個查詢是不會返回任何結(jié)果的。和其他謂詞一樣，IN 和 NOT IN 遇到NULL也會被判定為NULL。
你應(yīng)該使用NOT EXISTS重寫這個查詢：
- SELECT a.*
- FROM a
- WHERE NOT EXISTS
- (
- SELECT NULL
- FROM b
- WHERE b.column = a.column
- )
不像IN，EXISTS總是被判定為true或false的。
#5. 對隨機(jī)的樣本進(jìn)行排序
- SELECT *
- FROM a
- ORDER BY
- RAND(), column
- LIMIT 10
這個查詢試圖選出10個隨機(jī)的記錄，按照column來排序。
ORDER BY會按照自然順序來對輸出結(jié)果進(jìn)行排序：這就是說，當(dāng)?shù)谝粋€表達(dá)式的值相等的時候，這些記錄才會按照第二個表達(dá)式來排序。
但是，RAND()的結(jié)果是隨機(jī)的。要讓RAND()的值相等是行不通的，所以，按照RAND()排序以后，再按照column來排序也是沒有意義的。
要對隨機(jī)的樣本記錄進(jìn)行排序，可以使用這個查詢：
- SELECT *
- FROM (
- SELECT *
- FROM mytable
- ORDER BY
- RAND()
- LIMIT 10
- ) q
- ORDER BY
- column
#4. 通過一個組來選取任意的記錄
這個查詢打算通過某個組（定義為grouper來）來選出一些記錄
- SELECT DISTINCT(grouper), a.*
- FROM a
DISTINCT不是一個函數(shù)，它是SELECT子句的一部分。它會應(yīng)用到SELECT列表中的所有列，實(shí)際上，這里的括號是可以省略的。所以，這個查詢可能會選出grouper中的值都相同的記錄（如果在其他列中，至少有一個列的值是不同的）。
有時，這個查詢可以正常地使用( 這主要依賴于MySQL對GROUP BY的擴(kuò)展）：
- SELECT a.*
- FROM a
- GROUP BY
- grouper
在某個組中返回的非聚合的列可以被任意地使用。
首先，這似乎是一個很好的解決方案，但是，它存在著一個很嚴(yán)重的缺陷。它依賴于這樣一個假設(shè)：雖然可以通過組來任意地獲取，但是返回的所有值都要屬于一條記錄。
雖然當(dāng)前的實(shí)現(xiàn)似乎就是這樣的，但是它并沒有文檔化，無論何時，它都有可能被改變（尤其是，當(dāng)MySQL學(xué)會了在GROUP BY的后面使用index_union的時候）。所以依賴于這個行為并不安全。
如果MySQL支持分析函數(shù)的話，這個查詢可以很容易地用另一種更清晰的方式來重寫。但是，如果這張表擁有一個PRIMARY KEY的話，即使不使用分析函數(shù)，也可以做到這一點(diǎn)：
- 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')
這不會正常發(fā)揮作用的，因?yàn)樵贗N列表中，那個字符串并不會被展開。
如果列column是一個VARCHAR，那么它（作為一個字符串）會和整個列表（也作為一個字符串）進(jìn)行比較，當(dāng)然，這不可能匹配。如果 column是某個數(shù)值類型，那么這個列表會被強(qiáng)制轉(zhuǎn)換為那種數(shù)值類型（在最好的情況下，只有第一項(xiàng)會匹配）
處理這個查詢的正確方法應(yīng)該是使用合適的IN列表來重寫它：
- SELECT *
- FROM a
- WHERE column IN (1, 2, 3)
或者，也可以使用內(nèi)聯(lián)：
- 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
但是，有時這是不可能的。
如果不想改變那個查詢的參數(shù)，可以使用FIND_IN_SET：
- SELECT *
- FROM a
- WHERE FIND_IN_SET(column, '1,2,3')
但是，這個函數(shù)不可以利用索引從表中檢索行，會在a上執(zhí)行全表掃描。
#1. LEFT JOIN和COUNT(*)
- SELECT a.id, COUNT(*)
- FROM a
- LEFT JOIN
- b
- ON b.a = a.id
- GROUP BY
- a.id
這個查詢試圖統(tǒng)計出對于a中的每條記錄來說，在b中匹配的記錄的數(shù)目。
問題是,在這樣一個查詢中，COUNT(*)永遠(yuǎn)不會返回一個0。對于a中某條記錄來說，如果沒有匹配的記錄，那么那條記錄還是會被返回和計數(shù)。
只有需要統(tǒng)計b中的記錄數(shù)目的時候才應(yīng)該使用COUNT。既然可以使用COUNT(*)，那么我們也可以使用一個參數(shù)來調(diào)用它（忽略掉NULL），我們可以把b.a傳遞給它。在這個例子中，作為一個連接主鍵，它不可以為空，但是如果不想匹配，它也可以為空。
文章標(biāo)題：MySQL的10件事—它們也許和你預(yù)想的不一樣
轉(zhuǎn)載源于：http://m.5511xx.com/article/ccchjop.html
