MySQL database table Representation

How is a MySQL database table expressed? The following describes how the MySQL database table is expressed in the MySQL database.

Each database has three files in the database Directory: A style description file), a data file, and an index file. The basic name of each file is the table name, and the file name extension represents the file type. The following table lists the extensions. The extension of the data and index file indicates that the table uses the old IASM index or the new MyISAM index.

Table MySQL file type
File Type	File name extension	File Content
Style File	. Frm	Describes the structure of a table, including its columns, column types, and indexes ).
Data Files	. ISD (ISAM) Or. MYD (MyISAM)	The index tree that contains all the indexes on the data file.
Index File	. ISM (ISAM) Or. MYI (MyISAM)	The index file depends on whether the table has an index.

When you issue a create table tbl_name statement to define the TABLE structure, the server creates a file named tbl_name.frm, which includes the internal encoding of the structure, at the same time, an empty data and index file is created and initialized to contain information indicating no record or no index. If the create table statement includes the specified index, the index file reflects these indexes ). The owner and mode of the file corresponding to the table are set to allow access only by the MySQL server user.

When you issue an alter table tbl_name statement, the server recodes tbl_name.frm and modifies the data and index file to reflect the structure change specified by the statement. The same is true for create index and drop index, because they are considered equivalent to alter table by the server. Drop table is implemented by deleting three files corresponding to the TABLE.

Although you can delete the three files corresponding to the table in the database directory, you cannot manually create or modify a table. For example, if my_db is the current database, drop table my_tbl is roughly equivalent to the following command.

% Rm-rf DATADIR/my_db/my_tbl .*

The output of show table my_db only lists the base file names of the. frm file in the my_db database directory. Some database systems use a registry to list all tables contained in a database. MySQL is not. Because it is not necessary, the Registry is hidden in the structure of the data directory.

The above is the description of the MySQL database table.

Location of MySQL DATA DIRECTORY

Introduction to modifying table structure statements in MySQL

10 common MySQL command lines

Seven common MySQL command lines

Use the MySQL command line to change the password