In MySQL, the sys.extract_schema_from_file_name() function is a stored function in the sys schema.

It accepts a path name, and returns the path component that represents the schema name.

This enables us to get the schema from a given file path.

Syntax

The syntax goes like this:

sys.extract_schema_from_file_name(path)

Where path is a VARCHAR(512) that represents the full path to a data file from which to extract the schema name.

Example

Here’s an example to demonstrate:

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

Result:

krankykranes

In this example, krankykranes is the name of the schema. This is a database that I created in MySQL. This database contains a table called Products.

The first part of the path that I provided is the data directory. 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, each one corresponding to a different database.

We can use the @@datadir system variable to return the data directory. So we could replace the above example with the following:

SELECT sys.extract_schema_from_file_name(
    '@@datadir/krankykranes/Products.ibd'
    );

Result:

krankykranes

Passing a Non-Existent Path

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

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

Result:

oops

So it didn’t actually check whether or not the path exists. It simply returned the oops node without error.

Passing an Empty String

Even passing an empty string simply returns an empty string without error:

SELECT sys.extract_schema_from_file_name( '' );

Result:

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

Passing NULL

Passing NULL returns NULL:

SELECT sys.extract_schema_from_file_name( NULL );

Result:

+-------------------------------------------+
| sys.extract_schema_from_file_name( 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.extract_schema_from_file_name( );

Result:

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