W3cubDocs

/MariaDB

Identifier to File Name Mapping

Some identifiers map to a file name on the filesystem. Databases each have their own directory, while, depending on the storage engine, table names and index names may map to a file name.

Not all characters that are allowed in table names can be used in file names. Every filesystem has its own rules of what characters can be used in file names. To let the user create tables using all characters allowed in the SQL Standard and to not depend on whatever particular filesystem a particular database resides, MariaDB encodes "potentially unsafe" characters in the table name to derive the corresponding file name.

This is implemented using a special character set. MariaDB converts a table name to the "filename" character set to get the file name for this table. And it converts the file name from the "filename" character set to, for example, utf8 to get the table name for this file name.

The conversion rules are as follows: if the identifier is made up only of basic Latin numbers, letters and/or the underscore character, the encoding matches the name (see however Identifier Case Sensitivity). Otherwise they are encoded according to the following table:

Code Range Pattern Number Used Unused Blocks
00C0..017F [@][0..4][g..z] 5*20= 100 97 3 Latin-1 Supplement + Latin Extended-A
0370..03FF [@][5..9][g..z] 5*20= 100 88 12 Greek and Coptic
0400..052F [@][g..z][0..6] 20*7= 140 137 3 Cyrillic + Cyrillic Supplement
0530..058F [@][g..z][7..8] 20*2= 40 38 2 Armenian
2160..217F [@][g..z][9] 20*1= 20 16 4 Number Forms
0180..02AF [@][g..z][a..k] 20*11=220 203 17 Latin Extended-B + IPA Extensions
1E00..1EFF [@][g..z][l..r] 20*7= 140 136 4 Latin Extended Additional
1F00..1FFF [@][g..z][s..z] 20*8= 160 144 16 Greek Extended
.... .... [@][a..f][g..z] 6*20= 120 0 120 RESERVED
24B6..24E9 [@][@][a..z] 26 26 0 Enclosed Alphanumerics
FF21..FF5A [@][a..z][@] 26 26 0 Halfwidth and Fullwidth forms

Code Range values are UCS-2.

All of this encoding happens transparently at the filesystem level with one exception. Until MySQL 5.1.6, an old encoding was used. Identifiers created in a version before MySQL 5.1.6, and which haven't been updated to the new encoding, the server prefixes mysql50 to their name.

Examples

Find the file name for a table with a non-Latin1 name:

select convert("this_is_таблица" USING filename);
+------------------------------------------+
| convert("this_is_таблица" USING filename)|
+------------------------------------------+
| [email protected]@[email protected]@[email protected]@[email protected]            |
+------------------------------------------+

Find the table name for a file name:

select convert(_filename "[email protected]@[email protected]@[email protected]@[email protected]" USING utf8);
+---------------------------------------------------------------+
| convert(_filename "[email protected]@[email protected]@[email protected]@[email protected]" USING utf8) |
+---------------------------------------------------------------+
| this_is_таблица                                               |
+---------------------------------------------------------------+

An old table created before MySQL 5.1.6, with the old encoding:

SHOW TABLES;
+--------------------+
| Tables_in_test     |
+--------------------+
| #mysql50#[email protected]   |
+--------------------+

The prefix needs to be supplied to reference this table:

SHOW COLUMNS FROM `[email protected]`;
ERROR 1146 (42S02): Table 'test.ta[email protected]' doesn't exist

SHOW COLUMNS FROM `#mysql50#[email protected]`;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/identifier-to-file-name-mapping/