MySQL關於資料字典的一個疑問

今天看著MySQL的資料字典，突然想到一個問題：為什麼MySQL資料字典 information_schema中的表名是大寫，而performance_schema和其他庫中的是小寫？

帶著這個問題，我開始了一些猜測和自我論證。

首先大小寫的這個情況是相對不相容的。

比如在performance_schema中，根據關鍵字user可以找到兩個相關的表。

mysql> show tables  like 'user%';

+--------------------------------------+

| Tables_in_performance_schema (user%) |

+--------------------------------------+

| user_variables_by_thread             |

| users                                |

+--------------------------------------+

2 rows in set (0.00 sec)

但是如果我改做大寫，是不能識別的，這在其他的資料庫裡也是類似的處理方式。

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)

而在information_schema中，則是相對相容的。

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)

如果從物理檔案的角度來看，你會發現在MySQL中information_schema這個資料庫和其他資料庫不同，沒有一個指定的目錄存在。

[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

這個資料的儲存就好比Oracle裡面的系統表空間，所以information_schema是名副其實的資料字典庫。

而performance_schema則是一個記憶體庫，它的儲存引擎是特別的一種，不是InnoDB也不是MyISAM,Memory,而是performance_schema

帶著疑問我繼續切換到了information_schema中，可以很明顯的發現information_schema中的資料字典大多是Memory儲存引擎。

mysql> show create table tables \G

*************************** 1. row ***************************

       Table: TABLES

Create Table: CREATE TEMPORARY TABLE `TABLES` (

  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',

 。。。

  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''

) ENGINE=MEMORY DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

還要一些是InnoDB的。

mysql>  show create table PLUGINS\G

*************************** 1. row ***************************

       Table: PLUGINS

Create Table: CREATE TEMPORARY TABLE `PLUGINS` (

  `PLUGIN_NAME` varchar(64) NOT NULL DEFAULT '',

  `PLUGIN_VERSION` 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)

所以資料字典的結構其實還算是比價繁雜，涉及多個儲存引擎，涉及多中規則和處理方式。

如果我們仔細檢視上面的語句，就會發現，這些資料字典都是temporary table.

明白了這些，對我們分析問題的方向就很有利了。

所以我的初步設想就是透過這種命名方式能夠標識出來它就是臨時表，避免混淆。

怎麼理解呢。

如果一個資料庫中存在一個臨時表，一個普通表，名字都是test，可不可行？

不要猜行不行，而是快速驗證一下。

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)

這個時候插入一條記錄，顯示成功，但是我們卻沒有辦法判斷到底是插入到了哪個表裡。

mysql> insert into tmp values(1,'aa');

Query OK, 1 row affected (0.00 sec)

所以我們可以用排除的方式來驗證，我們刪掉tmp,然後檢視剩下的資料到底在哪裡？

刪除成功，但是這個時候我們還需要其他的資訊來佐證。

mysql> drop table tmp ;

Query OK, 0 rows affected (0.00 sec)

檢視tmp的定義資訊，很明顯drop的tmp是臨時表。

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)

那麼插入的資料到了哪裡呢，一查便知，顯示為0，則很顯然資料是插入到了臨時表tmp中。

mysql> select count(*)from tmp ;

+----------+

| count(*) |

+----------+

|        0 |

+----------+

1 row in set (0.00 sec)

而如果我們繼續換個思路，定義兩個表，一個是大寫的TABLES,一個是小寫的tables

則預設的情況下也是不會衝突的，儘管tables是在資料字典層面的一個表，但是在其他資料庫中依舊可以正常處理，命名還是不會衝突。

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)

所以這個問題的初步理解就是為了在資料字典層面作為一種清晰的標識，而如果想得到更多的資訊，還是得翻翻程式碼的實現了。