mysql动态变量_Mysql的变量⼀览
Server System Variables(系统变量)
MySQL系统变量(system variables)是指MySQL实例的各种系统变量，实际上是⼀些系统参数，⽤于初始化或设定数据库对系统资源的占⽤，⽂件存放位置等等，这些变量包含MySQL编译时的参数默认值，或者myf配置⽂件⾥配置的参数值。默认情况下系统变量都是⼩写字母。
作⽤域范围
系统变量(system variables)按作⽤域范围可以分为会话级别系统变量和全局级别系统变量。如果要确认系统变量是全局级别还是会话级别，如果Scope其值为GLOBAL或SESSION，表⽰变量既是全局级别系统变量，⼜是会话级别系统变量。如果其Scope其值为GLOBAL，表⽰系统变量为全局级别系统变量。
系统级别
查看系统变量的全局值
select * from information_schema.global_variables;
select * from information_schema.global_variables
where variable_name='xx';
select * from performance_schema.global_variables;
会话级别
查看系统变量的当前会话值
select * from information_schema.session_variables;
select * from information_schema.session_variables  where variable_name='xxxx';
select * from performance_schema.session_variables;
区分变量和操作变量
SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;
mysql> show variables like '%connect_timeout%';
mysql> show local variables like '%connect_timeout%';
mysql> show session variables like '%connect_timeout%';
mysql> show global variables like '%connect_timeout%';
注意：对于SHOW VARIABLES，如果不指定GLOBAL、SESSION或者LOCAL，MySQL返回SESSION值。
如果某⼀个系统变量是全局级别的，那么在当前会话的值也就是全局级别的值。例如系统变量AUTOMATIC_SP_PRIVILEGES，它是⼀个全局级别系统变量，但是 show session variables like '%automatic_sp_privileges%'⼀样能查到其值。
如果要区分系统变量是全局还是会话级别，可以⽤下⾯⽅式：
⽅法1： 查官⽅⽂档中系统变量的Scope属性。
⽅法2： 使⽤SET VARIABLE_NAME=xxx; 如果报ERROR 1229 (HY000)，则表⽰该变量为全局，如果不报错，那么证明该系统变量为全局和会话两个级别。
修改全局级别系统变量
注意：更改全局变量的值，需要拥有SUPER权限
修改会话级别系统变量
SET @@session.max_join_size=DEFAULT;
SET max_join_size=DEFAULT;  --默认为会话变量。如果在变量名前没有级别限定符，表⽰修改会话级变量。
SET SESSION max_join_size=DEFAULT;
如果修改系统全局变量没有指定GLOBAL或@@global的话，就会报“Variable 'xxx' is a GLOBAL variable and should be set with SET GLOBAL”这类错误。
动态变量静态变量
系统变量(system variables)按是否可以动态修改，可以分为系统动态变量(Dynamic System Variables)和系统静态变量。怎么区分系统变量是动态和静态的呢？ 这个只能查看官⽅⽂档，系统变量的"Dynamic"属性为Yes，则表⽰可以动态修改。
有些系统变量是只读的，不能修改的。如下所⽰：
mysql> set global innodb_version='5.6.21';
ERROR 1238 (HY000): Variable 'innodb_version' is a read only variable
Server Status Variables(服务器状态变量)
MySQL状态变量(Server Status Variables)是当前服务器从启动后累计的⼀些系统状态信息，例如最⼤连接数，累计的中断连接等等，主要⽤于评估当前系统资源的使⽤情况以进⼀步分析系统性能⽽做出相应的调整决策。状态变量是动态变化的。
状态变量是只读的：只能由MySQL服务器本⾝设置和修改，对于⽤户来说是只读的，不可以通过SET语句设置和修改它们，⽽系统变量则可以随时修改。
状态变量也分为会话级与全局级别状态信息。
有些状态变量可以⽤FLUSH STATUS语句重置为零值。
查看状态变量
关于查看状态变量，show status也⽀持like匹配查询。如下所⽰：
show status like '%variable_name%'
正则表达式匹配数字包含负数show global status like '%variable_name%'
#当前测试环境
mysql> select version() from dual;
mysql> show status;  --查看所有的状态变量
mysql> show global status like 'Aborted_connects%';
mysql> show session status like 'Aborted_connects%';mysql是什么系统
mysql> select * from information_schema.global_status;
ERROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for
'show_compatibility_56'
mysql> show variables like '%show_compatibility_56%';
查出来是flase。
函数名和函数体
这样就可以查询了。
select * from performance_schema.global_status;
select * from performance_schema.session_status;
注意：MySQL 5.7以后系统变量和状态变量需要从performance_schema中进⾏获取，information_schema仍然保留了
GLOBAL_STATUS，GLOBAL_VARIABLES两个表做兼容，如果希望沿⽤information_schema中进⾏查询的习惯，5.7提供了
show_compatibility_56参数，设置为ON可以兼容5.7之前的⽤法，否则就会报错(ERROR 3167 (HY000)).
User-Defined Variables(⽤户⾃定义变量)
⽤户⾃定义变量就是⽤户⾃⼰定义的变量。
⽤户⾃定义变量是基于当前会话的。 也就是说⽤户⾃定义变量的作⽤域局限于当前会话(连接)，由⼀个客户端定义的⽤户⾃定义变量不能被其他客户端看到或使⽤。
例外：可以访问performance_schema.user_variables_by_thread表的⽤户可以看到所有会话的定义的⽤户⾃定义变量，当然仅仅能看到那些会话定义了哪些变量，⽽不能访问这些变量。
当客户端会话退出时，当前会话所有的⾃定义变量都会⾃动释放。
⼀般可以在SQL语句将值存储在⽤户⾃定义变量中，然后再利⽤另⼀条SQL语句来查询⽤户⾃定义变量。这样以来，可以在不同的SQL间传递值。
⽤户⾃定义变量是⼤⼩写不敏感的，最⼤长度为64个字符，⽤户⾃定义变量的形式⼀般为@var_name，其中变量名称由字母、数字、“.”、“_”和“$”组成。当然，在以字符串或者标识符引⽤时也可以包含其他特殊字符(例如：@'my-var'，@"my-var"，或者@`my-var`)。。使⽤SET设置变量时，可以使⽤“=”或者“:=”操作符进⾏赋值。对于SET，可以使⽤=或:=来赋值，对于SELECT只能使⽤:=来赋值。
如下所⽰：
mysql> set @$test1="test";
mysql> select @$test1 from dual;
bt蚂蚁磁力搜索天堂
mysql> select connection_id() from dual;  返回当前连接id。149379
mysql> SELECT c.id,  b.thread_id FROM  performance_schema.threads b  join information_schema.processlist c ON
b.processlist_id =
c.id where c.id=149379;
+--------+-----------+
| id    | thread_id |
+--------+-----------+
| 149379 |    149404 |
+--------+-----------+
1 row in set (0.00 sec)
mysql> select * from performance_schema.user_variables_by_thread;
+-----------+---------------+----------------+
| THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE |
+-----------+---------------+----------------+
|    149404 | my_test      | 1200          |
1 row in set (0.00 sec)
⽤户⾃定义变量注意事项，以下为总结：
1：未定义的⽤户⾃定义变量初始值是NULL
mysql> select @kerry from dual;
注意：使⽤未定义变量不会产⽣任何语法错误，由于其被初始化为NULL值，如果没有意识到这⼀点，⾮常容易犯错。
如下所⽰：
mysql> select @num1, @num2 :=@num1+1 from dual;
+-------+-----------------+
| @num1 | @num2 :=@num1+1 |
+-------+-----------------+
| NULL  |            NULL |
+-------+-----------------+
mating翻译
2：⽤户变量名对⼤⼩写不敏感。
3：⾃定义变量的类型是⼀个动态类型。MySQL中⽤户⾃定义变量，不严格限制数据类型的，它的数据类型根据你赋给它的值⽽随时变化。⽽且⾃定义变量如果赋予数字值，是不能保证进度的。
4：赋值的顺序和赋值的时间点并不总是固定的，这依赖于优化器的决定。
使⽤⽤户⾃定义变量的⼀个最常见的问题就是没有注意到在赋值和读取⽤户⾃定义变量的时候可能是在查询的不同阶段。
例如，在SELECT语句中进⾏赋值然后再WHERE⼦句中读取⽤户⾃定义变量，则可能⽤户⾃定义变量取值并不不是你所想象的那样。
如下例⼦所⽰，因为按照MySQL语句的执⾏顺序，WHERE部分优先与SELECT部分操作，所以你会看到msgid 和 @rownum的最⼤值为6.
mysql> select msgid from message order by msgid limit 12;+-------+
| msgid |
+-------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 11 |
| 12 |
| 13 |
+-------+
中国春节的英语作文12 rows in set (0.00sec)
mysql> set @rownum := 0;
Query OK,0 rows affected (0.00sec)
mysql> select msgid , @rownum := @rownum +1 as rownum from message where @rownum <=5;+-------+--------+
| msgid | rownum |
+-------+--------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
+-------+--------+
6 rows in set (0.00sec)
mysql> select msgid , @rownum := @rownum +1 as rownum from messagewhere @rownum <=5;
Emptyset (0.00sec)
mysql> select @rownum fromdual;+---------+
| @rownum |
+---------+
| 6 |
+---------+
1 row in set (0.00 sec)
如上所⽰，第⼆次查询可能你想要的逻辑跟实际逻辑已经出现了偏差，这个是使⽤⾃定义变量需要⼩⼼的地⽅。因为⽤户⾃定义变量在当前会话中也算⼀个“全局变量”，它已经变成了6，where条件后⾯的 @rownum <= 5 逻辑为false了。⼀不⼩⼩⼼就会出现和你预想的结果出现偏差。
不要在同⼀个⾮SET语句中同时赋值并使⽤同⼀个⽤户⾃定义变量，因为WHERE和SELECT是在查询执⾏的不同阶段被执⾏的。
如果在查询中再加⼊ORDER BY的话，结果可能会更不同；
mysql> set @rownum :=0;
Query OK,0 rows affected (0.00sec)
mysql> select msgid , @rownum := @rownum +1 as rownum from message where @rownum <=5;+-------+--------+
| msgid | rownum |
+-------+--------+

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。