溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
本文實(shí)例講述了mysql中null（IFNULL，COALESCE和NULLIF）相關(guān)知識點(diǎn)。分享給大家供大家參考，具體如下：
在MySQL中，NULL值表示一個未知值，它不同于0或空字符串''，并且不等于它自身。
我們?nèi)绻麑ULL值與另一個NULL值或任何其他值進(jìn)行比較，則結(jié)果為NULL，因?yàn)橐粋€不知道是什么的值(NULL值)與另一個不知道是什么的值(NULL值)比較，其值當(dāng)然也是一個不知道是什么的值(NULL值)。
然而我們通常，使用NULL值來表示數(shù)據(jù)丟失，未知或不適用的情況。 例如，潛在客戶的電話號碼可能為NULL，并且可以稍后添加。所以我們創(chuàng)建表時(shí)，可以通過使用NOT NULL約束來指定列是否接受NULL值。接下來，我們來創(chuàng)建一張leads表，并且以此為依據(jù)來具體了解下：
CREATE TABLE leads ( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, source VARCHAR(255) NOT NULL, email VARCHAR(100), phone VARCHAR(25) );
我們可以看出來，id是主鍵列，它不接受任何NULL值，然后first_name，last_name和source列使用NOT NULL約束，因此，不能在這些列中插入任何NULL值，而email和phone列則可接受NULL值。
所以，我們可以在insert語句中使用NULL值來指定數(shù)據(jù)丟失。 例如，以下語句將一行插入到線索表中。 因?yàn)殡娫捥柎a丟失，所以使用NULL值：
INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('John','Doe','Web Search','john.doe@yiibai.com',NULL);
因?yàn)閑mail列的默認(rèn)值為NULL，可以按照以下方式在INSERT語句中省略電子郵件：
INSERT INTO leads(first_name,last_name,source,phone)
VALUES('Lily','Bush','Cold Calling','(408)-555-1234'),
('David','William','Web Search','(408)-888-6789');
完事如果我們要將列的值設(shè)置為NULL，可以使用賦值運(yùn)算符(=)。 例如，要將David William的手機(jī)(phone)更新為NULL，請使用以下UPDATE語句：
UPDATE leads SET phone = NULL WHERE id = 3;
但是如果使用order by子句按升序?qū)Y(jié)果集進(jìn)行排序，則MySQL認(rèn)為NULL值低于其他值，因此，它會首先顯示NULL值。以下查詢語句按照電話號碼(phone)升序排列：
SELECT * FROM leads ORDER BY phone;
執(zhí)行上面查詢語句，結(jié)果如下：
+----+------------+-----------+--------------+---------------------+----------------+ | id | first_name | last_name | source | email | phone | +----+------------+-----------+--------------+---------------------+----------------+ | 1 | John | Doe | Web Search | john.doe@yiibai.com | NULL | | 3 | David | William | Web Search | NULL | NULL | | 2 | Lily | Bush | Cold Calling | NULL | (408)-555-1234 | +----+------------+-----------+--------------+---------------------+----------------+
如果使用ORDER BY DESC，NULL值將顯示在結(jié)果集的最后：
SELECT * FROM leads ORDER BY phone DESC;
執(zhí)行上面查詢語句，結(jié)果如下：
+----+------------+-----------+--------------+---------------------+----------------+ | id | first_name | last_name | source | email | phone | +----+------------+-----------+--------------+---------------------+----------------+ | 2 | Lily | Bush | Cold Calling | NULL | (408)-555-1234 | | 1 | John | Doe | Web Search | john.doe@yiibai.com | NULL | | 3 | David | William | Web Search | NULL | NULL | +----+------------+-----------+--------------+---------------------+----------------+ 3 rows in set
我們?nèi)绻诓樵冎袦y試NULL，可以在where子句中使用IS NULL或IS NOT NULL運(yùn)算符。例如，要獲得尚未提供電話號碼的潛在客戶，請使用IS NULL運(yùn)算符，如下所示：
SELECT * FROM leads WHERE phone IS NULL;
執(zhí)行上面查詢語句，結(jié)果如下：
+----+------------+-----------+------------+---------------------+-------+ | id | first_name | last_name | source | email | phone | +----+------------+-----------+------------+---------------------+-------+ | 1 | John | Doe | Web Search | john.doe@yiibai.com | NULL | | 3 | David | William | Web Search | NULL | NULL | +----+------------+-----------+------------+---------------------+-------+ 2 rows in set
我們還可以使用IS NOT運(yùn)算符來獲取所有提供電子郵件地址的潛在客戶：
SELECT * FROM leads WHERE email IS NOT NULL;
執(zhí)行上面查詢語句，結(jié)果如下：
+----+------------+-----------+------------+---------------------+-------+ | id | first_name | last_name | source | email | phone | +----+------------+-----------+------------+---------------------+-------+ | 1 | John | Doe | Web Search | john.doe@yiibai.com | NULL | +----+------------+-----------+------------+---------------------+-------+ 1 row in set
然而，即使NULL不等于NULL，GROUP BY子句中視兩個NULL值相等，來看下sql實(shí)例：
SELECT email, count(*) FROM leads GROUP BY email;
該查詢只返回兩行，因?yàn)槠溧]箱(email)列為NULL的行被分組為一行，結(jié)果如下所示：
+---------------------+----------+ | email | count(*) | +---------------------+----------+ | NULL | 2 | | john.doe@yiibai.com | 1 | +---------------------+----------+ 2 rows in set
我們要知道在列上使用唯一約束或UNIQUE索引時(shí)，可以在該列中插入多個NULL值，在這種情況下，MySQL認(rèn)為NULL值是不同的。接下來我們通過為phone列創(chuàng)建一個UNIQUE索引來驗(yàn)證這一點(diǎn)：
CREATE UNIQUE INDEX idx_phone ON leads(phone);
這里我們要注意，如果使用BDB存儲引擎的話，mysql會認(rèn)為NULL值相等，因此我們不能將多個NULL值插入到具有唯一約束的列中。
既然知道了null的好處和壞處，我們就來看下在mysql中應(yīng)該如何處理它吧。mysql一共提供了三個函數(shù)，分別是IFNULL，COALESCE和NULLIF。
我們來分別看下，首先，IFNULL函數(shù)接受兩個參數(shù)。 如果IFNULL函數(shù)不為NULL，則返回第一個參數(shù)，否則返回第二個參數(shù)。例如，如果不是NULL，則以下語句返回電話號碼(phone)，否則返回N/A，而不是NULL。來看個實(shí)例：
SELECT id, first_name, last_name, IFNULL(phone, 'N/A') phone FROM leads;
執(zhí)行上面查詢語句，得到以下結(jié)果：
+----+------------+-----------+----------------+ | id | first_name | last_name | phone | +----+------------+-----------+----------------+ | 1 | John | Doe | N/A | | 2 | Lily | Bush | (408)-555-1234 | | 3 | David | William | N/A | +----+------------+-----------+----------------+ 3 rows in set
完事就是COALESCE函數(shù)，它接受參數(shù)列表，并返回第一個非NULL參數(shù)。 例如，可以使用COALESCE函數(shù)根據(jù)信息的優(yōu)先級按照以下順序顯示線索的聯(lián)系信息：phone, email和N/A。以下是案例：
SELECT id, first_name, last_name, COALESCE(phone, email, 'N/A') contact FROM leads;
執(zhí)行上面查詢語句，得到以下代碼：
+----+------------+-----------+---------------------+ | id | first_name | last_name | contact | +----+------------+-----------+---------------------+ | 1 | John | Doe | john.doe@yiibai.com | | 2 | Lily | Bush | (408)-555-1234 | | 3 | David | William | N/A | +----+------------+-----------+---------------------+ 3 rows in set
最后就是NULLIF函數(shù)了，它接受兩個參數(shù)。如果兩個參數(shù)相等，則NULLIF函數(shù)返回NULL。 否則，它返回第一個參數(shù)。在列中同時(shí)具有NULL和空字符串值時(shí)，NULLIF函數(shù)很有用。 例如，我們錯誤地將以下行插入到leads表中：
INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('Thierry','Henry','Web Search','thierry.henry@yiibai.com','');
因?yàn)閜hone是一個空字符串:''，而不是NULL。所以，如果我們想獲得潛在客戶的聯(lián)系信息，則最終得到空phone，而不是電子郵件，如下所示：
SELECT id, first_name, last_name, COALESCE(phone, email, 'N/A') contact FROM leads;
執(zhí)行上面查詢語句，得到以下代碼：
+----+------------+-----------+---------------------+ | id | first_name | last_name | contact | +----+------------+-----------+---------------------+ | 1 | John | Doe | john.doe@yiibai.com | | 2 | Lily | Bush | (408)-555-1234 | | 3 | David | William | N/A | | 4 | Thierry | Henry | | +----+------------+-----------+---------------------+
我們?nèi)绻鉀Q這個問題，就要使用NULLIF函數(shù)將電話與空字符串('')進(jìn)行比較，如果相等，則返回NULL，否則返回電話號碼：
SELECT id, first_name, last_name, COALESCE(NULLIF(phone, ''), email, 'N/A') contact FROM leads;
執(zhí)行上面查詢語句，得到以下代碼：
+----+------------+-----------+--------------------------+ | id | first_name | last_name | contact | +----+------------+-----------+--------------------------+ | 1 | John | Doe | john.doe@yiibai.com | | 2 | Lily | Bush | (408)-555-1234 | | 3 | David | William | N/A | | 4 | Thierry | Henry | thierry.henry@yiibai.com | +----+------------+-----------+--------------------------+ 4 rows in set
好啦，本次記錄就到這里了。
更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《MySQL查詢技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲過程技巧大全》、《MySQL數(shù)據(jù)庫鎖相關(guān)技巧匯總》及《MySQL常用函數(shù)大匯總》
希望本文所述對大家MySQL數(shù)據(jù)庫計(jì)有所幫助。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機(jī)網(wǎng)站二維碼