[MySQL] LAST_INSERT_ID( expr ) 의 사용, Sequence 흉내내기.

[참고] http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id

SELECT last_insert_id() FROM T;

last_insert_id() function은 같은 Connection 에서 가장 최근에 insert 된 row의 auto increment column 의 값을 반환한다.

그런데, INSERT 전에 SELECT last_insert_id() 를 최초로 실행할 경우, 올바른 값을 가져오지 못한다.

( 0 이 조회됨. )

테스트 해 보려면 관리 메뉴에 들어가서 MySQL 서비스를 재시작 하고, 다시 MySQL 에 접속해서 쿼리를 날려보자.

LAST_INSERT_ID( expr )

그런데 위와 같이 last_insert_id() Function 안에 어떤 값을 인자로 넣을 수도 있다.

이런 경우 다음에 last_insert_id() 를 호출할 때 반환할 값을 셋팅하는 것이다.

이 글 맨 처음에 참고한 MySQL 웹사이트에 설명이 잘 나와있다.

< 일부 발췌 >

If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences:

1. Create a table to hold the sequence counter and initialize it:

   mysql> CREATE TABLE sequence (id INT NOT NULL);
   mysql> INSERT INTO sequence VALUES (0);
   

2. Use the table to generate sequence numbers like this:

   mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
   mysql> SELECT LAST_INSERT_ID();

SELECT LAST_INSERT_ID(컬럼명) FROM 테이블명;

위에서 box 안의 발췌한 글대로 Oracle DB의 sequence를 흉내낼 수도 있을 것 같다.

sequence 가 필요한 개수대로 table을 만드는 것은 좀 부담스러우니, sequence 테이블을 하나 만들고, sequence 가 필요한 개수만큼 컬럼을 만들어서, 

UPDATE sequence SET a_table_seq = LAST_INSERT_ID( a_table_seq + 1 );

SELECT LAST_INSERT_ID();

위와 같이 사용할 수 있을것 같다.

위 2개의 SQL을 간단히 Function 으로 만들어서,

fn_get_seq('테이블명') 을 실행하면 해당 테이블 용으로 만든 컬럼값을 반환 받아서 sequence 로 사용하면 될것 같다.

---

CommCodeMapper.xml

     <select id="getSequence" parameterType="string" resultType="long">
     	/* SQL_ID : CommCodeMapper - getSequence */
	SELECT fn_get_seq(#{seq_name})
     </select>

-- SEQUENCE 를 가져오는 Function

DROP FUNCTION IF EXISTS test.fn_get_seq;

DELIMITER $$

CREATE FUNCTION fn_get_seq(p_seq_name VARCHAR(30))

RETURNS BIGINT READS SQL DATA

BEGIN

DECLARE RESULT_ID BIGINT;

UPDATE t_sequence SET id=LAST_INSERT_ID(id+1) WHERE seq_name = p_seq_name;

SET RESULT_ID = (SELECT LAST_INSERT_ID());

RETURN RESULT_ID;

END $$

DELIMITER ;

CREATE TABLE T_SEQUENCE

(

SEQ_NAME     VARCHAR(30) NOT NULL,

ID                   BIGINT NOT NULL,

PRIMARY KEY (SEQ_NAME)

);

Spring Framework 에서 사용할 때 주의할 점은 Service 에서 getSequence() 메서드를 만들어서, 사용하고자 하는 Service 파일과 다른 Service 파일에 두고, 호출해야 sequence를 얻어오는 동작이 다른 메서드와 별개의 트랜잭션으로 실행된다.

물론 getSequence() 메서드는 @Transactional(propagation = Propagation.REQUIRES_NEW) 어노테이션을 붙여야 한다.

@Override

@Transactional(propagation = Propagation.REQUIRES_NEW)

public long getSequence(String seqName)

{

long id = commDAO.getSequence(seqName);

return id;

}

그 이유는 Service 클래스를 Spring Framework 에서 생성할 때 proxy 를 생성하면서 트랜잭션 처리하는 코드를 넣기 때문이다. 그래서 다른 Service 클래스의 메서드를 호출해야 어노테이션으로 선언한 트랜잭션이 적용되는 것이다.

호출할 때는 이렇게 사용.

long dbSessionId = commCodeService.getSequence("테이블명");