[mysql/mariadb] 자주 사용 쿼리 & 메타데이터 (continue)

-- DBMS Version 확인
select @@version; --또는 select version();

-- 모든 시스템 변수 리스트
SHOW GLOBAL VARIABLES;
-- SHOW GLOBAL VARIABLES LIKE 'char%';
-- SHOW GLOBAL VARIABLES LIKE 'collation%';

-- database(=schema) 목록
SELECT DISTINCT table_schema FROM INFORMATION_SCHEMA.TABLES;

-- 현재 DB스미마
SET @current_db = DATABASE();
SELECT @current_db;
 
-- 사용자 정보
SELECT * FROM INFORMATION_SCHEMA.USER_PRIVILIEGES;
-- 실행중인 커맨드
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;

-- 테이블 구조/설명 조회
DESC INFORMATION_SCHEMA.TABLES;

-- 테이블/컬럼 구조
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'XXX'
SELECT * FROM INFORMATION_SCHEMA.COLUMNS;

-- 간단히 변수 선언해서 사용하기
SET @PARAM1 = 1; -- 변수명 앞에 @골뱅이 필수!!
SELECT @PARAM1;

-- 날짜 다루기
SELECT CAST(NOW()) as DATE);
SELECT STR_TO_DATE('20230111', '%Y%m%d');
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); // MINUTE, MONTH, YEAR
SELECT DATE_SUB(NOW(), INTERVAL 1 DAY); // 또는 DATE_ADD의 INTERVAL 값을 음수로 넣으면 같은 결과
SELECT DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y%m%d%H%i%s');

-- 유효체크
SELECT CASE WHEN STR_TO_DATE('2023XXXX') IS NOT NULL THEN '정상' ELSE '비정상' END; // 날짜문자열인지 체크
SELECT CASE WHEN '99' REGEXP '^[0-9]+$ = 1 THEN '숫자' ELSE '숫자아닌문자열포함' END;

MariaDB 레퍼런스: https://mariadb.com/kb/en/ (MariaDB Server > Tutorials)

-- 현재 설정 확인
SHOW VARIABLES LIKE 'max_connections'; -- Too many connection 확인.
-- 임시 증가 (재시작시 초기화됨)
-- SET GLOBAL max_connections = 200;

SHOW PROCESSLIST;

SHOW FULL PROCESSLIST;

-- 오래된 연결만 확인
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST 
-- WHERE TIME > 3600 -- 1시간
ORDER BY TIME DESC;

SELECT ID, USER, HOST, STATE, TIME 
	,CONCAT(
        IF(TIME >= 3600, CONCAT(FLOOR(TIME/3600), 'h '), ''),
        IF(TIME >= 60, CONCAT(FLOOR((TIME % 3600)/60), 'm '), ''),
        (TIME % 60), 's'
    ) AS time_formatted
FROM INFORMATION_SCHEMA.PROCESSLIST 
WHERE 1=1
  and COMMAND = 'Sleep' -- 1. Sleep: 유휴 상태/2. Query: 쿼리 실행 중/3. Connect: 연결 중/4. Binlog Dump: 복제 중/5. Killed: 종료 중
  and user = 'she_admin'
--   AND TIME > 1800 -- 30분 이상
  and HOST like '%10.10.10.10%'
ORDER by TIME DESC;

kill 8197;

▼ DB테이블 상세설계 조회

-- 테이블 목록
SELECT TABLE_NAME AS 테이블ID, TABLE_COMMENT AS 테이블명
	   ,DATE_FORMAT(CREATE_TIME, '%Y-%m-%d') AS 생성일
FROM information_schema.TABLES
WHERE 1=1
   AND TABLE_TYPE = 'BASE TABLE' -- SYSTEM VIEW, BASE TABLE, VIEW, SEQUENCE
   AND TABLE_SCHEMA in ('XXX','YYY')
   AND TABLE_NAME NOT LIKE '!_%' ESCAPE '!' /*미사용(prefix: _) 테이블 제거*/
ORDER BY TABLE_SCHEMA, TABLE_NAME;

