The concept and characteristics of MySQL custom variables

A MySQL custom value is a temporary container for storing values. As long as the connection to the server is active, the values ​​in the container can be saved and used. Custom variables can be set with a simple SET or SELECT statement, as follows:

SET @one := 1;
SET @min_actor := (SELECT MIN(actor_id) FROM sakila.actor);
SET @last_week := CURRENT_DATE-INTERNAL 1 WEEK;

After defining the variable, you can use it in SQL statements:

SELECT * FROM film WHERE created_date <= @last_week;

Although MySQL custom variables are very powerful, you also need to know that custom variables themselves have flaws, including:

• Custom variables disable query caching.
• It may not be used as an identifier with alternative semantics, such as a table or column name, or in a LIMIT clause.
• Custom variables are based on a single connection and therefore cannot be used across connections.
• If you use connection pools or persistent connections, custom variables will isolate your code from interactions. When this occurs, it may be a bug in your code or a bug in the connection pool, but it is possible.
• In versions prior to MySQL 5.0, it is case-sensitive, so please be careful (it is no longer case-sensitive after MySQL 5.0).
• It is not possible to explicitly specify the variable type, and the actual variable type depends on uncertain type inference, and different versions may differ. The best way is to specify the type when defining it at the beginning, such as using 0 to set integer variables, 0.0 to set floating-point variables, and '' for strings. However, if a new value is specified later, the type will change with the new value. This is because the type of MySQL custom variables is dynamic.
• In some cases, the optimizer may optimize custom variables, resulting in failure to perform queries as expected.
• The order and even the timing of the assignments are uncertain, depending on the query plan chosen by the optimizer. Therefore, the end result can be confusing.
• The assignment operator has lower precedence than any other operator, so explicit parentheses must be used.
• Undefined variables will not report an error, which can easily lead to errors.
  

Custom variables can be used in all types of statements, not just SELECT statements. In fact, this is one of the biggest advantages of custom variables. For example, we can rewrite complex queries to use subqueries to perform sort calculations, or to perform a low-cost UPDATE statement.

Sometimes, the optimizer will think that the variable is a compile-time constant and not assign a value to it, which can cause expected behavior to be strange. Putting custom variable assignments into a function like LEAST usually avoids this problem. Another way is to check if the custom variable has a value before using it. Sometimes you want to do it, and sometimes you don't.

With a little experimentation, we can do a lot of interesting things with custom variables, such as:

• Calculate totals and averages;
• Simulate FIRST and LAST functions for grouped queries;
• Perform mathematical operations on very large numbers;
• Convert an entire table into an MD5 hash;
• Filter out sample values ​​that are close to 0 but exceed the set boundary;
• Simulate read and write pointer positions;
• Embed variables into WHERE conditions and display them in SHOW statements.
  

Custom variables are useful in some applications, such as counting the number of occurrences of sorted values, calculating the number of updates and inserts of the same data, and delayed joint queries. The next article will introduce the specific usage.

The above is the detailed content of the concept and characteristics of MySQL custom variables. For more information about MySQL custom variables, please pay attention to other related articles on 123WORDPRESS.COM!