sql语句内变量的使用

0.原始表格如下：

 

1.定义变量，并在sql语句内查询：

set @user_name = "成龙";
SELECT * FROM `tb_user` where userName = @user_name;

 

2.定义变量，接受语句的执行结果，并在下一条语句内引用：

set @user_name = (SELECT userName FROM `tb_user` where id = 2);
select * from game where player_name = @user_name;

 

 3.使用动态sql查询：可以定义表名、变量、sql语句

set @table = "tb_user";
set @id =1 ;
SET @sql = CONCAT('SELECT * FROM ', @table_name, ' WHERE id = ', @id);
PREPARE stmt FROM @sql;
EXECUTE stmt;