How to assign variables to mysql stored procedures

Method: 1. Use the set statement variable assignment, the syntax is "set variable name = value or assignment statement"; 2. Use the query statement and into keyword variable assignment, the syntax is "SELECT c1, c2.. .INTO @v1, @v2...FROM table name WHERE condition".

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How to assign variables to mysql stored procedures

MySQL variable assignment is a pitfall. There are two ways to fill this hole.

The first one is well known: set variable name = value/assignment statement

The second one is: select ... into variable name ...; or select ... into variable name;

The first type, I saw many people on the Internet saying that @ must be added in front of the variable name, but in fact it is unnecessary. Only in some special cases, @ must be added. Most of the time, it will not affect the stored process if it is not added. in use.

For example:

set g_grant_ex='gamedb.tb_exchange_';
set @g_grant_ex='gamedb.tb_exchange_';
/*这其实是一样的效果*/

The second type is even more confusing. I've seen others use Yunliwuli before. I discovered it after trying it several times. . . . . . . It’s quite easy to use~ Examples are as follows

For example:

1.select count(*) into g_err from information_schema.TABLES t where t.TABLE_SCHEMA='gamedb' and t.TABLE_NAME=concat('tb_recharge_',g_year);
2.select count(*) from information_schema.TABLES t where t.TABLE_SCHEMA='gamedb' and t.TABLE_NAME=concat('tb_recharge_',g_year) into g_err;
/*这两个效果也是一样的*/

Recommended learning:mysql video tutorial

The above is the detailed content of How to assign variables to mysql stored procedures. For more information, please follow other related articles on the PHP Chinese website!