程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> 教你編寫可重用的MySQL查詢

教你編寫可重用的MySQL查詢

編輯:關於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查詢的通用性和可重用性。為了便於理解,我們還給出了一些實例代碼,以便幫助讀者理解本文講到的內容。根據局部性原理,現在執行的操作,近期內很可能會再次執行該操作,所以提高可重用性是非常有幫助的。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved