Regardless of the storage engine you choose, every MySQL table you
create is represented on disk by a .frm
file
that describes the table's format (that is, the table definition).
The file bears the same name as the table, with an
.frm
extension. The .frm
format is the same on all platforms, but in the description of the
.frm
format that follows, the examples come
from tables created under the Linux operating system.
First, let's create an example table, using the mysql client:
mysql> CREATE TABLE table1 (column1 CHAR(5)) ENGINE=MYISAM COMMENT '*';
Query OK, 0 rows affected (0.00 sec)
The .frm
file associated with
table1
can be found in the directory that
represents the database (or schema) to which the table belongs.
The datadir
system variable contains the name
of this directory:
mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+-----------------------+
| Variable_name | Value |
+---------------+-----------------------+
| datadir | /usr/local/mysql/var/ |
+---------------+-----------------------+
1 row in set (0.00 sec)
The DATABASE()
function contains the name of
the relevant database:
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| ff |
+------------+
1 row in set (0.00 sec)
Since MySQL stores .frm
files in
datadir/
,
it's a simple matter to locate the corresponding
database_name
.frm
file for table1
. For
example, within a Linux shell:
shell>su root
shell>cd /usr/local/mysql/var/ff
shell>ls table1.*
table1.frm table1.MYD table1.MYI shell>ls -l table1.*
-rw-rw---- 1 root root 8566 2006-09-22 11:22 table1.frm -rw-rw---- 1 root root 0 2006-09-22 11:22 table1.MYD -rw-rw---- 1 root root 1024 2006-09-22 11:22 table1.MYI
The .MYD
(data) and .MYI
(index) files are not our concern here; they are described at
Section 21.1, “MyISAM
Record Structure”. To understand the
.frm
format, let's look at
table1.frm
using a hexadecimal-dump utility:
shell> hexdump -v -C table1.frm
00000000 fe 01 09 09 03 00 00 10 01 00 00 30 00 00 10 00 |...........0....|
00000010 06 00 00 00 00 00 00 00 00 00 00 02 08 00 08 00 |................|
00000020 00 05 00 00 00 00 08 00 00 00 00 00 00 00 00 10 |................|
00000030 00 00 00 c0 c3 00 00 10 00 00 00 00 00 00 00 00 |................|
00000040 2f 2f 00 00 20 00 00 00 00 00 00 00 00 00 00 00 |//.. ...........|
... | (many 0s) |
00001000 00 00 00 00 02 00 ff 00 00 00 00 00 00 00 00 00 |................|
00001010 ff 20 20 20 20 20 00 00 06 00 4d 79 49 53 41 4d |. ....MyISAM|
... | (many 0s) |
00002000 6c 01 00 10 00 00 00 00 00 00 00 00 00 00 00 00 |l...............|
00002010 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
00002020 00 00 00 00 00 00 00 00 00 00 00 00 00 00 01 2a |...............*|
...
00002100 01 00 01 00 3b 00 05 00 00 00 06 00 0a 00 00 00 |....;...........|
00002110 00 00 00 00 00 00 50 00 16 00 01 00 00 00 00 00 |......P.........|
00002120 3b 00 02 01 02 14 29 20 20 20 20 20 20 20 20 20 |;.....) |
00002130 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 | |
00002140 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 00 | .|
00002150 04 00 08 63 6f 6c 75 6d 6e 31 00 04 08 05 05 00 |...column1......|
00002160 02 00 00 00 80 00 00 00 fe 08 00 00 ff 63 6f 6c |.............col|
00002170 75 6d 6e 31 ff 00 |umn1..|
00002176
The details just shown might change, especially since there is a
transition underway from an old (“binary”) format to
a new (“text based”) .frm
format. You can confirm that the details are correct by comparing
this description with the statements in
sql/table.cc
,
create_frm()
. The following tables explain the
meaning of each byte in the hexadecimal dump shown in the
preceding example:
Offset: The byte position in the file.
Length: The number of bytes.
Value: What's in the given byte position for the given length (remember that storage is “low byte first”, so 0010 means 0x1000, not 0x0010!).
Explanation: A brief explanation of the contents.
Table 12.1. .frm File Header Section
Offset | Length | Value | Explanation |
---|---|---|---|
0000 | 1 | fe | Always |
0001 | 1 | 01 | Always |
0002 | 1 | 09 |
|
0003 | 1 | 09 |
See |
0004 | 1 | 03 | ?? |
0005 | 1 | 00 | Always |
0006 | 2 | 0010 |
|
0008 | 2 | 0100 | ?? |
000a | 4 | 00300000 |
Length, based on |
000e | 2 | 1000 |
"tmp_key_length", based on |
0010 | 2 | 0600 |
|
0012 | 4 | 00000000 |
|
0016 | 4 | 00000000 |
|
001b | 1 | 02 | Always (means “use long pack-fields”) |
001c | 2 | 0800 |
|
001e | 2 | 0800 |
|
0020 | 1 | 00 | Always |
0021 | 1 | 05 | Always (means “version 5 frm file”) |
0022 | 4 | 00000000 |
|
0026 | 1 | 08 |
|
0027 | 1 | 00 | Always |
0028 | 1 | 00 |
|
0029 | 6 | 00..00 | Always (formerly used for RAID support) |
002f | 4 | 10000000 |
|
0033 | 4 | c0c30000 |
|
0037 | 4 | 10000000 |
|
003b | 2 | 0000 |
Reserved for |
003d | 1 | 00 |
Reserved for |
003e | 2 | 0000 |
|
Table 12.2. .frm File Key Information Section
Offset | Length | Value | Explanation |
---|---|---|---|
1000 | 1 | 00 | Always 00 when there are no keys (that is, indexes) |
... | ?? | ?? | ?? |
101a | 6 | "MyISAM" | Name of engine. If partitioning, the partition clauses are here |
Table 12.3. .frm File Comment Section
Offset | Length | Value | Explanation |
---|---|---|---|
202e | 1 | 01 | Length of comment |
202f | 40 | "*" |
The string in the |
Table 12.4. .frm File Column Information Section
Offset | Length | Value | Explanation |
---|---|---|---|
2100 | 2 | 01 | Always |
2102 | 2 | 0100 |
|
2104 | 2 | 3b00 |
|
2106 | 2 | 0500 | Based on number of bytes in row. |
210c | 2 | 0500 |
|
210e | 2 | 0000 |
|
2110 | 2 | 0000 |
|
2112 | 2 | 0000 |
|
211a | 2 | 0100 |
|
211c | 2 | 0000 |
|
2152 | 1 | 08 |
Length of column-name including |
2153 | 3 | "column1\0" | column-name |
215b | 1 | 04 | ?? |
215c | 1 | 03 | ?? |
215d | 1 | 05 | Number of bytes in column |
215e | 1 | 05 | Number of bytes in column |
215f | 4 | 00020000 | ?? |
2163 | 1 | 00 | Flags for zerofill, unsigned, etc. |
2164 | 1 | 80 | Additional flags, and scale if decimal/numeric |
2168 | 1 | fe |
Data type (fe= |
2169 | 1 | 08 | Character set or geometry type |
(later) | ?? | ?? |
Column names again, defaults,
|
The .frm
file for a partitioned table
contains partition information, in clear text, in addition to the
usual table definition details. Let's create a partitioned table
and do a hexadecimal dump of its .frm
file:
mysql> CREATE TABLE table2 (column1 INT) ENGINE=MYISAM COMMENT '*'
PARTITION BY HASH(column1) PARTITIONS 2;
Query OK, 0 rows affected (0.00 sec)
The hexadecimal dump from table2
is shown here:
00000000 fe 01 09 14 03 00 00 10 01 00 00 30 00 00 10 00 |...........0....| 00000010 05 00 00 00 00 00 00 00 00 00 00 02 08 00 08 00 |................| 00000020 00 05 00 00 00 00 08 00 00 00 00 00 00 00 00 10 |................| 00000030 00 00 00 c0 c3 00 00 3d 00 00 00 00 00 09 00 00 |.......=........| 00000040 2f 2f 00 00 20 00 00 00 00 00 00 00 00 00 00 00 |//.. ...........| 00000050 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| ... 00001000 00 00 00 00 02 00 ff 00 00 00 00 00 00 00 00 00 |................| 00001010 ff 00 00 00 00 00 00 09 00 70 61 72 74 69 74 69 |.........partiti| 00001020 6f 6e 2a 00 00 00 20 50 41 52 54 49 54 49 4f 4e |on*... PARTITION| 00001030 20 42 59 20 48 41 53 48 20 28 63 6f 6c 75 6d 6e | BY HASH (column| 00001040 31 29 20 50 41 52 54 49 54 49 4f 4e 53 20 32 20 |1) PARTITIONS 2 | 00001050 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| ... 00002000 76 01 00 10 00 00 00 00 00 00 00 00 00 00 00 00 |v...............| 00002010 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00002020 00 00 00 00 00 00 00 00 00 00 00 00 00 00 01 2a |...............*| 00002030 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| ... 00002100 01 00 01 00 3b 00 0b 00 00 00 05 00 0a 00 00 00 |....;...........| 00002110 00 00 00 00 00 00 50 00 16 00 01 00 00 00 00 00 |......P.........| 00002120 3b 00 02 01 02 14 29 20 20 20 20 20 20 20 20 20 |;.....) | 00002130 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 | | 00002140 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 00 | .| 00002150 04 00 08 63 6f 6c 75 6d 6e 31 00 04 08 0b 0b 00 |...column1......| 00002160 02 00 00 1b 80 00 00 00 03 08 00 00 ff 63 6f 6c |.............col| 00002170 75 6d 6e 31 ff 00 |umn1..| 00002176
In the example output, notice that position 00001010 and following
contains the clear text of the CREATE TABLE ...
PARTITION
clause and not just the
MYISAM
engine information, as in
table1
, which shows the
.frm
of a nonpartitioned table.
Finally, CREATE VIEW
also causes creation of a
.frm
file, but a view
.frm
bears no resemblance to a base table
.frm
; it's purely textual. Here's an example
of a .frm
for a view made with:
mysql> CREATE VIEW v AS SELECT 5;
Query OK, 0 rows affected (0.00 sec)
Just looking at the text will tell you what it's about:
00000000 54 59 50 45 3d 56 49 45 57 0a 71 75 65 72 79 3d |TYPE=VIEW.query=| 00000010 73 65 6c 65 63 74 20 35 20 41 53 20 60 35 60 0a |select 5 AS `5`.| 00000020 6d 64 35 3d 38 64 39 65 32 62 62 66 64 35 33 35 |md5=8d9e2bbfd535| 00000030 66 35 37 39 64 34 61 39 34 39 62 39 65 62 37 64 |f579d4a949b9eb7d| 00000040 32 33 34 39 0a 75 70 64 61 74 61 62 6c 65 3d 30 |2349.updatable=0| 00000050 0a 61 6c 67 6f 72 69 74 68 6d 3d 30 0a 64 65 66 |.algorithm=0.def| 00000060 69 6e 65 72 5f 75 73 65 72 3d 72 6f 6f 74 0a 64 |iner_user=root.d| 00000070 65 66 69 6e 65 72 5f 68 6f 73 74 3d 6c 6f 63 61 |efiner_host=loca| 00000080 6c 68 6f 73 74 0a 73 75 69 64 3d 32 0a 77 69 74 |lhost.suid=2.wit| 00000090 68 5f 63 68 65 63 6b 5f 6f 70 74 69 6f 6e 3d 30 |h_check_option=0| 000000a0 0a 72 65 76 69 73 69 6f 6e 3d 31 0a 74 69 6d 65 |.revision=1.time| 000000b0 73 74 61 6d 70 3d 32 30 30 36 2d 30 39 2d 32 32 |stamp=2006-09-22| 000000c0 20 31 32 3a 31 34 3a 34 38 0a 63 72 65 61 74 65 | 12:14:48.create| 000000d0 2d 76 65 72 73 69 6f 6e 3d 31 0a 73 6f 75 72 63 |-version=1.sourc| 000000e0 65 3d 73 65 6c 65 63 74 20 35 0a |e=select 5.|
User Comments
Add your own comment.