MySQL AB :: MySQL 5.1 Reference Manual :: 9.2.3 Mapping of Identifiers to Filenames

9.2.3. Mapping of Identifiers to Filenames

There is a correspondence between database and table identifiers and names in the filesystem. MySQL represents each database as a directory in the data directory, and each table by one or more files in the appropriate database directory.

Before MySQL 5.1.6, there are some limitations on the characters that can be used in identifiers for database objects that correspond to filesystem objects. For example, pathname separator characters are disallowed, and “.” is disallowed because it begins the extension for table files.

As of MySQL 5.1.6, any character is legal in database or table identifiers except ASCII NUL (0x00). MySQL encodes any characters that are problematic in the corresponding filesystem objects when it creates database directories or table files:

Basic Latin letters (a..zA..Z) and digits (0..9) are encoded as is. Consequently, their case sensitivity directly depends on filesystem features.
All other national letters from alphabets that have uppercase/lowercase mapping are encoded as follows:
```
Code range Pattern            Number   Used Unused  Blocks
-----------------------------------------------------------------------------
00C0..017F [@][0..4][g..z] 5*20= 100   97     3  Latin1 Supplement + Ext A
0370..03FF [@][5..9][g..z] 5*20= 100   88    12  Greek + Coptic
0400..052F [@][g..z][0..6] 20*7= 140  140   137  Cyrillic
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] 28*11=220  203    17  Latin Ext B + IPA
1E00..0EFF [@][g..z][l..r] 20*7= 140  136     4  Latin Additional Extended
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  Full Width forms
```
One of the bytes in the sequence encodes lettercase. For example: LATIN CAPITAL LETTER A WITH GRAVE is encoded as @0G, whereas LATIN SMALL LETTER A WITH GRAVE is encoded as @0g. Here the third byte (G or g) indicates lettercase. (On a case-insensitive filesystem, both letters will be treated as the same.)

For some blocks, such as Cyrillic, the second byte determines lettercase. For other blocks, such as Latin1 Supplement, the third byte determines lettercase. If two bytes in the sequence are letters (as in Greek Extended), the leftmost letter character stands for lettercase. All other letter bytes must be in lowercase.
All non-letter characters, as well as letters from alphabets that do not have uppercase/lowercase mapping (such Hebrew) are encoded using hexadecimal representation using lowercase letters for hex digits a..f:
```
0x003F -> @003f
0xFFFF -> @ffff
```
The hexadecimal values corrrespond to character values in the ucs2 double-byte character set.

On Windows, some names such as nul, prn, and aux cannot be used as filenames because they are reserved as device names. As of MySQL 5.1.10, these are allowable names in MySQL. They are encoded by appending @@@ to the name when the server creates the corresponding file or directory. This occurs on all platforms for portability of the corresponding database object between platforms.

If you have databases or tables from a version of MySQL older than 5.1.6 that contain special characters and that have not been updated to use the new encoding, you will see their names displayed with a prefix of #mysql50# in the output from INFORMATION_SCHEMA tables or SHOW statements. For example, if you have a table named a@b and its name encoding has not been updated, SHOW TABLES displays it like this:

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| #mysql50#a@b   |
+----------------+

To refer to such a name for which the encoding has not been updated, you must supply the #mysql50# prefix:

mysql> SHOW COLUMNS FROM `a@b`;
ERROR 1146 (42S02): Table 'test.a@b' doesn't exist

mysql> SHOW COLUMNS FROM `#mysql50#a@b`;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

To update old names to eliminate the need to use the special prefix to refer to them, re-encode them with mysqlcheck. The following command updates all names to the new encoding:

shell> mysqlcheck --check-upgrade --fix-db-names --fix-table-names --all-databases

To check only specific databases or tables, omit --all-databases and provide the appropriate database or table arguments. For information about mysqlcheck invocation syntax, see Section 8.12, “mysqlcheck — A Table Maintenance and Repair Program”.

9.2.3. Mapping of Identifiers to Filenames

User Comments