字符串与数字转换函数 | 全方位认识 sys 系统库

最新推荐文章于 2021-01-27 12:24:14 发布

老叶茶馆_

最新推荐文章于 2021-01-27 12:24:14 发布

阅读量381

点赞数

文章标签：数据库字符串 python mysql java

原文链接：https://ke.qq.com/course/package/28037?saleToken=2203389\x26amp;from=pclink

版权

本系列在之前的文章中我们为大家介绍了sys 系统库的快捷视图、函数，本期开始我们将为大家介绍 sys 系统库的函数。

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

| extract_schema_from_file_name()

从给定文件的绝对路径中提取schema名称，该函数在sys.x$ps_schema_table_statistics_io视图中调用，当然你也可以在自定义视图中调用

此函数假定给定的所有数据文件都位于datadir目录下，因此，如果说表是分区表或者使用了单独的表选项定义了自己的DATA_DIRECTORY路径，那么虽然能够正确返回数据库的名称，但是后续使用这个数据库名称与其他视图联结使用时将无法在datadir下找到相应的表数据文件
该函数在拥有一个数据文件的完整路径时用这个路径作为传入参数提取performance_schema中的文件I/O信息非常实用，它提供了一种便捷的方式来获取schema名，比完整路径名更容易理解，并且该返回的schema名称字符串值后续可以用于联结查询

参数：

path VARCHAR(512)：一个用于提取schema名称的完整数据文件路径

返回值：是一个VARCHAR(64)字符串，即schema名称字符串

函数定义语句文本

DROP FUNCTION IF EXISTS extract_schema_from_file_name;
DELIMITER $$
CREATE DEFINER='root'@'localhost' FUNCTION extract_schema_from_file_name (
    path VARCHAR(512)
)
RETURNS VARCHAR(64)
COMMENT '
        Description
        -----------

        Takes a raw file path, and attempts to extract the schema name from it.

        Useful for when interacting with Performance Schema data 
        concerning IO statistics, for example.

        Currently relies on the fact that a table data file will be within a 
        specified database directory (will not work with partitions or tables
        that specify an individual DATA_DIRECTORY).

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

        path (VARCHAR(512)):
          The full file path to a data file to extract the schema name from.

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

        VARCHAR(64)

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

        mysql> SELECT sys.extract_schema_from_file_name(\'/var/lib/mysql/employees/employee.ibd\');
        +----------------------------------------------------------------------------+
        | sys.extract_schema_from_file_name(\'/var/lib/mysql/employees/employee.ibd\') |
        +----------------------------------------------------------------------------+
        | employees                                                                  |
        +----------------------------------------------------------------------------+
        1 row in set (0.00 sec)
        '
SQL SECURITY INVOKER
DETERMINISTIC
NO SQL
BEGIN
RETURN LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(path, '\\', '/'), '/', -2), '/', 1), 64);
END$$
DELIMITER ;

| extract_table_from_file_name()

从给定文件绝对路径名中提取表名，该函数在sys.x$ps_schema_table_statistics_io视图中调用，当然你也可以在自定义视图中调用

该函数在拥有一个数据文件的完整路径时用这个路径作为传入参数提取performance_schema中的文件I/O信息非常实用，它提供了一种便捷的方式来获取表名，比完整路径名更容易理解，并且该返回的表名称字符串值后续可以用于联结查询

参数：

path VARCHAR(512)：一个用于提取表名称的完整数据文件路径

返回值：是一个VARCHAR(64)字符串，即表名称字符串

函数定义语句文本

DROP FUNCTION IF EXISTS extract_table_from_file_name;
DELIMITER $$
CREATE DEFINER='root'@'localhost' FUNCTION extract_table_from_file_name (
    path VARCHAR(512)
)
RETURNS VARCHAR(64)
COMMENT '
        Description
        -----------

        Takes a raw file path, and extracts the table name from it.

        Useful for when interacting with Performance Schema data 
        concerning IO statistics, for example.

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

        path (VARCHAR(512)):
          The full file path to a data file to extract the table name from.

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

        VARCHAR(64)

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

        mysql> SELECT sys.extract_table_from_file_name(\'/var/lib/mysql/employees/employee.ibd\');
        +---------------------------------------------------------------------------+
        | sys.extract_table_from_file_name(\'/var/lib/mysql/employees/employee.ibd\') |
        +---------------------------------------------------------------------------+
        | employee                                                                  |
        +---------------------------------------------------------------------------+
        1 row in set (0.02 sec)
        '
SQL SECURITY INVOKER
DETERMINISTIC
NO SQL
BEGIN
RETURN LEFT(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(path, '\\', '/'), '/', -1), '@0024', '$'), '.', 1), 64);
END$$
DELIMITER ;

