반응형
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 테이블, 컬럼, 오브젝트 정보에 대해 설명하였습니다.
반응형
'Database > Mysql' 카테고리의 다른 글
| Mysql/Mariadb 컬럼 변경/추가 (0) | 2020.12.16 |
|---|---|
| Mysql/Mariadb 버전 확인 (0) | 2020.12.16 |
| MySQL/mariadb Table 및 Index Size 확인 (0) | 2020.12.16 |
| Mysql/Mariadb IFNULL과 NULLIF 차이점 (0) | 2020.12.16 |
| Mysql/Mariadb 권한 확인 (grant / revoke) (0) | 2020.12.15 |
댓글