In MySQL, the sys.format_path() function is a stored function in the sys schema. It accepts a path name, and returns the modified path name after replacing subpaths that match the values of certain system variables.

So we can use the function to dynamically replace part or all of our path with a system variable that matches that path segment.

These system variables are as follows:

datadir
tmpdir
slave_load_tmpdir
replica_load_tmpdir
innodb_data_home_dir
innodb_log_group_home_dir
innodb_undo_directory
basedir

So if the given path, or a segment of the given path, matches a path that’s stored in any of the above system variables, then sys.format_path() replaces it with the system variable. If it’s just a segment of the path that matches, then only that segment is replaced, and the rest of the path remains intact.

Syntax

The syntax goes like this:

sys.format_path(path)

Where path is a VARCHAR(512) that represents the path to format.

Example

Here’s an example to demonstrate:

SELECT sys.format_path(
    '/opt/homebrew/var/mysql/krankykranes/Products.ibd'
    );

Result:

@@datadir/krankykranes/Products.ibd

In this example, /opt/homebrew/var/mysql is my data directory. The reason the sys.format_path() function returned @@datadir is because that’s the name of a system variable that contains the data directory, which in my case is /opt/homebrew/var/mysql/.

In MySQL, the data directory stores information managed by the MySQL server. Each subdirectory of the data directory is a database directory and corresponds to a database managed by the server. In my case, krankykranes is one of many directories in the data directory. It was created when I created a database called krankykranes. This database has a table called Products, hence the Products.ibd part of the path.

We can find out where our data directory is located by returning the @@datadir in a SELECT statement:

SELECT @@datadir;

Result:

/opt/homebrew/var/mysql/

Here’s another example:

SELECT sys.format_path(
    '/opt/homebrew/Cellar/mysql/8.0.29/'
    );

Result:

@@basedir/

This time I passed a path that matches the base directory, and so MySQL replaced it with @@basedir/.

Let’s check the base directory:

SELECT @@basedir;

Result:

/opt/homebrew/Cellar/mysql/8.0.29/

Passing a Non-Existent Path

Here’s what happens when I pass a path that doesn’t exist:

SELECT sys.format_path(
    '/opt/homebrew/var/mysql/oops/Products.ibd'
    );

Result:

@@datadir/oops/Products.ibd

So it didn’t actually check whether or not the path exists (I don’t actually have a directory called oops).

Non-Matching Path

Here’s what happens when no part of the given path matches any of the system variables listed above:

SELECT sys.format_path(
    '/Users/Shared/bak/Solutions.bak'
    );

Result:

/Users/Shared/bak/Solutions.bak

So format_path() simply returned the full path unchanged.

Passing an Empty String

Passing an empty string simply returns an empty string without error:

SELECT sys.format_path( '' );

Result:

+-----------------------+
| sys.format_path( '' ) |
+-----------------------+
|                       |
+-----------------------+
1 row in set (0.00 sec)

Passing NULL

Passing NULL returns NULL:

SELECT sys.format_path( NULL );

Result:

+-------------------------+
| sys.format_path( NULL ) |
+-------------------------+
| NULL                    |
+-------------------------+
1 row in set (0.00 sec)

Omitting the Path

Omitting the path altogether (i.e. calling the function without an argument) results in an error:

SELECT sys.format_path( );

Result:

ERROR 1318 (42000): Incorrect number of arguments for FUNCTION sys.format_path; expected 1, got 0