| format_bytes()

将字节值转换为其他单位的值（可读性更好的格式），根据字节数值大小，自动转换为KB、MB、GB、TB、PB这些可读单位(返回值由转换过的值+单位组成)，该函数在其他视图中大量使用

参数：

bytes TEXT：要格式化的字节文本值

回报值：一个TEXT文本值

函数定义语句文本

 DROP FUNCTION IF EXISTS format_bytes;
 DELIMITER $$
 CREATE DEFINER='root'@'localhost' FUNCTION format_bytes (
    -- We feed in and return TEXT here, as aggregates of
    -- bytes can return numbers larger than BIGINT UNSIGNED
    bytes TEXT
)
RETURNS TEXT
COMMENT '
        Description
        -----------

        Takes a raw bytes value, and converts it to a human readable format.

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

        bytes (TEXT):
          A raw bytes value.

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

        TEXT

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

        mysql> SELECT sys.format_bytes(2348723492723746) AS size;
        +----------+
        | size    |
        +----------+
        | 2.09 PiB |
        +----------+
        1 row in set (0.00 sec)

        mysql> SELECT sys.format_bytes(2348723492723) AS size;
        +----------+
        | size    |
        +----------+
        | 2.14 TiB |
        +----------+
        1 row in set (0.00 sec)

        mysql> SELECT sys.format_bytes(23487234) AS size;
        +-----------+
        | size      |
        +-----------+
        | 22.40 MiB |
        +-----------+
        1 row in set (0.00 sec)
        '
SQL SECURITY INVOKER
DETERMINISTIC
NO SQL
BEGIN
IF bytes IS NULL THEN RETURN NULL;
ELSEIF bytes >= 1125899906842624 THEN RETURN CONCAT(ROUND(bytes / 1125899906842624, 2), ' PiB');
ELSEIF bytes >= 1099511627776 THEN RETURN CONCAT(ROUND(bytes / 1099511627776, 2), ' TiB');
ELSEIF bytes >= 1073741824 THEN RETURN CONCAT(ROUND(bytes / 1073741824, 2), ' GiB');
ELSEIF bytes >= 1048576 THEN RETURN CONCAT(ROUND(bytes / 1048576, 2), ' MiB');
ELSEIF bytes >= 1024 THEN RETURN CONCAT(ROUND(bytes / 1024, 2), ' KiB');
ELSE RETURN CONCAT(ROUND(bytes, 0), ' bytes');
END IF;
END$$
DELIMITER ;

| format_path()

用变量符号值替换路径名中匹配到的datadir、tmpdir、slave_load_tmpdir、innodb_data_home_dir、innodb_log_group_home_dir、basedir、innodb_undo_directory系统变量值，给定null值返回null，给定值不匹配的直接返回原值，该函数在其他视图中大量使用

在MySQL 5.7.14之前，Windows路径名中的反斜杠()将在返回值中转换为正斜杠(/)

参数：

path VARCHAR(512)：要格式化转换的完整路径名

返回值：一个VARCHAR(512) CHARACTER SET utf8 值

示例

mysql> SELECT format_path('/usr/local/mysql/data/world/City.ibd');
+-----------------------------------------------------+
| format_path('/usr/local/mysql/data/world/City.ibd') |
+-----------------------------------------------------+
| @@datadir/world/City.ibd                            |
+-----------------------------------------------------+

| format_statement()

将长SQL语句文本截断为固定长度，该长度由配置变量@sys.statement_truncate_len控制，默认值在sys_config表中为64字节，如果语句文本少于statement_truncate_len，@sys.statement_truncate_len配置选项的长度则语句不会被截断，如果大于了配置选项指定的长度，则语句会被截断，执行截取操作时，中间部分被省略号替换（截取前30字节+'... '+ 后30字节，然后把这64字节中包含的\n字符替换为空格），该函数在其他视图中大量使用

