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                            |
+-----------------------------------------------------+

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-format-path.html

---

上一篇： format_bytes()函数 | 下一篇： format_statement()函数