巧解如何編寫可重用的MySQL查詢
導(dǎo)讀：當(dāng)人們提及可重用的MySQL查詢的時(shí)候，立即映入腦海的往往就是存儲(chǔ)過(guò)程了。雖然這些存儲(chǔ)過(guò)程是編寫可重用代碼不可分割的一部分，但要記住的是，它們只是很少的一部分而已，而非全部。此外，其它可重用代碼包括視圖、內(nèi)置函數(shù)以及用戶定義的函數(shù)。在本文中，我們將向讀者詳細(xì)介紹如何編寫可重用的MySQL查詢，以令我們的選擇語(yǔ)句可以更好的適用于各種查詢，也可以將MySQL查詢的工作做的更好。
一、關(guān)于視圖
視圖的用途很多，例如簡(jiǎn)化復(fù)雜的模式及查詢，或者提供安全性等等。視圖提供安全性的一種途徑是對(duì)開(kāi)發(fā)者隱藏審計(jì)字段。視圖還可通過(guò)減少列的數(shù)目來(lái)提高性能。這個(gè)想法是只引用索引字段，而索引字段的搜索速度是非常之快的。實(shí)際上，這種想法實(shí)現(xiàn)起來(lái)很費(fèi)勁，因?yàn)槟惚仨毚_保不會(huì)訪問(wèn)隱藏列。然而，我們這里主要是利用視圖模擬兩個(gè)或更多個(gè)表之間的連接，以降低查詢的復(fù)雜性。很多時(shí)候，要想將數(shù)據(jù)庫(kù)中用戶的概要信息整理成符合第三范式的形式，可能需要多達(dá)六次連接操作，例如：select *
from Users u
inner join UserPhoneNumbers upn on u.user_id = upn.user_id
inner join UserScreenNames usn on u.user_id = usn.user_id
inner join UserAffiliations ua on u.user_id = ua.user_id
inner join Affiliations a on a.affiliation_id = ua.affiliation_id
inner join UserWorkHistory uwh on u.user_id = uwh.user_id
inner join Affiliations wa on uwh.affiliation_id = wa.affiliation_id
下面，我們用一個(gè)視圖來(lái)替換上面的查找過(guò)程：
CREATE VIEW `vusers` AS
select *
from Users u
inner join UserPhoneNumbers upn on u.user_id = upn.user_id
inner join UserScreenNames usn on u.user_id = usn.user_id
inner join UserAffiliations ua on u.user_id = ua.user_id
inner join Affiliations a on a.affiliation_id = ua.affiliation_id
inner join UserWorkHistory uwh on u.user_id = uwh.user_id
inner join Affiliations wa on uwh.affiliation_id = wa.affiliation_id;
現(xiàn)在，我們可以通過(guò)以下簡(jiǎn)單的選擇語(yǔ)句來(lái)檢索用戶概要信息了：
select *
from vusers u
where u.user_id = 100
#p#
二、關(guān)于MySQL內(nèi)置函數(shù)
GROUP_CONCAT()函數(shù)可以用來(lái)聚集表中的所有行，并返回組成交叉表水平軸的SELECT列表。實(shí)際上，這使得將交叉表的選擇語(yǔ)句移植到存儲(chǔ)過(guò)程中成為可能。其他的函數(shù)，如Count()、Month()和MonthName()，以及過(guò)濾語(yǔ)句，如CASE WHEN ELSE，都可以讓我們的代碼更具通用性。
三、建立自己的函數(shù)
如果在MySQL內(nèi)建的函數(shù)中沒(méi)有我們所想要的，那么我們不妨自己動(dòng)手，豐衣足食。 在編寫自己的函數(shù)的時(shí)候，一定要考慮到該函數(shù)的通用性。
下面是一個(gè)示例函數(shù)，用來(lái)檢查是否指定了強(qiáng)制性的存儲(chǔ)過(guò)程參數(shù)。這里不允許使用空行或者空白符，所以該函數(shù)將進(jìn)行相應(yīng)的檢查：
BEGIN
DECLARE isEmpty TINYINT;
SET isEmpty = (param_name IS NULL or char_length(trim(param_name)) = 0);
RETURN isEmpty;
END
注意，在我們的函數(shù)中調(diào)用了內(nèi)建的兩個(gè)函數(shù)，即char_length()和trim()。現(xiàn)在，我們總能夠?qū)⑵渥鳛橐粋€(gè)通用函數(shù)使用了。
需要提示的是，在我們編寫自己的函數(shù)之前，***先在網(wǎng)上搜索一下，看看別人是否已經(jīng)做過(guò)這項(xiàng)工作了，免得重復(fù)相同的工作。這時(shí)，我們要特別留意那些MySQL函數(shù)倉(cāng)庫(kù)站點(diǎn)，如www.mysqludf.org，我們很可能在這里找到所需的函數(shù)。
#p#
四、存儲(chǔ)過(guò)程
我們知道，存儲(chǔ)過(guò)程能夠起到代碼模塊化和集中化的作用。然而，將SQL代碼放入存儲(chǔ)過(guò)程本身并不意味著就能提高通用性或者可重用性。舉例來(lái)說(shuō)，下面的語(yǔ)句將生成一份反映各員工去年獎(jiǎng)金總數(shù)的報(bào)告：
SELECT e.name,
e.salary,
COUNT(b.bonus_id) AS "Total Bonuses"
FROM employees e
LEFT OUTER JOIN
(SELECT emp_id, bonus_id FROM bonuses WHERE YEAR(award_date) = 2010) AS b
ON e.id = b.emp_id
GROUP BY e.id;
下面我們將其轉(zhuǎn)變成一個(gè)存儲(chǔ)過(guò)程：
CREATE PROCEDURE `p_2010_bonuses_lst`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
SELECT e.name,
e.salary,
COUNT(b.bonus_id) AS "Total Bonuses"
FROM employees e
LEFT OUTER JOIN
(SELECT emp_id, bonus_id FROM bonuses WHERE YEAR(award_date) = 2010) AS b
ON e.id = b.emp_id
GROUP BY e.id;
END;
現(xiàn)在，其他人或程序就可以方便的使用這個(gè)過(guò)程了，不過(guò)這里有個(gè)時(shí)間限制，就是只能在明年之前使用。但是，我們?yōu)槭裁匆獎(jiǎng)?chuàng)建這種有限制的東西呢?因?yàn)椋覀兠磕甓伎赡苄枰上嗨频膱?bào)告，所以下面我們要去掉這個(gè)時(shí)間限制。
為此，我們將該過(guò)程中的硬編碼的日期刪除掉，如下所示：
CREATE PROCEDURE `p_yearly_bonuses_lst`(IN `@year` INT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
SELECT e.name,
e.salary,
COUNT(b.bonus_id) AS "Total Bonuses"
FROM employees e
LEFT OUTER JOIN
(SELECT emp_id, bonus_id FROM bonuses WHERE YEAR(award_date) = @year) AS b
ON e.id = b.emp_id
GROUP BY e.id;
END;
作為一名有上進(jìn)心的開(kāi)發(fā)人員，我們會(huì)自問(wèn)是否可以做得更好呢?客戶程序也許對(duì)起始日期和結(jié)束日期方面有更高的靈活性要求，比如他們可能要求日期范圍與財(cái)政年度一致。從這方面考慮，不管客戶程序是否要求，我們的都必須提供一個(gè)起始日期和終止日期參數(shù)。 MySQL有一個(gè)非常不錯(cuò)的BETWEEN運(yùn)算符，可以用來(lái)處理某個(gè)范圍內(nèi)的值。
下面我們就將其用于起始日期和終止日期：
CREATE PROCEDURE `p_bonuses_lst`( IN `@StartDate` DATETIME,
IN `@EndDate` DATETIME )
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
SELECT e.name,
e.salary,
COUNT(b.bonus_id) AS "Total Bonuses"
FROM employees e
LEFT OUTER JOIN
(SELECT emp_id,
bonus_id
FROM bonuses
WHERE award_date Between @StartDate AND @EndDate) AS b
ON e.id = b.emp_id
GROUP BY e.id;
END;
五、小結(jié)
在本文中，我們討論了如何利用視圖、內(nèi)建函數(shù)和用戶定義函數(shù)以及存儲(chǔ)過(guò)程來(lái)提高SELECT查詢的通用性和可重用性。為了便于理解，我們還給出了一些實(shí)例代碼，以便幫助讀者理解本文講到的內(nèi)容。根據(jù)局部性原理，現(xiàn)在執(zhí)行的操作，近期內(nèi)很可能會(huì)再次執(zhí)行該操作，所以提高可重用性是非常有幫助的。希望大家通過(guò)這篇文章的閱讀，能夠掌握如何編寫可重用的MySQL查詢的方法，為以后的工作能有所幫助。
【編輯推薦】
