mysql function to pretty print sizes (pg_size_pretty equivialent)?

postgresql have the pg_size_pretty() conven开发者_运维问答ience function:

> select pg_size_pretty(100000);
 pg_size_pretty
----------------
 98 kB

Does MySQL have something similar ?

If not, before I make my own have anyone already made this, that they can share ?

There is no shipped function like that in MySQL. So, I just created one : function pretty_size https://github.com/lqez/pastebin/blob/master/mysql/pretty_size.sql

CREATE FUNCTION pretty_size( size DOUBLE )
    RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
    DECLARE c INT DEFAULT 0;
    DECLARE unit INT DEFAULT 1000;
    DECLARE unitChar CHAR(6) DEFAULT 'KMGTPE';
    DECLARE binaryPrefix BOOLEAN DEFAULT 1;

    /* Set binaryPrefix = 1 to use binary unit & prefix */
    /* See IEC 60027-2 A.2 and ISO/IEC 80000 */
    IF binaryPrefix = 1 THEN
        SET unit = 1024;
    END IF;

    WHILE size >= unit AND c < 6 DO
        SET size = size / unit;
        SET c = c + 1;
    END WHILE;

    /* Under 1K, just add 'Byte(s)' */
    IF c = 0 THEN
        RETURN CONCAT( size, ' B' );
    END IF;

    /* Modify as your taste */
    RETURN CONCAT(
        FORMAT( size, 2 ),
        ' ',
        SUBSTR( unitChar, c, 1 ),
        IF( binaryPrefix, 'iB', 'B' )
        );
END $$

DELIMITER ;

pg_size_pretty will give you Kb or MB or GB ,etc according to the internal code ,and you wont operate or sum this result ...

better use :

pg_relation_size : returns the on-disk size in bytes ,so you can convert it to the format (kb,mb,gb) that you want.