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

This article mainly explains the "mysql table how to see where there is", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "mysql table how to see where there is" it!

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 and the syntax is "show variables like 'datadir';". You can use this command to view the exact location of the data catalog storage.

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

What do you think of mysql's watch? where is it?

As we all know, MySQL needs to support persistence, it stores data on disk, and the device that manages the disk is called file system in the operating system. For our research and development, most of us only care about MySQL index and SQL performance optimization, while it is like a black box for such problems as 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 (that is, disk). When you need to read data, InnoDB will read the data from the file system and return it to us; when you need to write data, 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?

2 MySQL data directory

When the MySQL server starts, it will load files from a directory of the file system, and the data generated at run time will also be written to this directory of the file system, which is the data directory.

2.1 where is the MySQL data directory

The MySQL data directory corresponds to a system variable datadir. You can use this command to view the specific location where the data directory is stored:

Show variables like 'datadir'

In other words, the data directory of my native MySQL is in / usr/local/mysql/data/.

3 the structure of the data directory

We know that the data generated during the operation of MySQL include databases, tables, views, servers, etc., as well as additional data created by MySQL to make the program run better. Next, let's take a look at the contents of the data directory.

3.1 representation of databases in the file system

When we execute the create database mydb statement to create a database, MySQL does two things:

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

Create a file called db.opt under mydb, which contains various attributes of the database, such as the character set of the database, comparison rules, and so on.

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

The databases I have created on this machine are:

Then I go to the directory / usr/local/mysql/data/ to see if there are these folders above:

Indeed, the highlighted folders correspond to the databases in MySQL. However, if you look closely, information_schema actually does not exist, and because it is special, the designers of MySQL have given special treatment to its implementation, so there is no corresponding database directory.

Thank you for your reading, the above is the content of "how to see where the mysql table exists". After the study of this article, I believe you have a deeper understanding of the problem of how to see where the mysql table exists, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.