In-depth MySQL user-defined variables: Usage details and case studies of usage scenarios

First, preface

In the previous section of the work, there have been several times the super topic points missed the user feedback. Through the reading analysis of the source code, it is found that the problem is on the counter in the high concurrency distributed scenario. The value of the counter affects the size of the points earned by the user's current behavior. For example, when a user forwards a post with a continuous nth (n that is, the value of a counter) in a super topic, the score associated with N is obtained. However, the problem persists after the first improvement. So, this time, on a previous basis, we solved the problem by using the path of the MySQL variable.

Second, exactly what kinds of MySQL variables?

MySQL variables are divided into two main categories: User-defined variables and system variables. As follows:

• User-defined variables
  • Local variables
  • Session variables
• System variables
  • Session variables
  • Global variables

This article deals with user-defined session variables, and if you have no sense of other categories, click here.

PS: What is the difference between a user-defined session variable and a system-defined session variable?

Local variables

Local variables are typically used in statement blocks of SQL, such as the Begin and end statement blocks in a stored procedure. Its scope is limited to the statement block. The life cycle is also limited to the call period of the stored procedure.

1 Drop procedure if exists Add;2 Create procedure Add3 (4     inchAint,5     inchBint6 )7 begin8     DeclareCint default 0;9     SetC=A+b;Ten     SelectC asC; One End;

The variable c defined in the stored procedure above is a local variable.

Session variables

Session variables are variables that the server maintains for each client connection. When a client connects, the client's reply variable is initialized with the current value of the corresponding global variable. Setting a session variable does not require special permissions, but the client can only change its own session variables. Its scope and life cycle are limited to current client connections.

Assignment of Session variables:

1 Set = value; 2 Set @ @session = value; 3 Set = value;

Query for Session variables:

1 Select @ @var_name ; 2 Select @ @session . Var_name; 3  like "%var%";

Global variables

Global variables affect the overall operation of the server. When the server starts, it initializes all global variables to their default values. These default values can be changed in the options file or in the options specified on the command line. To change global variables, you must have super permissions. Global Variables Act on the entire life cycle of the server, but not across reboots. That is, the global variables for all settings are invalidated after the reboot. For the global variable to continue to take effect after it restarts, the appropriate configuration file needs to be changed.

Settings for global variables:

1 Set = // NOTE: Global cannot be omitted here. According to the manual, the set command sets the variable without specifying global, session, or local, by default using session2set@ @global=  Ibid.

Query for global variables:

1 Select @ @global . Var_name; 2  like "%var%";

Third, MySQL user custom variable description

You can use SQL statements to store values in user-defined variables, and then use another SQL statement to query user-defined variables. In this way, values can be passed between different SQL.

The declaration method of a user-defined variable is @var_name, where the variable name consists of a letter, a number, a ".", "_", and "$". Of course, you can also include other characters (for example: @ ' My-var ', @ "My-var", or @ ' My-var ') when referring to a string or identifier.

User-defined variables are session-level variables. The scope of its variables is limited to the client link that declares it. When this client disconnects, all of its session variables will be freed.

User-defined variables are case-insensitive.

Use the SET statement to declare a user-defined variable:

1 SET @var_name = expr[, @var_name = expr] ...

When setting a variable with set, you can use the "=" or ": =" operator to assign a value.

Of course, there are other ways to assign values in addition to the SET statement. For example, the assignment operator can only use ": =". Because the "=" operator will be considered a comparison operator.

Mysql> SET @t1 =1, @t2 =2, @t3:=4;mysql> SELECT @t1, @t2, @t3, @t4: = @[email protected][email protected];+------+---- --+------+--------------------+| @t1  | @t2  | @t3  | @t4: = @[email protected][email protected] |+------+------+------+--------------------+|    1 |    2 |    4 |                  7 |+------+------+------+--------------------+

The types of user variables are limited to: shaping, floating-point, binary and non-binary strings, and null. When you assign a floating-point number, the system does not preserve precision. Other types of values will be converted to the corresponding above types. For example, a value that contains a time or spatial data type (temporal or spatial) will be converted into a binary string.

If the value of the user-defined variable is returned as a result set, the system converts it to a string form.

If you query a variable that is not initialized, NULL is returned as a string type.

Do not assign a value at the same time in the same non-set statement and use the same user-defined variable

User-defined variables can be used in a number of contexts. But it does not currently include those expressions that explicitly use constants, such as the limit clause in select, or the Ignore N lines in load data.

In general, in addition to the SET statement, do not assign a value to the same SQL statement at the same time and use the same user-defined variable. To give an example of a variable increment, here's the question:

1 SET @a = @a + 1;

