配置查询与线程追踪函数|全方位认识 sys 系统库

老叶茶馆

发布于 2020-12-15 11:02:28

1.8K0

发布于 2020-12-15 11:02:28

不知不觉中，我们的"全方位认识 sys 系统库" 系列文章已经接近尾声了，在上一篇《字符串与数字转换函数|全方位认识 sys 系统库》中，我们介绍了sys 系统库中用于字符串和数字格式化转换的函数，本期的内容给大家介绍 sys 系统库中的剩余函数，这也是本系列文章的最后一篇。

PS：下文中如果函数定义文本较短的会列出部分函数的定义文本，以便大家更直观地学习它们。过长的函数定义文本请自行按照《初相识|全方位认识 sys 系统库》一文中介绍的下载路径下载查看。

1.ps_is_account_enabled()

检查在performance_schema.setup_actors表中是否启用了对应account的性能事件监控功能，返回YES或者NO

参数：

in_host VARCHAR（60）：要检查的帐户的主机名
in_user VARCHAR（32）：要检查的帐户的用户名

返回值：一个枚举类型值，ENUM('YES','NO')，注意：返回值仅仅是依赖于在performance_schema.setup_actors表中找匹配记录而不管该帐号是否在mysql.user表中存在，如果在setup_actors表中开启了任意帐号的监控，那么就算在mysql.user中不存在的用户，也会返回YES

函数定义语句文本

