FORMAT_BYTES() in MySQL

In MySQL, FORMAT_BYTES() is a built in function that converts a numeric byte count to a more human-readable format. It returns a string consisting of the value and a units indicator.

We provide the numeric byte count when we call the function.

The FORMAT_BYTES() function was introduced in MySQL 8.0.16, and it is intended to be a replacement for the sys.format_bytes() function, which is deprecated (as of MySQL 8.0.16).

Syntax

The syntax goes like this:

FORMAT_BYTES(count)

Where count is the numeric byte count that we want to be formatted.

The return string is rounded to 2 decimal places and a minimum of 3 significant digits. Numbers less than 1024 bytes are represented as whole numbers and are not rounded.

Example

Here’s a simple example:

SELECT FORMAT_BYTES( 123456789 );

Result:

+---------------------------+
| FORMAT_BYTES( 123456789 ) |
+---------------------------+
| 117.74 MiB                |
+---------------------------+

The value we provided was converted to mebibytes (MiB) and formatted accordingly.

Values Lower than 1024

And here’s another example that uses a number that’s less than 1024:

SELECT FORMAT_BYTES( 1023 );

Result:

+----------------------+
| FORMAT_BYTES( 1023 ) |
+----------------------+
| 1023 bytes           |
+----------------------+

In this case the numeric value remains unchanged, and it’s appended with bytes.

Just to be sure, here’s what happens when we increment it to 1024:

SELECT FORMAT_BYTES( 1024 );

Result:

+----------------------+
| FORMAT_BYTES( 1024 ) |
+----------------------+
| 1.00 KiB             |
+----------------------+

This time it was converted to kibibytes (KiB).

Unit Indicators

The unit indicator applied to the result depends on the value provided.

The following table shows which unit indicator is applied to each value range:

Argument Value	Result Units	Result Units Indicator
Up to 1023	bytes	bytes
Up to 1024² − 1	kibibytes	KiB
Up to 1024³ − 1	mebibytes	MiB
Up to 1024⁴ − 1	gibibytes	GiB
Up to 1024⁵ − 1	tebibytes	TiB
Up to 1024⁶ − 1	pebibytes	PiB
1024⁶ and up	exbibytes	EiB

Source: MySQL documentation

The following example demonstrates each of these unit indicators:

SELECT 
    FORMAT_BYTES( 224 ) AS r1,
    FORMAT_BYTES( 224166 ) AS r2,
    FORMAT_BYTES( 224166440 ) AS r3,
    FORMAT_BYTES( 224166440717 ) AS r4,
    FORMAT_BYTES( 224166440717497 ) AS r5,
    FORMAT_BYTES( 224166440717497513 ) AS r6,
    FORMAT_BYTES( 22416644071749751325 ) AS r7;

Result:

+------------+------------+------------+------------+------------+------------+-----------+
| r1         | r2         | r3         | r4         | r5         | r6         | r7        |
+------------+------------+------------+------------+------------+------------+-----------+
|  224 bytes | 218.91 KiB | 213.78 MiB | 208.77 GiB | 203.88 TiB | 199.10 PiB | 19.44 EiB |
+------------+------------+------------+------------+------------+------------+-----------+

Wrong Argument Type

Passing a non-numeric argument results in 0 bytes being returned.

SELECT FORMAT_BYTES( 'Ten gazillion' );

Result:

+---------------------------------+
| FORMAT_BYTES( 'Ten gazillion' ) |
+---------------------------------+
|    0 bytes                      |
+---------------------------------+

Null Arguments

Passing null results in NULL being returned.

SELECT FORMAT_BYTES(null);

Result:

+--------------------+
| FORMAT_BYTES(null) |
+--------------------+
| NULL               |
+--------------------+

Missing Argument

Calling FORMAT_BYTES() without passing an argument results in an error:

SELECT FORMAT_BYTES();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'FORMAT_BYTES'