This article uses examples to illustrate the usage of MySQL variables. Share with you for your reference, the details are as follows:

In this article:

• System variables
• User variables
  • Local variables

Release date: 2018-04-18

System variables:

• System variables are variables that have been defined in advance by the system.
• System variables generally have special meanings. For example, some variables represent character sets, and some variables represent certain MySQL file locations.
• System variables include session-level variables (variables that take effect when the session is connected, such as names), and global variables (variables that are always effective). [Global variables and session variables in the system actually use a set of variables. The difference is that session variables are only effective when the session is connected. 】
• • Assignment of session variables: set variable name = value; [such as the commonly used set names = "utf8";] or set @@variable name = value
  • Assignment of global variables: set global variable name = value;

View system variables:

• show variables;

Calling system variables:

• select @@variable name;

User variables:

• User variables are variables defined by the user.
• In order to distinguish between system variables and user-defined variables, the system stipulates that user-defined variables must use an @ symbol
• The variables are defined as follows:
  • set @變量名=1
  • select @variable name:=value;
  • select value into @variable name;
• User variables can be used directly without being declared, but the default value is null.
• User variables are session-level variables and are only valid during the current connection.

Local variables:

• Since local variables are user-defined, they can be considered as user variables [but they are different, you don’t need to use @ in local variables]
• Local variables are generally used in SQL statement blocks, such as stored procedure blocks, trigger blocks, etc.
• How to define local variables:
• • First, use declare to declare a local variable. The optional default can be followed by a default value for the variable: [ This is a very important step, otherwise it will be set as a user variable] [Note: the variable declaration statement must be before other statements such as select statements]
    • Example: declare myq int;
    • Example: declare myq int default 666;
  • Set the value of the variable:
    • set variable name = value;
  • Get the value of a variable:
    • select variable name;

create procedure myset()
begin 
 declare mya int;
 declare myq int default 777;
 select mya,myq;
 set myq=6;
 set mya=666;
 select mya,myq;
end;

call myset();

Replenish:

• Some people may find that directly setting variable name = value can also define "user variables"; but this is a bad behavior [this behavior ignores the function of each variable], because you don't know whether it will conflict with system variables, so it is best to add @ to user variables .
• Because = is used in many places to determine whether it is equal, in order to avoid ambiguity, you can also use := to assign values
• [ Although some other assignment methods are given above, it seems that some of them are not universal, such as := which is only used for user variables, so use them with caution ].

Readers who are interested in more MySQL-related content can check out the following topics: "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.