mysql script examples

mysql table 모델링을 하려면 mysql workbench를 주로 사용한다. 모델링 쿼리 등에 아주 유용하게 사용할 수 있으니 툴 사용법을 배워 놓으면 좋다.

VERSION(major.minor.patch) 명을 숫자로 비교하기

select T.version , 
CAST(SUBSTRING_INDEX(T.version, '.', 1) AS SIGNED) major,
  CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(T.version, '.', -2),'.',1) AS SIGNED)  minor,
  CAST(SUBSTRING_INDEX(T.version, '.', -1) AS SIGNEd)  patch
from VER T
  order by major, minor, patch;

결과는 아래처럼. 스트링으로 비교하면 1.10.0이 1.2.0 보다 앞에 오게 되어서 숫자로 CAST 처리를 한 것임

version	major	minor	patch
‘1.2.0’	‘1’	‘2’	‘0’
‘1.10.0’	‘1’	‘10’	‘0’
‘2.0.0’	‘2’	‘0’	‘0’

alter/delete

alter/delete시 foreign_key constraint가 걸려 있으면 명령이 실패하는데, FOREIGN_KEY_CHECKS=0으로 해주면 이 제약을 없앨 수 있다.(아래 참고)

# delete all rows in a table
delete from table_name;

# alter table
set FOREIGN_KEY_CHECKS=0;
alter table table_name modify id BIGINT(20) NOT NULL AUTO_INCREMENT;

import csv file

# path should be full path
LOAD DATA LOCAL INFILE '/file.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (colum1, column2, column3);

subquery

# `ORDER_BY` 때문에 name을 제거하지 못해서 Select문으로 한번더 싼 예제. 서브쿼리 다음에 그 테이블을 대변하는 테이블 명(아래는 C)이 있어야 함.
select id from (select * from Customer ORDER_BY name) C;

create ERD from mysql workbench

1. Database > reverse engineer
2. select schema & filter tables you want to add to your diagram
3. done

removing foreign key constraint in production

https://dba.stackexchange.com/questions/15388/where-should-you-define-foreign-keys 여기를 보면 설명이 나오는데 트래픽이 많이 몰리는 db인 경우에는 lock으로 인해서 동시성이 떨어지는 현상이 발생하므로 foreign key를 쓰지 않을 수도 있다.

그래서 우리의 경우는 dev/stg tier에서는 constraint를 걸어 로직 검증을 한후, prd로 갈때는 제거하는 방식을 하고 있다.

References

1. mysql workbench-reserve engineering from script