In-depth MySQL User-Defined variables: Usage Details and use cases, mysql Usage Details

In-depth MySQL User-Defined variables: Usage Details and use cases, mysql Usage Details
I. Preface

In the previous work, I received several user feedback on the points missed in the super topic. After reading and analyzing the source code, it is found that the problem lies in the counters in high concurrency distributed scenarios. The value of the counter will affect the size of the points that the user receives in the current behavior. For example, when a user forwards a post continuously for the nth (n is the counter value) in a super topic, the score related to n is obtained. However, the problem persists after the first improvement. Therefore, based on the previous steps, we used MySQL variables to solve this problem.

2. What are the types of MySQL variables?

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

• User-Defined variables
  • Local variable
  • Session variable
• System Variables
  • Session variable
  • Global Variables

This article covers User-Defined session variables. If you do not have a sense of other categories, click here.

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

Local variable

Local variables are generally used in SQL statement blocks, such as the begin and end statement blocks in the stored procedure. Its scope is limited to this statement block. The lifecycle is also limited to the calling period of the stored procedure.

 1 drop procedure if exists add; 2 create procedure add 3 ( 4     in a int, 5     in b int 6 ) 7 begin 8     declare c int default 0; 9     set c = a + b;10     select c as c;11 end;

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

Session variable

Session variables are the variables that the server maintains for each client connection. During client connection, use the current value of the corresponding global variable to initialize the client's session variable. You do not need special permissions to set session variables, but the client can only change its session variables. Its scope and lifecycle are limited to the current client connection.

Assignment of session variables:

1 set session var_name = value;2 set @@session.var_name = value;3 set var_name = value;

Query session variables:

1 select @@var_name;2 select @@session.var_name;3 show session variables like "%var%";

Global Variables

Global variables affect the overall operation of the server. When the server starts, it initializes all global variables to the default value. These default values can be changed in the options file or the options specified in the command line. To change global variables, you must have the SUPER permission. Global variables apply to the entire lifecycle of the server, but cannot be restarted across regions. That is, all the global variables set after the restart are invalid. To make global variables take effect after restart, you need to change the corresponding configuration file.

Global variable settings:

1 set global var_name = value; // Note: The global here cannot be omitted. According to the Manual, if GLOBAL, SESSION, or LOCAL is not specified when the set command is used to set variables, SESSION2 set @ global. var_name = value is used by default; // same as above

Global variable query:

1 select @@global.var_name;2 show global variables like "%var%";

Iii. MySQL User-Defined variables

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 statements.

The declaration method of User-Defined variables is like @ var_name. The variable name consists of letters, numbers, ".", "_", and "$. Of course, other characters (for example, @ 'my-var', @ "my-var", or @ 'my-var') can also be contained when a string or identifier is referenced ').

User-Defined variables are session-level variables. Its variable scope is limited to declaring its client link. When the client is disconnected, all its session variables will be released.

User-Defined variables are case-insensitive.

Use the SET statement to declare User-Defined variables:

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

When using SET to SET variables, you can use the "=" or ": =" operator to assign values.

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

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

The types of user variables are limited to: integer, floating point, binary, non-binary string, and NULL. When a floating point value is assigned, the system does not retain the precision. Values of other types will be converted to the above types. For example, a value containing the time or space data type (temporal or spatial data type) will be converted into a binary string.

If the value of a custom variable is returned as a result set, the system converts it to a string.

If a variable is not initialized, NULL is returned as a string.

Do not assign values in the same non-SET statement and use the same user-defined variable.

User-Defined variables can be used in many contexts. But it does not include the expressions that explicitly use constants, such as the LIMIT clause in the SELECT statement or the ignore n lines in the load data statement.

Generally, apart from the SET statement, do not assign values to the same SQL statement and use the same user-defined variable. For example, there is no problem with auto-increment variables:

1 SET @a = @a + 1;

Other statements, such as SELECT, may have the expected effect, but this is really unreliable. For example, in the following statement, you may naturally think that MySQL will first execute the value of @ a and then assign values:

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

However, the computing sequence of user-defined variable expressions is not defined yet.

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 shown in the following example:

1 mysql> SET @a='test';2 mysql> SELECT @a,(@a:=20) FROM tbl_name;

In the preceding SELECT statement, MySQL reports the field type in the first column of the client as a string, and converts all use of the @ a variable to a string for processing, although the @ a variable is set to the numeric type in the SELECT statement. After the SELECT statement is executed, the @ a variable is recognized as a number in the next statement.

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

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

In a SELECT statement, the calculation is performed only after each select expression is sent to the client. This means that the statement will not be effective as scheduled when only variables defined BY the select expression are used in statements such as HAVING, group by, and order.

1 mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5;

The alias B defined in the current select list is used in the HAVING clause, and the variable @ aa is used. This statement does not get the expected result: the @ aa variable is the ID value in the result set of the last SQL statement execution, not the current one.

Iv. Examples of MySQL User-Defined variables

Super topic points system

Terms

Credit Behavior: such as forwarding, commenting on a post under a super topic, signing in to a super topic, or replying to a post by others.

Number of points: the cumulative number of points generated.

Business scenarios

In a super topic, the user generates accumulated points for the nth time. For example, forwarding Weibo will increase the total number of points that the user has accumulated under the super topic. For detailed credit rules, see the article.

Problem

Some users reported that super topic points were missing: Why did I comment but didn't add points? Why did I forward super topic posts without adding points. Then, we immediately query the credits record in the background, and we can see that when the forwarding behavior is 5th times, the points increase to 0. This is obviously abnormal.

First, the problem of calculating the point value based on the number of points. For example, the number of Weibo posts forwarded for 5th times should be increased by 6 points. This rule is written into the program using the binary method. It has also been used for unit testing and won't be a problem. Then, the number of points that the problem is locked in.

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

1 public static function find($uid, $aid, $status) {  2     $sql = 'SELECT * FROM '.self::table($aid).' WHERE uid = ? AND aid = ? AND status = ?';3     return Comm_Db::d(Comm_Db::DB_BASIC)->fetchRow($sql, array($uid, $aid, $status));4 }

Then, you can use the find () method to obtain the cumulative number of points that the user performs on a super topic. This is problematic because it is read from the slave database, but it is not guaranteed that the slave database value is up-to-date, as a result, the number of points currently obtained is not necessarily correct (less than or equal to the actual value ).

Then, the program calculates the point value based on the current number of times, and updates the point value and the point behavior value for this row respectively.

Therefore, this time we use MySQL user-defined session variables to solve the above problems.

1 public static function incCounter($uid, $aid, $status) {2     $db = Comm_Db::d(Comm_Db::DB_BASIC);3     $sql = "UPDATE ". self::table($aid) ." SET `ctn_counter`=@ctn_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 improvement, for example, the program will first call the incCounter () function, increase the number of points and store the value in the current variable. Then, read it immediately and return it to PHP for points processing. This ensures the correctness of the number of points.

V. Concluding remarks on MySQL User-Defined variables

During this "fill in" process, MySQL variables were used to solve the problem of synchronization latency of MySQL Master/Slave services. This article also records the deep learning of MySQL User-Defined variables.

In addition, there is still a problem. User-Defined session variables exist in the process memory. But is there a client process or a server process?

References:

• 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

Source: Hu xuboke => in-depth MySQL User-Defined variables: Usage Details and Use Cases

Reprinted, please indicate the source, the offenders must investigate!