[MySQL] Query CheetSheet

ROW_FORMAT 일괄 적용 쿼리 가져오기

SELECT CONCAT('ALTER TABLE `', table_name, '` ROW_FORMAT=DYNAMIC;') AS aQuery
FROM information_schema.tables
WHERE table_schema = 'SCHEMA NAME';

Index 생성 쿼리 가져오기

SELECT CONCAT_WS('', 'alter table ',
      table_name,
      IF( index_name ='PRIMARY',
       ' add primary key',
       IF( non_unique =1, 
        CONCAT_WS('',' add index ', index_name ),
        CONCAT_WS('',' add unique index ', index_name )
       )
       ),
      '(',
      GROUP_CONCAT(column_name ORDER BY seq_in_index SEPARATOR ','),
      ');' ) AS idxqry
FROM information_schema.statistics
WHERE table_schema = 'TABLE NAME'
GROUP BY table_name, index_name;

10초 이상 진행중인 sql query check query

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where time > 10 and info is not null;
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;

DB Size Check group by table in Schema

SELECT table_schema "DB Name",
        ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM information_schema.tables 
GROUP BY table_schema; 

DB Dump

mysqldump -u root -p --databases {database} --master-data=2 --default-character-set=utf8 --opt --single-transaction --hex-blob --routines --triggers > /{저장경로및파일명}.sql

제약조건 조회

select * from information_schema.table_constraints;