Tutorial on how MYSQL automatically serializes the results of query data-Mysql Tutorial-php.cn

This article mainly introduces to you the relevant information on how MYSQL automatically serializes the results of query data. The article introduces it in detail through the example code. It has certain reference learning value for everyone to learn or use mysql. Friends who need it Let's take a look with the editor below, I hope it can help everyone.

SQL:

SELECT (@i:=@i+1) i,user_id,user_name FROM `dt_user_all_orders`, (SELECT @i:=0) as i WHERE user_name='qqqqqqqqqq' LIMIT 0,10;

Copy after login

Result:

If you need to group the serial numbers before displaying them:

SELECT drug_productor,@y:=@y+1 as num FROM( SELECT drug_productor FROM ts_drug a GROUP BY drug_productor) c,(SELECT @y:=0) d

Copy after login

Result :

Analysis:

At the beginning, a variable i is defined, Let the result for each additional item be ➕1,

@i:=1;

Here is a way to review the way mysql defines user variables: select @variable name

There are two ways to assign values to user variables, one is to use the "=" sign directly, and the other is to use the ":=" sign. The difference is that when using the set command to assign values to user variables, both methods can be used; when using the select statement to assign values to user variables, only the ":=" method can be used, because in the select statement, the "=" sign Considered as a comparison operator

(@i:=@i+1), it can also be written as@i:=@i+1, The parentheses are added to make the structure visually clearer. After defining a variable, each query will increment the variable. However, we do not need this variable to increment each time we execute the query statement to obtain the results, so we need to reset it to 0 and use a comma after the table name. Just use(SELECT @i:=0) as iunder the grid. Let’s talk about why this as i is used like this. It’s because the derived table must need an alias. This is its alias. You can Any character.

Related recommendations:

php generates column serial numbers in excel

php implements reading memory sequence numbers_PHP tutorial

MySQL rownumber SQL generated self-increasing serial number usage introduction

The above is the detailed content of Tutorial on how MYSQL automatically serializes the results of query data. For more information, please follow other related articles on the PHP Chinese website!