该函数在其他视图，存储过程中大量使用，用于把performance_schema中的超长语句格式化为固定的长度

参数：

statement LONGTEXT：需要执行格式化的SQL语句文本

配置选项：可以使用以下配置选项或其相应的用户定义变量来修改format_statement()函数的截断最大长度

statement_truncate_len，@sys.statement_truncate_len：format_statement()函数返回的语句文本的最大长度。超长的语句文本将被截断。默认值为64字节

返回值：一个LONGTEXT长文本值

函数定义语句文本

DROP FUNCTION IF EXISTS format_statement;
DELIMITER $$
CREATE DEFINER='root'@'localhost' FUNCTION format_statement (
    statement LONGTEXT
)
RETURNS LONGTEXT
COMMENT '
        Description
        -----------

        Formats a normalized statement, truncating it if it is > 64 characters long by default.

        To configure the length to truncate the statement to by default, update the `statement_truncate_len`
        variable with `sys_config` table to a different value. Alternatively, to change it just for just 
        your particular session, use `SET @sys.statement_truncate_len := <some new value>`.

        Useful for printing statement related data from Performance Schema from 
        the command line.

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

        statement (LONGTEXT): 
          The statement to format.

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

        LONGTEXT

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

        mysql> SELECT sys.format_statement(digest_text)
            ->  FROM performance_schema.events_statements_summary_by_digest
            ->  ORDER by sum_timer_wait DESC limit 5;
        +-------------------------------------------------------------------+
        | sys.format_statement(digest_text)                                |
        +-------------------------------------------------------------------+
        | CREATE SQL SECURITY INVOKER VI ... KE ? AND `variable_value` > ?  |
        | CREATE SQL SECURITY INVOKER VI ... ait` IS NOT NULL , `esc` . ... |
        | CREATE SQL SECURITY INVOKER VI ... ait` IS NOT NULL , `sys` . ... |
        | CREATE SQL SECURITY INVOKER VI ...  , `compressed_size` ) ) DESC  |
        | CREATE SQL SECURITY INVOKER VI ... LIKE ? ORDER BY `timer_start`  |
        +-------------------------------------------------------------------+
        5 rows in set (0.00 sec)
        '
SQL SECURITY INVOKER
DETERMINISTIC
NO SQL
BEGIN
-- Check if we have the configured length, if not, init it
IF @sys.statement_truncate_len IS NULL THEN
  SET @sys.statement_truncate_len = sys_get_config('statement_truncate_len', 64);
END IF;

IF CHAR_LENGTH(statement) > @sys.statement_truncate_len THEN
  RETURN REPLACE(CONCAT(LEFT(statement, (@sys.statement_truncate_len/2)-2), ' ... ', RIGHT(statement, (@sys.statement_truncate_len/2)-2)), '\n', ' ');
ELSE
  RETURN REPLACE(statement, '\n', ' ');
END IF;
END$$
DELIMITER ;

| format_time()

将皮秒值转换为其他可读性更好的单位值，根据皮秒值大小，自动转换为ns、us、ms、s、m、h、d、w这些可读单位(返回值由转换过的值+单位组成)，该函数在其他视图中大量使用

参数：

picoseconds TEXT：要进行单位转换的皮秒文本值

返回值：一个TEXT文本值

函数定义语句文本

DROP FUNCTION IF EXISTS format_time;
DELIMITER $$
CREATE DEFINER='root'@'localhost' FUNCTION format_time (
    -- We feed in and return TEXT here, as aggregates of
    -- picoseconds can return numbers larger than BIGINT UNSIGNED
    picoseconds TEXT
)
RETURNS TEXT CHARSET UTF8
COMMENT '
        Description
        -----------

        Takes a raw picoseconds value, and converts it to a human readable form.

        Picoseconds are the precision that all latency values are printed in 
        within Performance Schema, however are not user friendly when wanting
        to scan output from the command line.

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

        picoseconds (TEXT): 
          The raw picoseconds value to convert.

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

        TEXT

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

        mysql> select format_time(342342342342345);
        +------------------------------+
        | format_time(342342342342345) |
        +------------------------------+
        | 00:05:42                    |
        +------------------------------+
        1 row in set (0.00 sec)

        mysql> select format_time(342342342);
        +------------------------+
        | format_time(342342342) |
        +------------------------+
        | 342.34 us              |
        +------------------------+
        1 row in set (0.00 sec)

        mysql> select format_time(34234);
          +--------------------+
        | format_time(34234) |
        +--------------------+
        | 34.23 ns          |
        +--------------------+
        1 row in set (0.00 sec)
        '
