系统变量

变量由系统提供，无需用户定义，属于服务器层面。
@为会话变量
@@为全局变量

查看所有的系统变量

  1. mysql> show global variables like '%XXX%';
  2. mysql> show session variables like '%XXX%';

查看某个系统变量

select @@global/session.系统变量名;

mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+
1 row in set (0.06 sec)

为某个系统变量赋值

set global/session 系统变量名 = 值;
set @@global/session 系统变量名 = 值;

自定义变量

变量由用户自定义。
针对于当前会话有效，同于系统会话变量的作用域。

  • 声明初始化变量：

set @用户变量名= 值;
set @用户变量名 := 值;
select @用户变量名 := **值;**

  • 更新用户变量值

set @用户变量名= 值;
set @用户变量名 := 值;
select @用户变量名 := **值;
select 字段 into @变量名 from 表名;**

mysql> set @name = 'zhangsan';
Query OK, 0 rows affected (0.01 sec)

mysql> select @name;
+----------+
| @name    |
+----------+
| zhangsan |
+----------+
1 row in set (0.08 sec)

mysql> select @name := 'lisi';
+-----------------+
| @name := 'lisi' |
+-----------------+
| lisi            |
+-----------------+
1 row in set (0.05 sec)

mysql> select @name;
+-------+
| @name |
+-------+
| lisi  |
+-------+
1 row in set (0.05 sec)

mysql> select ename into @name from emp limit 1;
Query OK, 1 row affected (0.01 sec)

mysql> select @name;
+----------+
| @name    |
+----------+
| zhangsan |
+----------+
1 row in set (0.05 sec)

mysql> select * from emp;
+-------+----------+-----------+------+------------+---------+---------+--------+
| EMPNO | ename    | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+----------+-----------+------+------------+---------+---------+--------+
|  7369 | zhangsan | CLERK     | 7902 | 1980-12-17 |  800.00 | NULL    |     20 |
|  7521 | li       | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES    | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL    |     20 |
|  7654 | MARTIN   | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE    | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL    |     30 |
|  7782 | CLARK    | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL    |     10 |
|  7788 | SCOTT    | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL    |     20 |
|  7839 | KING     | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL    |     10 |
|  7844 | TURNER   | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS    | CLERK     | 7788 | 1987-05-23 | 1100.00 | NULL    |     20 |
|  7900 | JAMES    | CLERK     | 7698 | 1981-12-03 |  950.00 | NULL    |     30 |
|  7902 | FORD     | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL    |     20 |
|  7934 | MILLER   | CLERK     | 7782 | 1982-01-23 | 1300.00 | NULL    |     10 |
+-------+----------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.08 sec)

局部变量

仅仅在定义它的begin end中有效，且必须为第一句。

  • 局部变量声明：

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

  • 赋值

set 用户变量名= 值;
set 用户变量名 := 值;
select @用户变量名 := **值;
select 字段 into 变量名 from 表名;**