mysql 用户变量（@）和系统变量（@@ show variables 和 show global variables 区别）

最新推荐文章于 2024-05-18 19:21:38 发布

Michaelwubo

最新推荐文章于 2024-05-18 19:21:38 发布

阅读量9k

点赞数

http://www.uuboku.com/392.html

MySQL服务 - MySQL变量类型及变量设置

MySQL变量类型：

MySQL通过变量来定义当前服务器的特性，保存状态信息等。我们可以通过手动更改变量的值来配置MySQL，也可以通过变量获得MySQL的当前状态信息。MySQL的变量类型可以从变量的修改方式和变量的生效范围划分。

根据变量修改的方式：

动态变量：可以在MySQL运行时调整其指，并立即生效；set global sort_buffer_size=value

静态变量：需要在配置文件中修改，重启服务后生效；/etc/my.cnf

根据变量的生效范围：

全局变量：服务级别的设定，对整个服务生效,所有回话，当时已经连接不生效，重新联系才生效；set global sort_buffer_size=value

会话变量：仅对当前会话生效，其他会话和新会话不受影响；会话结束值即销毁；set session sort_buffer_size=value

二、查看MySQL的变量：

1.查看可以在配置文件中定义的变量：

shell> mysqld --help --verbose

2.查看MySQL的服务当前运行时的变量：

mysql> SHOW [{GLOBAL|SESSION}] VARIABLES [LIKE ''];
mysql> SELECT @@{GLOBAL|SESSION}.VARIABLE_NAME;
mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='VARIABLE_NAME';
mysql> SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='VARIABLE_NAME';

三、修改MySQL的变量：

1.通过配置文件修改：

通过配置文件修改，只需要在配置文件中指定变量命和值，重启服务即可，例如：

shell> vim /etc/my.cnf
       innodb_file_per_table = 1
shell> service mysqld restart

通过配置文件修改的变量会永久生效。

2.运行时直接修改：

运行时修改使用SET命令，并指定变量类型(全局或会话)给变量赋值即可，例如：

mysql> SET GLOBAL innodb_file_per_table = 1
mysql> SET SESSION innodb_file_per_table = 1

需要注意的是，全局变量中运行时修改的值不会永久生效，重启后会失效，如果是会话变量的话，会话断开即失效。

MySQL服务 - MySQL列类型、SQL模式、数据字典

MySQL列类型的作用：

列类型可以简单理解为用来对用户往列种存储数据时做某种范围"限定"，它可以定义数据的有效值(字符、数字等)、所能占据的最大存储空间、字符长度(定长或变长)、是否能够被索引、如何被索引及如何排序等。在关系型数据库系统中，为了实现数据的易于管理，必须为表字段(列)指定对应的列类型。MySQL的列类型可以分为4类：字符型、数值型、日期及时间型、自有类型。

字符型：

字符型限定值只能为字符，MySQL的字符类型如下：

类型            最大字符长度         名称及特性
char            255                定长字符串，区分大小写
varchar         65535              变长字符串，区分大小写
binary          255                二进制定长字符串，区分大小写
varbinary       65535              二进制变长字符串，区分大小写

tinytext        255                text类型是文本大对象存储，就是说它存储的并不是数据本身，而是一个指针，指向数据的存储所在位置；text不区分大小写
text            65535
mediumtext      16777215
longtext        4294967295

tinyblob        255                blob类型是二进制大对象存储，它是text的二进制形式，区分大小写
blob            65535
mediumblob      16777215
longblob        4294967295

数值型：

数值型限定只能存储数值类数据，MySQL的数值类型如下：

类型            名称              取值范围(有符号,无符号)
TINYINT         最小整数　　　　　　-128到127，0到255
SMALLINT        小整数            -32768到32767，0到65535
MEDIUMINT       中整数            -8388607到8388607，0到16777215
INT             整数　　　　　　　　-2147483647到2147483647，0到4294967295
BIGINT          大整数            -9223372036854775807到9223372036854775807，0到18446744073709551615
decimal         十进制            
FLOAT           单精度浮点
DOUBAL          双精度浮点

日期及时间型：

日期时间型用来记录日期或时间，MySQL的日期时间类型如下：

类型                名称                取值范围
DATE                日期                '1000-01-01'到'9999-12-31'
TIME                时间                '-838:59:59'到'838:59:59'
DATETIME            日期时间             '1000-01-01 00:00:00'到'9999-12-31 23:59:59'
TIMESTAMP           时间戳　　　　　　　　 '1970-01-01 00:00:00'到’2037年某时'
YEAR                年                  4位格式：1901到2155，2位格式：1970-2069(70-69)，或：0000

自有类型：

自有类型是MySQL内置的类型，有两种：

SET：字符串集合，事先给出多个元素(最多64个)供用户选择，用户可以选多个元素填入
ENUM：枚举，事先给出多个元素(最多65536个)供用户选择，用户只能选一个元素填入

MySQL的SQL模式：