For other statements, such as SELECT, you may get the desired effect, but this is really not true. For example, in the following statement, you might naturally think that MySQL will execute the @a value before assigning the value:

1 SELECT @a @a:=@a+1, ...;

However, the order in which user-defined variable expressions are evaluated is not yet defined.

In addition, there is another problem. The default return type of a variable is determined by the type at the beginning of the statement, as in the following example:

1 mysql>SET@a='test'; 2 mysql>SELECT@a, (@a:=from Tbl_name;

In the SELECT statement above, MySQL reports the field type of the first column to the client as a string, while all use of the @a variable is converted to string processing, although the @a variable is set to a numeric type in the SELECT statement. After the SELECT statement executes, the @a variable is recognized as a number type in the next statement.

To avoid this problem, either assign a value in the same statement and use the variable, or set the variable to 0, 0.0, or "before use, to determine its data type.

The value of the variable is calculated after the SQL is sent to the client.

In a SELECT statement, the calculation occurs after each select expression is sent to the client. This means that, in the case of a sentence in the form having,group by and order BY, a variable defined in the current select expression is used, the statement will not get as expected.

1 mysql>SELECT (@aa:= as A, (@aa+3   as from having b=5;

The above is used in the having only sentence alias B defined in the current select list, which uses the variable @aa. This statement does not work as expected: The @aa variable is the ID value in the result set that was executed by the previous SQL statement, not the current one.

Iv. examples of practical applications of MySQL user-defined variables

Super Topic Integration System

Terms

Integration behavior: such as forwarding, commenting on super topic posts, check in a super topic or post by other people to respond to the behavior.

Number of integral behaviors: the cumulative number of points generated.

Business Scenarios

Users in a super topic, the nth generation of accumulated points, such as the forwarding of micro-blog, will increase the user's total points under the Super topic. The specific points rule is the article.

Problem

There has been a user feedback that Super topic points have missed the situation: why I comment but no bonus points, why the Super topic posts have not added points and so on. Then, we immediately through the query backstage points record found that the forwarding behavior in the 5th time, the increase in the number of points is 0. This is obviously not normal.

First, the problem of calculating the integral value based on the number of integral behaviors is excluded. For example, the 5th time to forward the microblog should add 6 points. This rule, using the dichotomy to write dead in the program, also done unit tests, there will be no problem. Then, the problem is locked in the number of times this integration behavior.

First, take a look at the number of points obtained:

1  Public Static functionFind$uid,$aid,$status) {  2     $sql= ' SELECT * from '. Self::table ($aid).‘ WHERE uid =? and aid =? and status =? ';3     returncomm_db::d (comm_db::D b_basic)->fetchrow ($sql,Array($uid,$aid,$status));4}

Then, use the Find () method above to get the cumulative number of points that the user has in a super topic. This is problematic in that it is read from the library, but does not guarantee that the value from the library is up to date, so the number of points currently obtained is not necessarily correct (less than or equal to the actual value).

The program then calculates the integral value based on the current number of times and updates the integral value and the value of the integral behavior number of the behavior respectively.

So, this time using the MySQL user to customize the session variable way to solve the above problem.

1  Public Static functionInccounter ($uid,$aid,$status) {2     $db= comm_db::d (comm_db::db_basic);3     $sql= "UPDATE". Self::table ($aid) ." SET ' ctn_counter ' [email protected]_counter:= ' ctn_counter ' +1 WHERE ' uid ' =? and ' aid ' =? and ' status ' =? ";4     $db->execute ($sql,Array($uid,$aid,$status));5     $sql= "Select @ctn_counter";6     $rs=$db->fetchone ($sql,NULL,true);7     return $rs;8}

After the improvement, such as the above function, the program will first call the Inccounter () function, the current number of integration behavior increment, and the value into the current variable. It then reads it immediately and returns it to PHP for integral processing. Thus, the correctness of the number of integral behaviors is ensured.

Five, about the MySQL user custom variable closing

In this "pits" process, MySQL variable was used to solve the MySQL master-slave service synchronization delay problem. This article is also a record of deep learning about MySQL user-defined variables.

In addition, there is a problem that user-defined session variables exist in process memory. But is there a client process or a server-side process?

Reference article:

• https://my.oschina.net/guanyue/blog/211706
• Http://dev.mysql.com/doc/refman/5.6/en/user-variables.html
• Http://www.uuboku.com/392.html

Article Source: Hu Xu Blog + in-depth MySQL user-defined variables: Usage details and case studies of usage scenarios

Reprint please specify the source, offenders must investigate!

In-depth MySQL user-defined variables: Usage details and case studies of usage scenarios