变量
1. 系统变量:
全局变量: 作用域: 服务器每次启动将为所有的全局变量赋初始值,
针对于所有的的会话 (连接) 有效, 但不垮重启.
会话变量: 仅仅针对当前的会话 (连接) 有效
2. 自定义变量:
用户变量
局部变量
一. 系统变量
即该变量由系统提供, 不是用户定义, 属于服务层面
1. 使用语法:
1. 查看所有的系统的变量
show grobal|[session] variables;
注意: 如果是全局级别, 则需要要加 global, 如果是会话级别, 则需要加 session, 如果不写, 则默认 session.
- mysql> show global variables;
- +----------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- | Variable_name | Value |
- +----------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- | activate_all_roles_on_login | OFF |
- | auto_generate_certs | ON |
- | auto_increment_increment | 1 |
- | auto_increment_offset | 1 |
- | autocommit | ON
- ...
- ...
- 531 rows in set (0.01 sec)
- mysql> show session variables;
- +----------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- | Variable_name | Value |
- +----------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- | activate_all_roles_on_login | OFF |
- | auto_generate_certs | ON |
- | auto_increment_increment | 1 |
- | auto_increment_offset | 1
- ...
- ...
- 548 rows in set (0.00 sec)
2. 查看满足条件的部分系统变量
- show global | [session] variables like '要查看变量的部分字符' ;
- mysql> show global variables like '%char%';
- +--------------------------+-----------------------------------------------------------+
- | Variable_name | Value |
- +--------------------------+-----------------------------------------------------------+
- | character_set_client | utf8mb4 |
- | character_set_connection | utf8mb4 |
- | character_set_database | utf8mb4 |
- | character_set_filesystem | binary |
- | character_set_results | utf8mb4 |
- | character_set_server | utf8mb4 |
- | character_set_system | utf8 |
- | character_sets_dir | /usr/local/mysql-8.0.11-macos10.13-x86_64/share/charsets/ |
- +--------------------------+-----------------------------------------------------------+
- 8 rows in set (0.01 sec)
3. 查看指定的某个系统变量的值
select @@global | [session] . 系统变量名
- mysql> select @@global.character_set_system;
- +-------------------------------+
- | @@global.character_set_system |
- +-------------------------------+
- | utf8 |
- +-------------------------------+
- 1 row in set (0.00 sec)
4. 为某个系统变量赋值
方式一:
set global|[session] 系统变量名 = 值;
方式二:
set @@global|[session]. 系统变量名 = 值;
- mysql> show global variables like '%isolation%';
- +-----------------------+-----------------+
- | Variable_name | Value |
- +-----------------------+-----------------+
- | transaction_isolation | REPEATABLE-READ |
- +-----------------------+-----------------+
- 1 row in set (0.00 sec)
- mysql> set global transaction_isolation ='read-committed';
- Query OK, 0 rows affected (0.00 sec)
- mysql> show global variables like '%isolation%';
- +-----------------------+----------------+
- | Variable_name | Value |
- +-----------------------+----------------+
- | transaction_isolation | READ-COMMITTED |
- +-----------------------+----------------+
- 1 row in set (0.00 sec)
- mysql> set @@global.transaction_isolation='REPEATABLE-READ';
- Query OK, 0 rows affected (0.07 sec)
- mysql> show global variables like '%isolation%';
- +-----------------------+-----------------+
- | Variable_name | Value |
- +-----------------------+-----------------+
- | transaction_isolation | REPEATABLE-READ |
- +-----------------------+-----------------+
- 1 row in set (0.00 sec)
二. 自定义变量
(1)用户变量
1. 即用户自己定义的, 不是用系统提供的.
2. 使用步骤:
声明
赋值
使用(查看, 比较, 运算等)
3. 作用域:
针对当前会话 (连接) 有效, 同于会话变量的作用域
赋值操作符:= 或 :=
(1)声明并初始化
set @用户变量名 = 值; 或
set @用户变量名 := 值; 或
select @用户变量名 := 值;
(2)赋值(更新用户变量的值)
方式一(声明并初始化一样): 通过 set 或 select
set @用户变量名 = 值; 或
set @用户变量名 := 值; 或
select @用户变量名 := 值;
方式二: 通过 select into
select 字段 into 变量名 from 表;
(3)使用: select @用户变量名;
案例
- mysql> set @name ='keen';
- Query OK, 0 rows affected (0.06 sec)
- mysql> set @name =22;
- Query OK, 0 rows affected (0.00 sec)
- mysql> set @count =1;
- Query OK, 0 rows affected (0.00 sec)
- mysql> #赋值
- mysql> use myemployees;
- Reading table information for completion of table and column names
- You can turn off this feature to get a quicker startup with -A
- Database changed
- mysql> select count(*) into @count from employees;
- Query OK, 1 row affected (0.06 sec)
- mysql> #查看
- mysql> select @count;
- +--------+
- | @count |
- +--------+
- | 107 |
- +--------+
- 1 row in set (0.00 sec)
- mysql> select @name;
- +-------+
- | @name |
- +-------+
- | 22 |
- +-------+
- 1 row in set (0.00 sec)
- mysql> set @name='keen';
- Query OK, 0 rows affected (0.00 sec)
- mysql> select @name;
- +-------+
- | @name |
- +-------+
- | keen |
- +-------+
- 1 row in set (0.00 sec)
2. 局部变量
作用域: 仅仅在定义它的 begin end 中有效
应用在 begin end 中的第一句话
(1)声明:
declare 变量名 类型;
declare 变量名 类型 default 值;
(2)赋值
方式一:
通过 set 或 select
set 局部变量名 = 值; 或
set 局部变量名 := 值; 或
select @局部变量名 := 值;
方式二: 通过 select into
select 字段 into 局部变量名 from 表;
(3)使用
select 局部变量名;
练习: 声明两个变量并赋初始值, 求和, 并打印;
@用户变量名
- set @n=1;
- set @m=2;
- set @sum =@n+@m;
- select @sum;
- #2. 局部变量
- declare m int default 1;
- declare n int default 2;
- declare sum int;
- set sum=m+n;
- select sum;
- //declare is not valid at this position. 因为它得在 begin end 里
注: 这是本人的学习笔记及练习, 如果有错误的地方望指出一起讨论, 谢谢!
来源: http://www.jianshu.com/p/f6934dd0ad51