Mysql/Mariadb 테이블 및 오브젝트 정보 확인

Mysql/maria DB에서 테이블, 컬럼, 오브젝트 정보에 대해 알아보겠습니다.

​

​

1. Table 정보 확인

Table에 대한 여러가지 정보를 확인 할 수 있다.

update_time은 최종 dml 시간이 표기되지만 DB를 재기동하게되면 update_time이 null로 변하게 된다.

MariaDB [(none)]> desc information_schema.tables ;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(2048)       | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+

MariaDB [(none)]> select table_schema, table_name, create_time, update_time
    -> from information_schema.tables
    -> where table_schema = 'test'
    -> order by table_schema, table_name ;
+--------------+------------+---------------------+---------------------+
| table_schema | table_name | create_time         | update_time         |
+--------------+------------+---------------------+---------------------+
| test         | audit_log  | 2020-08-26 10:55:45 | NULL                |
| test         | test_user  | 2020-10-23 10:04:58 | NULL                |
| test         | test       | 2020-09-08 08:23:52 | NULL                |
| test         | test11     | 2020-10-23 09:21:33 | 2020-10-23 10:26:43 |
| test         | t1         | 2020-09-02 14:28:47 | NULL                |
| test         | t2         | 2020-11-23 09:23:00 | 2020-11-23 09:24:31 |
+--------------+------------+---------------------+---------------------+

2. Column 정보 확인

column에 대한 모든 정보를 확인 할 수 있다.

MariaDB [(none)]> desc information_schema.tables ;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(2048)       | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+

MariaDB [(none)]> select table_schema, table_name, ordinal_position, column_name, column_type
    -> from information_schema.columns
    -> where table_schema = 'test'
    ->   and table_name = 't1';
+--------------+------------+------------------+-------------+-------------+
| table_schema | table_name | ordinal_position | column_name | column_type |
+--------------+------------+------------------+-------------+-------------+
| test         | t1         |                1 | id          | varchar(10) |
| test         | t1         |                2 | dt1         | datetime    |
| test         | t1         |                3 | dt2         | timestamp   |
| test         | t1         |                4 | dt3         | varchar(30) |
+--------------+------------+------------------+-------------+-------------+

3. object 정보 확인

procedure, function 등의 정보를 확인할 수 있다.

MariaDB [(none)]> desc information_schema.routines ;
+--------------------------+---------------------+------+-----+---------------------+-------+
| Field                    | Type                | Null | Key | Default             | Extra |
+--------------------------+---------------------+------+-----+---------------------+-------+
| SPECIFIC_NAME            | varchar(64)         | NO   |     |                     |       |
| ROUTINE_CATALOG          | varchar(512)        | NO   |     |                     |       |
| ROUTINE_SCHEMA           | varchar(64)         | NO   |     |                     |       |
| ROUTINE_NAME             | varchar(64)         | NO   |     |                     |       |
| ROUTINE_TYPE             | varchar(9)          | NO   |     |                     |       |
| DATA_TYPE                | varchar(64)         | NO   |     |                     |       |
| CHARACTER_MAXIMUM_LENGTH | int(21)             | YES  |     | NULL                |       |
| CHARACTER_OCTET_LENGTH   | int(21)             | YES  |     | NULL                |       |
| NUMERIC_PRECISION        | int(21)             | YES  |     | NULL                |       |
| NUMERIC_SCALE            | int(21)             | YES  |     | NULL                |       |
| DATETIME_PRECISION       | bigint(21) unsigned | YES  |     | NULL                |       |
| CHARACTER_SET_NAME       | varchar(64)         | YES  |     | NULL                |       |
| COLLATION_NAME           | varchar(64)         | YES  |     | NULL                |       |
| DTD_IDENTIFIER           | longtext            | YES  |     | NULL                |       |
| ROUTINE_BODY             | varchar(8)          | NO   |     |                     |       |
| ROUTINE_DEFINITION       | longtext            | YES  |     | NULL                |       |
| EXTERNAL_NAME            | varchar(64)         | YES  |     | NULL                |       |
| EXTERNAL_LANGUAGE        | varchar(64)         | YES  |     | NULL                |       |
| PARAMETER_STYLE          | varchar(8)          | NO   |     |                     |       |
| IS_DETERMINISTIC         | varchar(3)          | NO   |     |                     |       |
| SQL_DATA_ACCESS          | varchar(64)         | NO   |     |                     |       |
| SQL_PATH                 | varchar(64)         | YES  |     | NULL                |       |
| SECURITY_TYPE            | varchar(7)          | NO   |     |                     |       |
| CREATED                  | datetime            | NO   |     | 0000-00-00 00:00:00 |       |
| LAST_ALTERED             | datetime            | NO   |     | 0000-00-00 00:00:00 |       |
| SQL_MODE                 | varchar(8192)       | NO   |     |                     |       |
| ROUTINE_COMMENT          | longtext            | NO   |     |                     |       |
| DEFINER                  | varchar(189)        | NO   |     |                     |       |
| CHARACTER_SET_CLIENT     | varchar(32)         | NO   |     |                     |       |
| COLLATION_CONNECTION     | varchar(32)         | NO   |     |                     |       |
| DATABASE_COLLATION       | varchar(32)         | NO   |     |                     |       |
+--------------------------+---------------------+------+-----+---------------------+-------+

