지금까지 테이블을 만들면서 PK의 경우 auto_increment를 활용해왔다. 하지만 하나의 프로젝트당 테이블이 많아지니까 어떤 테이블의 PK인지 알기가 힘들어 이번에 시퀀스를 만들어 활용해보기로 했다. 오라클에는 시퀀스를 활용하기가 쉽다 하던데 mysql에는 시퀀스에 대한 개념이 없어서 내가 직접 만들어 사용해야 한다고 한다.
LAST_INSERT_ID() 함수의 문제점
mysql에서 시퀀스 만드는 방법을 구글링을 통해 찾아봤었다. 대부분 게시글에서 last_insert_id()라는 것을 활용했던데 last_insert_id()는 맨 마지막으로 인서트한 auto_increment로 돼 있는 PK의 데이터를 가져오는 함수인데 이걸로 시퀀스를 만들기엔 문제가 있었다.
CREATE TABLE tbl_category (
category_id INT PRIMARY KEY AUTO_INCREMENT,
category_name VARCHAR(20)
);나는 tbl_category라는 테이블을 만들었고, 이 테이블의 PK는 category_id이다.
INSERT INTO tbl_category(category_name) VALUES ('clothes');
INSERT INTO tbl_category(category_name) VALUES ('food');우선 테이블에 데이터를 하나씩 적재했을 땐
성공적으로 데이터가 생성된 것을 확인할 수 있고,
SELECT LAST_INSERT_Id();
가장 최근의 insert된 auto_increment의 칼럼인 category_id의 데이터값이 2임을 확인할 수 있다.
하지만 위의 insert와는 다른 방식으로 한 번에 여러개의 데이터를 적재할 경우 문제가 발생한다.
INSERT INTO tbl_category(category_name) VALUES
('home appliances'),
('office appliances');이런 식으로 insert 쿼리문을 작성하면
데이터는 성공적으로 적재된 것을 확인할 수 있다.
SELECT LAST_INSERT_Id();
하지만 LAST_INSERT_ID()의 경우 4가 아닌 3이 된다. LAST_INSERT_ID() 함수의 문제를 해결하려면 트랜잭션을 잘 활용하면 된다는 것 같던데... DB에 대해 아직 지식이 얕은 나에겐 이해가 안 되는 부분이 많았다. 구글링을 통해 찾은 자료들은 LAST_INSERT_ID() 함수를 활용한 예제였고, 쿼리가 이해가 안되니 사용하기가 찝찝했다. 그래서 내가 한 번 만들어보기로 했다..!
시퀀스를 만드는 함수를 직접 만들어보자!
우선 내가 사용할 PK에 대해 설명하자면
1. 8자리의 VARCHAR 데이터이다.
2. 앞에 4자리는 어떤 테이블의 PK인지 확인할 수 있는 문자, 뒤 4자리는 해당 데이터의 고유 인덱스 번호를 가진다.
예를 들어 고객테이블의 PK라면
'user0001', 'user0002', 'user0003', .... 이런 식으로 말이다..!
가장 먼저 user 테이블을 만들 것이다.
CREATE TABLE tbl_user (
user_id VARCHAR(8) PRIMARY KEY,
user_name VARCHAR(20) NOT NULL,
create_date DATETIME NOT NULL DEFAULT NOW());그리고 각 테이블들의 시퀀스 정보를 가지고 있는 sequence 테이블을 만들어준다.
CREATE TABLE tbl_sequence (
sequence_name VARCHAR(4) PRIMARY KEY,
last_no INT NOT NULL DEFAULT 0);sequence_name은 테이블을 설명해주는 문자(위의 예시에서 user와 같은 것이다.), last_no는 해당 테이블의 맨 마지막 인덱스 번호를 가리킨다.
INSERT INTO tbl_sequence(sequence_name) VALUES ('user');
SELECT * FROM tbl_sequence;
user 테이블의 sequence 데이터를 생성해주었고, 잘 적재된 것을 확인할 수 있다.
이제 진짜로 함수를 작성해보자!
DROP FUNCTION IF EXISTS get_sequence; //get_sequence란 함수가 존재하면 해당 함수를 삭제한다.
DELIMITER $$
//get_sequence란 함수를 만들 것이다.
//파라미터로 VARCHAR(4) 데이터타입의 데이터를 입력받고, VARCHAR(8) 데이터타입의 데이터를 리턴하는 함수이다.
CREATE FUNCTION get_sequence(p_sequence_name VARCHAR(4)) RETURNS VARCHAR(8)
BEGIN
DECLARE r_last_no INT;
DECLARE r_sequence VARCHAR(8);
//tbl_sequence 테이블의 last_no를 +1한다.(
UPDATE tbl_sequence SET last_no=last_no+1 WHERE sequence_name=p_sequence_name;
//원하는 p_sequence_name의 last_no를 검색한다. last_no는 +1 돼 있을거고 해당 last_no를 현재 insert하는 PK의 인덱스로 넘겨줄 것이다.
SELECT last_no INTO r_last_no FROM tbl_sequence WHERE sequence_name=p_sequence_name;
//CANCAT 함수를 이용해 시퀀스이름+인덱스값을 연결하고 r_sequence 변수에 저장한다.
SELECT CONCAT(p_sequence_name, LPAD(r_last_no, 4, '0')) INTO r_sequence;
//r_sequence를 리턴한다.(현재 insert될 PK값이 될 것임.)
RETURN r_sequence;
END $$
DELIMITER ;INSERT INTO tbl_user(user_id, user_name) VALUES(get_sequence('user'), '김철수');
SELECT * FROM tbl_user;get_sequence() 함수를 이용해 user_id의 값을 저장하면
원하던 대로 PK가 저장된 것을 확인할 수 있다.
INSERT INTO tbl_user(user_id, user_name) VALUES
(get_sequence('user'), '김순이'),
(get_sequence('user'), '장선장'),
(get_sequence('user'), '강꽃님');
SELECT * FROM tbl_user;
LAST_INSERT_ID() 함수를 사용했을 때 발생했던 문제들도 잘 해결된 것을 확인할 수 있다.
마무리
많은 블로그에서 LAST_INSERT_ID() 함수를 사용해 시퀀스를 만든 것은 분명 이유가 있을 것이다. 하지만 아직 DB에 관해 지식이 얕은 나로써는 해당 함수를 사용했을 때 발생하는 문제를 해결할 방법에 대해 찾지 못해서 내가 직접 만들게 되었다.
(제 글을 보시게 되는 분들 중에서 제가 작성한 방법으로 사용해도 될지, 아니면 더 좋은 방법이 있을지, 또 아니면 LAST_INSERT_IN() 문제의 해결방법을 아시는 분께서는 제 메일이나 댓글로 말씀해주시면 감사하겠습니다. :P)
