[SQL] MySQL - Advanced 01

1. Transactions

• (다 성공하던지 혹은 다 실패하던지 해야 하는) 여러 쿼리문을 묶어서 하나의 작업처럼 처리하는 방법

START TRANSATION; # 트랜잭션 구문의 시작을 알림
state_ments;
...
[ROLLBACK||COMMIT];
# COMMIT : 	모든 작업이 정상적으로 완료되면 한꺼번에 DB에 반영
# ROLLBACK : 부분적으로 작업이 실패하면 트랜잭션에서 진행한 모든 연산을 취소하고 트랜잭션 실행 전으로 되돌림

[출처] multicampus

① Transaction practice ROLLBACK vs COMMIT

 

• SQL은 자동 commit 상태인데 ROLLBACK을 위해서 commit 비활성화 시킴 
• 테이블 생성

 

• 자동 commit을 비활성화 시킨 상태이기 때문에 저장은 되지 않음
• ROLLBACK전 TRANCATION 실행 - INSERT INTO 실행

 

• 다시 되돌리기 ROLLBACK

 

• AUTO_INCREMENT 때문에 id는 그대로 카운팅
• COMMIT 하면 최종 저장

 

2. Triggers

• 특정 이벤트(INSERT, UPDATE, DELETE)에 대한 응답으로 자동으로 실행되는 것
• ~를 추가한 후에 ~하겠다 / ~를 수정하기 전에 ~하겠다 / ~를 삭제한 후에 ~하겠다

CREATE TRIGGER trigger_name # 다음에 생성하려는 트리거의 이름 지정
	{BEFORE|AFTER} {INSERT|UPDATE|DELETE} # 각 레코드의 어느 시점에 트리거가 실행될지 지정
    ON table_name FOR EACH ROW # ON 키워드 뒤에 트리거가 속한 테이블의 이름 지정
    trigger_body; # 트리거가 활성화될 때 실행할 코드를 지정
    # 여러 명령문을 실행하려면 BEGIN END 키워드로 묶어서 사용
    
# 트리거는 DML의 영향을 받는 필드 값에만 적용할 수 있음

 

① Triggeres practice

• 트리거를 사용해 기존 게시글이 수정되면, 게시글의 수정일자 필드 값을 최신 일자로 업데이트하기

⎣ updatedAt 후의 필드에 CURRENT_TIME

⎣ 트리거에서 특정 시점 전/후의 값에 접근할 수 있도록 제공하는 키워드 : NEW / OLD

⎣ 위의 구문 실행 시 다음과 같이 나타나는데 처음 DELIMITER 옆의 슬래쉬 지우고 실행해야 함..

 

▶ 기억하기!!

반드시 기억하기!!!

⎣ INSERT OLD는 삽입 전이라 없고

⎣ DELETE NEW는 삭제 후라 없음

 

 

⎣ UPDATE 후 확인

 

 

➁ 트리거를 사용해 기존 게시글이 작성되면, 별도의 테이블에 해당 게시글이 작성되었다는 것을 기록하기

⎣ 테이블 생성

⎣ 트리거 생성하고 기록하기

⎣  게시글이 작성된 후 : AFTER INSERT

⎣  아티클 테이블에서 INSERT이벤트가 끝난 후에  recordLogs 트리거를 생성할 거야: ON articles FOR EACH ROW

⎣ ~ 하겠다 : BEGIN ~ END

    ⎣  articleLogs에 글을 작성할 거야 

 

➂ ➁번문제 심화) 트리거를 사용해 기존 게시글이 작성되면, 별도의 테이블에 몇 번 게시글이 작성되었다는 것을 기록하기

 

➃ 트리거를 사용해 기존 게시글이 삭제되면, 삭제된 게시글의 구조 그대로 별도의 테이블에 기록하기

테이블 생성

풀이 확인

---

▶ 기억하기!!

# 트리거 목록 확인
SHOW TRIGGERS;

# 트리거 삭제
DROP RIGGER trigger_name;

 

▶ 에러 해결

• 트랜잭션 생성 후 정상 적으로 종료되지 않아 발생하는 에러 발생 시 해결법
• Error Code : 2013.Lost connection to MySQL server during query
• Error Code : 2015. Lock wait timeout exceeded; try restarting transation

# 실행중인 프로세스 목록 확인
SELECT * FROM information_schema.INNODB_TRX;

# 특정 프로세스의 trx_mysql-thread_id 삭제
KILL [trx_mysql_thread_id1];