MySQLにおける、Information_Schema
INFORMATION_SCHEMAでは、データベースのメタデータを参照してテーブル、カラム情報、権限などの MySQLのメタ情報を確認する事が可能です。
Information_Schema Over View
参照：第 26 章 INFORMATION_SCHEMA テーブル
文字コードや照合順序の確認
mysql> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME,DEFAULT_ENCRYPTION from SCHEMATA;
+--------------------+----------------------------+------------------------+--------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | DEFAULT_ENCRYPTION |
+--------------------+----------------------------+------------------------+--------------------+
| mysql | utf8mb4 | utf8mb4_0900_ai_ci | NO |
| information_schema | utf8 | utf8_general_ci | NO |
| performance_schema | utf8mb4 | utf8mb4_0900_ai_ci | NO |
| sys | utf8mb4 | utf8mb4_0900_ai_ci | NO |
| POC | utf8mb4 | utf8mb4_0900_ai_ci | NO |
+--------------------+----------------------------+------------------------+--------------------+
5 rows in set (0.01 sec)
mysql> show create table SCHEMATA\G
*************************** 1. row ***************************
View: SCHEMATA
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `SCHEMATA` AS select `cat`.`name` AS `CATALOG_NAME`,`sch`.`name` AS `SCHEMA_NAME`,`cs`.`name` AS `DEFAULT_CHARACTER_SET_NAME`,`col`.`name` AS `DEFAULT_COLLATION_NAME`,NULL AS `SQL_PATH`,`sch`.`default_encryption` AS `DEFAULT_ENCRYPTION` from (((`mysql`.`schemata` `sch` join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) join `mysql`.`collations` `col` on((`sch`.`default_collation_id` = `col`.`id`))) join `mysql`.`character_sets` `cs` on((`col`.`character_set_id` = `cs`.`id`))) where (0 <> can_access_database(`sch`.`name`))
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
MySQLのプラグイン確認
mysql> select * from plugins limit 1\G
*************************** 1. row ***************************
PLUGIN_NAME: binlog
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: STORAGE ENGINE
PLUGIN_TYPE_VERSION: 80027.0
PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
PLUGIN_AUTHOR: Oracle Corporation
PLUGIN_DESCRIPTION: This is a pseudo storage engine to represent the binlog in a transaction
PLUGIN_LICENSE: GPL
LOAD_OPTION: FORCE
1 row in set (0.00 sec)
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 '',
`PLUGIN_TYPE` varchar(80) NOT NULL DEFAULT '',
`PLUGIN_TYPE_VERSION` varchar(20) NOT NULL DEFAULT '',
`PLUGIN_LIBRARY` varchar(64) DEFAULT NULL,
`PLUGIN_LIBRARY_VERSION` varchar(20) DEFAULT NULL,
`PLUGIN_AUTHOR` varchar(64) DEFAULT NULL,
`PLUGIN_DESCRIPTION` longtext,
`PLUGIN_LICENSE` varchar(80) DEFAULT NULL,
`LOAD_OPTION` varchar(64) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)
mysql>
InnoDBテーブルスペースのEncryption等の状況を確認可能
mysql> select SPACE,NAME,ROW_FORMAT,ZIP_PAGE_SIZE,SPACE_TYPE,FS_BLOCK_SIZE,SERVER_VERSION,ENCRYPTION,STATE from INFORMATION_SCHEMA.INNODB_TABLESPACES limit 1\G
*************************** 1. row ***************************
SPACE: 4294967294
NAME: mysql
ROW_FORMAT: Any
ZIP_PAGE_SIZE: 0
SPACE_TYPE: General
FS_BLOCK_SIZE: 4096
SERVER_VERSION: 8.0.27
ENCRYPTION: N
STATE: normal
1 row in set (0.00 sec)
mysql>
構成しているテーブルスペースFILEを確認
mysql> SELECT FILE_ID,FILE_NAME,FILE_TYPE,TABLESPACE_NAME,TABLE_SCHEMA,LOGFILE_GROUP_NAME, FILE_NAME, EXTRA FROM INFORMATION_SCHEMA.FILES limit 1\G
*************************** 1. row ***************************
FILE_ID: 4294967294
FILE_NAME: ./mysql.ibd
FILE_TYPE: TABLESPACE
TABLESPACE_NAME: mysql
TABLE_SCHEMA: NULL
LOGFILE_GROUP_NAME: NULL
FILE_NAME: ./mysql.ibd
EXTRA: NULL
1 row in set (0.00 sec)
MySQL8.0.30では、79テーブル程有りました。詳細は以下のオフィシャルマニュアルを参照下さい。
第 26 章 INFORMATION_SCHEMA テーブル
PostgreSQLにおける、Informaiton_Schema
MySQLではInformation_Schemaは独立したデータベース（スキーマ）として存在していますが、PostgreSQLでは、すべてのデータベースに共通する組み込みスキーマとして存在しています。
POC=# SELECT table_name, constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name='p12a-14_01';
table_name | constraint_name | constraint_type
------------+-----------------------+-----------------
p12a-14_01 | p12a-14_01_pkey | PRIMARY KEY
p12a-14_01 | 2200_18447_1_not_null | CHECK
(2 rows)
POC=# SELECT table_name, constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name='sample_partition';
table_name | constraint_name | constraint_type
------------------+-----------------------+-----------------
sample_partition | sample_partition_pkey | PRIMARY KEY
sample_partition | 2200_18597_1_not_null | CHECK
sample_partition | 2200_18597_2_not_null | CHECK
(3 rows)
POC=# SELECT table_name, constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name='sample_pm01';
table_name | constraint_name | constraint_type
-------------+-----------------------+-----------------
sample_pm01 | sample_pm01_pkey | PRIMARY KEY
sample_pm01 | 2200_18865_1_not_null | CHECK
sample_pm01 | 2200_18865_2_not_null | CHECK
(3 rows)
POC=# SELECT grantor,grantee,table_catalog,table_schema, table_name, privilege_type
FROM information_schema.role_table_grants where table_catalog = 'POC' limit 10;
grantor | grantee | table_catalog | table_schema | table_name | privilege_type
----------+----------+---------------+--------------+------------------+----------------
postgres | postgres | POC | public | geometry_columns | INSERT
postgres | postgres | POC | public | geometry_columns | SELECT
postgres | postgres | POC | public | geometry_columns | UPDATE
postgres | postgres | POC | public | geometry_columns | DELETE
postgres | postgres | POC | public | geometry_columns | TRUNCATE
postgres | postgres | POC | public | geometry_columns | REFERENCES
postgres | postgres | POC | public | geometry_columns | TRIGGER
postgres | postgres | POC | public | spatial_ref_sys | INSERT
postgres | postgres | POC | public | spatial_ref_sys | SELECT
postgres | postgres | POC | public | spatial_ref_sys | UPDATE
(10 rows)
POC=# SELECT sequence_catalog,sequence_name,data_type,numeric_precision,minimum_value,maximum_value,increment FROM information_schema.sequences limit 2;
-[ RECORD 1 ]-----+--------------------
sequence_catalog | POC
sequence_name | sample_data_id
data_type | bigint
numeric_precision | 64
minimum_value | 1
maximum_value | 9223372036854775807
increment | 1
-[ RECORD 2 ]-----+--------------------
sequence_catalog | POC
sequence_name | p12a-14_01_gid_seq
data_type | integer
numeric_precision | 32
minimum_value | 1
maximum_value | 2147483647
increment | 1
POC=#
MySQLのinformation_schemaと同様にViewで構成されているようですね。
POC=# \d information_schema.collations;
View "information_schema.collations"
Column | Type | Collation | Nullable | Default
-------------------+-----------------------------------+-----------+----------+---------
collation_catalog | information_schema.sql_identifier | | |
collation_schema | information_schema.sql_identifier | | |
collation_name | information_schema.sql_identifier | | |
pad_attribute | information_schema.character_data | | |
POC=# \d+ information_schema.collations;
View "information_schema.collations"
Column | Type | Collation | Nullable | Default | Storage | Description
-------------------+-----------------------------------+-----------+----------+---------+----------+-------------
collation_catalog | information_schema.sql_identifier | | | | plain |
collation_schema | information_schema.sql_identifier | | | | plain |
collation_name | information_schema.sql_identifier | | | | plain |
pad_attribute | information_schema.character_data | | | | extended |
View definition:
SELECT current_database()::information_schema.sql_identifier AS collation_catalog,
nc.nspname::information_schema.sql_identifier AS collation_schema,
c.collname::information_schema.sql_identifier AS collation_name,
'NO PAD'::character varying::information_schema.character_data AS pad_attribute
FROM pg_collation c,
pg_namespace nc
WHERE c.collnamespace = nc.oid AND (c.collencoding = ANY (ARRAY['-1'::integer, ( SELECT pg_database.encoding
FROM pg_database
WHERE pg_database.datname = current_database())]));
詳細は以下のオフィシャルマニュアルを参照下さい。