MySQL判别InnoDB表是独立表空间还是共享表空间的方法详解

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name  | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.01 sec)
 
mysql> use MyDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> create table Independent_tablespace(name varchar(64));
Query OK, 0 rows affected (0.03 sec)
 
mysql> exit
 
[root@DB-Server ~]# cd /data/mysql/MyDB/
[root@DB-Server MyDB]# ls -lrt Independent_tablespace*
-rw-rw---- 1 mysql mysql 8560 Aug 21 22:05 Independent_tablespace.frm
-rw-rw---- 1 mysql mysql 98304 Aug 21 22:05 Independent_tablespace.ibd
[root@DB-Server MyDB]# 

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name  | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF |
+-----------------------+-------+
1 row in set (0.00 sec)
 
mysql> use MyDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> create table common_tablespace(name varchar(64));
Query OK, 0 rows affected (0.02 sec)
 
mysql> exit
Bye
[root@DB-Server MyDB]# ls -lrt common_tablespace*
-rw-rw---- 1 mysql mysql 8560 Aug 21 22:08 common_tablespace.frm
[root@DB-Server MyDB]# 

SELECT TABLE_SCHEMA
 ,TABLE_NAME
 ,TABLE_TYPE
 ,N'共享表空间' AS TABLE_SPACE
 ,ENGINE
 ,VERSION
 ,TABLE_ROWS
 ,AVG_ROW_LENGTH
 ,CREATE_TIME
 ,UPDATE_TIME
FROM INFORMATION_SCHEMA.TABLES T
LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME
WHERE I.NAME IS NULL AND T.TABLE_SCHEMA='MyDB' AND T.ENGINE='InnoDB';

SELECT TABLE_SCHEMA
 ,TABLE_NAME
 ,TABLE_TYPE
 ,N'独立表空间' AS TABLE_SPACE
 ,ENGINE
 ,VERSION
 ,TABLE_ROWS
 ,AVG_ROW_LENGTH
 ,CREATE_TIME
 ,UPDATE_TIME
FROM INFORMATION_SCHEMA.TABLES T
INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME
WHERE T.TABLE_SCHEMA='MyDB' AND T.ENGINE='InnoDB';

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE SPACE_TYPE='Single';
 
 
 
SELECT TABLE_SCHEMA
 ,TABLE_NAME
 ,TABLE_TYPE
 ,N'独立表空间' AS TABLE_SPACE
 ,ENGINE
 ,VERSION
 ,TABLE_ROWS
 ,AVG_ROW_LENGTH
 ,CREATE_TIME
 ,UPDATE_TIME
FROM INFORMATION_SCHEMA.TABLES T
INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME
WHERE T.TABLE_SCHEMA='YourDatabase' AND T.ENGINE='InnoDB';

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE SPACE_TYPE='System';
 
 
 
SELECT TABLE_SCHEMA
 ,TABLE_NAME
 ,TABLE_TYPE
 ,N'共享表空间' AS TABLE_SPACE
 ,ENGINE
 ,VERSION
 ,TABLE_ROWS
 ,AVG_ROW_LENGTH
 ,CREATE_TIME
 ,UPDATE_TIME
FROM INFORMATION_SCHEMA.TABLES T
LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME
WHERE I.NAME IS NULL AND T.TABLE_SCHEMA='YourDatabase' AND T.ENGINE='InnoDB'

SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE SPACE_TYPE='Single';