Data table representation in the system _ MySQL

Data tables in the system are represented by the following MySQL data table types: ISAM, MyISAM, MERGE, BDB, InnoDB, and HEAP. Each data table has a different representation in the file system. one thing in common is that each data table has at least one. frm file that stores the data table structure definition.

The following describes each data table file:

The ISAM data table is the original data table with three files:

. Frm: structure definition for storing data tables;

. ISD, data file, which is empty in each data row in the data table;

. ISM, an index file that stores all the index information of a data table.

The MyISAM data table is the successor of the ISAM data table. There are also three files:

. Frm, structure definition file;

. MYD, data file;

. MYI, index file.

A merge data table is a logical structure that represents a set of MyISAM data tables with identical structures. It has two files in the file system:

. Frm, structure definition file;

. MRG: a list of MyISAM data tables in the MERGE table. each MyISAM data table name occupies one row. That is to say, you can change the structure of the MERGE data table by changing the table content. Please refresh the cache (flush tables) before modification, but it is not recommended to modify the MERGE data table like this.

BDB data tables are represented by two files:

. Frm, structure definition file;

. Db, data in data tables and index files

Because InnoDB uses the table space concept to manage data tables, it only has one. frm file corresponding to the data table. other files in the same directory are represented as table spaces, storing data and indexes of the data table.

HEAP data table is a table in memory, so its data and indexes are stored in the memory. the file system has only one. frm file to define the structure.

After learning about the format of MySQL data tables in the file system, we can see that creating, modifying, or deleting data tables is actually operating these files. For example, some data tables (except InnoDB and HEAP data tables) can be deleted directly from the file system.

% Cd datadir
% Rm-f mydb/mydb .*
The preceding command deletes the mydb data table in the mydb database.