mysql path匹配_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 值

定义语句

DROP FUNCTION IF EXISTS format_path;

DELIMITER $$

CREATE DEFINER='root'@'localhost' FUNCTION format_path (

in_path VARCHAR(512)

)

RETURNS VARCHAR(512) CHARSET UTF8

COMMENT '

Description

-----------

Takes a raw path value, and strips out the datadir or tmpdir

replacing with @@datadir and @@tmpdir respectively.

Also normalizes the paths across operating systems, so backslashes

on Windows are converted to forward slashes

Parameters

-----------

path (VARCHAR(512)):

The raw file path value to format.

Returns

-----------

VARCHAR(512) CHARSET UTF8

Example

-----------

mysql> select @@datadir;

+-----------------------------------------------+

| @@datadir |

+-----------------------------------------------+

| /Users/mark/sandboxes/SmallTree/AMaster/data/ |

+-----------------------------------------------+

NO SQL

BEGIN

DECLARE v_path VARCHAR(512);

DECLARE v_undo_dir VARCHAR(1024);

DECLARE path_separator CHAR(1) DEFAULT '/';

IF @@global.version_compile_os LIKE 'win%' THEN

SET path_separator = '\\';

END IF;

-- OSX hides /private/ in variables, but Performance Schema does not

IF in_path LIKE '/private/%' THEN

SET v_path = REPLACE(in_path, '/private', '');

ELSE

SET v_path = in_path;

END IF;

-- @@global.innodb_undo_directory is only set when separate undo logs are used

SET v_undo_dir = IFNULL((SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'innodb_undo_directory'), '');

IF v_path IS NULL THEN

RETURN NULL;

ELSEIF v_path LIKE CONCAT(@@global.datadir, IF(SUBSTRING(@@global.datadir, -1) = path_separator, '%', CONCAT(path_separator, '%'))) ESCAPE '|' THEN

SET v_path = REPLACE(v_path, @@global.datadir, CONCAT('@@datadir', IF(SUBSTRING(@@global.datadir, -1) = path_separator, path_separator, '')));

ELSEIF v_path LIKE CONCAT(@@global.tmpdir, IF(SUBSTRING(@@global.tmpdir, -1) = path_separator, '%', CONCAT(path_separator, '%'))) ESCAPE '|' THEN

SET v_path = REPLACE(v_path, @@global.tmpdir, CONCAT('@@tmpdir', IF(SUBSTRING(@@global.tmpdir, -1) = path_separator, path_separator, '')));

ELSEIF v_path LIKE CONCAT(@@global.slave_load_tmpdir, IF(SUBSTRING(@@global.slave_load_tmpdir, -1) = path_separator, '%', CONCAT(path_separator, '%'))) ESCAPE '|' THEN

SET v_path = REPLACE(v_path, @@global.slave_load_tmpdir, CONCAT('@@slave_load_tmpdir', IF(SUBSTRING(@@global.slave_load_tmpdir, -1) = path_separator, path_separator, '')));

ELSEIF v_path LIKE CONCAT(@@global.innodb_data_home_dir, IF(SUBSTRING(@@global.innodb_data_home_dir, -1) = path_separator, '%', CONCAT(path_separator, '%'))) ESCAPE '|' THEN

SET v_path = REPLACE(v_path, @@global.innodb_data_home_dir, CONCAT('@@innodb_data_home_dir', IF(SUBSTRING(@@global.innodb_data_home_dir, -1) = path_separator, path_separator, '')));

ELSEIF v_path LIKE CONCAT(@@global.innodb_log_group_home_dir, IF(SUBSTRING(@@global.innodb_log_group_home_dir, -1) = path_separator, '%', CONCAT(path_separator, '%'))) ESCAPE '|' THEN

SET v_path = REPLACE(v_path, @@global.innodb_log_group_home_dir, CONCAT('@@innodb_log_group_home_dir', IF(SUBSTRING(@@global.innodb_log_group_home_dir, -1) = path_separator, path_separator, '')));

ELSEIF v_path LIKE CONCAT(v_undo_dir, IF(SUBSTRING(v_undo_dir, -1) = path_separator, '%', CONCAT(path_separator, '%'))) ESCAPE '|' THEN

SET v_path = REPLACE(v_path, v_undo_dir, CONCAT('@@innodb_undo_directory', IF(SUBSTRING(v_undo_dir, -1) = path_separator, path_separator, '')));

ELSEIF v_path LIKE CONCAT(@@global.basedir, IF(SUBSTRING(@@global.basedir, -1) = path_separator, '%', CONCAT(path_separator, '%'))) ESCAPE '|' THEN

SET v_path = REPLACE(v_path, @@global.basedir, CONCAT('@@basedir', IF(SUBSTRING(@@global.basedir, -1) = path_separator, path_separator, '')));

END IF;

RETURN v_path;

END$$

DELIMITER ;

示例

mysql> SELECT format_path('/usr/local/mysql/data/world/City.ibd');

+-----------------------------------------------------+

| format_path('/usr/local/mysql/data/world/City.ibd') |

+-----------------------------------------------------+

| @@datadir/world/City.ibd |

+-----------------------------------------------------+