Shulou(Shulou.com)05/31 Report--

This article focuses on "how to write reusable MySQL queries". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to write reusable MySQL queries.

When people mention reusable queries, stored procedures are often immediately remembered. While these stored procedures are an integral part of writing reusable code, keep in mind that they are only a few, not all. In addition, other reusable code includes views, built-in functions, and user-defined functions. In this article, we will show readers how to combine these elements in detail so that our selection statements can be better applied to various queries.

I. about the view

Views have many uses, such as simplifying complex schemas and queries, or providing security, and so on. One way for views to provide security is to hide audit fields from developers. Views can also improve performance by reducing the number of columns. The idea is to reference only index fields, and index fields are searched very fast. In fact, this idea is hard to implement because you have to make sure that you don't access hidden columns. However, we mainly use views to simulate joins between two or more tables to reduce the complexity of queries. In many cases, it may take up to six connection operations to organize the profile information of users in the database into a form that conforms to the third paradigm, such as:

Double click code 1 2 3 4 5 6 7 8select * 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 an 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

Next, we replace the above lookup process with a view:

Double click the code to select all 1 2 3 4 5 6 7 8 9CREATE 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 an 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

Now we can retrieve the user profile with the following simple select statement:

Double-click the code to select all 1 2 3select * from vusers uwhere u.user_id = 100

II. About the built-in functions of MySQL

The GROUP_CONCAT () function can be used to aggregate all the rows in the table and return a list of SELECT that make up the horizontal axis of the crosstab. In fact, this makes it possible to migrate crosstab selection statements to stored procedures. Other functions, such as Count (), Month (), and MonthName (), as well as filtering statements such as CASE WHEN ELSE, can make our code more versatile.

Third, set up your own function

If we don't have what we want in the built-in functions of MySQL, then we might as well do it ourselves. When writing your own function, be sure to consider the versatility of the function.

The following is an example function to check whether mandatory stored procedure parameters are specified. Blank lines or blank characters are not allowed here, so the function will check accordingly:

Double click code 1 2 3 4 5BEGIN DECLARE isEmpty TINYINT; SET isEmpty = (param_name IS NULL or char_length (trim (param_name)) = 0); RETURN isEmpty;END

Notice that the two built-in functions, char_length () and trim (), are called in our function. Now, we can always use it as a general function.

It is important to note that before we write our own function, it is best to search the Internet to see if someone else has already done the work, so as not to repeat the same work. At this point, we should pay special attention to those MySQL function repository sites, such as www.mysqludf.org, where we are likely to find the functions we need.

IV. Stored procedures

We know that stored procedures can play the role of code modularization and centralization. However, putting SQL code into a stored procedure does not in itself mean that it improves versatility or reusability. For example, the following statement will generate a report that reflects the total bonus of each employee last year:

Double click code 12 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25SELECT 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_idGROUP BY e.id Let's turn it into a stored procedure: CREATE PROCEDURE `pause 2010 bonuseslst` () LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGINSELECT 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_idGROUP BY e.id; END

Now, other people or programs can easily use this process, but there is a time limit, that is, it can only be used before next year. But why should we create such a limited thing? Because we may need to generate similar reports every year, let's remove this time limit.

To do this, we delete the hard-coded dates in the process, as follows:

Double-click the code 12 3 4 5 6 7 8 9 10 11 12 13 14 15 16CREATE PROCEDURE `paged yearbook bonuseslst` (IN `@ year` INT) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGINSELECT 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_idGROUP BY e.id; END

As motivated developers, we ask ourselves if we can do better. Client programs may have more flexibility in terms of start and end dates, for example, they may require that the date range be the same as the fiscal year. From this point of view, we must provide a start and end date parameter regardless of whether the client requires it or not. MySQL has a very nice BETWEEN operator that can be used to handle values within a range.

Let's use it for the start and end dates:

Double-click the code 12 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20CREATE PROCEDURE `paired bonuseslst` (IN `@ StartDate` DATETIME, IN` @ EndDate` DATETIME) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGINSELECT 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_idGROUP BY e.id; END

5. Summary

In this article, we discussed how to use views, built-in and user-defined functions, and stored procedures to improve the versatility and reusability of SELECT queries. In order to make it easier to understand, we also give some sample code to help readers understand what is mentioned in this article. According to the locality principle, the operation performed now is likely to be performed again in the near future, so it is very helpful to improve reusability.

At this point, I believe you have a deeper understanding of "how to write reusable MySQL queries". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.