Translation: User variables (user-defined Variable) (submitted to MARIADB official manual)

This is the official manual of mariadb: user-defined variables.

Original:https://mariadb.com/kb/en/user-defined-variables/
I submit it to MARIADB official manual:https://mariadb.com/kb/zh-cn/user-defined-variables/

User variables are variables that are created by the user and coexist in the session. This means that other users cannot access it, and the user variable automatically expires when the current session is closed. However, these variables can be shared between multiple queries and stored program stored programs .

The name of the user variable must be preceded by a single at sign (@). The name part of the user variable can be used safely with reserved keywords, and the characters allowed in the name section include ASCII letters, numbers, dollar symbols ($), underscores (_), and dots (.). If you want to use a different character, you need to surround it with the following types of references:

• @ ' Var_name '
• @ ' Var_name '
• @ "Var_name"

These characters can be escaped normally (escaping).

User variables are now case insensitive, but are case-sensitive in MySQL 4.1 and older versions.

The user variable cannot be declared. You can directly read a user variable that exists or does not exist, except that the non-existent user variable returns a value of NULL. To set up a user variable, you can use the following methods:

• SET statement;
• Used in SQL statements : = operator;
• SELECT ... into.

Because the types of user variables cannot be declared, the only way to enforce them is by using cast () or convert () :

SET @str = CAST(123 AS CHAR(5));

If the variable is not assigned a value, its value is null:

SELECT @x IS NULL;+------------+| @x IS NULL |+------------+|          1 |+------------+

It is unsafe to read a variable at the same time in the same statement, setting the value of the variable (unless you are using the SET command) because the order of these operations is variable.

User variables can be used in the vast majority of MARIADB statements and clauses that can accept SQL expressions. However, there are some exceptions, such as the LIMIT clause.

User variables cannot replace keywords, identifiers, etc. in statements unless they are used in precompiled PREPARE statements. (Translator note: The English version of the content is missing, this sentence for my own amendment.) For example @a="SELECT" , but not @a * from t . As in the following example, if the user variable @sql is not precompiled in the prepare statement, it cannot be executed directly)

@sql = ‘DELETE FROM my_table WHERE c>1;‘;PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;

Another more common feature is to act as a counter in a query statement:

SET @var = 0;SELECT a, b, c, (@var:[email protected]var+1) AS counter FROM my_table;

Back to Linux series article outline: http://www.cnblogs.com/f-ck-need-u/p/7048359.html
Back to Site Architecture series article outline: http://www.cnblogs.com/f-ck-need-u/p/7576137.html
Back to Database series article outline: http://www.cnblogs.com/f-ck-need-u/p/7586194.html
Reprint Please specify source: http://www.cnblogs.com/f-ck-need-u/p/8620915.html

Note: If you think this article is not bad please click on the lower right corner of the recommendation, your support can inspire the author more enthusiasm for writing, thank you very much!

Translation: User variable (user-defined Variable) (submitted to MARIADB official manual)