[MySQL] Information Schema - System : 2번째
- Databases/MySQL
- 2020. 12. 20.
■ EFERENTIAL_CONSTRAINTS Table
• REFERENTIAL_CONSTRAINTS 테이블은 외부참조 키에 대한 정보를 제공합니다.
각 컬럼의 정보는 다음과 같습니다.
• CONSTRAINT_CATALOG
The name of the catalog to which the constraint belongs. This value is always def.
제약조건이 속한 카탈로그의 이름입니다. 이 값은 항상 def입니다.
• CONSTRAINT_SCHEMA
제약조건이 속한 스키마 (데이터베이스)의 이름입니다.
• CONSTRAINT_NAME
제약조건의 이름입니다.
• UNIQUE_CONSTRAINT_CATALOG
제약 조건이 참조하는 유니크한 제약 조건이 포함된 카탈로그의 이름입니다. 이 값은 항상 def입니다.
• UNIQUE_CONSTRAINT_SCHEMA
제약 조건이 참조하는 유니크 제약 조건을 포함하는 스키마 (데이터베이스)의 이름입니다.
• UNIQUE_CONSTRAINT_NAME
제약 조건이 참조하는 유니크 제약 조건의 이름입니다.
• MATCH_OPTION
제약 조건 MATCH 속성의 값입니다. 현재 유효한 값은 NONE뿐입니다.
• UPDATE_RULE
제한 조건 ON UPDATE 속성의 값입니다. 가능한 값은 CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION입니다.
• DELETE_RULE
제약 조건 ON DELETE 속성의 값입니다. 가능한 값은 CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION입니다.
• TABLE_NAME
테이블의 이름입니다. 이 값은 TABLE_CONSTRAINTS 테이블에서와 동일합니다.
• REFERENCED_TABLE_NAME
제약 조건에서 참조하는 테이블의 이름입니다.
■ ROUTINES Table
ROUTINES 테이블은 스토어드 루틴 (스토어드 프로 시저 및 스토어드 함수)에 대한 정보를 제공합니다. ROUTINES 테이블에는 내장SQL 함수 또는 사용자 정의 함수 (UDF)가 포함되지 않습니다.
"mysql.proc 이름"이라는 컬럼은 INFORMATION_SCHEMA ROUTINES 테이블 컬럼 (있는 경우)에 해당하는 mysql.proc 테이블 컬럼을 나타냅니다.
각 컬럼의 정보는 다음과 같습니다.
• SPECIFIC_NAME
루틴의 이름입니다.
• ROUTINE_CATALOG
루틴이 속한 카탈로그의 이름입니다. 이 값은 항상 def입니다.
• ROUTINE_SCHEMA
루틴이 속한 스키마(데이터베이스)의 이름입니다.
• ROUTINE_NAME
루틴의 이름입니다.
• ROUTINE_TYPE
저장 프로 시저의 경우 PROCEDURE, 저장 함수의 경우 FUNCTION으로 표기됩니다.
• DATA_TYPE
루틴이 저장된 함수 인 경우 반환 값 데이터 유형입니다. 루틴이 스토어드 프로 시저인 경우 이 값은 비어 있습니다.
DATA_TYPE 값은 다른 정보가없는 유형 이름입니다. DTD_IDENTIFIER 값에는 유형 이름과 정밀도 또는 길이와 같은 기타 정보가 포함됩니다.
• CHARACTER_MAXIMUM_LENGTH
저장된 함수 문자열 반환 값의 경우 최대 문자 길이입니다. 루틴이 스토어드 프로 시저 인 경우 이 값은 NULL입니다.
• CHARACTER_OCTET_LENGTH
스토어드 함수 문자열 반환 값의 경우 최대 길이 (바이트)입니다. 루틴이 스토어드 프로 시저인 경우 이 값은 NULL입니다.
• NUMERIC_PRECISION
스토어드 함수 숫자 반환 값의 경우 숫자 정밀도입니다. 루틴이 스토어드 프로시저인 경우 이 값은 NULL입니다.
• NUMERIC_SCALE
스토어드 함수 숫자 반환 값의 경우 숫자 스케일입니다. 루틴이 스토어드 프로시저인 경우 이 값은 NULL입니다.
• DATETIME_PRECISION
스토어드 함수 시간 반환 값의 경우, 소수점 초의 자리수입니다.(예 : '2018-09-08 17:51:04.777')
루틴이 스토어드 프로시저인 경우 이 값은 NULL입니다.
• CHARACTER_SET_NAME
스토어드 함수 문자열 반환 값의 경우 문자 집합 이름입니다. 루틴이 스토어드 프로 시저 인 경우이 값은 NULL입니다.
• COLLATION_NAME
저장된 함수 문자열 반환 값의 경우 데이터 정렬 이름입니다. 루틴이 스토어드 프로 시저 인 경우이 값은 NULL입니다.
• DTD_IDENTIFIER
루틴이 저장된 함수 인 경우 반환 값 데이터 유형입니다. 루틴이 스토어드 프로 시저 인 경우이 값은 비어 있습니다.
DATA_TYPE 값은 다른 정보가없는 유형 이름입니다. DTD_IDENTIFIER 값에는 유형 이름과 정밀도 또는 길이와 같은 기타 정보가 포함됩니다.
• ROUTINE_BODY
루틴 정의에 사용되는 언어입니다. 이 값은 항상 SQL입니다.
• ROUTINE_DEFINITION
루틴에 의해 실행된 SQL 문의 텍스트.
• EXTERNAL_NAME
이값은 항상 NULL입니다.
• EXTERNAL_LANGUAGE
스토어드 루틴의 언어입니다. MySQL은 EXTERNAL_LANGUAGE를 다음과 같이 계산합니다.
- mysql.proc.language = 'SQL'이면 EXTERNAL_LANGUAGE는 NULL입니다.
- 그렇지 않으면 EXTERNAL_LANGUAGE는 mysql.proc.language에 있습니다. 그러나 아직 외부 언어가 없으므로 항상 NULL입니다.
• PARAMETER_STYLE
이 값은 항상 SQL입니다.
• IS_DETERMINISTIC
루틴이 DETERMINISTIC 특성으로 정의되었는지 여부에 따라 YES 또는 NO로 표시됩니다.
• SQL_DATA_ACCESS
루틴에 대한 데이터 액세스 특성입니다. 값은 CONTAINS SQL, NO SQL, READS SQL DATA 또는 MODIFIES SQL DATA 중 하나입니다.
• SQL_PATH
이 값은 항상 NULL입니다.
• SECURITY_TYPE
루틴 SQL SECURITY의 특성입니다. 값은 DEFINER 또는 INVOKER 중 하나입니다.
• CREATED
루틴이 작성된 날짜 및 시간입니다. 이것은 TIMESTAMP 값입니다.
• LAST_ALTERED
루틴이 마지막으로 수정된 날짜 및 시간입니다. 이것은 TIMESTAMP 값입니다. 루틴이 작성된 이후 수정되지 않은 경우 이 값은 CREATED 값과 동일합니다.
• SQL_MODE
루틴이 작성 또는 변경 될 때 적용되고 루틴이 실행되는 SQL 모드입니다.
• ROUTINE_COMMENT
루틴에 주석이 있는 경우 주석 텍스트입니다. 그렇지 않은 경우, 이 값은 비어 있습니다.
• DEFINER
'user_name'@'host_name'형식의 DEFINER절(일반적으로 루틴을 만든 사용자)에 이름이 지정된 계정입니다.
• CHARACTER_SET_CLIENT
루틴이 작성될 때 character_set_client 시스템 변수의 세션 값입니다.
• COLLATION_CONNECTION
루틴이 작성되었을 때 collation_connection 시스템 변수의 세션값입니다.
• DATABASE_COLLATION
루틴이 연관된 데이터베이스의 데이터 정렬입니다.
참고사항
루틴에 대한 정보를 보려면 루틴 DEFINER 절에 이름이 지정된 사용자이거나 mysql.proc 테이블에 대한 SELECT 액세스 권한이 있어야합니다. 루틴 자체에 대한 권한이 없는 경우 ROUTINE_DEFINITION 열에 대해 표시되는 값은 NULL입니다.
저장된 함수 반환 값에 대한 정보는 PARAMETERS 테이블에서도 사용할 수 있습니다. 저장된 함수의 반환 값 행은 ORDINAL_POSITION 값이 0인 행으로 식별 할 수 있습니다.
■ SCHEMATA Table
스키마는 데이터베이스이므로 SCHEMATA 테이블은 데이터베이스에 대한 정보를 제공합니다.
각 컬럼의 정보는 다음과 같습니다.
• CATALOG_NAME
스키마가 속한 카탈로그의 이름입니다. 이 값은 항상 def입니다.
• SCHEMA_NAME
스키마 이름입니다.
• DEFAULT_CHARACTER_SET_NAME
스키마의 기본 캐릭터셋입니다.
• DEFAULT_COLLATION_NAME
스키마 기본 데이터 정렬입니다.
• SQL_PATH
이 값은 항상 NULL입니다.
• SQL 및 명령어 조회
SHOW DATABASES 문에서도 스키마 이름을 사용할 수 있습니다. 사용법은 다음과 같습니다.
SELECT SCHEMA_NAME AS `Database`
FROM INFORMATION_SCHEMA.SCHEMATA
[WHERE SCHEMA_NAME LIKE 'wild']
SHOW DATABASES
[LIKE 'wild']전역 SHOW DATABASES 권한이 없는 경우 특정 종류의 권한이 있는 데이터베이스만 표시됩니다.
• 유의사항
전역 권한은 모든 데이터베이스에 대한 권한으로 간주되기 때문에 모든 전역 권한을 사용하면 SHOW DATABASES를 사용하거나 INFORMATION_SCHEMA SCHEMATA 테이블을 검사하여 모든 데이터베이스 이름을 볼 수 있습니다.
■ SCHEMA_PRIVILEGES Table
SCHEMA_PRIVILEGES 테이블은 스키마 (데이터베이스) 권한에 대한 정보를 제공합니다. mysql.db 시스템 테이블에서 값을 가져옵니다.
각 컬럼의 정보는 다음과 같습니다.
• GRANTEE
The name of the account to which the privilege is granted, in 'user_name'@'host_name' format.
권한이 부여된 계정의 이름이며 'user_name'@ 'host_name'형식입니다.
• TABLE_CATALOG
스키마가 속한 카탈로그의 이름입니다. 이 값은 항상 def입니다.
• TABLE_SCHEMA
스키마 이름입니다.
• PRIVILEGE_TYPE
부여된 권한입니다. 값은 스키마 레벨에서 부여할 수 있는 모든 권한이 될 수 있습니다. 각 행은 단일 권한을 나열하므로 피 부여자가 보유한 스키마 권한당 하나의 행이 있습니다.
• IS_GRANTABLE
사용자에게 GRANT OPTION 권한이 있으면 YES, 그렇지 않으면 NO. 출력에는 GRANT OPTION이 PRIVILEGE_TYPE = 'GRANT OPTION'인 별도의 행으로 나열되지 않습니다.
• 참고사항.
SCHEMA_PRIVILEGES는 비표준 INFORMATION_SCHEMA 테이블입니다.
• 중요사항.
다음 명령문은 동일하지 않습니다. 반드시 구분해야 합니다.
1. SELECT ... FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
2. SHOW GRANTS ...
■ STATISTICS Table
STATISTICS 테이블은 테이블 인덱스에 대한 정보를 제공합니다.
각 컬럼의 정보는 다음과 같습니다.
• TABLE_CATALOG
인덱스를 포함하는 테이블이 속한 카탈로그의 이름입니다. 이 값은 항상 def입니다.
• TABLE_SCHEMA
인덱스가 포함 된 테이블이 속한 스키마 (데이터베이스)의 이름입니다.
• TABLE_NAME
인덱스가 포함 된 테이블의 이름입니다.
• NON_UNIQUE
인덱스에 중복을 포함 할 수 없으면 0, 가능하면 1입니다.
• INDEX_SCHEMA
인덱스가 속한 스키마 (데이터베이스)의 이름입니다.
• INDEX_NAME
색인의 이름입니다. 인덱스가 기본 키인 경우 이름은 항상 PRIMARY입니다.
• SEQ_IN_INDEX
1로 시작하는 인덱스의 컬럼 시퀀스 번호입니다.
• COLUMN_NAME
열 이름입니다. EXPRESSION 컬럼에 대한 설명도 참조합니다.
• COLLATION
인덱스에서 열이 정렬되는 방식. A(오름차순), D(내림차순) 또는 NULL(정렬되지 않음) 값을 가질 수 있습니다.
• CARDINALITY
인덱스의 고유값 수에 대한 추정치입니다. 이 번호를 업데이트하려면 InnoDB일경우 ANALYZE TABLE 또는 MyISAM 테이블의 경우 myisamchk -a를 실행합니다.
CARDINALITY는 정수로 저장된 통계를 기반으로 계산되므로 작은 테이블에서도 값이 반드시 정확하지는 않습니다. 카디널리티가 높을수록 MySQL이 조인을 수행할 때 인덱스를 사용할 가능성이 높아집니다.
• SUB_PART
색인 접두사. 즉, 열이 부분적으로 만 인덱싱 된 경우 인덱싱 된 문자 수, 전체 열이 인덱싱 된 경우 NULL입니다.
- 참고사항
접두사 제한은 바이트 단위로 측정됩니다. 그러나 CREATE TABLE, ALTER TABLE 및 CREATE INDEX 문의 인덱스 사양에 대한 접두사 길이는 바이너리가 아닌 문자열 유형(CHAR, VARCHAR, TEXT)의 문자 수와 바이너리 문자열 유형 (BINARY, VARBINARY, BLOB)의 바이트 수로 해석됩니다. 멀티 바이트 문자 세트를 사용하는 바이너리가 아닌 문자열 컬럼의 접두사 길이를 지정할 때 이를 고려해야 합니다.
• PACKED
키가 패킹되는 방법을 나타냅니다. 그렇지 않은 경우 NULL입니다.
• NULLABLE
Contains YES if the column may contain NULL values and '' if not.
열에 NULL 값이 포함될 수 있는 경우 YES를 포함하며, 그렇지 않은 경우 ''을 포함합니다.
• INDEX_TYPE
사용 된 인덱스 방법 (BTREE, FULLTEXT, HASH, RTREE)을 표시합니다.
• COMMENT
인덱스가 비활성화된 경우 비활성화되는 것과 같이 자체 컬럼에 설명되지 않은 인덱스에 대한 정보입니다.
• INDEX_COMMENT
인덱스가 작성될 때 COMMENT속성이 있는 인덱스에 제공된 모든 주석입니다.
• 참고사항.
인덱스에 대한 표준 INFORMATION_SCHEMA 테이블이 없습니다. MySQL컬럼 목록은 QUALIFIER 및 OWNER가 각각 CATALOG 및 SCHEMA로 대체된다는 점을 제외하면 SQL Server 2000이 sp_statistics에 대해 반환하는 것과 유사합니다.
• SQL 및 명령어 조회
테이블 인덱스에 대한 정보는 SHOW INDEX 문에서도 사용할 수 있습니다. 다음과 같이 사용됩니다.
SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_name = 'tbl_name'
AND table_schema = 'db_name'
SHOW INDEX
FROM tbl_name
FROM db_name
■ TABLES Table
TABLES 테이블은 데이터베이스의 테이블에 대한 정보를 제공합니다.
각 컬럼의 정보는 다음과 같습니다.
• TABLE_CATALOG
테이블이 속한 카탈로그의 이름입니다. 이 값은 항상 def입니다.
• TABLE_SCHEMA
테이블이 속한 스키마 (데이터베이스)의 이름입니다.
• TABLE_NAME
테이블 이름입니다.
• TABLE_TYPE
테이블의 경우 BASE TABLE, 뷰의 경우 VIEW, INFORMATION_SCHEMA 테이블의 경우 SYSTEM VIEW.
TABLES 테이블은 TEMPORARY 테이블을 나열하지 않습니다.
• ENGINE
테이블의 스토리지 엔진입니다.
파티션을 나눈 테이블의 경우 ENGINE은 모든 파티션에서 사용하는 스토리지 엔진의 이름을 표시합니다.
• VERSION
테이블 .frm 파일의 버전 번호입니다.
• ROW_FORMAT
행 저장 형식(고정, 동적, 압축, 중복, 압축[Fixed, Dynamic, Compressed, Redundant, Compact])입니다. MyISAM 테이블의 경우 Dynamic은 myisamchk -dvv가 Packed로보고하는 것에 해당합니다. InnoDB 테이블 형식은 Antelope 파일 형식을 사용하는 경우 Redundant 또는 Compact, Barracuda 파일 형식을 사용하는 경우 Compressed 또는 Dynamic입니다.
• TABLE_ROWS
행 수입니다. MyISAM과 같은 일부 스토리지 엔진은 정확한 개수를 저장합니다. InnoDB와 같은 다른 스토리지 엔진의 경우이 값은 근사치이며 실제 값에서 40%에서 50%까지 다를 수 있습니다. 이러한 경우 SELECT COUNT (*)를 사용하여 정확한 개수를 얻어야 합니다.
INFORMATION_SCHEMA 테이블의 경우 TABLE_ROWS가 NULL입니다.
InnoDB 테이블의 경우, 행 수는 SQL최적화에 사용되는 대략적인 추정치 일뿐입니다. (InnoDB 테이블이 분할된 경우에도 마찬가지입니다.)
• AVG_ROW_LENGTH
평균 행 길이입니다.
관련 정보는 이 섹션 끝에있는 참고를 확인합니다.
• DATA_LENGTH
MyISAM의 경우 DATA_LENGTH는 데이터 파일의 길이 (바이트)입니다.
InnoDB의 경우 DATA_LENGTH는 클러스터형 인덱스에 할당된 대략적인 공간(바이트)입니다. 특히, 클러스터된 인덱스 크기(페이지 단위)에 InnoDB 페이지 크기를 곱한 값입니다.
다른 스토리지 엔진에 대한 정보는 이 섹션 끝에있는 참고를 참조합니다.
• MAX_DATA_LENGTH
MyISAM의 경우 MAX_DATA_LENGTH는 데이터 파일의 최대 길이입니다. 사용된 데이터 포인터 크기를 고려하여 테이블에 저장할 수 있는 총 데이터 바이트 수입니다.
InnoDB에는 사용되지 않습니다. 다른 스토리지 엔진에 대한 정보는 이 섹션 끝에있는 참고를 참조합니다.
• INDEX_LENGTH
MyISAM의 경우 INDEX_LENGTH는 인덱스 파일의 길이(바이트)입니다.
InnoDB의 경우 INDEX_LENGTH는 비 클러스터형 인덱스에 할당된 대략적인 공간(바이트)입니다. 특히, 비 클러스터형 인덱스 크기(페이지 단위)에 InnoDB 페이지 크기를 곱한 값입니다.
다른 스토리지 엔진에 대한 정보는 이 섹션 끝에있는 참고를 참조합니다.
• DATA_FREE
할당되었지만 사용되지 않은 바이트 수입니다.
InnoDB 테이블은 테이블이 속한 테이블 스페이스의 여유 공간을 보고합니다. 공유 테이블 스페이스에 있는 테이블의 경우 공유 테이블 스페이스의 여유 공간입니다. 여러 테이블 스페이스를 사용하고 테이블에 자체 테이블 스페이스가 있는 경우 여유 공간은 해당 테이블에만 사용됩니다. 여유 공간은 완전 여유 범위의 바이트 수에서 안전한 여유공간을 뺀 값을 의미합니다. 여유 공간이 0으로 표시 되더라도 새 익스텐트를 할당할 필요가 없는한 행을 삽입 할 수 있습니다.
NDB 클러스터의 경우 DATA_FREE는 디스크 데이터 테이블 또는 디스크 조각에 사용되지는 않지만 디스크에 할당된 공간을 표시합니다. (메모리 내 데이터 리소스 사용량은 DATA_LENGTH 열에 의해보고됩니다.)
파티션을 나눈 테이블의 경우, 이 값은 추정치일 뿐이며 절대적으로 정확하지 않을 수 있습니다. 이러한 경우 이 정보를 얻는 보다 정확한 방법은 다음 예에 표시된대로 INFORMATION_SCHEMA PARTITIONS 테이블을 쿼리하는 것입니다.
SELECT SUM(DATA_FREE)
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'mydb'
AND TABLE_NAME = 'mytable';
• AUTO_INCREMENT
다음 AUTO_INCREMENT 값입니다.
• CREATE_TIME
테이블이 생성 된 날짜입니다.
• UPDATE_TIME
데이터 파일이 마지막으로 업데이트 된 시기입니다. 일부 스토리지 엔진의 경우 이 값은 NULL입니다. 예를 들어 InnoDB는 시스템 테이블 스페이스에 여러 테이블을 저장하고 데이터 파일 타임 스탬프는 적용되지 않습니다. 개별 .ibd 파일에 각 InnoDB 테이블이 있는 테이블별 파일 모드에서도 변경 버퍼링은 데이터 파일에 대한 쓰기를 지연시킬 수 있으므로 파일 수정 시간은 마지막 삽입, 업데이트 또는 삭제 시간과 다릅니다. MyISAM의 경우 데이터 파일 타임 스탬프가 사용됩니다. 그러나 Windows에서는 타임 스탬프가 업데이트로 업데이트되지 않으므로 값이 정확하지 않습니다.
UPDATE_TIME은 분할되지 않은 InnoDB 테이블에서 수행된 마지막 UPDATE, INSERT 또는 DELETE에 대한 타임 스탬프 값을 표시합니다. MVCC의 경우 타임 스탬프 값은 마지막 업데이트 시간으로 간주되는 COMMIT 시간을 반영합니다. 타임 스탬프는 서버가 다시 시작되거나 테이블이 InnoDB 데이터 사전 캐시에서 제거 될 때 유지되지 않습니다.
UPDATE_TIME 열에는 분할된 InnoDB 테이블에 대한 이 정보도 표시됩니다.
• CHECK_TIME
테이블이 마지막으로 확인된 시간입니다. 이번에 모든 스토리지 엔진이 업데이트되는 것은 아닙니다. 이 경우 값은 항상 NULL입니다.
분할된 InnoDB 테이블의 경우 CHECK_TIME은 항상 NULL입니다.
• TABLE_COLLATION
테이블 기본 데이터 정렬입니다. 출력은 테이블 기본 문자 세트를 명시적으로 나열하지 않지만 데이터 정렬 이름은 문자 세트 이름으로 시작합니다.
• CHECKSUM
라이브 체크섬 값입니다 (있는 경우).
• CREATE_OPTIONS
CREATE TABLE과 함께 사용되는 추가 옵션입니다.
CREATE_OPTIONS는 테이블이 분할된 경우 분할됨을 표시합니다.
CREATE_OPTIONS는 테이블당 파일 테이블 스페이스에서 생성된 테이블에 대해 지정된 ENCRYPTION 절을 보여줍니다.
엄격 모드(strict mode)가 비활성화된 테이블을 생성할 때 지정된 행 형식이 지원되지 않으면 스토리지 엔진의 기본 행 형식이 사용됩니다. 테이블의 실제 행 형식은 ROW_FORMAT컬럼에 보고됩니다. CREATE_OPTIONS는 CREATE TABLE 문에 지정된 행 형식을 보여줍니다.
테이블의 스토리지 엔진을 변경하면 새 스토리지 엔진에 적용할 수 없는 테이블 옵션이 테이블 정의에 유지되어 필요한 경우 이전에 정의된 옵션이 있는 테이블을 원래 스토리지 엔진으로 되돌릴 수 있습니다. CREATE_OPTIONS컬럼은 유지된 옵션을 표시 할 수 있습니다.
• TABLE_COMMENT
테이블을 생성할 때 사용된 코멘트(또는 MySQL이 테이블 정보에 액세스 할 수없는 이유에 대한 정보) 입니다.
• 참고사항
+ NDB 테이블의 경우 이 명령문의 출력은 BLOB컬럼이 고려되지 않는다는 점을 제외하고 AVG_ROW_LENGTH 및 DATA_LENGTH 열에 대한 적절한 값을 표시합니다.
+ NDB 테이블의 경우 DATA_LENGTH에는 주 메모리에만 저장된 데이터가 포함됩니다. MAX_DATA_LENGTH 및 DATA_FREE컬럼은 디스크 데이터에 적용됩니다.
+ NDB 클러스터 디스크 데이터 테이블의 경우 MAX_DATA_LENGTH는 디스크 데이터 테이블 또는 조각의 디스크 부분에 할당된 공간을 보여줍니다. (메모리 내 데이터 리소스 사용량은 DATA_LENGTH 열에 의해 보고됩니다.)
+ MEMORY 테이블의 경우 DATA_LENGTH, MAX_DATA_LENGTH 및 INDEX_LENGTH 값은 할당된 실제 메모리 양에 가깝습니다. 할당 알고리즘은 할당 작업수를 줄이기 위해 메모리를 대량으로 예약합니다.
+ 뷰의 경우 TABLE_NAME이 뷰 이름을 나타내고 TABLE_COMMENT가 VIEW를 나타내는 것을 제외하고 모든 TABLES 열은 NULL입니다.
• SQL 및 명령어 조회
테이블 정보는 SHOW TABLE STATUS 및 SHOW TABLES 문에서도 사용할 수 있습니다. 다음 문은 동일합니다.
SELECT
TABLE_NAME, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH,
DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT,
CREATE_TIME, UPDATE_TIME, CHECK_TIME, TABLE_COLLATION, CHECKSUM,
CREATE_OPTIONS, TABLE_COMMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'db_name'
[AND table_name LIKE 'wild']
SHOW TABLE STATUS
FROM db_name
[LIKE 'wild']
다음 명령문은 동일합니다.
SELECT
TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'db_name'
[AND table_name LIKE 'wild']
SHOW FULL TABLES
FROM db_name
[LIKE 'wild']
■ TABLE_CONSTRAINTS Table
TABLE_CONSTRAINTS 테이블은 제약 조건이있는 테이블을 설명합니다.
각 컬럼의 정보는 다음과 같습니다.
• CONSTRAINT_CATALOG
제약 조건이 속한 카탈로그의 이름입니다. 이 값은 항상 def입니다.
• CONSTRAINT_SCHEMA
제약 조건이 속한 스키마 (데이터베이스)의 이름입니다.
• TABLE_SCHEMA
테이블이 속한 스키마 (데이터베이스)의 이름입니다.
• TABLE_NAME
테이블의 이름입니다.
• The CONSTRAINT_TYPE
제약 유형입니다. 값은 UNIQUE, PRIMARY KEY, FOREIGN KEY 또는 CHECK 일 수 있습니다. 이것은 CHAR (ENUM 아님) 컬럼입니다. CHECK 값은 MySQL이 CHECK를 지원할 때까지 사용할 수 없습니다.
UNIQUE 및 PRIMARY KEY 정보는 Non_unique컬럼이 0일때 SHOW INDEX의 출력에서 Key_name컬럼에서 얻은 정보와 거의 같습니다.
■ TABLE_PRIVILEGES Table
TABLE_PRIVILEGES 테이블은 테이블 권한에 대한 정보를 제공합니다. mysql.tables_priv 시스템 테이블에서 값을 가져옵니다.
각 컬럼의 정보는 다음과 같습니다.
• GRANTEE
권한이 부여된 계정의 이름이며 'user_name'@'host_name'형식입니다.
• TABLE_CATALOG
테이블이 속한 카탈로그의 이름입니다. 이 값은 항상 def입니다.
• TABLE_SCHEMA
테이블이 속한 스키마 (데이터베이스)의 이름입니다.
• TABLE_NAME
테이블의 이름입니다.
• PRIVILEGE_TYPE
부여된 권한입니다. 값은 테이블 수준에서 부여할 수 있는 모든 권한이 될 수 있습니다. 각 행에는 단일 권한이 나열되므로 피 부여자가 보유한 테이블 권한당 하나의 행이 있습니다.
• IS_GRANTABLE
사용자에게 GRANT OPTION 권한이 있으면 YES, 그렇지 않으면 NO. 출력에는 GRANT OPTION이 PRIVILEGE_TYPE='GRANT OPTION'인 별도의 행으로 나열되지 않습니다.
• 참고사항
TABLE_PRIVILEGES는 비표준 INFORMATION_SCHEMA 테이블입니다.
• 다음 문은 동일하지 않습니다.
SELECT ... FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
SHOW GRANTS ...
■ TRIGGERS Table
TRIGGERS 테이블은 트리거에 대한 정보를 제공합니다. 테이블의 트리거에 대한 정보를 보려면 테이블에 대한 TRIGGER 권한이 있어야합니다.
각 컬럼의 정보는 다음과 같습니다.
• TRIGGER_CATALOG
트리거가 속한 카탈로그의 이름입니다. 이 값은 항상 def입니다.
• TRIGGER_SCHEMA
트리거가 속한 스키마 (데이터베이스)의 이름입니다.
• TRIGGER_NAME
트리거의 이름입니다.
• EVENT_MANIPULATION
트리거 이벤트입니다. 트리거가 활성화되는 연관된 테이블에 대한 작업 유형입니다. 값은 INSERT(행이 삽입 됨), DELETE(행이 삭제됨) 또는 UPDATE(행이 수정 됨)입니다.
• EVENT_OBJECT_CATALOG, EVENT_OBJECT_SCHEMA 및 EVENT_OBJECT_TABLE
모든 트리거는 정확히 하나의 테이블과 연관됩니다. 이러한 컬럼은 이 테이블이 발생하는 카탈로그 및 스키마 (데이터베이스)와 테이블 이름을 각각 나타냅니다. EVENT_OBJECT_CATALOG 값은 항상 def입니다.
• ACTION_ORDER
동일한 EVENT_MANIPULATION 및 ACTION_TIMING 값을 가진 동일한 테이블의 트리거 목록 내에서 트리거 조치의 서수 위치입니다.
• ACTION_CONDITION
이 값은 항상 NULL입니다.
• ACTION_STATEMENT
트리거 바디(내용); 즉, 트리거가 활성화될 때 실행되는 명령문입니다. 이 텍스트는 UTF-8인코딩을 사용합니다.
• ACTION_ORIENTATION
이 값은 항상 ROW입니다.
• ACTION_TIMING
트리거가 이벤트를 트리거하기 전, 또는 후에 활성화되는지 여부. 값은 BEFORE 또는 AFTER입니다.
• ACTION_REFERENCE_OLD_TABLE
이 값은 항상 NULL입니다.
• ACTION_REFERENCE_NEW_TABLE
이 값은 항상 NULL입니다.
• ACTION_REFERENCE_OLD_ROW 및 ACTION_REFERENCE_NEW_ROW
각각 이전 및 새 컬럼 식별자입니다. ACTION_REFERENCE_OLD_ROW 값은 항상 OLD이고 ACTION_REFERENCE_NEW_ROW 값은 항상 NEW입니다.
• CREATED
트리거가 생성된 날짜 및 시간입니다. 이것은 MySQL 5.7.2 이상에서 생성 된 트리거의 경우 TIMESTAMP(2)값(1/100 초 단위의 소수 부분 포함)이고 5.7.2 이전에 생성된 트리거의 경우 NULL입니다.
• SQL_MODE
트리거가 작성 될 때 적용되고 트리거가 실행되는 SQL 모드입니다.
• DEFINER
'user_name'@'host_name'형식의 DEFINER 절(종종 트리거를 만든 사용자)에 이름이 지정된 계정입니다.
• CHARACTER_SET_CLIENT
트리거가 생성되었을 때 character_set_client 시스템 변수의 세션값입니다.
• COLLATION_CONNECTION
트리거가 생성되었을 때 collation_connection 시스템 변수의 세션값입니다.
• DATABASE_COLLATION
트리거가 연결된 데이터베이스의 데이터 정렬입니다.
• 예제
mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA='test' AND TRIGGER_NAME='ins_sum'\G
*************************** 1. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: test
TRIGGER_NAME: ins_sum
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: test
EVENT_OBJECT_TABLE: account
ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: SET @sum = @sum + NEW.amount
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: 2018-08-08 10:10:12.61
SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE,NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
DEFINER: me@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: latin1_swedish_ciSHOW TRIGGERS 문에서도 트리거 정보를 사용할 수 있습니다.
■ USER_PRIVILEGES Table
USER_PRIVILEGES 테이블은 전역 권한에 대한 정보를 제공합니다. mysql.user 시스템 테이블에서 값을 가져옵니다.
각 컬럼의 정보는 다음과 같습니다.
• GRANTEE
권한이 부여 된 계정의 이름이며 'user_name'@ 'host_name'형식입니다.
• TABLE_CATALOG
카탈로그의 이름입니다. 이 값은 항상 def입니다.
• PRIVILEGE_TYPE
부여 된 권한입니다. 값은 전역 수준에서 부여할 수 있는 모든 권한이 될 수 있습니다. 각 행은 단일 권한을 나열하므로 피 부여자가 보유한 전역 권한당 하나의 행이 있습니다.
• IS_GRANTABLE
사용자에게 GRANT OPTION 권한이 있으면 YES, 그렇지 않으면 NO. 출력에는 GRANT OPTION이 PRIVILEGE_TYPE='GRANT OPTION'인 별도의 행으로 나열되지 않습니다.
• 참고사항
USER_PRIVILEGES는 비표준 INFORMATION_SCHEMA 테이블입니다.
• 다음 문은 동일하지 않습니다.
SELECT ... FROM INFORMATION_SCHEMA.USER_PRIVILEGES
SHOW GRANTS ...
■ VIEWS Table
VIEWS 테이블은 데이터베이스의 뷰에 대한 정보를 제공합니다. 이 테이블에 액세스하려면 SHOW VIEW 권한이 있어야합니다.
각 컬럼의 정보는 다음과 같습니다.
• TABLE_CATALOG
뷰(VIEW)가 속한 카탈로그의 이름입니다. 이 값은 항상 def입니다.
• TABLE_SCHEMA
뷰(VIEW)가 속한 스키마 (데이터베이스)의 이름입니다.
• TABLE_NAME
뷰(VIEW)의 이름입니다.
• VIEW_DEFINITION
뷰의 정의를 제공하는 SELECT 문입니다. 이 컬럼에는 SHOW CREATE VIEW가 생성하는 Create Table 컬럼에 표시되는 내용이 대부분 있습니다. SELECT 전에 단어를 건너 뛰고 WITH CHECK OPTION 단어를 건너 뜁니다.
원본 명령문이 다음과 같다고 가정합니다.
그러면 뷰 정의는 다음과 같습니다.
SELECT s2,s1 FROM t WHERE s1 > 5 ORDER BY s1
• CHECK_OPTION
CHECK_OPTION 속성의 값입니다. 값은 NONE, CASCADE 또는 LOCAL 중 하나입니다.
• IS_UPDATABLE
MySQL은 CREATE VIEW 시간에 뷰 업데이트 가능성 플래그라는 플래그를 설정합니다. UPDATE 및 DELETE(및 유사한 작업)가 뷰에 대해 적법한 경우 플래그는 YES (true)로 설정됩니다. 그렇지 않으면 플래그가 NO (거짓)로 설정됩니다.
VIEWS 테이블의 IS_UPDATABLE컬럼은 이 플래그의 상태를 표시합니다.
뷰를 업데이트 할 수 없는 경우 UPDATE, DELETE 및 INSERT와 같은 문은 실행이 거부됩니다.(뷰가 업데이트 가능하더라도 삽입이 불가능할 수 있습니다.)
뷰가 하나 이상의 다른 뷰에 의존하고 이러한 기본 뷰 중 하나가 업데이트 된 경우 IS_UPDATABLE 플래그는 신뢰할 어려울 수 있습니다.
IS_UPDATABLE 값에 관계없이 서버는 뷰의 업데이트 가능성을 추적하고 업데이트할 수 없는 뷰에 대한 데이터 변경 작업을 올바르게 거부합니다.
뷰에 대한 IS_UPDATABLE 값이 기본 뷰의 변경으로 인해 부정확해진 경우 뷰를 삭제하고 다시 생성하여 값을 업데이트 할 수 있습니다.
• DEFINER
보기를 만든 사용자의 계정 ( 'user_name'@ 'host_name'형식)입니다.
• SECURITY_TYPE
보기 SQL SECURITY 특성. 값은 DEFINER 또는 INVOKER 중 하나입니다.
• CHARACTER_SET_CLIENT
뷰가 생성되었을 때 character_set_client 시스템 변수의 세션 값입니다.
• COLLATION_CONNECTION
뷰가 작성되었을 때 collation_connection 시스템 변수의 세션 값입니다.
• 참고사항
MySQL은 지원할 SQL구문 유형을 서버에 알리기 위해 다른 sql_mode 설정을 허용합니다. 예를 들어 ANSI SQL 모드를 사용하여 MySQL이 쿼리에서 표준 SQL연결 연산자인 이중 막대(||)를 올바르게 해석하도록 할 수 있습니다.
그런 다음 항목을 연결하는 뷰를 만들면 sql_mode 설정을 ANSI와 다른 값으로 변경하면 뷰가 유효하지 않게 될 수 있습니다. 그러나 이것은 사실이 아닙니다. 뷰 정의를 어떻게 작성하든 MySQL은 항상 동일한 방식으로 표준 형식으로 저장합니다.
다음은 서버가 이중 막대 연결 연산자를 CONCAT() 함수로 변경하는 방법을 보여주는 예입니다.
mysql> SET sql_mode = 'ANSI';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE VIEW test.v AS SELECT 'a' || 'b' as col1;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
+----------------------------------+
| VIEW_DEFINITION |
+----------------------------------+
| select concat('a','b') AS `col1` |
+----------------------------------+
1 row in set (0.00 sec)뷰 정의를 표준 형식으로 저장하는 이점은 나중에 sql_mode 값을 변경해도 뷰의 결과에 영향을주지 않는다는 것입니다. 그러나 추가 결과는 SELECT 이전의 주석이 서버에 의해 정의에서 제거된다는 것입니다.
'Databases > MySQL' 카테고리의 다른 글
| [MySQL] Information Schema - InnoDB : 2번째 (0) | 2020.12.22 |
|---|---|
| [MySQL] Information Schema - InnoDB : 1번째 (0) | 2020.12.22 |
| [MySQL] Information Schema - System : 1번째 (0) | 2020.12.20 |
| [MySQL] FEDERATED Storage Engine - 실무 (0) | 2020.12.17 |
| [MySQL] Federated Sotrage Engine - 이론편 (0) | 2020.12.15 |