In MySQL, the sys.extract_table_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 table name.

Syntax

The syntax goes like this:

sys.extract_table_from_file_name(path)

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

Example

Here’s an example to demonstrate:

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

Result:

Products

In this example, Products is a table in the krankykranes database.

The first part of the path that I provided (/opt/homebrew/var/mysql/) 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. This directory contains a file called Products.ibd, for the Products table.

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_table_from_file_name(
    '@@datadir/krankykranes/Products.ibd'
    );

Result:

Products

Passing a Non-Existent Path

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

SELECT sys.extract_table_from_file_name(
    '/opt/homebrew/var/mysql/krankykranes/oops.ibd'
    );

Result:

oops

So it didn’t actually check whether or not the path exists (in my case there’s no oops.ibd file in the directory).

Passing an Empty String

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

SELECT sys.extract_table_from_file_name( '' );

Result:

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

Passing NULL

Passing NULL returns NULL:

SELECT sys.extract_table_from_file_name( NULL );

Result:

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

Result:

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