SQL SECURITY INVOKER
DETERMINISTIC
NO SQL
BEGIN
IF picoseconds IS NULL THEN RETURN NULL;
ELSEIF picoseconds >= 604800000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 604800000000000000, 2), ' w');
ELSEIF picoseconds >= 86400000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 86400000000000000, 2), ' d');
ELSEIF picoseconds >= 3600000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 3600000000000000, 2), ' h');
ELSEIF picoseconds >= 60000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 60000000000000, 2), ' m');
ELSEIF picoseconds >= 1000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 1000000000000, 2), ' s');
ELSEIF picoseconds >= 1000000000 THEN RETURN CONCAT(ROUND(picoseconds / 1000000000, 2), ' ms');
ELSEIF picoseconds >= 1000000 THEN RETURN CONCAT(ROUND(picoseconds / 1000000, 2), ' us');
ELSEIF picoseconds >= 1000 THEN RETURN CONCAT(ROUND(picoseconds / 1000, 2), ' ns');
ELSE RETURN CONCAT(picoseconds, ' ps');
END IF;
END$$
DELIMITER ;

| list_add()

将指定文本添加到一个指定名称的列表中，由两个传参定义，传入一个列表和一个字符串，返回值为在这个传入列表基础上添加了传入字符串的列表，如添加会话级别的sql_mode：set sql_mode=select sys.list_add(@@sql_mode,'ANSI_QUOTES');利用该函数来操作一些列表型系统变量可以减少人工赋值的失误

此函数和list_drop()函数一起可用于操作具有列表类型值的系统变量值的添加和删除，例如：sql_mode和optimizer_switch系统变量值具有一个逗号分隔的列表值
此函数在MySQL 5.7.9中新增

参数：

in_list TEXT：要修改值的列表变量名称，注意：该参数只能传入变量类型值，不能是字符串值，详见函数定义语句注释部分示例
in_add_value TEXT：要添加到列表变量中的具体字符串值

返回值：一个TEXT文本值

函数定义语句文本

DROP FUNCTION IF EXISTS list_add;
DELIMITER $$
CREATE DEFINER='root'@'localhost' FUNCTION list_add (
    in_list TEXT,
    in_add_value TEXT
)
RETURNS TEXT
COMMENT '
        Description
        -----------

        Takes a list, and a value to add to the list, and returns the resulting list.

        Useful for altering certain session variables, like sql_mode or optimizer_switch for instance.

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

        in_list (TEXT):
          The comma separated list to add a value to

        in_add_value (TEXT):
          The value to add to the input list

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

        TEXT

        Example
        --------

        mysql> select @@sql_mode;
        +-----------------------------------------------------------------------------------+
        | @@sql_mode                                                                        |
        +-----------------------------------------------------------------------------------+
        | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
        +-----------------------------------------------------------------------------------+
        1 row in set (0.00 sec)

        mysql> set sql_mode = sys.list_add(@@sql_mode, ''ANSI_QUOTES'');
        Query OK, 0 rows affected (0.06 sec)

        mysql> select @@sql_mode;
        +-----------------------------------------------------------------------------------------------+
        | @@sql_mode                                                                                    |
        +-----------------------------------------------------------------------------------------------+
        | ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
        +-----------------------------------------------------------------------------------------------+
        1 row in set (0.00 sec)

        '
SQL SECURITY INVOKER
DETERMINISTIC
CONTAINS SQL
BEGIN

IF (in_add_value IS NULL) THEN
    SIGNAL SQLSTATE '02200'
      SET MESSAGE_TEXT = 'Function sys.list_add: in_add_value input variable should not be NULL',
          MYSQL_ERRNO = 1138;
END IF;

IF (in_list IS NULL OR LENGTH(in_list) = 0) THEN
    -- return the new value as a single value list
    RETURN in_add_value;
END IF;

RETURN (SELECT CONCAT(TRIM(BOTH ',' FROM TRIM(in_list)), ',', in_add_value));
END$$
DELIMITER ;

