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

This article mainly explains why the table names in the MySQL data dictionary information_schema should be capitalized. The explanation in the article is simple and clear and easy to learn and understand. Please follow Xiaobian's train of thought to study and learn why the table names in the MySQL data dictionary information_schema should be capitalized.

Question: why is the table name uppercase in the MySQL data dictionary information_schema and lowercase in performance_schema and other libraries?

First of all, the case of case is relatively incompatible.

For example, in performance_schema, two related tables can be found based on the keyword user.

Mysql > show tables like 'user%'

+-+

| | Tables_in_performance_schema (user%) |

+-+

| | user_variables_by_thread |

| | users |

+-+

2 rows in set (0.00 sec)

But if I change uppercase, I can't recognize it, which is handled in a similar way in other databases.

Mysql > desc USERS

ERROR 1146 (42S02): Table 'performance_schema.USERS' doesn't exist

Mysql > select database ()

+-+

| | database () |

+-+

| | performance_schema |

+-+

1 row in set (0.00 sec)

In information_schema, it is relatively compatible.

Mysql > select count (*) from tables; select count (*) from TABLES

+-+

| | count (*) |

+-+

| | 383 |

+-+

1 row in set (0.01 sec)

+-+

| | count (*) |

+-+

| | 383 |

+-+

1 row in set (0.00 sec)

If you look at it from a physical file point of view, you will find that the information_schema database in MySQL is different from other databases in that no specified directory exists.

[root@dev01 mysql] # ll

Total 188796

-rw-r- 1 mysql mysql 56 Jan 2 12:37 auto.cnf

-rw-r- 1 mysql mysql 5 Mar 13 14:26 dev01.pid

Drwxr-x--- 2 mysql mysql 12288 Mar 9 10:44 devopsdb

Drwxr-x--- 2 mysql mysql 4096 Jan 2 12:38 dms_metadata

-rw-r- 1 mysql mysql 1292 Jan 26 19:44 ib_buffer_pool

-rw-r- 1 mysql mysql 79691776 Mar 13 23:27 ibdata1

-rw-r- 1 mysql mysql 50331648 Mar 13 23:27 ib_logfile0

-rw-r- 1 mysql mysql 50331648 Mar 13 23:27 ib_logfile1

-rw-r- 1 mysql mysql 12582912 Mar 13 23:36 ibtmp1

Drwxr-x--- 2 mysql mysql 4096 Jan 24 19:04 kmp

Drwxr-x--- 2 mysql mysql 4096 Jan 2 12:37 mysql

-rw-r- 1 mysql mysql 324407 Mar 13 21:54 mysqld.log

Drwxr-x--- 2 mysql mysql 4096 Jan 2 12:37 performance_schema

Drwxr-x--- 2 mysql mysql 12288 Jan 2 12:37 sys

Drwxr-x--- 2 mysql mysql 4096 Mar 13 23:27 test

The storage of this data is like the system tablespace in Oracle, so information_schema is a veritable data dictionary.

Performance_schema is a memory library, and its storage engine is special. It is neither InnoDB nor MyISAM,Memory, but performance_schema.

With doubt, I continued to switch to information_schema, and it was obvious that most of the data dictionaries in information_schema were Memory storage engines.

Mysql > show create table tables\ G

* * 1. Row *

Table: TABLES

Create Table: CREATE TEMPORARY TABLE `TABLES` (

`CATALOG`varchar (512) NOT NULL DEFAULT''

. . .

`TABLE_ content 'varchar (2048) NOT NULL DEFAULT''

) ENGINE=MEMORY DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

And some are from InnoDB.

Mysql > show create table PLUGINS\ G

* * 1. Row *

Table: PLUGINS

Create Table: CREATE TEMPORARY TABLE `PLUGINS` (

`NAME`varchar (64) NOT NULL DEFAULT''PLUGIN_

`PLUGIN_ Versione`varchar (20) NOT NULL DEFAULT''

`PLUGIN_ status`varchar (10) NOT NULL DEFAULT''

. . .

`LOAD_ option `varchar (64) NOT NULL DEFAULT''

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

Therefore, the structure of the data dictionary is actually complicated, involving multiple storage engines, multiple rules and processing methods.

If we take a closer look at the above statements, we will find that these data dictionaries are all temporary table.

Understanding these will be very beneficial to the direction of our analysis of the problem.

So my initial idea is that through this naming method, it can be identified as a temporary table to avoid confusion.

How to understand.

Is it feasible if there is a temporary table and a regular table in a database with the name test?

Don't guess yes or no, but check it out quickly.

Mysql > create table tmp (id int,name varchar (30))

Query OK, 0 rows affected (0.09 sec)

Mysql > create temporary table tmp (id int,name varchar (30))

Query OK, 0 rows affected (0.00 sec)

At this time, a record is inserted to show success, but we have no way to tell which table it was inserted into.

Mysql > insert into tmp values (1)

Query OK, 1 row affected (0.00 sec)

So we can use exclusion to verify that we delete the tmp and see where the rest of the data is.

The deletion was successful, but at this time we need other information to support it.

Mysql > drop table tmp

Query OK, 0 rows affected (0.00 sec)

Looking at the definition information of tmp, it is obvious that the tmp of drop is a temporary table.

Mysql > show create table tmp

+-+ +

| | Table | Create Table |

+-+

| | tmp | CREATE TABLE `tmp` (

`id`int (11) DEFAULT NULL

`name` varchar (30) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-- +

1 row in set (0.00 sec)

So where does the inserted data go? if you look it up and show it as 0, it is obvious that the data is inserted into the temporary table tmp.

Mysql > select count (*) from tmp

+-+

| | count (*) |

+-+

| | 0 |

+-+

1 row in set (0.00 sec)

And if we continue to think differently, define two tables, one in uppercase TABLES and the other in lowercase tables.

By default, there is no conflict, although tables is a table at the data dictionary level, it can still be handled normally in other databases, and naming will not conflict.

Mysql > create table TABLES (id INT)

Query OK, 0 rows affected (0.12 sec)

Mysql > create table tables (id INT)

Query OK, 0 rows affected (0.11 sec)

So the preliminary understanding of this problem is to serve as a clear identification at the data dictionary level, and if you want to get more information, you still have to go through the implementation of the code.

Thank you for reading, the above is the "MySQL data dictionary information_schema why capitalization" of the content, after the study of this article, I believe you have a deeper understanding of why the MySQL data dictionary information_schema table names should be capitalized this problem, the specific use of the need for our practice to verify. 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.