-- 컬럼 목록 추출 
SELECT
    t.TABLE_SCHEMA,
    t.TABLE_NAME AS 테이블ID, t.TABLE_COMMENT AS 테이블명,
    c.ORDINAL_POSITION as COL_ORD, c.COLUMN_NAME AS 컬럼ID, c.COLUMN_COMMENT AS 컬럼명,
    CONCAT(c.COLUMN_TYPE) AS 데이터타입,
    CASE WHEN c.IS_NULLABLE = 'NO' THEN 'Y' ELSE '' END AS NOT_NULL,
    COALESCE(c.COLUMN_DEFAULT, '') AS 디폴트값,
    -- LEFT JOIN으로 외래 키 정보를 가져와서, k.REFERENCED_TABLE_NAME이 NULL이 아니면 'Y'
    CASE WHEN k.REFERENCED_TABLE_NAME IS NOT NULL THEN 'Y' ELSE '' END AS FK
FROM
    information_schema.COLUMNS c
    INNER JOIN
        information_schema.TABLES t ON c.TABLE_NAME = t.TABLE_NAME AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
    LEFT JOIN
        information_schema.KEY_COLUMN_USAGE k
        ON k.TABLE_SCHEMA = c.TABLE_SCHEMA
        AND k.TABLE_NAME = c.TABLE_NAME
        AND k.COLUMN_NAME = c.COLUMN_NAME
        AND k.REFERENCED_TABLE_NAME IS NOT NULL -- 외래 키 조건
WHERE 1=1
  AND t.TABLE_TYPE = 'BASE TABLE' -- SYSTEM VIEW, BASE TABLE, VIEW, SEQUENCE
  AND t.TABLE_SCHEMA in ('xxx','ttt')
GROUP BY t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME -- 중복 제거 (FK 정보가 여러 번 잡힐 수 있으므로)
ORDER BY c.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME;

▼ DB프로시저,함수 조회

-- DB프로시저/함수 목록 조회(본문 스크립트 포함)
SELECT 
     routine_schema AS '데이터베이스명'
    ,routine_name AS '루틴명'
    ,routine_type AS '타입'
    ,data_type AS '반환_데이터타입'
    ,routine_definition AS 'DDL스크립트'
    ,created AS '생성일시',last_altered AS '최종수정일시'
FROM 
    information_schema.ROUTINES
WHERE 1=1
  AND routine_schema IN ('스키마1', '스키마2')
ORDER BY 
    routine_type, routine_name;
    
-- DB프로시저/함수 전문(선언문+본문) 스크립트 조회
SHOW CREATE FUNCTION db_schema.함수이름;
SHOW CREATE PROCEDURE db_schema.프로시저이름;

▼ 백업테이블 생성

-- CREATE TABLE
CREATE TABLE 생성할_테이블명 
AS 
SELECT * FROM 원본_테이블명
WHERE 복사할_데이터_조건;

▼ 쉼표(,)로 연결된 값 목록 뽑기

-- 코드명(값), ... 
SELECT  GROUP_CONCAT(
        CONCAT(TRIM(cl_nm), '(', cl_cd, ')')
        SEPARATOR ', '
    ) AS combined_text
FROM comn_code where grp_cd = '공통코드그룹ID'

-- [MIG] AS-IS vs TO-BE 컬럼 목록
-- (필수조건: AS-IS/TO-BE 컬럼 순서를 동일하게 만들었을때)
SELECT
    COALESCE(T1.ORDINAL_POSITION, T2.ORDINAL_POSITION) AS ORD,
    T1.COLUMN_NAME AS COL_2,
    T1.COLUMN_COMMENT AS COMMT_2,
    T2.COLUMN_NAME AS COL_1,
    T2.COLUMN_COMMENT AS COMMT_1
FROM (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = 'TOBE테이블' AND TABLE_SCHEMA = 'db스키마'
      ) T1
LEFT JOIN
    (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'ASIS테이블' AND TABLE_SCHEMA = 'db스키마'
    ) T2
    ON T1.ORDINAL_POSITION = T2.ORDINAL_POSITION
WHERE 1=1
  UNION 
SELECT
    COALESCE(T1.ORDINAL_POSITION, T2.ORDINAL_POSITION) AS ORD,
    T1.COLUMN_NAME AS COL_2,
    T1.COLUMN_COMMENT AS COMMT_2,
    T2.COLUMN_NAME AS COL_1,
    T2.COLUMN_COMMENT AS COMMT_1
FROM (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = 'TOBE테이블' AND TABLE_SCHEMA = 'db스키마'
      ) T1
RIGHT JOIN
    (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'ASIS테이블' AND TABLE_SCHEMA = 'db스키마'
    ) T2
    ON T1.ORDINAL_POSITION = T2.ORDINAL_POSITION
WHERE 1=1