Information_Schema

information_scheama

MySQLにおける、Information_Schema

INFORMATION_SCHEMAでは、データベースのメタデータを参照してテーブル、カラム情報、権限などの MySQLのメタ情報を確認する事が可能です。

Information_Schema Over View

information_schema

参照:第 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)
SCHEMATA

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>
PLUGINS

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=#

COLLATION

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())]));
DDL for information_schema

詳細は以下のオフィシャルマニュアルを参照下さい。

Chapter 37. The Information Schema

カテゴリー:

最近のコメント

表示できるコメントはありません。