How to compile reusable MySQL queries

When people mention reusable queries, the stored procedure is often immediately reflected in their minds. 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 introduce you in detail how to combine these elements so that our selection statements can be better suited to various queries.

　　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, you may need up to six connection operations to organize the user profile information in the database into a third paradigm. For example:

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