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 ;

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

---

上一篇： extract_table_from_file_name()函数 | 下一篇： format_path()函数