伊利网站建设水平评价,专业建站流程,wordpress文章页的宽度,上海浦东建筑建设网站文章目录MySQL 变量分类系统变量查看系统变量设置系统变量如何通过配置文件来设置变量值通过命令行选项来设置变量值动态设置全局级的系统变量设置静态的系统变量设置会话级的系统变量引用系统变量总结用户自定义变量用户变量声明定义用户变量查看用户变量的值参考示例使用命令…
文章目录MySQL 变量分类系统变量查看系统变量设置系统变量如何通过配置文件来设置变量值通过命令行选项来设置变量值动态设置全局级的系统变量设置静态的系统变量设置会话级的系统变量引用系统变量总结用户自定义变量用户变量声明定义用户变量查看用户变量的值参考示例使用命令 set 声明定义会话级的用户变量使用 select 声明定义会话级的用户变量使用 select into 语句声明定义用户变量综合示例局部变量声明定义局部变量局部变量的声明局部变量的赋值局部变量的使用查看、比较、运算等参考示例用户变量和局部变量的区别状态变量注意MySQL 变量分类
MySQL 通过变量来定义当前服务器的特性保存状态信息等。我们可以通过手动更改变量的值来配置MySQL也可以通过变量获得MySQL的当前状态信息。
MySQL 的变量类型可以从多个维度来划分
1.从生效范围来划分可以分为全局变量GLOBAL VARIABLES和会话变量SESSION VARIABLES。 全局变量影响 MySQL 服务的整体运行方式和状态的变量会话变量是影响具体客户端会话一个数据库连接产生一个会话的操作方式及状态的变量。
2.从变量的修改方式来划分可以分为动态变量和静态变量。
动态变量是指在 MySQL 运行过程中可以通过命令 set 随时调整变量值的变量静态变量是指不能通过命令 set 随时调整变量值必须通过配置文件设置其值的变量。在配置文件中修改变量的值必须重启服务后才能生效。
3.从变量定义主体划分可以分为系统变量和用户自定义变量。
3.1 系统变量就是系统已经定义好的变量系统变量以 开头。在系统变量中可以分为全局变量和会话变量。有些系统变量既是全局变量也是会话变量。特别注意系统变量都是全局变量绝对不存在只是会话变量的系统变量。
全局级的系统变量存在动态变量和静态变量两种会话级的系统变量也存在动态变量和静态变量两种。并不是所有的会话变量都是可以使用命令 set 随时修改其值的。
3.2 用户自定义变量顾名思义就是用户自己定义的变量但是用户自定义的变量中包含两种变量用户变量、局部变量。
3.2.1 用户变量是基于会话变量实现的仅对当前连接会话有效作用域等同于会话级的系统变量所以用户变量就是会话级的变量。用户变量以 开头。
3.2.2 局部变量是使用 declare 关键字在 begin...end 语句块中声明定义的变量其作用范围在begin...end 语句块中离开这个语句块则自动失效。
注意在各种文档中提到的用户变量实际是指用户自定义的会话级变量。用户变量不存在全局的变量换句话说用户无法自定义全局性的变量。
MySQL 还有一种比较特殊的变量这些通常用于监控 MySQL 服务器的运行状态可以使我们及时了解 MySQL 服务器的运行状况我们称之为状态变量状态变量也分为全局级和会话级。状态变量可以使用 show status 语句查看show status 也支持 like 匹配查询。 系统变量
服务器维护着两种系统变量即全局变量和会话变量。每一个客户端成功连接服务器后都会产生与之对应的会话。会话期间MySQL 服务实例会在服务器内存中生成与该会话对应的会话变量这些会话变量的初始值是全局变量值的拷贝。
MySQL 中的系统变量以两个“”开头
1.global 仅仅用于标记全局变量 2.session 仅仅用于标记会话变量 3. 首先标记会话变量如果会话变量不存在则标记全局变量。
查看系统变量
无论是在设置系统变量还是查询系统变量值的时候只要没有指定到底是全局变量还是会话变量都当做会话变量来处理。
在 shell 中使用命令 mysqladmin 查看所有的全局级的系统变量
[roothtlwk0001host ~]# mysqladmin -uroot -p variables;或者连接数据库后通过命令 show 查看所有的系统变量
mysql show variables; # 不指定SESSION、GLOBAL、LOCAL则优先显示会话级变量的值如果没有对应的会话级变量则显示全局级变量的值可以使用以下命令查看 MySQL 中所有的全局变量信息
mysql SHOW GLOBAL VARIABLES; 可以使用以下命令查看与当前会话相关的所有会话变量
mysql SHOW SESSION VARIABLES;查看满足条件的部分系统变量
mysql SHOW GLOBAL VARIABLES LIKE %data_file_path%; # 查看指定名称的全局性的系统变量
mysql SHOW SESSION VARIABLES LIKE %innodb_data%; # 查看指定名称的会话级别的系统变量
mysql SHOW VARIABLES LIKE %data_file_%; # 不指定关键词默认优先显示会话级的系统变量没有则显示全局的系统变量查看指定的系统变量可以在变量表中查询
mysql SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAMEVARIABLE_NAME; # 查询全局的系统变量
mysql SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAMEVARIABLE_NAME; # 查询会话级别的系统变量查看指定的系统变量也可以这样查询
mysql select global.sql_mode; # 查看指定的全局级的系统变量
mysql select session.sql_mode; # 查看指定的会话级的系统变量
mysql select sql_mode; # 不指定关键词默认查询的是会话级别的系统变量如果不存在会员级的变量则会查询对应的全局级的变量的值查看数据库服务器所有的静态变量即状态变量
mysql show status;设置系统变量
当服务器启动时会将所有全局变量初始化为默认值。这些默认值可以在选项文件中或在命令行中指定的选项进行更改。
可以通过以下方法设置系统变量
修改 MySQL 源代码然后对 MySQL 源代码重新编译该方法适用于 MySQL 高级用户这里不做阐述。 在 MySQL 配置文件mysql.ini 或 mysql.cnf 或 my.cnf中修改 MySQL 系统变量的值需要重启 MySQL 服务才会生效。 在 MySQL 服务运行期间使用 SET 命令重新设置系统变量的值。
如何通过配置文件来设置变量值
以变量 local_infile 的设置为例在命令终端使用 vim 打开配置文件进行编辑
[roothtlwk0001host ~]# vim /etc/my.cnf在其中的 [mysqld] 下面添加如下内容
local-infileON # 能不能写成 local_infileON我不知道没有试过说明 1.表示开启其值可以设置为 ON 或 TRUE 或 1 都可以表示关闭其值可以设置为 OFF 或 FALSE 或 0 都可以 2.通过配置文件设置的参数值必须重启数据库服务才能生效并且是永久生效
通过命令行选项来设置变量值
mysqld --max_connections200动态设置全局级的系统变量
更改全局变量必须具有 SUPER 权限。设置全局变量的值的方法如下
mysql SET global.innodb_file_per_tabledefault;
mysql SET global.innodb_file_per_tableON;
mysql SET global innodb_file_per_tableON;注 1.给全局性的系统变量赋值必须写关键词 global不写默认是给会话级的系统变量赋值如果不存在对应的会话级变量则会报错 2.表示某种功能特性开启还是关闭的变量其值设置为 ON 或 TRUE 或 1 都可以表示开启设置为 OFF 或 FALSE 或 0 都可以表示关闭 3.更改全局变量只影响更改后连接客户端的相应会话变量而不会影响目前已经连接的客户端的会话变量即使客户端执行 SET GLOBAL 语句也不影响。也就是说对于修改全局变量之前连接的客户端只有在客户端重新连接后才会影响到客户端。简而言之全局变量修改后客户端必须重新连接才会生效 4.使用 SET 设置全局变量成功后如果 MySQL 服务重启数据库的配置会重新初始化一切按照配置文件进行初始化因此数据库服务重启后全局变量的配置会失效
设置静态的系统变量
MySQL 中还有一些特殊的系统变量如 log_bin、tmpdir、version、datadir在 MySQL 服务实例运行期间它们的值不能动态修改也就是不能使用 SET 命令进行重新设置这种变量称为静态变量。数据库管理员可以使用前面提到的修改源代码或更改配置文件来重新设置静态变量的值。
设置会话级的系统变量
服务器还为每个连接的客户端维护一系列会话变量。在连接时使用相应全局变量的当前值对客户端的会话变量进行初始化。设置会话变量不需要特殊权限但客户端只能更改自己的会话变量而不能更改其它客户端的会话变量。设置会话变量的值的方法如下
mysql SET session.pseudo_thread_id5; # 如果不存在此会话级的系统变量会报错
mysql SET session pseudo_thread_id5; # 如果不存在此会话级的系统变量会报错
mysql SET pseudo_thread_id5; # 默认设置的是会话系统变量如果pseudo_thread_id是全局变量不是会话级的变量这样赋值会报错当然如果根本不存在此变量也会报错
mysql SET pseudo_thread_id 5; # 默认设置的是会话系统变量如果pseudo_thread_id是全局变量不存在对应的会话变量这样赋值会报错当然如果根本不存在此变量也会报错注 1.会话变量的配置在当前会话退出后就失效了会话断开即失效。 2.LOCAL 是 SESSION 的同义词所以 SESSION 可以替换成 LOCAL 3.不指定 SESSION 或 GLOBAL 或 LOCAL 默认是 SESSION
引用系统变量
GLOBAL.var_name
SESSION.var_name
LOCAL.var_name
var_name # 没有指定级别限定符默认优先获取会话级的系统变量的值总结
不论使用哪种方式查看或者获取系统变量的值如果没有指定级别限定符优先显示或获取会话变量的值同样的赋值的时候如果没有指定级别限定符默认是给会话变量赋值但是如果不存在该会话变量则会报错。
用户自定义变量
用户自定义变量分为用户自定义的会话变量即用户变量和局部变量。
用户变量
用户变量和会话级的系统变量类似与连接会话有关是针对当前登录数据库的会话的变量也就是说用户在一个客户端定义的变量不能被其它客户端看到或使用。当客户端退出时该客户端连接的所有的会话变量将自动释放。
用户变量的作用 可以先在用户变量中保存值然后在后面的命令语句中引用它。这样可以将值从一个语句传递到另一个语句。
用户变量表示形式 系统变量都是以 开头用户自定义的会话变量以 开头例如var_name其中变量名可以由当前字符集的英文、数字、.、_ 和 $ 组成。 默认字符集是 cp1252 (Latin1)。可以用mysqld 的 --default-character-set 选项更改字符集。用户变量名对大小写不敏感。
声明定义用户变量
/*方式1*/
mysql set 变量名值;
/*方式2*/
mysql set 变量名:值;
/*方式3*/
mysql select 变量名:值;
/*方式4*/
mysql select 字段 into 变量名 from 表;注用户变量的赋值和变量的声明一模一样。
查看用户变量的值
mysql select var_name;注show 命令只能查看系统变量的值不能查看用户变量的值。
参考示例
使用命令 set 声明定义会话级的用户变量
使用命令 set 定义会话级的用户变量定义形式是以 开始如var_name。分配符可以使用 或者 : 。未分配的用户变量有一个默认值 NULL类型为字符串。
mysql SET t10, t21, t3hello; # 声明定义多个用户变量字符串必须使用单引号或者双引号引起来
Query OK, 0 rows affected (0.00 sec)mysql select t1,t2,t3;
------------------
| t1 | t2 | t3 |
------------------
| 0 | 1 | hello|
------------------
1 row in set (0.00 sec)使用 select 声明定义会话级的用户变量
使用 select 定义用户变量或者赋值分配符必须为 : 而不能用 因为在非 set 语句中 被视为一个比较操作符。
mysql SELECT t1:(t2:1)t3:4,t1,t2,t3;
----------------------------------------
| t1:(t2:1)t3:4 | t1 | t2 | t3 |
----------------------------------------
| 5 | 5 | 1 | 4 |
----------------------------------------使用 select into 语句声明定义用户变量
mysql select name into name from student; # “select name”这个name的返回值必须只有一个才能给变量name赋值否则报错
ERROR 1172 (42000): Result consisted of more than one row
mysql select name into name from student where id00000000000000000001;
Query OK, 1 row affected (0.00 sec)mysql select name;
--------------
| name |
--------------
| liaowenxiong |
--------------
1 row in set (0.00 sec)综合示例
/*select :方式创建变量*/
mysql select first_name:路人甲Java,email:javacode2018163.com;
/*使用变量*/
mysql insert into employees (first_name,email) values (first_name,email);局部变量
局部变量是在 begin...end 语句块中使用关键字 declare 声明定义的变量。
声明定义局部变量
在存储过程和函数中通过 declare 关键字在 BEGIN...END 语句块中声明定义局部变量在 END 后变量无效。
注意 1.declare 定义的变量名不能带 符号。 2.声明局部变量必须要指定变量的类型。 3.声明在 begin...end 中的第一句话
局部变量的声明
mysql declare var_name 类型; # 仅声明了变量
mysql declare var_name 类型 default 值; # 声明变量且定义了变量的初始值局部变量的赋值
方法一
mysql set var_name value;
mysql set var_name : value;方法二
mysql select var_name : value;
mysql select field_name into var_name from table_name;局部变量的使用查看、比较、运算等
mysql select var_name;参考示例
示例一
DELIMITER $$ -- 声明定界符号为$$DROP PROCEDURE IF EXISTS insert_ten_rows $$CREATE PROCEDURE insert_ten_rows () BEGINDECLARE crs INT DEFAULT 0; # 声明定义局部变量crsWHILE crs 10 DOINSERT INTO continent(name) VALUES (contcrs);SET crs crs 1; # 局部变量赋值END WHILE;END $$DELIMITER ; -- 声明定界符为分号
CALL insert_ten_rows(); -- 调用存储过程使用分号结尾示例二
/*创建表test1*/
drop table IF EXISTS test1;
create table test1(a int PRIMARY KEY,b int);/*声明脚本的结束符为$$*/
DELIMITER $$
DROP PROCEDURE IF EXISTS proc1;
CREATE PROCEDURE proc1()
BEGIN/*声明了一个局部变量*/DECLARE v_a int;select ifnull(max(a),0)1 into v_a from test1;select v_b:v_a*2;insert into test1(a,b) select v_a,v_b;
end $$/*声明脚本的结束符为;*/
DELIMITER ;/*调用存储过程*/
call proc1();
/*查看结果*/
select * from test1;用户变量和局部变量的区别
1.用户变量必须以开头的局部变量没有这个符号 2.声明局部变量必须指定数据类型用户变量不需要 3.作用范围不同用户变量在整个连接会话中的任何地方都可以引用局部变量只在 begin...end 语句块内有效离开 begin...end 语句块则失效
状态变量
Server Status Variables(服务器状态变量
MySQL状态变量Server Status Variables是当前服务器从启动后累计的一些系统状态信息例如最大连接数累计的中断连接等等主要用于评估当前系统资源的使用情况以进一步分析系统性能而做出相应的调整决策。这个估计有人会跟常规的系统变量混淆其实状态变量是动态变化的另外状态变量是只读的只能由MySQL服务器本身设置和修改对于用户来说是只读的不可以通过SET语句设置和修改它们而系统变量则可以随时修改。状态变量也分为会话级与全局级别状态信息。有些状态变量可以用FLUSH STATUS语句重置为零值。
注意
MySQL 5.7 以后系统变量和状态变量需要从数据库 performance_schema 中进行获取数据库 information_schema 仍然保留了GLOBAL_STATUSGLOBAL_VARIABLES 两个表做兼容如果希望沿用在数据库 information_schema 中进行查询的习惯MySQL5.7 提供了参数 show_compatibility_56参数值设置为 ON 可以兼容 MySQL 5.7 之前的用法否则就会报错ERROR 3167 (HY000)