DROP FUNCTION IF EXISTS ps_is_account_enabled;
DELIMITER $$
CREATE DEFINER='root'@'localhost' FUNCTION ps_is_account_enabled (
    in_host VARCHAR(60),
    in_user VARCHAR(32)
)
RETURNS ENUM('YES', 'NO')
COMMENT '
        Description
        -----------

        Determines whether instrumentation of an account is enabled 
        within Performance Schema.

        Parameters
        -----------

        in_host VARCHAR(60): 
          The hostname of the account to check.
        in_user VARCHAR(32):
          The username of the account to check.

        Returns
        -----------

        ENUM(\'YES\', \'NO\', \'PARTIAL\')

        Example
        -----------

        mysql> SELECT sys.ps_is_account_enabled(\'localhost\', \'root\');
        +------------------------------------------------+
        | sys.ps_is_account_enabled(\'localhost\', \'root\') |
        +------------------------------------------------+
        | YES                                            |
        +------------------------------------------------+
        1 row in set (0.01 sec)
        '
SQL SECURITY INVOKER
DETERMINISTIC
READS SQL DATA
BEGIN
RETURN IF(EXISTS(SELECT 1
                  FROM performance_schema.setup_actors
                  WHERE (`HOST` = '%' OR in_host LIKE `HOST`)
                    AND (`USER` = '%' OR `USER` = in_user)
                    AND (`ENABLED` = 'YES')
                ),
          'YES', 'NO'
      );
END$$
DELIMITER ;

2.ps_is_consumer_enabled()

检查指定的consumers配置知否真正生效。根据performance_schema.setup_consumers表中所有consumers的enabled字段设置值，按照consumers生效优先级依次检查指定consumers所依赖的consumers配置项是否启用，如果所依赖的consumers都启用时才会返回该consumers为YES，而不仅仅是返回该指定的consumers在performance_schema.setup_consumers表中的enabled字段值设置

如果指定的consumers名称无效，则返回NULL

参数：

in_consumer VARCHAR（64）：要检查是否启用的consumers名称字符串

返回值：一个ENUM('YES','NO') 值

函数定义语句文本

DROP FUNCTION IF EXISTS ps_is_consumer_enabled;
DELIMITER $$
CREATE DEFINER='root'@'localhost' FUNCTION ps_is_consumer_enabled (
    in_consumer varchar(64)
)
RETURNS enum('YES', 'NO')
COMMENT '

        Determines whether a consumer is enabled (taking the consumer hierarchy into consideration)
        within the Performance Schema.

        Parameters
        -----------

        in_consumer VARCHAR(64): 
          The name of the consumer to check.

        Returns
        -----------

        ENUM(\'YES\', \'NO\')

        Example
        -----------

        mysql> SELECT sys.ps_is_consumer_enabled(\'events_stages_history\');
        +-----------------------------------------------------+
        | sys.ps_is_consumer_enabled(\'events_stages_history\') |
        +-----------------------------------------------------+
        | NO                                                  |
        +-----------------------------------------------------+
        1 row in set (0.00 sec)
        '
SQL SECURITY INVOKER
DETERMINISTIC
READS SQL DATA
BEGIN
RETURN (
    SELECT (CASE
              WHEN c.NAME = 'global_instrumentation' THEN c.ENABLED
              WHEN c.NAME = 'thread_instrumentation' THEN IF(cg.ENABLED = 'YES' AND c.ENABLED = 'YES', 'YES', 'NO')
              WHEN c.NAME LIKE '%\_digest'          THEN IF(cg.ENABLED = 'YES' AND c.ENABLED = 'YES', 'YES', 'NO')
              WHEN c.NAME LIKE '%\_current'          THEN IF(cg.ENABLED = 'YES' AND ct.ENABLED = 'YES' AND c.ENABLED = 'YES', 'YES', 'NO')
              ELSE IF(cg.ENABLED = 'YES' AND ct.ENABLED = 'YES' AND c.ENABLED = 'YES'
                      AND ( SELECT cc.ENABLED FROM performance_schema.setup_consumers cc WHERE NAME = CONCAT(SUBSTRING_INDEX(c.NAME, '_', 2), '_current')
                          ) = 'YES', 'YES', 'NO')
            END) AS IsEnabled
      FROM performance_schema.setup_consumers c
          INNER JOIN performance_schema.setup_consumers cg
          INNER JOIN performance_schema.setup_consumers ct
    WHERE cg.NAME      = 'global_instrumentation'
          AND ct.NAME  = 'thread_instrumentation'
          AND c.NAME    = in_consumer
  );
END$$
DELIMITER ;

3.ps_is_instrument_default_enabled()

在performance_schema.setup_instruments表中检查指定instruments默认是否启用，默认只启用'wait/io/file/%'、'wait/io/table/%'、'statement/%'、'memory/performance_schema/%'、'wait/lock/table/sql/handler'、'idle'、'stage/innodb/%'、'stage/sql/copy to tmp table'，传入参数值不匹配这些instruments即表示是默认关闭的instruments(返回NO)，如果匹配则表示是默认启用的instruments(返回YES)

参数：

in_instrument VARCHAR（128）：要检查默认是否启用的instruments的名称字符串

返回值：一个ENUM('YES','NO')值

函数定义语句文本

DROP FUNCTION IF EXISTS ps_is_instrument_default_enabled;
DELIMITER $$
CREATE DEFINER='root'@'localhost' FUNCTION ps_is_instrument_default_enabled (
    in_instrument VARCHAR(128)
)
RETURNS ENUM('YES', 'NO')
COMMENT '
        Description
        -----------

        Returns whether an instrument is enabled by default in this version of MySQL.

        Parameters
        -----------

        in_instrument VARCHAR(128): 
          The instrument to check.

        Returns
        -----------

        ENUM(\'YES\', \'NO\')

        Example
        -----------

        mysql> SELECT sys.ps_is_instrument_default_enabled(\'statement/sql/select\');
        +--------------------------------------------------------------+
        | sys.ps_is_instrument_default_enabled(\'statement/sql/select\') |
        +--------------------------------------------------------------+
        | YES                                                          |
        +--------------------------------------------------------------+
        1 row in set (0.00 sec)
        '
SQL SECURITY INVOKER
DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE v_enabled ENUM('YES', 'NO');

-- Currently the same in all versions
SET v_enabled = IF(in_instrument LIKE 'wait/io/file/%'
                    OR in_instrument LIKE 'wait/io/table/%'
                    OR in_instrument LIKE 'statement/%'
                    OR in_instrument LIKE 'memory/performance_schema/%'
                    OR in_instrument IN ('wait/lock/table/sql/handler', 'idle')
          /*!50707
                    OR in_instrument LIKE 'stage/innodb/%'
                    OR in_instrument = 'stage/sql/copy to tmp table'
          */
                  ,
                  'YES',
                  'NO'
                );

RETURN v_enabled;
END$$
DELIMITER ;

4.ps_is_instrument_default_timed()

在performance_schema.setup_instruments表中检查指定instruments是否启用定时器功能，默认只启用'wait/io/file/%'、'wait/io/table/%'、'statement/%'、'wait/lock/table/sql/handler'、'idle'、'stage/innodb/%'、'stage/sql/copy to tmp table'这些instruments的timed，传入参数值不匹配这些instruments即表示是指定instruments的定时器功能默认是关闭的（返回NO），如果匹配则表示指定的instruments的定时器功能默认是启用的(返回YES)

参数：

in_instrument VARCHAR（128）：要检查是否默认启用定时器功能的instruments的名称字符串

返回值：一个ENUM('YES','NO')值

函数定义语句文本

DROP FUNCTION IF EXISTS ps_is_instrument_default_timed;
DELIMITER $$
CREATE DEFINER='root'@'localhost' FUNCTION ps_is_instrument_default_timed (
    in_instrument VARCHAR(128)
)
RETURNS ENUM('YES', 'NO')
COMMENT '
        Description
        -----------

        Returns whether an instrument is timed by default in this version of MySQL.

        Parameters
        -----------

        in_instrument VARCHAR(128): 
          The instrument to check.

        Returns
        -----------

        ENUM(\'YES\', \'NO\')

        Example
        -----------

        mysql> SELECT sys.ps_is_instrument_default_timed(\'statement/sql/select\');
        +------------------------------------------------------------+
        | sys.ps_is_instrument_default_timed(\'statement/sql/select\') |
        +------------------------------------------------------------+
        | YES                                                        |
        +------------------------------------------------------------+
        1 row in set (0.00 sec)
        '
SQL SECURITY INVOKER
DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE v_timed ENUM('YES', 'NO');

-- Currently the same in all versions
SET v_timed = IF(in_instrument LIKE 'wait/io/file/%'
                    OR in_instrument LIKE 'wait/io/table/%'
                    OR in_instrument LIKE 'statement/%'
                    OR in_instrument IN ('wait/lock/table/sql/handler', 'idle')
          /*!50707
                    OR in_instrument LIKE 'stage/innodb/%'
                    OR in_instrument = 'stage/sql/copy to tmp table'
          */
                  ,
                  'YES',
                  'NO'
                );

RETURN v_timed;
END$$
DELIMITER ;

5.ps_is_thread_instrumented()

在performance_schema.threads表中检查指定线程是否启用了性能事件监测功能，给定参数值对应performance_schema.threads表中的PROCESSLIST_ID列或SHOW PROCESSLIST输出的Id列值

如果给定连接ID值在performance_schema.threads表中未查询到，则返回UNKNOWN值，如果给定ID值为NULL则返回NULL

参数：

in_connection_id BIGINT UNSIGNED：连接ID。对应performance_schema.threads表中的PROCESSLIST_ID列值或SHOW PROCESSLIST输出的Id列值

返回值：一个ENUM('YES','NO','UNKNOWN')值

函数定义语句文本

DROP FUNCTION IF EXISTS ps_is_thread_instrumented;
DELIMITER $$
CREATE DEFINER='root'@'localhost' FUNCTION ps_is_thread_instrumented (
    in_connection_id BIGINT UNSIGNED
) RETURNS ENUM('YES', 'NO', 'UNKNOWN')
COMMENT '
        Description
        -----------

        Checks whether the provided connection id is instrumented within Performance Schema.

        Parameters
        -----------

        in_connection_id (BIGINT UNSIGNED):
          The id of the connection to check.

        Returns
        -----------

        ENUM(\'YES\', \'NO\', \'UNKNOWN\')

        Example
        -----------

        mysql> SELECT sys.ps_is_thread_instrumented(CONNECTION_ID());
        +------------------------------------------------+
        | sys.ps_is_thread_instrumented(CONNECTION_ID()) |
        +------------------------------------------------+
        | YES                                            |
        +------------------------------------------------+
        '

SQL SECURITY INVOKER
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE v_enabled ENUM('YES', 'NO', 'UNKNOWN');

IF (in_connection_id IS NULL) THEN
    RETURN NULL;
END IF;

SELECT INSTRUMENTED INTO v_enabled
  FROM performance_schema.threads
WHERE PROCESSLIST_ID = in_connection_id;

IF (v_enabled IS NULL) THEN
    RETURN 'UNKNOWN';
ELSE
    RETURN v_enabled;
END IF;
END$$
DELIMITER ;

6.ps_thread_account()

在performance_schema.threads表中查询并返回给定内部线程ID号相关联的account名称(user_name@host_name)

该函数在MySQL 5.7.9中新增

参数：

in_thread_id BIGINT UNSIGNED：指定一个内部线程ID，返回该内部线程ID相关联的account名称，该值与performance_schema.threads表中的thread_id列值对应

返回值：一个TEXT文本值

函数定义语句文本

DROP FUNCTION IF EXISTS ps_thread_account;
DELIMITER $$
CREATE DEFINER='root'@'localhost' FUNCTION ps_thread_account (
    in_thread_id BIGINT UNSIGNED
) RETURNS TEXT
COMMENT '
        Description
        -----------

        Return the user@host account for the given Performance Schema thread id.

        Parameters
        -----------

        in_thread_id (BIGINT UNSIGNED):
          The id of the thread to return the account for.

        Example
        -----------

        mysql> select thread_id, processlist_user, processlist_host from performance_schema.threads where type = ''foreground'';
          +-----------+------------------+------------------+
          | thread_id | processlist_user | processlist_host |
          +-----------+------------------+------------------+
          |        23 | NULL            | NULL            |
          |        30 | root            | localhost        |
          |        31 | msandbox        | localhost        |
          |        32 | msandbox        | localhost        |
          +-----------+------------------+------------------+
          4 rows in set (0.00 sec)

          mysql> select sys.ps_thread_account(31);
          +---------------------------+
          | sys.ps_thread_account(31) |
          +---------------------------+
          | msandbox@localhost        |
          +---------------------------+
          1 row in set (0.00 sec)
        '

SQL SECURITY INVOKER
NOT DETERMINISTIC
READS SQL DATA
BEGIN
RETURN (SELECT IF(
                  type = 'FOREGROUND',
                  CONCAT(processlist_user, '@', processlist_host),
                  type
                ) AS account
          FROM `performance_schema`.`threads`
        WHERE thread_id = in_thread_id);
END$$
DELIMITER ;

7.ps_thread_id()

在performance_schema.threads表中查询给定连接ID(processlist_id)的内部线程ID，如果给定连接ID为NULL值，则返回当前连接的内部线程ID

参数：

in_connection_id BIGINT UNSIGNED：要返回内部线程ID的连接的ID。对应performance_schema.threads表中的PROCESSLIST_ID列或SHOW PROCESSLIST输出的Id列值

返回值：一个BIGINT UNSIGNED值

函数定义语句文本

DROP FUNCTION IF EXISTS ps_thread_id;
DELIMITER $$
CREATE DEFINER='root'@'localhost' FUNCTION ps_thread_id (
    in_connection_id BIGINT UNSIGNED
) RETURNS BIGINT UNSIGNED
COMMENT '
        Description
        -----------

        Return the Performance Schema THREAD_ID for the specified connection ID.

        Parameters
        -----------

        in_connection_id (BIGINT UNSIGNED):
          The id of the connection to return the thread id for. If NULL, the current
          connection thread id is returned.

        Example
        -----------

        mysql> SELECT sys.ps_thread_id(79);
        +----------------------+
        | sys.ps_thread_id(79) |
        +----------------------+
        |                  98 |
        +----------------------+
        1 row in set (0.00 sec)

        mysql> SELECT sys.ps_thread_id(CONNECTION_ID());
        +-----------------------------------+
        | sys.ps_thread_id(CONNECTION_ID()) |
        +-----------------------------------+
        |                                98 |
        +-----------------------------------+
        1 row in set (0.00 sec)
        '

SQL SECURITY INVOKER
NOT DETERMINISTIC
READS SQL DATA
BEGIN
RETURN (SELECT THREAD_ID
          FROM `performance_schema`.`threads`
        WHERE PROCESSLIST_ID = IFNULL(in_connection_id, CONNECTION_ID())
      );
END$$
DELIMITER ;

8.ps_thread_stack()

在performance_schema下的events_statements_history_long、events_waits_history_long、events_stages_history_long表中查询并返回指定内部线程ID的事件信息（json格式返回），可以把这些事件信息看作是该指定内部线程ID堆信息

参数：

in_thread_id BIGINT：要跟踪堆信息的内部线程ID。该值对应performance_schema.threads表的THREAD_ID列值
in_verbose BOOLEAN：是否在输出的事件堆信息中包含事件的instruments所在的源文件名和代码行号信息

返回值：一个LONGTEXT CHARACTER SET latin1长字符串值

示例

root@localhost : (none) 02:45:39> SELECT sys.ps_thread_stack(50, FALSE) AS thread_stack\G;
*************************** 1. row ***************************
thread_stack: {"rankdir": "LR","nodesep": "0.10","stack_created": "2017-09-09 14:47:50","mysql_version": "5.7.18-log","mysql_user": "root@%","events": [{"nesting_event_id": "0", "event_id": "8", \
"timer_wait": 57.59, "event_info": "sql/commit", "wait_info": "commit\nerrors: 0\nwarnings: 0\nlock time: 0.00us\nrows affected: 0\nrows sent: 0\nrows examined: 0\ntmp tables: 0\ntmp disk tables: \
0\nselect scan: 0\nselect full join: 0\nselect full range join: 0\nselect range: 0\nselect range check: 0\nsort merge passes: 0\nsort rows: 0\nsort range: 0\nsort scan: 0\nno index used: FALSE\nno good index used: \
......
"event_type": "io/socket"},{"nesting_event_id": "42", "event_id": "87", "timer_wait": 0.81, "event_info": "sql/cleaning up", "wait_info": "", "source": "", "event_type": "stage"},{"nesting_event_id": "87", "event_id": \
"88", "timer_wait": 0.06, "event_info": "sql/THD::LOCK_thd_query\\n", "wait_info": "sql_class.cc:4320", "source": "sql_class.cc:4320", "event_type": "synch/mutex"}]}
1 row in set (0.00 sec)

9.ps_thread_trx_info()

在performance_schema下的events_transactions_current、events_transactions_history、events_statements_history表中查询并返回指定内部线程ID的事务、语句事件信息(json格式返回)，这些事件信息包括当前正在执行的事务以及已经执行完成的语句信息(必须启用events_transactions_current、events_transactions_history、events_statements_history对应的consumers配置才能够获取这些信息)

如果ps_thread_trx_info()函数输出json格式字符串长度超过默认的65535字节长度，则返回json错误对象(如：{ "error": "Trx info truncated: Row 6 was cut by GROUP_CONCAT()" })，然后函数中会对该错误做进一步处理
此函数在MySQL 5.7.9中新增

参数：

in_thread_id BIGINT UNSIGNED：用于返回事务、语句事件信息的内部线程ID，该值与performance_schema.threads表中的THREAD_ID列值对应

配置选项：

ps_thread_trx_info.max_length，@sys.ps_thread_trx_info.max_length：控制ps_thread_trx_info()函数输出的最大字节长度，默认为65535字节

返回值：一个LONGTEXT长文本值

示例

root@localhost : (none) 02:47:50> select sys.ps_thread_trx_info(50)\G
*************************** 1. row ***************************
sys.ps_thread_trx_info(50): [
{
"time": "10.99 m",
"state": "ACTIVE",
"mode": "READ WRITE",
"autocommitted": "NO",
"gtid": "AUTOMATIC",
"isolation": "READ COMMITTED",
"statements_executed": [
  {
    "sql_text": "select * from t_luoxiaobo limit 200",
    "time": "544.65 us",
    "schema": "luoxiaobo",
    "rows_examined": 200,
    "rows_affected": 0,
    "rows_sent": 200,
    "tmp_tables": 0,
    "tmp_disk_tables": 0,
    "sort_rows": 0,
    "sort_merge_passes": 0
   }
   ]
}
]
1 row in set (0.01 sec)

root@localhost : (none) 02:58:42> select sys.ps_thread_trx_info(50)\G
*************************** 1. row ***************************
sys.ps_thread_trx_info(50): [
{
"time": "12.51 m",
"state": "COMMITTED",
"mode": "READ WRITE",
"autocommitted": "NO",
"gtid": "AUTOMATIC",
"isolation": "READ COMMITTED",
"statements_executed": [
  {
    "sql_text": "select * from t_luoxiaobo limit 200",
    "time": "544.65 us",
    "schema": "luoxiaobo",
    "rows_examined": 200,
    "rows_affected": 0,
    "rows_sent": 200,
    "tmp_tables": 0,
    "tmp_disk_tables": 0,
    "sort_rows": 0,
    "sort_merge_passes": 0
  },
  {
    "sql_text": "commit",
    "time": "55.36 us",
    "schema": "luoxiaobo",
    "rows_examined": 0,
    "rows_affected": 0,
    "rows_sent": 0,
    "tmp_tables": 0,
    "tmp_disk_tables": 0,
    "sort_rows": 0,
    "sort_merge_passes": 0
  }
  ]
}
]
1 row in set (0.00 sec)

10.quote_identifier()

返回字符串作为引用的标识符，即给字符串前后加上反撇，对于已存在的反撇直接替换为两个反撇，当SQL语句文本中存在着保留字或者是反撇号(`)字符时，该函数可以快速帮助添加反撇

该函数在MySQL 5.7.14中新增

参数：

in_identifier TEXT：要作为引用标识符的文本字符串

返回值：一个TEXT文本值

函数定义语句文本

DROP FUNCTION IF EXISTS quote_identifier;
DELIMITER $$
-- https://dev.mysql.com/doc/refman/5.7/en/identifiers.html
-- Maximum supported length for any of the current identifiers in 5.7.5+ is 256 characters.
-- Before that, user variables could have any length.
--
-- Based on Paul Dubois' suggestion in Bug #78823/Bug #22011361.
CREATE DEFINER='root'@'localhost' FUNCTION quote_identifier(in_identifier TEXT)
RETURNS TEXT CHARSET UTF8
COMMENT '
        Description
        -----------

        Takes an unquoted identifier (schema name, table name, etc.) and
        returns the identifier quoted with backticks.

        Parameters
        -----------

        in_identifier (TEXT):
          The identifier to quote.

        Returns
        -----------

        TEXT

        Example
        -----------

        mysql> SELECT sys.quote_identifier(''my_identifier'') AS Identifier;
        +-----------------+
        | Identifier      |
        +-----------------+
        | `my_identifier` |
        +-----------------+
        1 row in set (0.00 sec)

        mysql> SELECT sys.quote_identifier(''my`idenfier'') AS Identifier;
        +----------------+
        | Identifier    |
        +----------------+
        | `my``idenfier` |
        +----------------+
        1 row in set (0.00 sec)
        '
SQL SECURITY INVOKER
DETERMINISTIC
NO SQL
BEGIN
RETURN CONCAT('`', REPLACE(in_identifier, '`', '``'), '`');
END$$
DELIMITER ;

11.sys_get_config()

返回给定sys 系统库配置选项名称的设置值，两个传参，第一个是要查看的配置变量名称，第二个是传入的默认值，如果在sys.sys_config表中查询到了该变量的非null值，则直接返回，如果查询到是null值，则使用第二个传参返回(如果在sys.sys_config表中没有查询到第一个传参的变量名---即可能传入的配置选项名称在sys.sys_config表中不存在，返回第二个传参值)

按照惯例，调用者在sys_get_config()函数之前需要先检查相应的用户定义变量是否存在并且是否非NULL。如果存在所需配置选项的自定义变量，且值不为NULL，该调用者直接使用自定义配置选项变量值而不是第哦啊用sys_get_config()函数读取sys.sys_config表中的值，除此之外，其他情形都需要调用sys_get_config()函数读取sys.sys_config表中的值并赋值给用户自定义配置选项变量，以便下次优先使用自定义配置选项变量中的值而不是直接查询sys.sys_config表中的值，关于配置选项和自定义配置选项变量详见《配置表|全方位认识 sys 系统库》
当调这需要获取配置选项值时，如果要检查配置选项是否设置了自定义配置选项变量，那么可以使用IFNULL（...）语句IF（...）THEN ... END IF;语句把这一些逻辑封装在一个流程控制语句里，但是，这两个语句中，IFNULL（...）语句在需要反复查询一个配置选项值时其执行速度会显著比IF（...）THEN ... END IF;语句慢，因为IFNULL（...）语句无法在加入一个干活的逻辑在里边，IF（...）THEN ... END IF;语句可以把一些干活的逻辑加入到里边，只在第一次调用时才需要去判断自定义变量知否存在以及是否为NULL值（两个语句如何使用详见函数定义语句中的注释示例文本）

参数：

in_variable_name VARCHAR（128）：给定的配置选项名称字符串
in_default_value VARCHAR（128）：如果在sys_config表中找不到给定的配置选项名称，则返回该参数给定的值

返回值：一个VARCHAR（128）文本值

示例

-- Get the configuration value from sys.sys_config falling back on 128 if the option is not present in the table.
mysql> SELECT sys.sys_get_config(''statement_truncate_len'', 128) AS Value;
+-------+
| Value |
+-------+
| 64 |
+-------+
1 row in set (0.00 sec)

-- Check whether the option is already set, if not assign - IFNULL(...) one liner example.
mysql> SET @sys.statement_truncate_len = IFNULL(@sys.statement_truncate_len, sys.sys_get_config(''statement_truncate_len'', 64));
Query OK, 0 rows affected (0.00 sec)

-- Check whether the option is already set, if not assign - IF ... THEN ... END IF example.
IF (@sys.statement_truncate_len IS NULL) THEN
SET @sys.statement_truncate_len = sys.sys_get_config(''statement_truncate_len'', 64);
END IF;

12.version_major()

通过version()函数获取并返回MySQL server的主版本号，该函数在MySQL 5.7.9中新增

该函数调用时无需传入任何参数

返回值：一个TINYINT UNSIGNED值

函数定义语句文本

DROP FUNCTION IF EXISTS version_major;
DELIMITER $$
CREATE DEFINER='root'@'localhost' FUNCTION version_major ()
RETURNS TINYINT UNSIGNED
COMMENT '
        Description
        -----------

        Returns the major version of MySQL Server.

        Returns
        -----------

        TINYINT UNSIGNED

        Example
        -----------

        mysql> SELECT VERSION(), sys.version_major();
        +--------------------------------------+---------------------+
        | VERSION()                            | sys.version_major() |
        +--------------------------------------+---------------------+
        | 5.7.9-enterprise-commercial-advanced | 5                  |
        +--------------------------------------+---------------------+
        1 row in set (0.00 sec)
        '
SQL SECURITY INVOKER
NOT DETERMINISTIC
NO SQL
BEGIN
RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(VERSION(), '-', 1), '.', 1);
END$$
DELIMITER ;

13.version_minor()

通过version()函数获取并返回MySQL server的次要版本号，该函数在MySQL 5.7.9中新增

该函数执行时无需传入任何参数

返回值：一个TINYINT UNSIGNED值

函数定义语句文本

DROP FUNCTION IF EXISTS version_minor;
DELIMITER $$
CREATE DEFINER='root'@'localhost' FUNCTION version_minor ()
RETURNS TINYINT UNSIGNED
COMMENT '
        Description
        -----------

        Returns the minor (release series) version of MySQL Server.

        Returns
        -----------

        TINYINT UNSIGNED

        Example
        -----------

        mysql> SELECT VERSION(), sys.server_minor();
        +--------------------------------------+---------------------+
        | VERSION()                            | sys.version_minor() |
        +--------------------------------------+---------------------+
        | 5.7.9-enterprise-commercial-advanced | 7                  |
        +--------------------------------------+---------------------+
        1 row in set (0.00 sec)
        '
SQL SECURITY INVOKER
NOT DETERMINISTIC
NO SQL
BEGIN
RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(VERSION(), '-', 1), '.', 2), '.', -1);
END$$
DELIMITER ;

14.version_patch()

通过version()函数获取并返回MySQL server的补丁版本号，该函数在MySQL 5.7.9中新增

该函数执行时无需传入任何参数

返回值：一个TINYINT UNSIGNED值

函数定义语句文本

DROP FUNCTION IF EXISTS version_patch;
DELIMITER $$
CREATE DEFINER='root'@'localhost' FUNCTION version_patch ()
RETURNS TINYINT UNSIGNED
COMMENT '
        Description
        -----------

        Returns the patch release version of MySQL Server.

        Returns
        -----------

        TINYINT UNSIGNED

        Example
        -----------

        mysql> SELECT VERSION(), sys.version_patch();
        +--------------------------------------+---------------------+
        | VERSION()                            | sys.version_patch() |
        +--------------------------------------+---------------------+
        | 5.7.9-enterprise-commercial-advanced | 9                  |
        +--------------------------------------+---------------------+
        1 row in set (0.00 sec)
        '
SQL SECURITY INVOKER
NOT DETERMINISTIC
NO SQL
BEGIN
RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(VERSION(), '-', 1), '.', -1);
END$$
DELIMITER ;

本期内容就介绍到这里，本期内容参考链接如下：

https://dev.mysql.com/doc/refman/5.7/en/sys-version-patch.html
https://dev.mysql.com/doc/refman/5.7/en/sys-ps-is-account-enabled.html
https://dev.mysql.com/doc/refman/5.7/en/sys-ps-is-instrument-default-enabled.html
https://dev.mysql.com/doc/refman/5.7/en/sys-ps-is-instrument-default-timed.html
https://dev.mysql.com/doc/refman/5.7/en/sys-ps-is-thread-instrumented.html
https://dev.mysql.com/doc/refman/5.7/en/sys-ps-thread-account.html
https://dev.mysql.com/doc/refman/5.7/en/sys-ps-thread-id.html
https://dev.mysql.com/doc/refman/5.7/en/sys-ps-thread-stack.html
https://dev.mysql.com/doc/refman/5.7/en/sys-ps-thread-trx-info.html
https://dev.mysql.com/doc/refman/5.7/en/sys-quote-identifier.html
https://dev.mysql.com/doc/refman/5.7/en/sys-sys-get-config.html
https://dev.mysql.com/doc/refman/5.7/en/sys-version-major.html
https://dev.mysql.com/doc/refman/5.7/en/sys-version-minor.html
https://dev.mysql.com/doc/refman/5.7/en/sys-ps-is-consumer-enabled.html

| 作者简介