Home > mysql, Tips > Function to format bytes in human readable format – MySQL

Function to format bytes in human readable format – MySQL

January 7th, 2012 Leave a comment Go to comments

Just searched for a similar one, and could not find any, converted one such function which I had in hand, and is posted here Function to format bytes in human readable format, which was in php.

DELIMITER $$
DROP FUNCTION IF EXISTS `humansize`$$
DETERMINISTIC: CREATE FUNCTION `humansize` (ibytes bigint) RETURNS text DETERMINISTIC 
BEGIN
 if ibytes <= 1000 then
   return concat(ibytes, ' B');
 else
   if ibytes <= (1000* 1000) then
     return concat(ibytes / 1000, ' KB');
   else
     if ibytes < (1000* 1000 * 1000) then
       return concat(ibytes / (1000 * 1000), ' MB');
     else
       if ibytes < (1000* 1000 * 1000 * 1000) then
          return concat(ibytes / (1000 * 1000 * 1000), ' GB');
       else
          return concat(ibytes / (1000 * 1000 * 1000 * 1000), ' TB');
       end if;
     end if;
   end if;
 end if;
END$$
DELIMITER ;

Categories: mysql, Tips Tags:
  1. Rutger
    January 13th, 2012 at 01:20 | #1

    Nice! I had to add DETERMINISTIC:

    CREATE FUNCTION `humansize` (ibytes bigint) RETURNS text DETERMINISTIC

  2. January 27th, 2012 at 08:29 | #2

    Great and Thanks, happy that some one has also used this.

  3. ajcorream
    November 16th, 2014 at 09:51 | #3

    this not works in mysql 4.1 …

    Error Code: 1064
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘DETERMINISTIC: CREATE FUNCTION `humansize` (ibytes bigint) RETURNS text DETERMIN’ at line 1

    Execution Time : 0 sec
    Transfer Time : 0 sec
    Total Time : 0.015 sec

  4. December 22nd, 2014 at 07:41 | #4

    According to http://dev.mysql.com/doc/refman/5.0/en/news-5-0-0.html, “basic support” for stored procedures was present in 5.0.0.

  1. No trackbacks yet.

seventy + = seventy three