怎样编写可重用的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;