[MYSQL] 정렬된 행 중간에 새로운 행 삽입과 삭제하기

프로젝트의 DB 구현 중 정렬된 행 사이에 새로운 행을 삽입하거나 삭제해야 하는 기능의 구현이 필요했다.

 

예를 들어 위와 같은 테이블에서 일련번호가 꼬이지 않게 하면서 점심식사와 낮잠 사이에 행 간식 행을 넣으려고 한다.

 

우선 정렬할 기준 필드로 일련번호를 삽입할 rowNo 필드를 생성했다.

 

AUTO_INCREMENT로 필드를 만들면 기본키이기 때문에 중간삽입같은 정렬에 사용할 수 없어서 직접 로직을 작성하여야 한다고 생각했다.

 

INSERT INTO schedule (planNo, userId, startDate, descript, planDay, rowNo) 
VALUES ('1', 'member1', '2021-05-10', '숙소 도착', '1', (SELECT COUNT(*) FROM schedule 일정 WHERE planNo = '1' AND userId = 'member1') + 1);

INSERT INTO schedule (planNo, userId, startDate, descript, planDay, rowNo) 
VALUES ('1', 'member1', '2021-05-10', '점심 식사', '1', (SELECT COUNT(*) FROM schedule 일정 WHERE planNo = '1' AND userId = 'member1') + 1);

INSERT INTO schedule (planNo, userId, startDate, descript, planDay, rowNo) 
VALUES ('1', 'member1', '2021-05-10', '산책', '1', (SELECT COUNT(*) FROM schedule 일정 WHERE planNo = '1' AND userId = 'member1') + 1);

위와 같이 INSERT문에 rowNo의 value에 서브쿼리를 추가해주었다.

 

(SELECT COUNT(*) FROM schedule 일정 WHERE planNo = '1' AND userId = 'member1') + 1);

서브쿼리의 내용은 해당 userId의 planNo에 있는 일정을 조회하여 행의 개수를 구한 뒤 그 값에 1을 더한 값을 리턴한다. 만약 일정이 3개가 있다면 다음 추가되는 일정의 rowNo의 값은 4가 될 것

 

원하는 대로 데이터 삽입이 되었다.

 

 

SET autocommit=0;

/*
데이터
처리
구문
*/

COMMIT;
ROLLBACK;

로직을 테스트하기 전에 작업하면서 데이터가 꼬이는 것을 방지하기 위해 트랜잭션 처리를 해주는 것을 추천한다.

 

행 중간 삽입하기

UPDATE schedule SET rowNo = rowNo + 1 WHERE rowNo <= @ROW;

INSERT INTO schedule (planNo, userId, startDate, descript, planDay, rowNo) 
VALUES ('1', 'member1', '2021-05-10', '간식','1', @ROW);

우선 UPDATE문으로 변경하고자 하는 행의 위의 값을 1씩 더해주어 자리를 만들어주고

 

INSERT문으로 빈 자리에 데이터를 삽입해준다.

 

깔끔하게 처리되었다.

 

 

중간 행 삭제하기

삽입에 문장 순서만 뒤집어주고 INSERT문을 DELETE문으로 바꾸어 주면 된다.

 

DELETE FROM SCHEDULE WHERE rowNo = @ROW;
UPDATE schedule SET rowNo = rowNo - 1 WHERE rowNo > @ROW;

바꾸고자 하는 행의 윗 행들의 rowNo 값을 한칸씩 당겨주어야 하기 때문에

SET rowNo = rowNo -1과 row > @ROW; 조건을 사용한다.

 

@ROW 변수에 4를 대입하여 낮잠 행을 삭제해보자.

 

 

잘 변경됐다.