您的位置:新葡亰496net > 网络数据库 > 新葡亰496netproxysql的布局体系,MySQL的变量分类总

新葡亰496netproxysql的布局体系,MySQL的变量分类总

发布时间:2019-12-10 14:10编辑:网络数据库浏览(151)

    在8之前的版本中,对于全局变量的修改,其只会影响其内存值,而不会持久化到配置文件中。数据库重启,又会恢复成修改前的值。从8开始,可通过SET PERSIST命令将全局变量的修改持久化到配置文件中。

    proxysql的配置系统特点

    1 允许轻松自动更新配置。为此,有一个MySQL兼容的管理界面
    2 允许在运行时修改尽可能多的配置项,而无需重新启动守护程序
    3 允许轻松回滚错误的配置

    这是使用多层配置系统实现的,允许设置从一层移动到另一层。配置系统的3层如下图所示:
    -------------------------
    |         RUNTIME         |
    -------------------------
           /|          |
            |           |
        [1] |       [2] |
            |          |/
    -------------------------
    |         MEMORY          |
    ------------------------- _
           /|          |      |
            |           |        
        [3] |       [4] |         [5]
            |          |/        
    -------------------------   -------------------------
    |          DISK           |  |       CONFIG FILE       |
    -------------------------   -------------------------

     

    MySQL环境变量的方法未必人人都会,下面就为您介绍修改MySQL环境变量的方法,供您参考,希望对您学习MySQL环境变量方面有所帮助。

    试举一例

    RUNTIME 

    表示正在处理请求的线程使用的ProxySQL的内存中数据结构。这些包含使用的全局变量的值,分组到主机组的后端服务器列表或可连接到代理的MySQL用户列表。请注意,操作者永远不能直接修改RUNTIME配置部分的内容,总是需要通过底层来修改。

    在MySQL中,my.cnf是参数文件(Option Files),类似于ORACLE数据库中的spfile、pfile参数文件,照理说,参数文件my.cnf中的都是系统参数(这种称呼比较符合思维习惯),但是官方又称呼其为系统变量(system variables),那么到底这个叫系统参数或系统变量(system variables)呢? 这个曾经是一个让我很纠结的问题,因为MySQL中有各种类型的变量,有时候语言就是这么博大精深;相信很多人也对这个问题或多或少有点困惑。其实抛开这些名词,它们就是同一个事情(东西),不管你叫它系统变量(system variables)或系统参数都可,无需那么纠结。 就好比王三,有人叫他王三;也有人也叫他王麻子绰号一样。

    MySQL修改环境变量值有两种方式: 配置文件my.ini for win || my.cnf for linux)和mysql命令(动态修改)。

    mysql> show variables like '%max_connections%';
     ------------------------ ------- 
    | Variable_name          | Value |
     ------------------------ ------- 
    | max_connections        | 151  |
    | mysqlx_max_connections | 100  |
     ------------------------ ------- 
    2 rows in set (0.00 sec)
    
    mysql> set persist max_connections=200;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show variables like '%max_connections%';
     ------------------------ ------- 
    | Variable_name          | Value |
     ------------------------ ------- 
    | max_connections        | 200  |
    | mysqlx_max_connections | 100  |
     ------------------------ ------- 
    2 rows in set (0.00 sec)
    

    MEMORY(有时也称为main)

    表示内存中的SQLite3数据库,它通过MySQL兼容界面暴露给外部。用户可以将MySQL客户端连接到此接口,并查询不同的表和数据库。通过此界面可用的配置表如下:

    mysql_servers  - 后端服务器列表
    mysql_users  - 可以连接到ProxySQL的用户列表和凭据。请注意,ProxySQL也将使用这些凭据连接到后端服务器
    mysql_query_rules  - 用于将流量路由到不同的后端服务器的规则列表。这些规则也可能导致重写查询或缓存结果
    global_variables  - 可以在运行时调整的整个代理中使用的全局变量列表。全局变量示例:
    proxysql>select * from global_variables limit 3;
    -------------------------------- ----------------
    | variable_name                  | variable_value |
    -------------------------------- ----------------
    | mysql-shun_on_failures         | 5              |
    | mysql-shun_recovery_time_sec   | 10             |
    | mysql-query_retries_on_failure | 1              |
    -------------------------------- ----------------
    3 rows in set (0.00 sec)
    mysql_collations - 可用于代理使用的MySQL排序规则列表。 这些是直接从客户端库提取的。
    [仅在调试版本中可用] debug_levels - ProxySQL与其详细级别一起发送的调试语句的类型列表。 这允许我们在运行时轻松配置在日志中有什么样的语句来调试不同的问题。 这仅在调试版本中可用,因为它可能会影响性能

     

    MySQL显示系统变量的命令为:show variables;

     

    DISK和CONFIG FILE

    DISK表示磁盘上的SQLite3数据库,默认位置为$(DATADIR)/proxysql.db。 在重新启动过程中,内存中未被保留的配置将丢失,因此将配置保留在DISK中是非常重要的。 CONFIG文件是经典的配置文件,我们将在下一节中看到它与其他配置层之间的关系。

    另外,MySQL中有很多变量类型,确实有时候让人有点混淆不清,本文打算总结一下MySQL数据库的各种变量类型,理清各种变量类型概念。能够从全局有个清晰思路。MySQL变量类型具体参考下图:

    调整MySQL服务器的系统变量

    全局变量的修改会保存在两处,

    配置系统的加载方式

     

    mysqld服务器维护两种环境变量。

    1.  数据目录下mysqld-auto.cnf文件,

    启动过程

    在正常启动时,ProxySQL读取其配置文件(如果存在),以确定其datadir。 接下来发生的事情取决于它的数据库文件(磁盘)是否存在于其datadir中。
    如果找到数据库文件,ProxySQL将从持久的磁盘数据库初始化其内存中的配置。 因此,磁盘被加载到内存中,然后传播到运行时配置。 如果找不到数据库文件,并且存在配置文件,则会将配置文件解析并将其内容加载到内存数据库中,然后将其保存在磁盘数据库中,并在运行时加载。 如果重要的是要注意,如果找到数据库文件,则不会解析配置文件。 也就是说,在正常启动期间,ProxySQL仅从持久存储的磁盘数据库中初始化其内存中的配置。

    新葡亰496net 1

    全局变量影响服务器的全局操作。会话变量影响具体客户端连接相关操作。服务器启动时,将所有全局变量初始化为默认值。可 以在选项文件或命令行中指定的选项来更改这些默认值。服务器启动后,通过连接服务器并执行SET GLOBAL var_name语句可以更改动态全局变量。要想更改全局变量,必须具有SUPER权限。

         注意,不是启动时--defaults-file指定的配置文件。

    初始启动(或 - 初始标志)

    在初始启动时,内存和运行时配置从配置文件而不是数据库文件中填充。 有可能强制初始启动运行proxysql的--initial标志,它通过重命名旧数据库来重置数据库。
    完成后,配置也会持续到磁盘数据库,这将用于下一次重新启动。

     

    mysqld服务器还为每个客户端连接维护会话变量。连接时使用相应全局变量的当前值对客户端会话变量进行初始化。客户可以通过SET [SESSION] var_name语句来更改动态会话变量。设置会话变量不需要特殊权限,但客户可以只更改自己的会话变量,而不更改其它客户的会话变量。

    [root@slowtech ~]# cat /var/lib/mysql/mysqld-auto.cnf 
    { "Version" : 1 , "mysql_server" : { "max_connections" : { "Value" : "200" , "Metadata" : { "Timestamp" : 1525509217566258 , "User" : "root" , "Host" : "localhost" } } } }
    

    重新加载启动(或--reload标志)

    如果proxysql使用--reload标志执行,则会尝试将配置文件中的配置与数据库文件的内容合并。 之后,它执行定期启动。 不能保证ProxySQL会成功管理这两个配置源的冲突,并且用户应该验证合并是否符合预期。

     

    通过命令有 session级别和 global级别两种 如
    session级别在设置后立即生效 断开连接后环境变量就失效了
    set session sort_buffer_size=10000 (无权限要求)

     

    在运行时修改配置

    在运行时修改配置是通过ProxyServer的MySQL管理端口完成的。 连接到它之后,我们会看到一个MySQL兼容的界面来查询几个与ProxySQL相关的表:
    proxysql>show tables;
    --------------------------------------
    | tables                               |
    --------------------------------------
    | global_variables                     |
    | mysql_collations                     |
    | mysql_query_rules                    |
    | mysql_replication_hostgroups         |
    | mysql_servers                        |
    | mysql_users                          |
    | runtime_global_variables             |
    | runtime_mysql_query_rules            |
    | runtime_mysql_replication_hostgroups |
    | runtime_mysql_servers                |
    | runtime_mysql_users                  |
    | runtime_scheduler                    |
    | scheduler                            |
    --------------------------------------
    13 rows in set (0.00 sec)
    每个这样的表在管理界面中具有明确定义的角色:

    mysql_servers包含用于连接到ProxySQL的后端服务器列表
    mysql_users包含用于向ProxySQL和后端服务器进行身份验证的用户列表
    mysql_query_rules包含缓存,路由或重写通过代理的SQL查询的规则
    global_variables包含单个表中的MySQL变量和管理变量
    debug_levels仅用于ProxySQL的调试版本
    这些表表示从上图的中间层(内存数据库),可以使用标准SQL查询进行操作。 为了从该层向上或向下移动配置,请参阅下一节。

     

    global不是立即生效 而是对新连接生效
    set global sort_buffer_size=100000要求有SUPER权限)

    持久化信息以json格式保存,其中,Metadata记录了这次修改的用户及时间信息。

    Moving config between layers(在层之间移动配置)

    为了在三层之间移动配置,通过管理界面有一组不同的管理命令。 一旦了解了三层中的每一层意味着什么,语义应该是相当明显的。 连同每个命令的说明,旁边写有一个数字。 数字对应于上图中的箭头。

     

    定义MySQL事务的例子

     

    For handling MySQL users:

    [1] LOAD MYSQL USERS FROM MEMORY / LOAD MYSQL USERS TO RUNTIME
    loads MySQL users from the in-memory database to the runtime data structures
    [2] SAVE MYSQL USERS TO MEMORY / SAVE MYSQL USERS FROM RUNTIME
    persists the MySQL users from the runtime data structures to the in-memory database
    [3] LOAD MYSQL USERS TO MEMORY / LOAD MYSQL USERS FROM DISK
    loads MySQL users from the on-disk database to the in-memory database
    [4] SAVE MYSQL USERS FROM MEMORY / SAVE MYSQL USERS TO DISK
    persists the MySQL users from the in-memory database to the on-disk database
    [5] LOAD MYSQL USERS FROM CONFIG
    loads from the configuration file the users into the in-memory database

    Server System Variables(系统变量)

    创建MySQL存储过程示例

    在数据库启动时,会首先读取其它配置文件,最后才读取mysqld-auto.cnf文件。不建议手动修改该文件,其有可能导致数据库在启动过程中因解析错误而失败。如果出现这种情况,可手动删除mysqld-auto.cnf文件或将persisted_globals_load变量设置为off来避免该文件的加载。

    For handling MySQL servers:

    [1] LOAD MYSQL SERVERS FROM MEMORY / LOAD MYSQL SERVERS TO RUNTIME
    loads MySQL servers from the in-memory database to the runtime data structures
    [2]新葡亰496net, SAVE MYSQL SERVERS TO MEMORY / SAVE MYSQL SERVERS FROM RUNTIME
    persists the MySQL servers from the runtime data structures to the in-memory database
    [3] LOAD MYSQL SERVERS TO MEMORY / LOAD MYSQL SERVERS FROM DISK
    loads MySQL servers from the on-disk database to the in-memory database
    [4] SAVE MYSQL SERVERS FROM MEMORY / SAVE MYSQL SERVERS TO DISK
    persists the MySQL servers from the in-memory database to the on-disk database
    [5] LOAD MYSQL SERVERS FROM CONFIG
    loads from the configuration file the servers into the in-memory database

     

    mysql存储过程的调用方法

     

    For handling MySQL query rules:

    [1] LOAD MYSQL QUERY RULES FROM MEMORY / LOAD MYSQL QUERY RULES TO RUNTIME
    loads MySQL query rules from the in-memory database to the runtime data structures
    [2] SAVE MYSQL QUERY RULES TO MEMORY / SAVE MYSQL QUERY RULES FROM RUNTIME
    persists the MySQL query rules from the runtime data structures to the in-memory database
    [3] LOAD MYSQL QUERY RULES TO MEMORY / LOAD MYSQL QUERY RULES FROM DISK
    loads MySQL query rules from the on-disk database to the in-memory database
    [4] SAVE MYSQL QUERY RULES FROM MEMORY / SAVE MYSQL QUERY RULES TO DISK
    persists the MySQL query rules from the in-memory database to the on-disk database
    [5] LOAD MYSQL QUERY RULES FROM CONFIG
    loads from the configuration file the query rules into the in-memory database

     

    MySQL DATEDIFF()语法介绍

    2. performance_schema.persisted_variables

    For handling MySQL variables:

    [1] LOAD MYSQL VARIABLES FROM MEMORY / LOAD MYSQL VARIABLES TO RUNTIME
    loads MySQL variables from the in-memory database to the runtime data structures
    [2] SAVE MYSQL VARIABLES FROM MEMORY / SAVE MYSQL VARIABLES TO DISK
    persists the MySQL variables from the in-memory database to the on-disk database
    [3] LOAD MYSQL VARIABLES TO MEMORY / LOAD MYSQL VARIABLES FROM DISK
    loads MySQL variables from the on-disk database to the in-memory database
    [4] SAVE MYSQL VARIABLES TO MEMORY / SAVE MYSQL VARIABLES FROM RUNTIME
    persists the MySQL variables from the runtime data structures to the in-memory database
    [5] LOAD MYSQL VARIABLES FROM CONFIG
    loads from the configuration file the variables into the in-memory database

    MySQL系统变量(system variables)是指MySQL实例的各种系统变量,实际上是一些系统参数,用于初始化或设定数据库对系统资源的占用,文件存放位置等等,这些变量包含MySQL编译时的参数默认值,或者my.cnf配置文件里配置的参数值。默认情况下系统变量都是小写字母。官方文档介绍如下:

    MySQL日期的相关函数介绍

    mysql> select * from performance_schema.persisted_variables;
     ----------------- ---------------- 
    | VARIABLE_NAME  | VARIABLE_VALUE |
     ----------------- ---------------- 
    | max_connections | 200            |
     ----------------- ---------------- 
    1 row in set (0.00 sec)
    

    For handling admin variables:

    [1] LOAD ADMIN VARIABLES FROM MEMORY / LOAD ADMIN VARIABLES TO RUNTIME
    loads admin variables from the in-memory database to the runtime data structures
    [2] SAVE ADMIN VARIABLES TO MEMORY / SAVE ADMIN VARIABLES FROM RUNTIME
    persists the admin variables from the runtime data structures to the in-memory database
    [3] LOAD ADMIN VARIABLES TO MEMORY / LOAD ADMIN VARIABLES FROM DISK
    loads admin variables from the on-disk database to the in-memory database
    [4] SAVE ADMIN VARIABLES FROM MEMORY / SAVE ADMIN VARIABLES TO DISK
    persists the admin variables from the in-memory database to the on-disk database

     

    MySQL修...

     

    The MySQL server maintains many system variables that indicate how it is configured. Each system variable has a default value. System variables can be set at server startup using options on the command line or in an option file. Most of them can be changed dynamically at runtime using the SET statement, which enables you to modify operation of the server without having to stop and restart it. You can also use system variable values in expressions.

    全局变量的持久化除了SET PERSIST外,还有SET PERSIST_ONLY,与前者相比,其只持久化全局变量,而不修改其内存值。同时,在权限方面,前者只需要SYSTEM_VARIABLES_ADMIN,后者还需要PERSIST_RO_VARIABLES_ADMIN权限。

     

     

     

    对于已经持久化了变量,可通过RESET PERSIST命令清除掉,注意,其只是清空mysqld-auto.cnf和performance_schema.persisted_variables中的内容,对于已经修改了的变量的值,不会产生任何影响。

    系统变量(system variables)按作用域范围可以分为会话级别系统变量和全局级别系统变量。如果要确认系统变量是全局级别还是会话级别,可以参考官方文档,如果Scope其值为GLOBAL或SESSION,表示变量既是全局级别系统变量,又是会话级别系统变量。如果其Scope其值为GLOBAL,表示系统变量为全局级别系统变量。

     

     

    除此之外,还可以通过下述方式将全局变量持久化为默认值。注意,是默认值,而不是修改前的值。

    --查看系统变量的全局值

    mysql> set persist max_connections=default;
    

     

     

    select * from information_schema.global_variables;

    这个命令同“set global max_connections=default”类似,都会将变量的值设置为默认值,只不过前者还会将默认值持久化到配置文件中。

    select * from information_schema.global_variables

     

      where variable_name='xxxx';

     

    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%'一样能查到其值。所以这种方式无法区别系统变量是会话级别还是全局级别。

     

    mysql> show session variables like '%automatic_sp_privileges%';
    
     ------------------------- ------- 
    
    | Variable_name           | Value |
    
     ------------------------- ------- 
    
    | automatic_sp_privileges | ON    |
    
     ------------------------- ------- 
    
    1 row in set (0.00 sec)
    
     
    
    mysql> select * from information_schema.global_variables
    
        -> where variable_name='automatic_sp_privileges';
    
     ------------------------- ---------------- 
    
    | VARIABLE_NAME           | VARIABLE_VALUE |
    
     ------------------------- ---------------- 
    
    | AUTOMATIC_SP_PRIVILEGES | ON             |
    
     ------------------------- ---------------- 
    
    1 row in set, 1 warning (0.00 sec)
    
     
    
    mysql> 
    

     

     

    如果要区分系统变量是全局还是会话级别,可以用下面方式:

     

    方法1: 查官方文档中系统变量的Scope属性。

    方法2: 使用SET VARIABLE_NAME=xxx; 如果报ERROR 1229 (HY000),则表示该变量为全局,如果不报错,那么证明该系统变量为全局和会话两个级别。

       

     

    mysql> SET AUTOMATIC_SP_PRIVILEGES=OFF;
    
     
    
    ERROR 1229 (HY000): Variable 'automatic_sp_privileges' is a GLOBAL variable and should be set with SET GLOBAL
    

     

     

     

    可以使用SET命令修改系统变量的值,如下所示:

     

    修改全局级别系统变量:

     

    SET GLOBAL max_connections=300;
    
     
    
    SET @@global.max_connections=300;
    

     

    注意:更改全局变量的值,需要拥有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”这类错误。

     

    mysql> set max_connections=300;
    
    ERROR 1229 (HY000): Variable 'max_connections' is a GLOBAL variable and should be set with SET GLOBAL
    
    mysql> set global max_connections=300;
    
    Query OK, 0 rows affected (0.00 sec)
    
     
    
    mysql> 
    

     

     

     

    系统变量(system variables)按是否可以动态修改,可以分为系统动态变量(Dynamic System Variables)和系统静态变量。怎么区分系统变量是动态和静态的呢? 这个只能查看官方文档,系统变量的"Dynamic"属性为Yes,则表示可以动态修改。Dynamic Variable具体可以参考

     

    另外,有些系统变量是只读的,不能修改的。如下所示:

     

    mysql>

    mysql> set global innodb_version='5.6.21';

    ERROR 1238 (HY000): Variable 'innodb_version' is a read only variable

    mysql>

     

     

    另外,还有一个Structured System Variables概念,其实就是系统变量是一个结构体(Strut),官方介绍如下所示:

     

    Structured System Variables

     

    A structured variable differs from a regular system variable in two respects:

     

    Its value is a structure with components that specify server parameters considered to be closely related.

     

    There might be several instances of a given type of structured variable. Each one has a different name and refers to a different resource maintained by the server.

     

     

     

     

    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%'

     

      

    #当前测试环境
    
    ysql> select version() from dual;
    
    ----------- 
    
     version() |
    
    ----------- 
    
     5.7.21    |
    
    ----------- 
    
     row in set (0.00 sec)
    
       
    
    mysql> show status;  --查看所有的状态变量
    
     
    
     
    
     
    
    ysql> show global status like 'Aborted_connects%';
    
    ------------------ ------- 
    
     Variable_name    | Value |
    
    ------------------ ------- 
    
     Aborted_connects | 2     |
    
    ------------------ ------- 
    
     row in set (0.01 sec)
    
     
    
    ysql> show session status like 'Aborted_connects%';
    
    ------------------ ------- 
    
     Variable_name    | Value |
    
    ------------------ ------- 
    
     Aborted_connects | 2     |
    
    ------------------ ------- 
    
     row in set (0.00 sec)
    
     
    
    ysql> select * from information_schema.global_status;
    
    RROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for 'show_compatibility_56'
    
    ysql> #
    
    ysql> show variables like '%show_compatibility_56%';
    
    ----------------------- ------- 
    
     Variable_name         | Value |
    
    ----------------------- ------- 
    
     show_compatibility_56 | OFF   |
    
    ----------------------- ------- 
    
     row in set (0.00 sec)
    
     
    
    ysql> set global show_compatibility_56=on;
    
    uery OK, 0 rows affected (0.00 sec)
    
     
    
    ysql> select * from information_schema.global_status;
    
    ----------------------------------------------- --------------------------------------- 
    
     VARIABLE_NAME                                  VARIABLE_VALUE                         |
    
    ----------------------------------------------- --------------------------------------- 
    
     ABORTED_CLIENTS                               | 138097                                |
    
     ABORTED_CONNECTS                              | 5                                     |
    
     BINLOG_CACHE_DISK_USE                         | 0                                     |
    
     BINLOG_CACHE_USE                              | 0                                     |
    
    ....................................................................................
    
     
    
     
    
     
    
    select * from performance_schema.global_status;
    
    select * from performance_schema.session_status;
    

     

     

    注意:MySQL 5.7以后系统变量和状态变量需要从performance_schema中进行获取,information_schema仍然保留了GLOBAL_新葡亰496netproxysql的布局体系,MySQL的变量分类总括。STATUS,GLOBAL_VARIABLES两个表做兼容,如果希望沿用information_schema中进行查询的习惯,5.7提供了show_compatibility_56参数,设置为ON可以兼容5.7之前的用法,否则就会报错(ERROR 3167 (HY000)).

     

     

     

     

    User-Defined Variables(用户自定义变量)

     

    新葡亰496netproxysql的布局体系,MySQL的变量分类总括。 

    用户自定义变量,顾名思义就是用户自己定义的变量。用户自定义变量是基于当前会话的。 也就是说用户自定义变量的作用域局限于当前会话(连接),由一个客户端定义的用户自定义变量不能被其他客户端看到或使用。(例外:可以访问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";
    
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @$test1 from dual;
    
     --------- 
    
    | @$test1 |
    
     --------- 
    
    | test    |
    
     --------- 
    
    1 row in set (0.00 sec)
    
     
    
    mysql> 
    
    mysql> set @"ac#k":='kerry';
    
    Query OK, 0 rows affected (0.00 sec)
    
     
    
    mysql> select @"ac#k" from dual;
    
     --------- 
    
    | @"ac#k" |
    
     --------- 
    
    | kerry   |
    
     --------- 
    
    1 row in set (0.00 sec)
    
     
    
    mysql> 
    
     
    
     
    
    mysql> select version() from dual;
    
     ----------- 
    
    | version() |
    
     ----------- 
    
    | 5.7.21    |
    
     ----------- 
    
    1 row in set (0.00 sec)
    
     
    
    mysql> 
    
    mysql> set @my_test=1200;
    
    Query OK, 0 rows affected (0.00 sec)
    
     
    
    mysql> select @my_test;
    
     ---------- 
    
    | @my_test |
    
     ---------- 
    
    |     1200 |
    
     ---------- 
    
    1 row in set (0.00 sec)
    
     
    
    mysql> select connection_id() from dual;
    
     ----------------- 
    
    | connection_id() |
    
     ----------------- 
    
    |          149379 |
    
     ----------------- 
    
    1 row in set (0.00 sec)
    
     
    
    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 @My_Test, @my_TEST from dual;
    
     ---------- ---------- 
    
    | @My_Test | @my_TEST |
    
     ---------- ---------- 
    
    |     1200 |     1200 |
    
     ---------- ---------- 
    
    1 row in set (0.00 sec)
    
     
    
    mysql> 
    

     

     

     新葡亰496net 2

     

     

    mysql> select connection_id() from dual;
    
     ----------------- 
    
    | connection_id() |
    
     ----------------- 
    
    |          151821 |
    
     ----------------- 
    
    1 row in set (0.00 sec)
    
     
    
    mysql> select @my_test from dual;
    
     ---------- 
    
    | @my_test |
    
     ---------- 
    
    | NULL     |
    
     ---------- 
    
    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)
    
     
    
    mysql> 
    

     

     

    新葡亰496net 3

     

     

     

    用户自定义变量注意事项,以下为总结:

     

    1:未定义的用户自定义变量初始值是NULL

     

    mysql> select @kerry from dual;
    
     
    
     -------- 
    
     
    
    | @kerry |
    
     
    
     -------- 
    
     
    
    | NULL   |
    
     
    
     -------- 
    
     
    
    1 row in set (0.00 sec)
    

     

    注意:使用未定义变量不会产生任何语法错误,由于其被初始化为NULL值,如果没有意识到这一点,非常容易犯错。如下所示:

     

    mysql> select @num1, @num2 :=@num1 1 from dual;
    
     ------- ----------------- 
    
    | @num1 | @num2 :=@num1 1 |
    
     ------- ----------------- 
    
    | NULL  |            NULL |
    
     ------- ----------------- 
    
    1 row in set (0.00 sec)
    
     
    
    mysql>
    

     

     

     

    2:用户变量名对大小写不敏感(上面已经叙述,此处从略)

     

    3:自定义变量的类型是一个动态类型。

     

    MySQL中用户自定义变量,不严格限制数据类型的,它的数据类型根据你赋给它的值而随时变化。而且自定义变量如果赋予数字值,是不能保证进度的。官方文档介绍:

     

    User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value. Assignment of decimal and real values does not preserve the precision or scale of the value. A value of a type other than one of the permissible types is converted to a permissible type. For example, a value having a temporal or spatial data type is converted to a binary string. A value having the JSON data type is converted to a string with a character set of utf8mb4 and a collation of utf8mb4_bin.

     

     

     

    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 |
    
    |    18 |
    
    |    19 |
    
     ------- 
    
    12 rows in set (0.00 sec)
    
     
    
    mysql> set @rownum := 0;
    
    Query OK, 0 rows affected (0.00 sec)
    
     
    
    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.00 sec)
    
     
    
    mysql> select msgid , @rownum := @rownum  1 as rownum
    
        -> from message
    
        -> where @rownum <=5;
    
    Empty set (0.00 sec)
    
     
    
    mysql> select @rownum from dual;
    
     --------- 
    
    | @rownum |
    
     --------- 
    
    |       6 |
    
     --------- 
    
    1 row in set (0.00 sec)
    
     
    
    mysql> 
    

     

    新葡亰496net 4

     

    如上所示,第二次查询可能你想要的逻辑跟实际逻辑已经出现了偏差,这个是使用自定义变量需要小心的地方。因为用户自定义变量在当前会话中也算一个“全局变量”,它已经变成了6,where条件后面的 @rownum <= 5 逻辑为false了。一不小小心就会出现和你预想的结果出现偏差。

     

     

    不要在同一个非SET语句中同时赋值并使用同一个用户自定义变量,因为WHERE和SELECT是在查询执行的不同阶段被执行的。如果在查询中再加入ORDER BY的话,结果可能会更不同;

     

    mysql> set @rownum :=0;
    
    Query OK, 0 rows affected (0.00 sec)
    
     
    
    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.00 sec)
    
     
    
    mysql> 
    
     
    
     
    
     
    
    mysql> set @rownum := 0;
    
    Query OK, 0 rows affected (0.00 sec)
    
     
    
    mysql> select msgid, @rownum := @rownum  1 as rownum
    
        -> from message
    
        -> where @rownum <=5
    
        -> order by msgcontent;
    
     ------- -------- 
    
    | msgid | rownum |
    
     ------- -------- 
    
    |    20 |      1 |
    
    |    28 |      2 |
    
    |    43 |      3 |
    
    |    47 |      4 |
    
    ..................
    
    ..................
    
    |    22 |     57 |
    
    |    69 |     58 |
    
    |    40 |     59 |
    
    |    52 |     60 |
    
    |    24 |     61 |
    
    |    66 |     62 |
    
    |    51 |     63 |
    
     ------- -------- 
    
    63 rows in set (0.00 sec)
    
     
    
    mysql> 
    

     

    新葡亰496net 5

     

     

    如果按msgid排序,那么又是正常的,那三者有啥区别呢?

     

    mysql> set @rownum :=0;
    
    Query OK, 0 rows affected (0.00 sec)
    
     
    
    mysql> select msgid, @rownum := @rownum  1 as rownum
    
        -> from message
    
        -> where @rownum <=5
    
        -> order by msgid;
    
     ------- -------- 
    
    | msgid | rownum |
    
     ------- -------- 
    
    |     1 |      1 |
    
    |     2 |      2 |
    
    |     3 |      3 |
    
    |     4 |      4 |
    
    |     5 |      5 |
    
    |     6 |      6 |
    
     ------- -------- 
    
    6 rows in set (0.00 sec)
    
     
    
    mysql> 
    

     

    我们先看执行计划

     

    新葡亰496net 6

     

    官方的解释如下:

     

    In a SELECT statement, each select expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, referring to a variable that is assigned a value in the select expression list does not work as expected

     

    在SELECT语句中,每个选择表达式仅在发送给客户端时才被计算。 这意味着在HAVING,GROUP BY或ORDER BY子句中,引用在选择表达式列表中指定值的用户自定义变量不能按预期工作。 也就是说用户自定义变量的值是在结果集发送到客户端后才计算的

     

    测试官方的例子:

     

    新葡亰496net 7

     

     

    新葡亰496net 8

     

     

    这种解释算是比较权威的,但是,让人有点不解的是,SQL执行顺序中WHERE在SELECT操作之前, 但是第一个SQL语句又怎么解释呢?有种解释是“MySQL优化器在某些场景下可能会将这些变量优化掉,这可能导致代码不按预想的方式运行。” 解决这个问题的办法是让变量的赋值和取值发生在执行查询的同一阶段,如下所示:

     

    新葡亰496net 9

     

     

     

     

    关于用户自定义变量,如果运用的好,能够写出高效简洁的SQL语句,如果运用不当,也可能把自己给坑了。这个完全取决于使用它的人。

     

    官方文档也有介绍用户自定义变量不适合使用场景。摘抄部分如下:

     

    User variables may be used in most contexts where expressions are permitted. This does not currently include contexts that explicitly require a literal value, such as in the LIMIT clause of a SELECT statement, or the IGNORE N LINES clause of a LOAD DATA statement.

     

    User variables are intended to provide data values. They cannot be used directly in an SQL statement as an identifier or as part of an identifier, such as in contexts where a table or database name is expected, or as a reserved word such as SELECT.

     

     

     

    局部变量

     

     

     

    局部变量:作用范围在begin到end语句块之间。在该语句块里设置的变量。declare语句专门用于定义声明局部变量。

     

     

    局部变量与用户自定义变量的区分在于下面这些方面:

     

    1.用户自定义变量是以"@"开头的。局部变量没有这个符号。

     

    2.定义变量方式不同。用户自定义变量使用set语句,局部变量使用declare语句定义

     

    3.作用范围不同。局部变量只在begin-end语句块之间有效。在begin-end语句块运行完之后,局部变量就消失了。而用户自定义变量是对当前连接(会话)有效。

     

     

     

     

     

     

     

    参考资料:

     

    本文由新葡亰496net发布于网络数据库,转载请注明出处:新葡亰496netproxysql的布局体系,MySQL的变量分类总

    关键词: