mysql 表结构定义文件frm

本文主要是介绍mysql 表结构定义文件frm，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

mysql .frm文件格式

mysql使用不同的文件格式存储信息，本文介绍一下不同的文件格式，怎么读写和理解

无论你选择什么存储引擎，每一个mysql表都使用.frm文件格式描述表结构定义，文件名和表名一致，.frm格式在所有平台上都是一样的，下面使用linux平台作为案例

第一，创建一个表

mysql> CREATE TABLE table1 (column1 CHAR(5)) ENGINE=MYISAM COMMENT '*';
Query OK, 0 rows affected (0.00 sec)

table1.frm文件可以在数据库的数据存储目录下被找到，datadir系统环境变量定义了数据存储目录，一般在/var/lib/mysql目录，

mysql> SHOW VARIABLES LIKE 'datadir';+---------------+-----------------------+| Variable_name | Value                 |+---------------+-----------------------+| datadir       | /usr/local/mysql/var/ |+---------------+-----------------------+1 row in set (0.00 sec)

database()函数可以获取数据库名称

mysql> SELECT DATABASE();+------------+| DATABASE() |+------------+| ff         |+------------+1 row in set (0.00 sec)

可以通过datadir/database_name的方式定位.frm文件，举个例子

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

.MYD（存储数据）和.MYI（存储索引）文件，后面的文章将会介绍，这里不做介绍，为了理解.frm文件，我们先看一下16进制下的数据

shell> hexdump -v -C table1.frm00000000  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

.frm文件头部分

Offset	Length	Value	Explanation
0000	1	fe	Always
0001	1	01	Always
0002	1	09	`FRM_VER` (which is in `include/mysql_version.h`) +3 +`test(create_info->varchar)`
0003	1	09	See `enum legacy_db_type` in `sql/handler.h`. For example, 09 is `DB_TYPE_MYISAM`, but 14 if MyISAM with partitioning.
0004	1	03	??
0005	1	00	Always
0006	2	0010	`IO_SIZE`
0008	2	0100	??
000a	4	00300000	Length, based on `key_length + rec_length + create_info->extra_size`
000e	2	1000	“tmp_key_length”, based on `key_length`
0010	2	0600	`rec_length`
0012	4	00000000	`create_info->max_rows`
0016	4	00000000	`create_info->min_rows`
001b	1	02	Always (means “use long pack-fields”)
001c	2	0800	`key_info_length`
001e	2	0800	`create_info->table_options` also known as `db_create_options?` one possible option is `HA_LONG_BLOB_PTR`
0020	1	00	Always
0021	1	05	Always (means “version 5 frm file”)
0022	4	00000000	`create_info->avg_row_length`
0026	1	08	`create_info->default_table_charset`
0027	1	00	Always
0028	1	00	`create_info->row_type`
0029	6	00..00	Always (formerly used for RAID support)
002f	4	10000000	`key_length`
0033	4	c0c30000	`MYSQL_VERSION_ID` from `include/mysql_version.h`
0037	4	10000000	`create_info->extra_size`
003b	2	0000	Reserved for `extra_rec_buf_length`
003d	1	00	Reserved for `default_part_db_type`, but 09 if MyISAM with partitioning
003e	2	0000	`create_info->key_block_size`

frm文件key信息部分

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

frm文件评论部分

Offset	Length	Value	Explanation
202e	1	01	Length of comment
202f	40	“*”	The string in the `COMMENT` table option

frm文件列信息部分

Offset	Length	Value	Explanation
2100	2	01	Always
2102	2	0100	`share->fields` (number of columns)
2104	2	3b00	`pos` (“length of all screens”). Goes up if `column-name` length increases. Doesn’t go up if add comment.
2106	2	0500	Based on number of bytes in row.
210c	2	0500	`n_length`. Goes up if row length increases.
210e	2	0000	`interval_count`. Number of different `ENUM`/`SET` columns.
2110	2	0000	`interval_parts`. Number of different strings in `ENUM`/`SET` columns.
2112	2	0000	`int_length`
211a	2	0100	`share->null_fields`. Number of nullable columns.
211c	2	0000	`com_length`
2152	1	08	Length of column-name including `‘\0’` termination
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=`char`, 02=`smallint`, 03=`int`, etc.) see `enum field_types` in `include/mysql_com.h`
2169	1	08	Character set or geometry type
(later)	??	??	Column names again, defaults, `ENUM`/`SET` strings, column comments … at end of row. not shown.

分区表的.frm文件包含了分区信息，为了清楚，我们现在创建一个分区表，并且看看他的16进制数据有什么不同

mysql> CREATE TABLE table2 (column1 INT) ENGINE=MYISAM COMMENT '*'PARTITION BY HASH(column1) PARTITIONS 2;
Query OK, 0 rows affected (0.00 sec)

使用16进制格式输出：

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

注意：00001010位置出现文本”CREATE TABLE … PARTITION“,就存储了分区信息

最后 CREATE VIEW 语句也会创建一个.frm文件，但是view的frm文件和普通的表不一样，而是一个纯文本文件，举个例子

mysql> CREATE VIEW v AS SELECT 5;
Query OK, 0 rows affected (0.00 sec)

16进制格式输出：

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.|