MySQL can customize temporary variables, which are useful for query optimization and static query analysis. Few people actually remember MySQL’s custom variables. This article describes the concept and features of MySQL custom variables.

MySQL customization is a temporary container for storing values that can be saved and used as long as the connection to the server is active. 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;
Copy the code

Once the variable is defined, it can be used in SQL statements:

SELECT * FROM film WHERE created_date < = @last_week;
Copy the code

While MySQL custom variables are powerful, it is important to know that custom variables have their own drawbacks. These include:

  • Custom variables disable query caching.
  • Should not be used as a substitute for semantic identifiers, such as table or column names, or in the LIMIT clause.
  • Custom variables are based on a single connection and therefore cannot be used across connections.
  • If you use connection pooling or persistent connections, custom variables can cause your code to be isolated from the interaction, which can be a code bug or a connection pool bug, but it can happen.
  • It is case-sensitive in MySQL prior to 5.0, so be careful (it has become case-insensitive since MySQL 5.0).
  • It is best to specify the type at the beginning of the definition, such as 0 for integer variables, 0.0 for floating-point variables, and “” for strings. However, if a new value is later specified, the type changes with the new value, because MySQL’s custom variable types are dynamic.
  • It is possible that the optimizer will, in some cases, tune out custom variables, resulting in queries that do not perform as expected.
  • The order of assignments, and even the timing of assignments, is uncertain, depending on the query plan chosen by the optimizer. So the end result can be confusing.
  • The assignment operator has a lower precedence than any other operator, so parentheses must be used explicitly.
  • Undefined variables do not generate errors, which can 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, such as sorting calculations using subqueries, or completing a low-cost UPDATE statement.

In some cases, the optimizer will assume that this variable is a compile-time constant and not assign it, leading to strange expected behavior. Putting the assignment of a custom variable into a function like LEAST usually avoids this problem. Another way is to check if a custom variable has a value before using it. Sometimes you want to, and sometimes you don’t.

With some small experiments, we can do a lot of interesting things with custom variables, such as:

  • Calculate the total and average value;
  • Simulate the FIRST and LAST functions for grouped queries;
  • Doing math on very large numbers;
  • Convert an entire table to an MD5 hash;
  • Filter out sample values near 0 but beyond the set boundary;
  • Analog read and write pointer position;
  • Embed the variable into the WHERE condition and display it in the SHOW statement.

Custom variables are useful in some applications, such as counting sorted values for occurrences, counting updates and inserts of the same data, and delayed union queries, which will be covered in the next article.