本次來講解與 SQL 查詢有關的兩個小知識點,掌握這些知識點,能夠讓你避免踩坑以及提高查詢效率。
1、允許字段的值為 null,往往會引發災難
首先,先準備點數據,後面好演示
create table animal(
id int,
name char(20),
index(id)
)engine=innodb;
index(id) 表示給 id 這個字段創建索引,並且 id 和 name 都允許為 null。
接著插入4條數據,其中最後一條數據的 id 為。
insert into animal(id, name) values(1, '貓');
insert into animal(id, name) values(2, '狗');
insert into animal(id, name) values(3, '豬');
insert into animal(id, name) values(null, '無名動物');
此時表中的數據為
本次來講解與 SQL 查詢有關的兩個小知識點,掌握這些知識點,能夠讓你避免踩坑以及提高查詢效率。
1、允許字段的值為 null,往往會引發災難
首先,先準備點數據,後面好演示
create table animal(
id int,
name char(20),
index(id)
)engine=innodb;
index(id) 表示給 id 這個字段創建索引,並且 id 和 name 都允許為 null。
接著插入4條數據,其中最後一條數據的 id 為。
insert into animal(id, name) values(1, '貓');
insert into animal(id, name) values(2, '狗');
insert into animal(id, name) values(3, '豬');
insert into animal(id, name) values(null, '無名動物');
此時表中的數據為
這時我們查詢表中 id != 1 的動物有哪些
select * from animal where id != 1;
結果如下:
本次來講解與 SQL 查詢有關的兩個小知識點,掌握這些知識點,能夠讓你避免踩坑以及提高查詢效率。
1、允許字段的值為 null,往往會引發災難
首先,先準備點數據,後面好演示
create table animal(
id int,
name char(20),
index(id)
)engine=innodb;
index(id) 表示給 id 這個字段創建索引,並且 id 和 name 都允許為 null。
接著插入4條數據,其中最後一條數據的 id 為。
insert into animal(id, name) values(1, '貓');
insert into animal(id, name) values(2, '狗');
insert into animal(id, name) values(3, '豬');
insert into animal(id, name) values(null, '無名動物');
此時表中的數據為
這時我們查詢表中 id != 1 的動物有哪些
select * from animal where id != 1;
結果如下:
此時我們只找到了兩行數據,按道理應該是三行的,但是 id = null 的這一行居然沒有被匹配到,,可能大家聽說過,null 與任何
其他值都不相等,按道理 null != 1 是成立的話,然而現實很殘酷,它就是不會被匹配到。
所以,堅決不允許字段的值為 null,否則可能會出現與預期不符合的結果。
反正我之前有踩過這個坑,不知道大家踩過木有?
但是萬一有人設置了允許為 null 值怎麼辦?如果真的這樣的話,對於 != 的查找,後面可以多加一個 or id is null 的子句(注意,是 is null,不是 = null,因為 id = null 也不會匹配到值為 null 的行)。即
select * from animal where id != 1 or id is null;
結果如下:
本次來講解與 SQL 查詢有關的兩個小知識點,掌握這些知識點,能夠讓你避免踩坑以及提高查詢效率。
1、允許字段的值為 null,往往會引發災難
首先,先準備點數據,後面好演示
create table animal(
id int,
name char(20),
index(id)
)engine=innodb;
index(id) 表示給 id 這個字段創建索引,並且 id 和 name 都允許為 null。
接著插入4條數據,其中最後一條數據的 id 為。
insert into animal(id, name) values(1, '貓');
insert into animal(id, name) values(2, '狗');
insert into animal(id, name) values(3, '豬');
insert into animal(id, name) values(null, '無名動物');
此時表中的數據為
這時我們查詢表中 id != 1 的動物有哪些
select * from animal where id != 1;
結果如下:
此時我們只找到了兩行數據,按道理應該是三行的,但是 id = null 的這一行居然沒有被匹配到,,可能大家聽說過,null 與任何
其他值都不相等,按道理 null != 1 是成立的話,然而現實很殘酷,它就是不會被匹配到。
所以,堅決不允許字段的值為 null,否則可能會出現與預期不符合的結果。
反正我之前有踩過這個坑,不知道大家踩過木有?
但是萬一有人設置了允許為 null 值怎麼辦?如果真的這樣的話,對於 != 的查找,後面可以多加一個 or id is null 的子句(注意,是 is null,不是 = null,因為 id = null 也不會匹配到值為 null 的行)。即
select * from animal where id != 1 or id is null;
結果如下:
2、儘可能用 union 來代替 or
(1)、剛才我們給 id 這個字段建立了索引,如果我們來進行等值操作的話,一般會走索引操作,不信你看:
explain select * from animal where id = 1;
結果如下:
本次來講解與 SQL 查詢有關的兩個小知識點,掌握這些知識點,能夠讓你避免踩坑以及提高查詢效率。
1、允許字段的值為 null,往往會引發災難
首先,先準備點數據,後面好演示
create table animal(
id int,
name char(20),
index(id)
)engine=innodb;
index(id) 表示給 id 這個字段創建索引,並且 id 和 name 都允許為 null。
接著插入4條數據,其中最後一條數據的 id 為。
insert into animal(id, name) values(1, '貓');
insert into animal(id, name) values(2, '狗');
insert into animal(id, name) values(3, '豬');
insert into animal(id, name) values(null, '無名動物');
此時表中的數據為
這時我們查詢表中 id != 1 的動物有哪些
select * from animal where id != 1;
結果如下:
此時我們只找到了兩行數據,按道理應該是三行的,但是 id = null 的這一行居然沒有被匹配到,,可能大家聽說過,null 與任何
其他值都不相等,按道理 null != 1 是成立的話,然而現實很殘酷,它就是不會被匹配到。
所以,堅決不允許字段的值為 null,否則可能會出現與預期不符合的結果。
反正我之前有踩過這個坑,不知道大家踩過木有?
但是萬一有人設置了允許為 null 值怎麼辦?如果真的這樣的話,對於 != 的查找,後面可以多加一個 or id is null 的子句(注意,是 is null,不是 = null,因為 id = null 也不會匹配到值為 null 的行)。即
select * from animal where id != 1 or id is null;
結果如下:
2、儘可能用 union 來代替 or
(1)、剛才我們給 id 這個字段建立了索引,如果我們來進行等值操作的話,一般會走索引操作,不信你看:
explain select * from animal where id = 1;
結果如下:
通過執行計劃可以看見,id 上的等值查找能夠走索引查詢(估計在你的意料之中),其中
type = ref :表示走非唯一索引
rows = 1 :預測掃描一行
(2)、那 id is null 會走索引嗎?答是會的,如圖
explain select * from animal where id is null;
本次來講解與 SQL 查詢有關的兩個小知識點,掌握這些知識點,能夠讓你避免踩坑以及提高查詢效率。
1、允許字段的值為 null,往往會引發災難
首先,先準備點數據,後面好演示
create table animal(
id int,
name char(20),
index(id)
)engine=innodb;
index(id) 表示給 id 這個字段創建索引,並且 id 和 name 都允許為 null。
接著插入4條數據,其中最後一條數據的 id 為。
insert into animal(id, name) values(1, '貓');
insert into animal(id, name) values(2, '狗');
insert into animal(id, name) values(3, '豬');
insert into animal(id, name) values(null, '無名動物');
此時表中的數據為
這時我們查詢表中 id != 1 的動物有哪些
select * from animal where id != 1;
結果如下:
此時我們只找到了兩行數據,按道理應該是三行的,但是 id = null 的這一行居然沒有被匹配到,,可能大家聽說過,null 與任何
其他值都不相等,按道理 null != 1 是成立的話,然而現實很殘酷,它就是不會被匹配到。
所以,堅決不允許字段的值為 null,否則可能會出現與預期不符合的結果。
反正我之前有踩過這個坑,不知道大家踩過木有?
但是萬一有人設置了允許為 null 值怎麼辦?如果真的這樣的話,對於 != 的查找,後面可以多加一個 or id is null 的子句(注意,是 is null,不是 = null,因為 id = null 也不會匹配到值為 null 的行)。即
select * from animal where id != 1 or id is null;
結果如下:
2、儘可能用 union 來代替 or
(1)、剛才我們給 id 這個字段建立了索引,如果我們來進行等值操作的話,一般會走索引操作,不信你看:
explain select * from animal where id = 1;
結果如下:
通過執行計劃可以看見,id 上的等值查找能夠走索引查詢(估計在你的意料之中),其中
type = ref :表示走非唯一索引
rows = 1 :預測掃描一行
(2)、那 id is null 會走索引嗎?答是會的,如圖
explain select * from animal where id is null;
其中type = ref :表示走非唯一索引
rows = 1 :預測掃描一行
(3)、那麼問題來了,那如果我們要找出 id = 1 或者 id = null 的動物,我們可能會用 or 語句來連接,即
select * from animal where id = 1 or id is null;
那麼這條語句會走索引嗎?
有沒有走索引,看執行計劃就知道了,如圖
explain select * from animal where id = 1 or id is null;
本次來講解與 SQL 查詢有關的兩個小知識點,掌握這些知識點,能夠讓你避免踩坑以及提高查詢效率。
1、允許字段的值為 null,往往會引發災難
首先,先準備點數據,後面好演示
create table animal(
id int,
name char(20),
index(id)
)engine=innodb;
index(id) 表示給 id 這個字段創建索引,並且 id 和 name 都允許為 null。
接著插入4條數據,其中最後一條數據的 id 為。
insert into animal(id, name) values(1, '貓');
insert into animal(id, name) values(2, '狗');
insert into animal(id, name) values(3, '豬');
insert into animal(id, name) values(null, '無名動物');
此時表中的數據為
這時我們查詢表中 id != 1 的動物有哪些
select * from animal where id != 1;
結果如下:
此時我們只找到了兩行數據,按道理應該是三行的,但是 id = null 的這一行居然沒有被匹配到,,可能大家聽說過,null 與任何
其他值都不相等,按道理 null != 1 是成立的話,然而現實很殘酷,它就是不會被匹配到。
所以,堅決不允許字段的值為 null,否則可能會出現與預期不符合的結果。
反正我之前有踩過這個坑,不知道大家踩過木有?
但是萬一有人設置了允許為 null 值怎麼辦?如果真的這樣的話,對於 != 的查找,後面可以多加一個 or id is null 的子句(注意,是 is null,不是 = null,因為 id = null 也不會匹配到值為 null 的行)。即
select * from animal where id != 1 or id is null;
結果如下:
2、儘可能用 union 來代替 or
(1)、剛才我們給 id 這個字段建立了索引,如果我們來進行等值操作的話,一般會走索引操作,不信你看:
explain select * from animal where id = 1;
結果如下:
通過執行計劃可以看見,id 上的等值查找能夠走索引查詢(估計在你的意料之中),其中
type = ref :表示走非唯一索引
rows = 1 :預測掃描一行
(2)、那 id is null 會走索引嗎?答是會的,如圖
explain select * from animal where id is null;
其中type = ref :表示走非唯一索引
rows = 1 :預測掃描一行
(3)、那麼問題來了,那如果我們要找出 id = 1 或者 id = null 的動物,我們可能會用 or 語句來連接,即
select * from animal where id = 1 or id is null;
那麼這條語句會走索引嗎?
有沒有走索引,看執行計劃就知道了,如圖
explain select * from animal where id = 1 or id is null;
其中:
ref = ALL:表示全表掃描
rows = 4 :預測掃描4行(而我們整個表就只有4行記錄)
通過執行計劃可以看出,使用 or 是很有可能不走索引的,這將會大大降低查詢的速率,所以一般不建議使用 or 子句來連接條件。
那麼該如何解決?
其實可以用 union 來取代 or,即如下:
select * from animal where id = 1 union select * from animal where id is null.
本次來講解與 SQL 查詢有關的兩個小知識點,掌握這些知識點,能夠讓你避免踩坑以及提高查詢效率。
1、允許字段的值為 null,往往會引發災難
首先,先準備點數據,後面好演示
create table animal(
id int,
name char(20),
index(id)
)engine=innodb;
index(id) 表示給 id 這個字段創建索引,並且 id 和 name 都允許為 null。
接著插入4條數據,其中最後一條數據的 id 為。
insert into animal(id, name) values(1, '貓');
insert into animal(id, name) values(2, '狗');
insert into animal(id, name) values(3, '豬');
insert into animal(id, name) values(null, '無名動物');
此時表中的數據為
這時我們查詢表中 id != 1 的動物有哪些
select * from animal where id != 1;
結果如下:
此時我們只找到了兩行數據,按道理應該是三行的,但是 id = null 的這一行居然沒有被匹配到,,可能大家聽說過,null 與任何
其他值都不相等,按道理 null != 1 是成立的話,然而現實很殘酷,它就是不會被匹配到。
所以,堅決不允許字段的值為 null,否則可能會出現與預期不符合的結果。
反正我之前有踩過這個坑,不知道大家踩過木有?
但是萬一有人設置了允許為 null 值怎麼辦?如果真的這樣的話,對於 != 的查找,後面可以多加一個 or id is null 的子句(注意,是 is null,不是 = null,因為 id = null 也不會匹配到值為 null 的行)。即
select * from animal where id != 1 or id is null;
結果如下:
2、儘可能用 union 來代替 or
(1)、剛才我們給 id 這個字段建立了索引,如果我們來進行等值操作的話,一般會走索引操作,不信你看:
explain select * from animal where id = 1;
結果如下:
通過執行計劃可以看見,id 上的等值查找能夠走索引查詢(估計在你的意料之中),其中
type = ref :表示走非唯一索引
rows = 1 :預測掃描一行
(2)、那 id is null 會走索引嗎?答是會的,如圖
explain select * from animal where id is null;
其中type = ref :表示走非唯一索引
rows = 1 :預測掃描一行
(3)、那麼問題來了,那如果我們要找出 id = 1 或者 id = null 的動物,我們可能會用 or 語句來連接,即
select * from animal where id = 1 or id is null;
那麼這條語句會走索引嗎?
有沒有走索引,看執行計劃就知道了,如圖
explain select * from animal where id = 1 or id is null;
其中:
ref = ALL:表示全表掃描
rows = 4 :預測掃描4行(而我們整個表就只有4行記錄)
通過執行計劃可以看出,使用 or 是很有可能不走索引的,這將會大大降低查詢的速率,所以一般不建議使用 or 子句來連接條件。
那麼該如何解決?
其實可以用 union 來取代 or,即如下:
select * from animal where id = 1 union select * from animal where id is null.
此時就會分別走兩次索引,找出所有 id = 1 和 所有 id = null 的行,然後再用一個臨時表來存放最終的結果,最後再掃描臨時表。
3、總結
1、定義表的時候,儘量不允許字段值為 null,可以用 default 設置默認值。
2、儘量用 union 來代替 or,避免查詢沒有走索引。
3、注意,用 id = null 的等值查詢,也是不會匹配到值為 null 的行的,而是應該用 id is null。
也歡迎大家說一說自己踩過的坑。
如果你覺得這篇內容對你挺有啟發,為了讓更多的人看到這篇文章:不妨
1、點贊,讓更多的人也能看到這篇內容(收藏不點贊,都是耍流氓 -_-)
2、關注我和專欄,讓我們成為長期關係
3、關注「苦逼的碼農」,主要寫算法、計算機基礎之類的文章,