教你編寫可重用的MySQL查詢

編寫可重用的MySQL查詢，很多人第一反映就是使用存儲過程，雖然這些存儲過程是編寫可重用代碼不可分割的一部分，但要記住的是，它們只是很少的一部分而已，而非全部。此外，其它可重用代碼包括視圖、內置函數以及用戶定義的函數。在本文中，我們將向讀者詳細介紹如何組合這些元素，以令我們的選擇語句可以更好的適用於各種查詢。

　　一、關於視圖

　　視圖的用途很多，例如簡化復雜的模式及查詢，或者提供安全性等等。視圖提供安全性的一種途徑是對開發者隱藏審計字段。視圖還可通過減少列的數目來提高性能。這個想法是只引用索引字段，而索引字段的搜索速度是非常之快的。實際上，這種想法實現起來很費勁，因為你必須確保不會訪問隱藏列。然而，我們這裡主要是利用視圖模擬兩個或更多個表之間的連接，以降低查詢的復雜性。很多時候，要想將數據庫中用戶的概要信息整理成符合第三范式的形式，可能需要多達六次連接操作，例如：

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

 　　下面，我們用一個視圖來替換上面的查找過程：

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;

 　　現在，我們可以通過以下簡單的選擇語句來檢索用戶概要信息了：

select * 
from   vusers u
where  u.user_id = 100

 　 二、關於MySQL內置函數

 

　　GROUP_CONCAT()函數可以用來聚集表中的所有行，並返回組成交叉表水平軸的SELECT列表。實際上，這使得將交叉表的選擇語句移植到存儲過程中成為可能。其他的函數，如Count()、Month()和MonthName()，以及過濾語句，如CASE WHEN ELSE，都可以讓我們的代碼更具通用性。

　　三、建立自己的函數

　　如果在MySQL內建的函數中沒有我們所想要的，那麼我們不妨自己動手，豐衣足食。 在編寫自己的函數的時候，一定要考慮到該函數的通用性。

　　下面是一個示例函數，用來檢查是否指定了強制性的存儲過程參數。這裡不允許使用空行或者空白符，所以該函數將進行相應的檢查：

BEGIN
  DECLARE isEmpty TINYINT;
  SET isEmpty = (param_name IS NULL or char_length(trim(param_name)) = 0);
  RETURN isEmpty;
END

 　　注意，在我們的函數中調用了內建的兩個函數，即char_length()和trim()。現在，我們總能夠將其作為一個通用函數使用了。

　　需要提示的是，在我們編寫自己的函數之前，最好先在網上搜索一下，看看別人是否已經做過這項工作了，免得重復相同的工作。這時，我們要特別留意那些MySQL函數倉庫站點，如www.MySQLudf.org，我們很可能在這裡找到所需的函數。

四、存儲過程

　　我們知道，存儲過程能夠起到代碼模塊化和集中化的作用。然而，將SQL代碼放入存儲過程本身並不意味著就能提高通用性或者可重用性。舉例來說，下面的語句將生成一份反映各員工去年獎金總數的報告：

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;

 　　下面我們將其轉變成一個存儲過程：

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;

 　　現在，其他人或程序就可以方便的使用這個過程了，不過這裡有個時間限制，就是只能在明年之前使用。但是，我們為什麼要創建這種有限制的東西呢?因為，我們每年都可能需要生成相似的報告，所以下面我們要去掉這個時間限制。

　　為此，我們將該過程中的硬編碼的日期刪除掉，如下所示：

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;

 　　作為一名有上進心的開發人員，我們會自問是否可以做得更好呢?客戶程序也許對起始日期和結束日期方面有更高的靈活性要求，比如他們可能要求日期范圍與財政年度一致。從這方面考慮，不管客戶程序是否要求，我們的都必須提供一個起始日期和終止日期參數。 MySQL有一個非常不錯的BETWEEN運算符，可以用來處理某個范圍內的值。

　　下面我們就將其用於起始日期和終止日期：

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;

 　　五、小結

　　在本文中，我們討論了如何利用視圖、內建函數和用戶定義函數以及存儲過程來提高SELECT查詢的通用性和可重用性。為了便於理解，我們還給出了一些實例代碼，以便幫助讀者理解本文講到的內容。根據局部性原理，現在執行的操作，近期內很可能會再次執行該操作，所以提高可重用性是非常有幫助的。