问题：在写 MySQL 编程的时候，一会是有 @ 符号修饰的变量、一会又没有 @ 符号修饰的变量，在某些时候两者都是可以的，有时候又是不行的，下面着重分析这两个变量的用法和区别。
两种 MySQL 变量定义
通过 @ 符号修饰的会话(全局)变量(会话变量通常和 set 成对出现)
通过 @ 符号修饰的变量，称作会话变量，可以简单理解，就是你打开一个 navicat 的查询窗口，你可以在这里面任意一个地方定义这个变量，整个查询窗口不管在任意位置都可以使用这个变量，如果你再打开一个 navicat 查询窗口，想要用刚才那个 navicat 查询窗口定义的变量，答案肯定是不行的，因为此时是在另一个会话窗口，超出了该变量的作用域。
那么既然是变量，那么肯定离不开初始化，不做初始化有意想不到的执行结果，可以自己测试试试。
变量的赋值(set 命令初始化)
一般 @ 符号定义的会话变量，80% 都是通过 set 命令做赋值，20% 可以通过 select xxx into @var。说白了不管是什么变量赋值 set 和 select xxx into @var 这两种方式都适用。
set 语句还有一点好处就是，人家不会限定变量是什么类型，那么就可以直接一下子可以定义很多不同类型的变量，并且可以在定义的时候就可以做初始化。简单方便快捷。
现在先定义两个变量 @var1、@var2，然后通过 select 查询下这两个的变量值(此时还没初始化)，如下所示：
发现这两个变量是 null 值的，很正常因为没有做初始化，现在通过先通过 set 命令为两个变量赋值，如下所示：
可以发现两个变量已经有值了，已经通过 set 初始化值，现在在这个会话窗口任意位置都可以使用这两个变量。下面就是在存储过程中直接查询一下这两个变量的值，如下所示：
此时如果你在这个存储过程中重新给这两个 @var3、@var4 初始化，因为 @var3、@var4 变量是会话的，可以理解为全局的，如果在这里修改了，必然整个会话 @var3、@var4 变量都会被修改，如下所示：
然后再在存储过程的外面查询一下 @var3、@var4 变量的值如下：
发现已经被存储过程修改了，所以再次可以证明 @ 符号修饰的变量是整个会话有效的，只要执行过初始化，就会修改掉会话变量的值，下面在介绍通过 select xxx into @var 变量赋值的方式。
变量的赋值(select xxx into @var 命令初始化)
下面演示的是通过 select xxx into @var 方式为变量赋值。
至此总结一下：@var 变量可以随时随地任意位置想在哪里定义就在哪里定义(但是有 in、out、inout 修饰的就不能使用 @var、或者游标中的 fetch into 变量，这个变量就不能使用 @var 变量，不过这些不能使用是因为存储过程传参和游标赋值的规范导致的)，并且随时随地任意位置都可以进行初始化赋值，方便快捷，但与此同时也有个缺点，就是作用域范围太广了，可能稍不小心这个变量的值就被修改了，导致程序出问题，有利有弊，如果想要缩小作用范围，可以使用 declare 定义变量。
通过 declare 声明的局部变量
declare 定义的都是局部变量，必须必须在 begin…end 语句块中使用，其他地方不能使用，declare 定义的变量只能作用域这个语句块，所以就不会怕数据被其他地方修改了。之前说到的 @var 全局变量作用域太大，数据容易被修改。
begin…end 语句块都是在 MySQL 编程中才会涉及，比如存储过程、游标、Function 自定义函数、触发器等编写的时候才会出现 begin…end 语句块，其他地方时不允许出现独立的 begin…end 语句块的
如下所示：
直接报错，不允许单独出现的这种 begin…end 语句块。
declare 声明的局部变量赋值
declare 声明的局部变量赋值和 @var 赋值一模一样，可以使用 set 、select xxx into var，注意这个是不带 @
符号修饰的。
因为 declare 必须在 begin…end 语句块中使用，所以我们需要再 MySQL 的存储过程等中定义并赋值。
例如1：下面在存储过程中定义了两个局部变量 var1、var2 并且通过 default 初始化(这是局部变量独特的一种 default 默认初始化方式), 可以发现 var1、var2 都已经赋值好了，都是 abc
注意：declare 一次性想定义多个变量 var1、var2 ，必须是同类型的，多个变量之间通过 ， 逗号分割
现在测试一下再存储过程外面查询一下 var1、var2 局部变量的值，发现直接报错，如下图示：
这也证明了 var1、var2 变量的作用范围只能在 begin…end 中。
在来看下局部变量通过 set 方式赋值操作，如下所示：
通过 select xxx into var 赋值如下：
@var 会话变量不能使用的场景
存储过程中有 in、out、inout 修饰的变量
游标 fetch…into…赋值
这里使用 declare 声明的局部变量 var1 是可以的。
但是如果换成了 @var666 会话变量的话，就不允许了，直接给我报错了，如下所示：
测试代码放这方便复制粘贴
表结构如下：
CREATE TABLE `count_tb` (
`id` int NOT NULL AUTO_INCREMENT,
`count_t` int DEFAULT NULL,
`count_str` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
存储过程如下：
drop PROCEDURE if EXISTS test_fun;
create PROCEDURE test_fun(in var666 varchar(120))
BEGIN
DECLARE done int DEFAULT 0;
declare var1,var2 varchar(120) default 'abc';
DECLARE mycusor CURSOR for select id from test_user;
set var1= 'hello',var2='mysql';
-- select 'var1','var2' into var1,var2;
-- select var1,var2;
OPEN mycusor;
break_loop:
LOOP
if done > 100 THEN
LEAVE break_loop;
end if;
FETCH mycusor into var1;
insert into count_tb(count_str) values(var1);
set done = done + 1;
end LOOP break_loop;
close mycusor;
select '程序结束';
END;
TRUNCATE count_tb;
CALL test_fun('111');
案例演示
数据库表结构如下：
CREATE TABLE `test_user` (
`id` int NOT NULL AUTO_INCREMENT,
`user_name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
`id_card` varchar(255) NOT NULL,
`a` int DEFAULT NULL,
`b` int DEFAULT NULL,
`sex` char(3) DEFAULT NULL,
`account` int NOT NULL DEFAULT '0',
`phone` varchar(255) DEFAULT NULL,
`birth_date` datetime DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `age_account_index` (`age`,`account`,`sex`) USING BTREE,
KEY `user_name_index` (`user_name`) USING BTREE,
KEY `a_b_index` (`a`,`b`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `count_tb` (
`id` int NOT NULL AUTO_INCREMENT,
`count_t` int DEFAULT NULL,
`count_str` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
测试例子存放在这，方便日后参考，看的时候一定要复制粘贴到 navicat 窗口中去，方便查看，因为有很多关键字这里显示的不太明显，容易造成误解。
user_data_fun() 存储过程是主入口，其他都是配角，主要是在这里造假数据。
select * from test_user;
select count(1) from test_user;
DROP PROCEDURE if EXISTS user_data_fun;
CREATE PROCEDURE user_data_fun(in threshold int)
begin
declare done int default 1;
declare sex char(3) DEFAULT '男';
declare phone char(11) DEFAULT '';
declare email VARCHAR(21) DEFAULT '';
declare rand_name2 VARCHAR(21) DEFAULT '';
start_loop:
loop
if done = threshold THEN
LEAVE start_loop;
end if;
-- call test_fun();
call rand_name_fun(@rand_name);
call rand_name_fun(rand_name2);
-- 赋值给声明的 @phone 变量，注意 @phone 和 phone 可不是同一个变量
-- set @abc = 100，这种方式表示声明了一个会话级别的变量，并且赋值了，而且不用指明类型
-- 方便快捷
SELECT generatePhone() into @phone;
-- 将返回值赋值给会话级别的变量 @phone2
SELECT generatePhone() into @phone2;
-- 将返回值赋值给局部变量(通过 declare 声明的变量) phone
SELECT generatePhone() into phone;
-- 换一种赋值方式
SET @my_email = (select generate_email('@qq.com'));
SET email = @my_email;
-- 身份证获取
set @birth_date = (select birth_date());
if done % 2 = 0 then
set sex = '女';
end if;
-- if done % 2 = 1 then
-- set @sex = '男';
-- end if;
-- 注意这里 generateDate() 函数需要使用 () 括号进行包裹，否则直接报错
insert into test_user(user_name,age,id_card,a,b,sex,account,phone,birth_date,email)
VALUEs(rand_name2,ROUND(70*RAND()),@birth_date,ROUND(100*RAND()+100),ROUND(100*RAND()+100),sex,ROUND(4000*RAND()+5000),phone,(select generateDate()),email);
set done = done + 1;
insert into count_tb(count_t) values(done);
end loop start_loop;
select CONCAT('循环结束 done=',done);
END;
TRUNCATE test_user;
TRUNCATE count_tb;
CAlL user_data_fun(1000);
show PROCESSLIST;
kill 197;
insert into test_user(user_name,age,id_card,a,b,sex,account) VALUEs(concat('gwm',100),ROUND(70*RAND()),'111',ROUND(100*RAND()),ROUND(100*RAND()),'女',ROUND(9000*RAND()));
SELECT ROUND(100*RAND());
-- 生成随机名称
drop PROCEDURE if EXISTS rand_name_fun;
CREATE PROCEDURE rand_name_fun(out rand_name varchar(120))
begin
-- 这里声明的两个局部变量其实在这里没有用到
-- declare SURNAME varchar(256);
-- declare SNAME varchar(255);
-- 这里重新定义了两个 @var 变量，并且还初始化了值，在这里面都是使用的这个 @var 变量
-- 注意 @var 是整个会话有效的，在这个打开的 session 期间(就是 navicat 其中一个窗口)都是有效的
set @SURNAME = '王李张刘陈杨黄赵吴周徐孙马朱胡郭何高林罗郑梁谢宋唐位许韩冯邓曹彭曾萧田董潘袁于蒋蔡余杜叶程苏魏吕丁任沈姚卢姜崔钟谭陆汪范金石廖贾夏韦傅方白邹孟熊秦邱江尹薛阎段雷侯龙史陶黎贺顾毛郝龚邵万钱严覃武戴莫孔向汤';
set @SNAME = '丹举义之乐书乾云亦从代以伟佑俊修健傲儿元光兰冬冰冷凌凝凡凯初力勤千卉半华南博又友同向君听和哲嘉国坚城夏夜天奇奥如妙子存季孤宇安宛宸寒寻尔尧山岚峻巧平幼康建开弘强彤彦彬彭心忆志念怀怜恨惜慕成擎敏文新旋旭昊明易昕映春昱晋晓晗晟景晴智曼朋朗杰松枫柏柔柳格桃梦楷槐正水沛波泽洁洋济浦浩海涛润涵渊源溥濮瀚灵灿炎烟烨然煊煜熙熠玉珊珍理琪琴瑜瑞瑶瑾璞痴皓盼真睿碧磊祥祺秉程立竹笑紫绍经绿群翠翰致航良芙芷苍苑若茂荣莲菡菱萱蓉蓝蕊蕾薇蝶觅访诚语谷豪赋超越轩辉达远邃醉金鑫锦问雁雅雨雪霖霜露青靖静风飞香驰骞高鸿鹏鹤黎';
-- 注意这种入参和出参在语句块中不需要使用 @ 符号修饰，用了不报错，但是值填充不进去
-- 只有自己通过 declare 声明的变量用 @ 符号修饰
SET rand_name= concat(substr(@surname,floor(rand()*length(@surname)/3+1),1), substr(@SNAME,floor(rand()*length(@SNAME)/3+1),1), substr(@SNAME,floor(rand()*length(@SNAME)/3+1),1));
end;
-- 这里继续查询刚刚在 rand_name_fun() 方法中的会话变量
-- 发现这里还是有效果的，所以在方法或者 begin/END 中最好使用局部变量
-- 避免被外部同一个窗口值其他地方修改了值，找原因都很难
select @SURNAME,@SNAME;
-- 直接通过 @ 符号创建
call rand_name_fun(@rand_name);
select @rand_name;
-- 随机生成手机号
drop function generatePhone;
CREATE FUNCTION generatePhone() RETURNS char(11) CHARSET utf8
DETERMINISTIC
BEGIN
DECLARE head VARCHAR(100) DEFAULT '000,156,136,176';
DECLARE content CHAR(10) DEFAULT '0123456789';
DECLARE phone CHAR(11) DEFAULT substring(head, 1+(FLOOR(1 + (RAND() * 3))*4), 3);#注意sql下标从1开始
DECLARE i int DEFAULT 1;-- 这个变量一定给我初始化，否则绝对出错
DECLARE len int DEFAULT LENGTH(content);
WHILE i<9 DO
SET i=i+1;
SET phone = CONCAT(phone, substring(content, floor(1 + RAND() * len), 1));
END WHILE;
-- insert into count_tb(count_t) values(10000);
RETURN phone;
END;
-- 调用过程直接使用 select 即可
SELECT generatePhone();
-- 随机生成一个日期类型
drop FUNCTION if EXISTS generateDate;
CREATE FUNCTION generateDate() RETURNS timestamp
DETERMINISTIC
BEGIN
RETURN date(from_unixtime(unix_timestamp('2018-01-01') +floor(rand() * ( unix_timestamp('2023-01-01') -unix_timestamp('2018-01-01') + 1 )))); #产生2018-2022年之间的日期
END;
-- 调用过程直接使用 select 即可
SELECT generateDate();
-- 随机生成邮件
drop FUNCTION if EXISTS generate_email;
CREATE FUNCTION generate_email(emailType varchar(20)) RETURNS char(20) CHARSET utf8
DETERMINISTIC
BEGIN
DECLARE head VARCHAR (100) DEFAULT '000,182,150,136,152,158,183';
DECLARE content CHAR(10) DEFAULT '0123456789';
DECLARE phone CHAR(11) DEFAULT SUBSTRING(head, 1+ (FLOOR(1 + (RAND() * 3)) * 4), 3);#定义手机号变量且长度为11
DECLARE email CHAR(20); #定义邮箱变量且长度为20
DECLARE i INT DEFAULT 1;
DECLARE len INT DEFAULT LENGTH(content);
WHILE
i < 9 DO SET i = i + 1;
SET phone = CONCAT(phone, SUBSTRING(content, FLOOR(1 + RAND() * len), 1));
END WHILE;
set email = CONCAT(phone,emailType);
-- 赋值到一个会话变量中去方便查看
SET @my_email = email;
RETURN email;
END;
SET @my_email2 = (select generate_email('@qq.com'));
select @my_email2;
select FLOOR(RAND()*1000+999)
-- 生成年份
select FLOOR(1800 +RAND() * 200);
-- 补齐位置 0
LPAD(str,len,padstr)
-- 生成月份
select LPAD(FLOOR(1 +RAND() * 12), 2, 0)
-- 生成日子
select LPAD(FLOOR(1 +RAND() * 31), 2, 0)
drop FUNCTION if EXISTS birth_date;
CREATE FUNCTION birth_date() RETURNS varchar(32)
DETERMINISTIC
BEGIN
DECLARE birth_date_g varchar(32) default '';
select CONCAT('36',LPAD(FLOOR(RAND()*9000+999),4,0),FLOOR(1800 +RAND() * 200),LPAD(FLOOR(1 +RAND() * 12), 2, 0),LPAD(FLOOR(1 +RAND() * 31), 2, 0),LPAD(FLOOR(RAND()*1000+999),4,0)) into @birth_date;
set birth_date_g = @birth_date;
return birth_date_g;
END;
select birth_date();
set @birth_date = (select birth_date());
select @birth_date;
-- 声明一个游标，给所有账户加上 10000 元
drop PROCEDURE if EXISTS update_account;
CREATE PROCEDURE update_account(in update_num int,out message varchar(255))
DETERMINISTIC
BEGIN
DECLARE done,sid INT DEFAULT FALSE;
DECLARE every_accounts INT DEFAULT FALSE;
DECLARE all_accounts CURSOR FOR select account,id from test_user;
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET 前半句是固定写法
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN all_accounts;
teams_loop:
LOOP
-- 注意这里 INTO 进去的只能是局部变量
FETCH all_accounts into every_accounts,sid;
-- 但是我可以把值在付给一个会话变量
set @sexx = sid;
if done THEN
LEAVE teams_loop;
end if;
insert into count_tb(count_t,count_str) values(every_accounts,concat('s-',sid));
end LOOP teams_loop;
set message = '操作成功';
close all_accounts;
END;
TRUNCATE count_tb;
call update_account(1,@msg);
select @msg,@sexx;
-- 声明一个游标，给所有账户加上 10000 元
drop PROCEDURE if EXISTS update_account2;
CREATE PROCEDURE update_account2(in update_num int,out message varchar(255))
DETERMINISTIC
BEGIN
DECLARE done,sid INT DEFAULT 0;
DECLARE every_accounts INT DEFAULT FALSE;
DECLARE all_accounts2 CURSOR FOR select account,id from test_user;
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET 前半句是固定写法
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN all_accounts2;
teams_loop:
LOOP
if done > 100 then
LEAVE teams_loop;
end if;
-- 注意这里 INTO 进去的只能是局部变量
FETCH all_accounts2 into every_accounts,sid;
-- 但是我可以把值在付给一个会话变量
set @sexx = sid;
insert into count_tb(count_t,count_str) values(every_accounts,concat('s-',sid));
set done = done + 1;
end LOOP teams_loop;
set message = '操作成功';
close all_accounts2;
END;
TRUNCATE count_tb;
call update_account2(1,@msg);
select @msg,@sexx;
-- 声明一个游标，给所有账户加上 10000 元
drop PROCEDURE if EXISTS update_account3;
CREATE PROCEDURE update_account3(in update_num int,out message varchar(255))
DETERMINISTIC
BEGIN
DECLARE done,sid INT DEFAULT 0;
DECLARE every_accounts INT DEFAULT FALSE;
DECLARE update_account3 CURSOR FOR select account,id from test_user;
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET 前半句是固定写法
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN update_account3;
while (done < 100) DO
-- 注意这里 INTO 进去的只能是局部变量
FETCH update_account3 into every_accounts,sid;
-- 但是我可以把值在付给一个会话变量
set @sexx = sid;
insert into count_tb(count_t,count_str) values(every_accounts,concat('b-',sid));
set done = done + 1;
END WHILE;
set message = '操作成功';
close update_account3;
END;
TRUNCATE count_tb;
call update_account3(1,@msg);
select @msg,@sexx;
-- 声明一个游标，给所有账户加上 10000 元
drop PROCEDURE if EXISTS update_account4;
CREATE PROCEDURE update_account4(in update_num int,out message varchar(255))
DETERMINISTIC
BEGIN
DECLARE done,sid INT DEFAULT 0;
DECLARE every_accounts INT DEFAULT FALSE;
DECLARE update_account4 CURSOR FOR select account,id from test_user;
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET 前半句是固定写法
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN update_account4;
while (done < 100) DO
-- 注意这里 INTO 进去的只能是局部变量
FETCH update_account4 into every_accounts,sid;
-- 但是我可以把值在付给一个会话变量
set @sexx = sid;
insert into count_tb(count_t,count_str) values(every_accounts,concat('b-',sid));
set done = done + 1;
END WHILE;
set message = '操作成功';
close update_account4;
END;
TRUNCATE count_tb;
call update_account3(1,@msg);
select @msg,@sexx;
select @var1,@var2;
SET @var3 = 10,@var4='aa';
select @var3,@var4;
select 'aaa','bbbb' INTO @var3,@var4;
-- SET @var3 = 10,@var4='aa';
begin
select var1,var2;
end;
select '测试结果' into @num;
select @num;
drop PROCEDURE if EXISTS test_fun;
create PROCEDURE test_fun(in var666 varchar(120))
BEGIN
DECLARE done int DEFAULT 0;
declare var1,var2 varchar(120) default 'abc';
DECLARE mycusor CURSOR for select id from test_user;
set var1= 'hello',var2='mysql';
-- select 'var1','var2' into var1,var2;
-- select var1,var2;
OPEN mycusor;
break_loop:
LOOP
if done > 100 THEN
LEAVE break_loop;
end if;
FETCH mycusor into @var666;
insert into count_tb(count_str) values(@var666);
set done = done + 1;
end LOOP break_loop;
close mycusor;
select '程序结束';
END;
TRUNCATE count_tb;
CALL test_fun('111');
select var1,var2;
执行结果如下：
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