MySQL服务器可以以不同的SQL模式来操作，并且可以为不同客户端应用不同模式。这样每个应用程序可以根据自己的需求来定制服务器的操作模式。这类模式定义了MySQL应支持的SQL语法，以及应该在数据上执行何种确认检查。这样，就能在众多不同的环境下、与其他数据库服务器一起更容易地使用MySQL。MySQL常用的有三种SQL模式，传统模式、严格模式和ANSI模式。MySQL的SQL模式默认为空，该模式为非严格模式。

可以通过如下命令查看MySQL当前的SQL模式：

mysql> SHOW GLOBAL VARIABLES LIKE 'sql_mode';
mysql> SELECT @@sql_mode;

设置MySQL的SQL模式：

mysql> SET [GLOBAL|SESSION] sql_mode='modes';

或启动MySQL时指定SQL模式：

shell> mysqld --sql-mode="modes"

TRADITIONAL，传统模式：

该模式的简单描述是当在列中插入不正确的值时“给出错误而不是警告”。注释：一旦发现错误立即放弃INSERT/UPDATE。如果你使用非事务存储引擎，那么出现错误前进行的数据更改不会“回滚”，会导致结果更新“只进行了一部分”。

mysql> SET [GLOBAL|SESSION] sql_mode='TRADITIONAL';

STRICT，严格模式：

如果不能将给定的值插入到表中，则放弃该语句。严格模式具有两种：STRICT_TRANS_TABLES和STRICT_ALL_TABLES。STRICT_TRANS_TABLES对支持事务的表实行严格模式，STRICT_ALL_TALBES在所有表执行严格模式。

mysql> SET [GLOBAL|SESSION] sql_mode='STRICT_TRANS_TABLES';
mysql> SET [GLOBAL|SESSION] sql_mode='STRICT_ALL_TABLES';

ANSI模式：

更改操作的语法和行为，使其更适合标准的SQL。

数据字典：

数据字典就类似于系统编目或花名册(和目录不太相同)，它保存数据库服务器上的元数据信息（数据库的整体属性信息）。元数据信息包括：关系(表)的名字、每个关系的各字段的名字、各字段的数据类型和长度约束、每个关系上的视图的名字及视图的定义、授权用户的名字、用户的授权和账户信息、统计类的数据：、每个关系字段的个数、每个关系中的行数、每个关系的存储方法；

在mysql上保存元数据的数据库有：mysql库、infomation_schema库、和performance_schema库，performance_schema库是一个虚拟的库，类似于Linux的/proc等伪文件系统。

用户变量

用户变量是针对当前登录MYSQL的用户的私有变量，可以先在用户变量中保存值然后在以后引用它；这样可以将值从一个语句传递到另一个语句。

用户变量与连接有关。也就是说，一个客户端定义的变量不能被其它客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。

用户变量的形式为@var_name，其中变量名var_name可以由当前字符集的文字数字字符、‘.’、‘_’和

‘$’组成。默认字符集是cp1252 (Latin1)。可以用mysqld的–default-character-set选项更改字符集

。用户变量名对大小写不敏感。

设置用户变量的一个途径是执行SET语句：

SET @var_name = expr [, @var_name = expr] ...

对于SET，可以使用=或:=作为分配符。分配给每个变量的expr可以为整数、实数、字符串或者NULL值。

也可以用语句（表达式）代替SET来为用户变量分配一个值。在这种情况下，分配符必须为:=而不能用=，因为在非

SET语句中=被视为一个比较操作符：

mysql> SET @t1=0, @t2=0, @t3=0;
mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1  | @t2  | @t3  |
+----------------------+------+------+------+
|                    5 |    5 |    1 |    4 |
+----------------------+------+------+------+

用户变量可以用于表达式中。目前不包括明显需要文字值的上下文中，例如SELECT语句的LIMIT子句，或

者LOAD DATA语句的IGNORE number LINES子句。

如果使用没有初始化的变量，其值是NULL。

如果用户变量分配了一个字符串值，其字符集和校对规则与该字符串的相同。用户变量的可压缩性

