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)