Shulou(Shulou.com)05/31 Report--

What is the function of the data file name of the MySQL part of the engine? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Data file name testing of all kinds of MySQL engines

InnoDB:

Create the table test01:

Mysql > create table test01 (id int, name varchar (8)) engine=innodb

Query OK, 0 rows affected (0.01 sec)

Mysql > insert into test01 values (1)

Query OK, 1 row affected (0.00 sec)

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

View the data file directory:

[root@mysqldb test] # ll-h

Total 112K

-rw-r- 1 mysql mysql 61 Mar 27 10:44 db.opt

-rw-r- 1 mysql mysql 8.4K Mar 31 10:43 test01.frm

-rw-r- 1 mysql mysql 96K Mar 31 10:43 test01.ibd

Description:

Test01.frm: stores metadata information related to tables

Test01.ibd: stores the data of the table; (this is a private tablespace file, and if it is a public tablespace, the file name is ibdata)

MyISAM:

Create the table test02:

Mysql > create table test02 (id int, name varchar (8)) engine=myisam

Query OK, 0 rows affected (0.00 sec)

Mysql > insert into test02 values (1)

Query OK, 1 row affected (0.00 sec)

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

View the data file directory:

[root@mysqldb test] # ll-h

Total 132K

-rw-r- 1 mysql mysql 61 Mar 27 10:44 db.opt

-rw-r- 1 mysql mysql 8.4K Mar 31 10:43 test01.frm

-rw-r- 1 mysql mysql 96K Mar 31 10:43 test01.ibd

-rw-r- 1 mysql mysql 8.4K Mar 31 10:48 test02.frm

-rw-r- 1 mysql mysql 20 Mar 31 10:49 test02.MYD

-rw-r- 1 mysql mysql 1.0K Mar 31 10:49 test02.MYI

Description:

Test02.frm: stores metadata information related to tables

The test02.MYD:myisam storage engine is dedicated to storing data from myisam tables

The test02.MYI:myisam storage engine is dedicated to storing index-related information on myisam tables

MEMORY:

Create the table test03:

Mysql > create table test03 (id int, name varchar (8)) engine=memory

Query OK, 0 rows affected (0.00 sec)

Mysql > insert into test03 values (1)

Query OK, 1 row affected (0.01sec)

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

View the data file directory:

[root@mysqldb test] # ll-h

Total 144K

-rw-r- 1 mysql mysql 61 Mar 27 10:44 db.opt

-rw-r- 1 mysql mysql 8.4K Mar 31 10:43 test01.frm

-rw-r- 1 mysql mysql 96K Mar 31 10:43 test01.ibd

-rw-r- 1 mysql mysql 8.4K Mar 31 10:48 test02.frm

-rw-r- 1 mysql mysql 20 Mar 31 10:49 test02.MYD

-rw-r- 1 mysql mysql 1.0K Mar 31 10:49 test02.MYI

-rw-r- 1 mysql mysql 8.4K Mar 31 10:54 test03.frm

Description:

Test03.frm: stores metadata information related to tables

ARCHIVE:

Create the table test04:

Mysql > create table test04 (id int, name varchar (8)) engine=ARCHIVE

Query OK, 0 rows affected (0.01 sec)

Mysql > insert into test04 values (1)

Query OK, 1 row affected (0.00 sec)

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

View the data file directory:

[root@mysqldb test] # ll-h

Total 168K

-rw-r- 1 mysql mysql 61 Mar 27 10:44 db.opt

-rw-r- 1 mysql mysql 8.4K Mar 31 10:43 test01.frm

-rw-r- 1 mysql mysql 96K Mar 31 10:43 test01.ibd

-rw-r- 1 mysql mysql 8.4K Mar 31 10:48 test02.frm

-rw-r- 1 mysql mysql 20 Mar 31 10:49 test02.MYD

-rw-r- 1 mysql mysql 1.0K Mar 31 10:49 test02.MYI

-rw-r- 1 mysql mysql 8.4K Mar 31 10:54 test03.frm

-rw-r- 1 mysql mysql 8.5K Mar 31 10:57 test04.ARZ

-rw-r- 1 mysql mysql 8.4K Mar 31 10:57 test04.frm

Description:

Test04.frm: stores metadata information related to tables

Test04.ARZ: storing table data

CSV:

Create the table test05:

Mysql > create table test05 (id int, name varchar (8)) engine=csv

ERROR 1178 (42000): The storage engine for the table doesn't support nullable columns

Mysql > create table test05 (id int not null, name varchar (8) not null) engine=csv

Query OK, 0 rows affected (0.00 sec)

Mysql > insert into test05 values (1)

Query OK, 1 row affected (0.00 sec)

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

View the data file directory:

[root@mysqldb test] # ll-h

Total 188K

-rw-r- 1 mysql mysql 61 Mar 27 10:44 db.opt

-rw-r- 1 mysql mysql 8.4K Mar 31 10:43 test01.frm

-rw-r- 1 mysql mysql 96K Mar 31 10:43 test01.ibd

-rw-r- 1 mysql mysql 8.4K Mar 31 10:48 test02.frm

-rw-r- 1 mysql mysql 20 Mar 31 10:49 test02.MYD

-rw-r- 1 mysql mysql 1.0K Mar 31 10:49 test02.MYI

-rw-r- 1 mysql mysql 8.4K Mar 31 10:54 test03.frm

-rw-r- 1 mysql mysql 8.5K Mar 31 10:57 test04.ARZ

-rw-r- 1 mysql mysql 8.4K Mar 31 10:57 test04.frm

-rw-r- 1 mysql mysql 35 Mar 31 11:02 test05.CSM

-rw-r- 1 mysql mysql 7 Mar 31 11:02 test05.CSV

-rw-r- 1 mysql mysql 8.4K Mar 31 11:02 test05.frm

Description:

Test05.frm: storing table structure information

Test05.CSV: store table data, save in CSV format

Test05.CSM: stores the metadata of a table, such as table status and data volume

BLACKHOLE:

Create the table test06:

Mysql > create table test06 (id int not null, name varchar (8) not null) engine=blackhole

Query OK, 0 rows affected (0.00 sec)

Mysql > insert into test06 values (1)

Query OK, 1 row affected (0.00 sec)

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from test06

Empty set (0.00 sec)

View the data file directory:

[root@mysqldb test] # ll-h

Total 200K

-rw-r- 1 mysql mysql 61 Mar 27 10:44 db.opt

-rw-r- 1 mysql mysql 8.4K Mar 31 10:43 test01.frm

-rw-r- 1 mysql mysql 96K Mar 31 10:43 test01.ibd

-rw-r- 1 mysql mysql 8.4K Mar 31 10:48 test02.frm

-rw-r- 1 mysql mysql 20 Mar 31 10:49 test02.MYD

-rw-r- 1 mysql mysql 1.0K Mar 31 10:49 test02.MYI

-rw-r- 1 mysql mysql 8.4K Mar 31 10:54 test03.frm

-rw-r- 1 mysql mysql 8.5K Mar 31 10:57 test04.ARZ

-rw-r- 1 mysql mysql 8.4K Mar 31 10:57 test04.frm

-rw-r- 1 mysql mysql 35 Mar 31 11:02 test05.CSM

-rw-r- 1 mysql mysql 7 Mar 31 11:02 test05.CSV

-rw-r- 1 mysql mysql 8.4K Mar 31 11:02 test05.frm

-rw-r- 1 mysql mysql 8.4K Mar 31 11:10 test06.frm

Description:

Test06.frm: stores metadata information related to tables

After reading the above, have you mastered the method of the data file name of the MySQL part of the engine? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.