怎样编写可重用的MySQL查询?[MySQL防范]

　　当人们说起可重用的查询的时刻,当即映入脑海的常常就是存储历程了.固然这些存储历程是编写可重用代码不可分割的一部份,但要记着的是,它们只是很少的一部份罢了,而非全部.此外,别的可重用代码包含视图、内置函数以及用户定义的函数.在本文中,我们将向读者具体介绍若何组合这些元素,以令我们的挑选语句可以更好的实用于各种查询.

　　步骤/办法

　　关于视图

　　视图的用处很多,比方简化复杂的情势及查询,大概供应安全性等等.视图供应安全性的一种途径是对开辟者躲藏审计字段.视图还可通过削减列的数目来提高性能.这个设法是只引用索引字段,而索引字段的搜索速度是非常之快的.实际上,这种设法实现起来很吃力,因为你必须确保不会拜候躲藏列.但是,我们这里主如果操纵视图模拟两个或更多个表之间的衔接,以降低查询的复杂性.很多时刻,要想将数据库顶用户的概要信息整理成符合第三范式的情势,大概需求多达六次衔接操作,比方:

　　select * from Users u

　　inner join UserPhoneNumbers upnon u.user_id = upn.user_id inner join

　　UserScreenNames usnon 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 uwhon 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 upnon

　　u.user_id = upn.user_id inner join UserScreenNames

　　usnon 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 uwhon u.user_id = uwh.user_id

　　inner join Affiliations wa on uwh.affiliation_id= wa.affiliation_id;

　　目前,我们可以通过以下简单的挑选语句来检索用户概要信息了:

　　select * from vusers uwhere u.user_id = 100

　　关于MySQL内置函数

　　GROUP_CONCAT()函数可以用来堆积表中的全部行,并返回构成穿插表水平轴的SELECT列表.实际上,这使得将穿插表的挑选语句移植到存储历程中成为大概.其他的函数,如Count()、Month()和MonthName(),以及过滤语句,如CASE WHEN ELSE,都可以让我们的代码更具通用性.

　　成立自己的函数

　　假如在MySQL内建的函数中没有我们所想要的,那么我们无妨自己着手,丰衣足食. 在编写自己的函数的时刻,一定要考虑到该函数的通用性.

　　下面是一个示例函数,用来查抄能否指定了强迫性的存储历程参数.这里不答应利用空行大概空白符,所以该函数将举行呼应的查抄:

　　BEGIN DECLARE isEmptyTINYINT;

　　SET isEmpty= (param_nameIS NULL or char_length(trim(param_name))= 0);

　　RETURN isEmpty;

　　END

　　注意,在我们的函数中调用了内建的两个函数,即char_length()和trim().目前,我们总可以将其作为一个通用函数利用了.

　　需求提醒的是,在我们编写自己的函数之前,最好先在网上搜索一下,看看别人能否已经做过这项工作了,免得反复相同的工作.这时,我们要分外留神那些MySQL函数仓库站点,我们极大概在这里找到所需的函数.

　　存储历程

　　我们知道,存储历程可以起到代码模块化和集合化的作用.但是,将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 bonusesWHERE YEAR(award_date)= 2010)AS b

　　ON e.id= b.emp_idGROUP 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_idFROM bonusesWHERE YEAR(award_date)= 2010)AS b

　　ON e.id= b.emp_idGROUP 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_idFROM bonusesWHERE YEAR(award_date)= @year)AS b ON e.id= b.emp_idGROUP 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_dateBetween @StartDate AND @EndDate)AS b

　　ON e.id= b.emp_idGROUP BY e.id;

　　END;

　　注意事项

　　按照部分性原理,目前履行的操作,近期内极大概会再次履行该操作,所以提高可重用性是非常有帮忙的.