MariaDB [(none)]> select routine_schema, routine_name, routine_type, created, last_altered
    -> from information_schema.routines
    -> where routine_schema = 'test' ;
+----------------+------------------+--------------+---------------------+---------------------+
| routine_schema | routine_name     | routine_type | created             | last_altered        |
+----------------+------------------+--------------+---------------------+---------------------+
| test           | fn_test          | FUNCTION     | 2020-09-08 16:55:50 | 2020-09-08 16:55:50 |
| test           | sp_test          | PROCEDURE    | 2020-09-29 08:51:27 | 2020-09-29 08:51:27 |
| test           | sp_user          | PROCEDURE    | 2020-09-09 15:16:25 | 2020-09-09 15:16:25 |
| test           | sp_user_name     | PROCEDURE    | 2020-09-16 11:19:51 | 2020-09-16 11:19:51 |
| test           | sp_user_name1    | PROCEDURE    | 2020-09-17 14:16:56 | 2020-09-17 14:16:56 |
+----------------+------------------+--------------+---------------------+---------------------+

4. trigger 정보 확인

trigger는 별도의 테이블에서 정보를 확인할 수 있다.

MariaDB [(none)]> desc information_schema.triggers;
+----------------------------+---------------+------+-----+---------+-------+
| Field                      | Type          | Null | Key | Default | Extra |
+----------------------------+---------------+------+-----+---------+-------+
| TRIGGER_CATALOG            | varchar(512)  | NO   |     |         |       |
| TRIGGER_SCHEMA             | varchar(64)   | NO   |     |         |       |
| TRIGGER_NAME               | varchar(64)   | NO   |     |         |       |
| EVENT_MANIPULATION         | varchar(6)    | NO   |     |         |       |
| EVENT_OBJECT_CATALOG       | varchar(512)  | NO   |     |         |       |
| EVENT_OBJECT_SCHEMA        | varchar(64)   | NO   |     |         |       |
| EVENT_OBJECT_TABLE         | varchar(64)   | NO   |     |         |       |
| ACTION_ORDER               | bigint(4)     | NO   |     | 0       |       |
| ACTION_CONDITION           | longtext      | YES  |     | NULL    |       |
| ACTION_STATEMENT           | longtext      | NO   |     |         |       |
| ACTION_ORIENTATION         | varchar(9)    | NO   |     |         |       |
| ACTION_TIMING              | varchar(6)    | NO   |     |         |       |
| ACTION_REFERENCE_OLD_TABLE | varchar(64)   | YES  |     | NULL    |       |
| ACTION_REFERENCE_NEW_TABLE | varchar(64)   | YES  |     | NULL    |       |
| ACTION_REFERENCE_OLD_ROW   | varchar(3)    | NO   |     |         |       |
| ACTION_REFERENCE_NEW_ROW   | varchar(3)    | NO   |     |         |       |
| CREATED                    | datetime(2)   | YES  |     | NULL    |       |
| SQL_MODE                   | varchar(8192) | NO   |     |         |       |
| DEFINER                    | varchar(189)  | NO   |     |         |       |
| CHARACTER_SET_CLIENT       | varchar(32)   | NO   |     |         |       |
| COLLATION_CONNECTION       | varchar(32)   | NO   |     |         |       |
| DATABASE_COLLATION         | varchar(32)   | NO   |     |         |       |
+----------------------------+---------------+------+-----+---------+-------+


MariaDB [(none)]> select trigger_schema, trigger_name, event_manipulation, action_timing
    -> from information_schema.triggers;
+----------------+----------------------------+--------------------+---------------+
| trigger_schema | trigger_name               | event_manipulation | action_timing |
+----------------+----------------------------+--------------------+---------------+
| sys            | sys_config_insert_set_user | INSERT             | BEFORE        |
| sys            | sys_config_update_set_user | UPDATE             | BEFORE        |
+----------------+----------------------------+--------------------+---------------+

이상 Mysql/MariaDB 테이블, 컬럼, 오브젝트 정보에 대해 설명하였습니다.