How to compile reusable MySQL queries (1)

Guidance:When people mention reusableMySQL QueryThe storage process is often reflected in your mind. Although these stored procedures are an integral part of the preparation of reusable code, remember that they are only a small part, not all. In addition, other reusable code includes views, built-in functions, and user-defined functions. In this article, we will explain howWriteReusable MySQL queries make our selection statement more suitable for various queries, or make MySQL queries better.

1. View
A view has many purposes, such as simplifying complex modes and queries, or providing security. View provides security by hiding audit fields from developers. The view can also improve performance by reducing the number of columns. This idea is to reference only index fields, and the search speed of index fields is very fast. In fact, this idea is very difficult to implement because you must ensure that hidden columns are not accessed. However, we use views to simulate the connection between two or more tables to reduce the query complexity. Most of the time, to sort the user profile information in the database into a third paradigm, it may take up to six connection operations, such 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

Next, we use a view to replace the above search process:

Create view 'vusers'
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;

Now, we can use the following simple selection statement to retrieve the user summary:

Select *
From vusers u

Where u. User ID = 100