How to see where mysql table exists

In mysql, you can use the show statement with datadir to see where the table data is stored. datadir is a system variable corresponding to the data directory. The syntax is "show variables like 'datadir';"; through this command you can View the specific location where the data directory is stored.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How to check where the mysql table exists

We all know that MySQL needs to support persistence. It stores data in the disk. In the operating system, the device that manages the disk is called a file. System, for our research and development, most of us only care about MySQL indexes and SQL performance optimization, but it is like a black box when it comes to how data is stored and where it exists.

1 Data is stored in the file system

Our commonly used storage engine InnoDB stores tables on the file system (i.e. disk). When data needs to be read When, InnoDB will read the data from the file system and return it to us; when data needs to be written, InnoDB will write the data back to the file system, so how is the data of the InnoDB storage engine stored in the file system? Woolen cloth?

2 MySQL data directory

When the MySQL server starts, it will load files from a directory in the file system, and the data generated during runtime will also be written. Stored in this directory of the file system, this directory is the data directory

2.1 Where is the MySQL data directory

The MySQL data directory corresponds to a system variable datadir, which can be passed Command to view the specific location where the data directory is stored:

show variables like 'datadir';

That is to say, the data directory of my local MySQL is in /usr/local/mysql/data/

3 data directory The structure of Data, next, let’s take a closer look at the contents of the data directory~

3.1 Representation of the database in the file system

When we execute the create database mydb statement to create a database, MySQL It will do two tasks:

Create a folder mydb with the same name as the database name in the data directory

Create a file named db.opt under mydb. This file contains Various attributes of the database, such as the character set of the database, comparison rules, etc.

Currently, by executing the show databases command, you can view

The databases I created locally include:

Then I entered the directory /usr/local/mysql/data/ to see if there are any of the above folders:

Indeed, the highlighted folders correspond to the databases in MySQL one by one. However, if you look carefully, information_schema does not actually exist. Because it is special, the designers of MySQL have treated its implementation specially, so there is no corresponding database directory.

Recommended learning:

mysql video tutorial

The above is the detailed content of How to see where mysql table exists. For more information, please follow other related articles on the PHP Chinese website!