| list_drop()

与list_add()函数类似，只是是从传入列表中删除传入字符串

此函数在MySQL 5.7.9中新增

参数：

in_list TEXT：要修改值的列表变量名称，注意：该参数只能传入变量类型值，不能是字符串值，详见函数定义语句注释部分示例
in_drop_value TEXT：要从列表变量中删除的文本值

返回值：一个TEXT文本值

函数定义语句文本

DROP FUNCTION IF EXISTS list_drop;
DELIMITER $$
CREATE DEFINER='root'@'localhost' FUNCTION list_drop (
    in_list TEXT,
    in_drop_value TEXT
)
RETURNS TEXT
COMMENT '
        Description
        -----------

        Takes a list, and a value to attempt to remove from the list, and returns the resulting list.

        Useful for altering certain session variables, like sql_mode or optimizer_switch for instance.

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

        in_list (TEXT):
          The comma separated list to drop a value from

        in_drop_value (TEXT):
          The value to drop from the input list

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

        TEXT

        Example
        --------

        mysql> select @@sql_mode;
        +-----------------------------------------------------------------------------------------------+
        | @@sql_mode                                                                                    |
        +-----------------------------------------------------------------------------------------------+
        | ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
        +-----------------------------------------------------------------------------------------------+
        1 row in set (0.00 sec)

        mysql> set sql_mode = sys.list_drop(@@sql_mode, ''ONLY_FULL_GROUP_BY'');
        Query OK, 0 rows affected (0.03 sec)

        mysql> select @@sql_mode;
        +----------------------------------------------------------------------------+
        | @@sql_mode                                                                |
        +----------------------------------------------------------------------------+
        | ANSI_QUOTES,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
        +----------------------------------------------------------------------------+
        1 row in set (0.00 sec)

        '
SQL SECURITY INVOKER
DETERMINISTIC
CONTAINS SQL
BEGIN

IF (in_drop_value IS NULL) THEN
    SIGNAL SQLSTATE '02200'
      SET MESSAGE_TEXT = 'Function sys.list_drop: in_drop_value input variable should not be NULL',
          MYSQL_ERRNO = 1138;
END IF;

IF (in_list IS NULL OR LENGTH(in_list) = 0) THEN
    -- return the list as it was passed in
    RETURN in_list;
END IF;

-- ensure that leading / trailing commas are remove, support values with either spaces or not between commas
RETURN (SELECT TRIM(BOTH ',' FROM REPLACE(REPLACE(CONCAT(',', in_list), CONCAT(',', in_drop_value), ''), CONCAT(', ', in_drop_value), '')));
END$$
DELIMITER ;

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

https://dev.mysql.com/doc/refman/5.7/en/sys-list-drop.html

https://dev.mysql.com/doc/refman/5.7/en/sys-extract-schema-from-file-name.html

https://dev.mysql.com/doc/refman/5.7/en/sys-format-bytes.html

https://dev.mysql.com/doc/refman/5.7/en/sys-format-path.html

https://dev.mysql.com/doc/refman/5.7/en/sys-format-statement.html

https://dev.mysql.com/doc/refman/5.7/en/sys-format-time.html

https://dev.mysql.com/doc/refman/5.7/en/sys-list-add.html

https://dev.mysql.com/doc/refman/5.7/en/sys-extract-table-from-file-name.html

| 作者简介

罗小波·数据库技术专家

《千金良方——MySQL性能优化金字塔法则》、《数据生态：MySQL复制技术与生产实践》作者之一。熟悉MySQL体系结构，擅长数据库的整体调优，喜好专研开源技术，并热衷于开源技术的推广，在线上线下做过多次公开的数据库专题分享，发表过近100篇数据库相关的研究文章。

全文完。

Enjoy MySQL :)

叶老师的「MySQL核心优化」大课已升级到MySQL 8.0，扫码开启MySQL 8.0修行之旅吧

老叶茶馆_

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
字符串与数字转换函数 | 全方位认识 sys 系统库

本系列在之前的文章中我们为大家介绍了sys 系统库的快捷视图、函数，本期开始我们将为大家介绍 sys 系统库的函数。PS：下文中如果函数定义文本较短的会列出部分函数的定义文本，以便大家更...
复制链接

扫一扫