MySQL中的变量

系统变量

全局变量

查看某个全局变量

mysql> select @@global.read_only;
+--------------------+
| @@global.read_only |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

设置全局变量

mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)

会话变量

查询所有会话变量

mysql> show session variables;

mysql> select @session.read_only;
+--------------------+
| @session.read_only |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0.00 sec)

设置会话变量，如果变量为全局变量会报错

mysql> set session read_only=1;
ERROR 1229 (HY000): Variable 'read_only' is a GLOBAL variable and should be set with SET GLOBAL

自定义变量——用户变量

作用域

针对当前会话生效，会话内任意位置使用，单独设置或在存储过程函数都可以使用，

定义和更新用户变量（变量不区分大小写）

使用set @var:=value的方式定义变量

mysql> set @age:=12;
Query OK, 0 rows affected (0.00 sec)

mysql> select @age;
+------+
| @age |
+------+
|   12 |
+------+
1 row in set (0.00 sec)

mysql> set @age=13;
Query OK, 0 rows affected (0.00 sec)

mysql> select @age;
+------+
| @age |
+------+
|   13 |
+------+
1 row in set (0.00 sec)

使用select @var:=value的方式复制

mysql> select @var1:=100;
+------------+
| @var1:=100 |
+------------+
|        100 |
+------------+
1 row in set, 1 warning (0.00 sec)

mysql> select @var1;
+-------+
| @var1 |
+-------+
|   100 |
+-------+
1 row in set (0.00 sec)

将某一查询的结果赋值给变量 select xxx into var

mysql> set @count:=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) into @count from employees;
Query OK, 1 row affected (0.21 sec)

mysql> select @count;
+--------+
| @count |
+--------+
| 300024 |
+--------+
1 row in set (0.00 sec)

调用自定义变量

mysql> set @name:='tom';
Query OK, 0 rows affected (0.00 sec)

mysql> set @age:=15;
Query OK, 0 rows affected (0.00 sec)

mysql> select concat(@name," is ",@age);
+---------------------------+
| concat(@name," is ",@age) |
+---------------------------+
| tom is 15                 |
+---------------------------+
1 row in set (0.00 sec)

自定义变量——局部变量

作用域

必须在存储过程内部使用，begin ... end

声明局部变量

DECLARE 变量名 类型;
DECLARE 变量名 类型 default 值;

赋值

方法1:
set var=值;
set var:=值;
select @var:=值;

方法2：
select count(*) into @count from table;

调用局部变量

select count；