（coercibility）是隐含的。(即为表列值的相同的可压缩性（coercibility）。

注释：在SELECT语句中，表达式发送到客户端后才进行计算。这说明在HAVING、GROUP BY或者ORDER BY子

句中，不能使用包含SELECT列表中所设的变量的表达式。例如，下面的语句不能按期望工作：

mysql> SELECT (@aa:=id) AS a，(@aa+3) AS b 从tbl_name HAVING b=5；

HAVING子句中引用了SELECT列表中的表达式的别名，使用@aa。不能按期望工作：@aa不包含当前行的值，

而是前面所选的行的id值。

一般原则是不要在语句的一个部分为用户变量分配一个值而在同一语句的其它部分使用该变量。可能会得

到期望的结果，但不能保证。

设置变量并在同一语句中使用它的另一个问题是变量的默认结果的类型取决于语句前面的变量类型。下面

的例子说明了该点：

mysql> SET @a='test';
mysql> SELECT @a,(@a:=20) FROM tbl_name;

对于该 SELECT语句，MySQL向客户端报告第1列是一个字符串，并且将@a的所有访问转换为字符串，即使

@a在第2行中设置为一个数字。执行完SELECT语句后，@a被视为下一语句的一个数字。

要想避免这种问题，要么不在同一个语句中设置并使用相同的变量，要么在使用前将变量设置为0、0.0或

者”以定义其类型。

未分配的变量有一个值NULL，类型为字符串。

系统变量

MySQL可以访问许多系统和连接变量。当服务器运行时许多变量可以动态更改。这样通常允许你修改服务

器操作而不需要停止并重启服务器。

mysqld服务器维护两种变量。全局变量影响服务器整体操作。会话变量影响具体客户端连接的操作。

当服务器启动时，它将所有全局变量初始化为默认值。这些默认值可以在选项文件中或在命令行中指定的

选项进行更改。服务器启动后，通过连接服务器并执行SET GLOBAL var_name语句，可以动态更改这些全

局变量。要想更改全局变量，必须具有SUPER权限。

服务器还为每个连接的客户端维护一系列会话变量。在连接时使用相应全局变量的当前值对客户端的会话

变量进行初始化。对于动态会话变量，客户端可以通过SET SESSION var_name语句更改它们。设置会话变

量不需要特殊权限，但客户端只能更改自己的会话变量，而不能更改其它客户端的会话变量。

对于全局变量的更改可以被访问该全局变量的任何客户端看见。然而，它只影响更改后连接的客户的从该

全局变量初始化的相应会话变量。不影响目前已经连接的客户端的会话变量(即使客户端执行SET GLOBAL

语句也不影响)。

可以使用几种语法形式来设置或检索全局或会话变量。下面的例子使用了sort_buffer_sizeas作为示例变

量名。

要想设置一个GLOBAL变量的值，使用下面的语法：

mysql> SET GLOBAL sort_buffer_size=value;
mysql> SET @@global.sort_buffer_size=value;

要想设置一个SESSION变量的值，使用下面的语法：

mysql> SHOW VARIABLES like 'sort_buffer_size';
mysql> SHOW SESSION VARIABLES like 'sort_buffer_size';
mysql> SELECT @@sort_buffer_size;
mysql> SELECT @@session.sort_buffer_size;

LOCAL是SESSION的同义词。

如果设置变量时不指定GLOBAL、SESSION或者LOCAL，默认使用SESSION。

要想检索一个GLOBAL变量的值，使用下面的语法：

mysql> SHOW GLOBAL VARIABLES like 'sort_buffer_size';（可搜索多个变量 例如：SHOW GLOBAL VARIABLES like '%buffer%';）
mysql> SELECT @@global.sort_buffer_size;（不能搜索多个变量 ）

要想检索一个SESSION变量的值，使用下面的语法：

mysql> SHOW VARIABLES like 'sort_buffer_size';（可搜索多个变量 例如：SHOW VARIABLES like '%buffer%';）
mysql> SHOW SESSION VARIABLES like 'sort_buffer_size';
mysql> SELECT @@sort_buffer_size;
mysql> SELECT @@session.sort_buffer_size;

这里，LOCAL也是SESSION的同义词。

当你用SELECT @@var_name搜索一个变量时(也就是说，不指定global.、session.或者local.)，MySQL返

回SESSION值（如果存在），否则返回GLOBAL值。

对于SHOW VARIABLES，如果不指定GLOBAL、SESSION或者LOCAL，MySQL返回SESSION值。

当设置GLOBAL变量需要GLOBAL关键字但检索时不需要它们的原因是防止将来出现问题。如果我们移除一个

与某个GLOBAL变量具有相同名字的SESSION变量，具有SUPER权限的客户可能会意外地更改GLOBAL变量而不

是它自己的连接的SESSION变量。如果我们添加一个与某个GLOBAL变量具有相同名字的SESSION变量，想更

改GLOBAL变量的客户可能会发现只有自己的SESSION变量被更改了。

全局变量小结：

①、无论是修改本次会话的变量还是全局变量，当mysql服务器重启时，都会失效。要想永久生效，还是要将

配置写入my.ini的配置文件的。

②>、对于全局变量的更改可以被访问该全局变量的任何客户端看见。然而，它只影响更改后连接的客户的从该

全局变量初始化的相应会话变量。不影响目前已经连接的客户端的会话变量(即使客户端执行SET GLOBAL

语句也不影响)。简而言之，全局变量修改后，客户端必须重新连接才会生效。

③>、对于局部变量修改后，只会对本次连接生效，客户端重新连接后失效。

Michaelwubo

关注

0
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
mysql 用户变量（@）和系统变量（@@ show variables 和 show global variables 区别）

http://www.uuboku.com/392.html用户变量用户变量是针对当前登录MYSQL的用户的私有变量，可以先在用户变量中保存值然后在以后引用它；这样可以将值从一个语句传递到另一个语句。用户变量与连接有关。也就是说，一个客户端定义的变量不能被其它客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。用户变量的形式为@var_name，其中变量名var_name可以由当前...
复制链接

扫一扫