Mysql auto_increment 값 가져오기 - mysql auto_increment gabs gajyeoogi

key_field  : auto_increment
field(1)
...
field(n)

Key Field를 auto_increment 형태로 하여, Insert를 하던 중

select 문을 이용하여 Where 조건에 key_field 값을 넣었을때, Record가 한개도 존재하지 않는다면, 이경우 이 Data는 원래부터 Insert 되지 않았던 Data였을까? 아니면 Insert후 Delete된 Data일까?

처음엔 간단하게 Max(key_field) 값을 가지고, Key_Field의 Max값보다 작으면 삭제된 데이터,
Key_Field의 Max값보다 큰 값이면, 원래부터 존재하지 않았던 데이터로 구분하면 되지 않을까?
라고 생각했는데, 다시 생각해보니 틀린 생각.

위와 같을때, "select max(key field) from 테이블명"을 하게 되면 100이 나오므로, 정상적인 결과를 얻을 수 있겠지만,
99, 100번 레코드를 삭제한후 "select max(key field) from 테이블명"을 하게되면 max값은 98이 나오므로, 실제로 99와 100번 레코드의 경우는 삭제되었음을 알지 못하고, 이전에 입력이 되지 않았던 데이터로 분류되게 된다.

key Field가 Auto_increment 속성을 가지고 있으므로,

show table status where name = '테이블명' 을 이용하면 해당 Table의 Auto_Increment 값을 확인 할 수 있으므로, 이를 이용하여 처리하면 된다.

어쨌든, 설명이 장황한데.... 결론은 결국 원하는 Table의 Auto_Increment 값을 가져오고자 할때 어떻게 할 것인가? 이므로, 위의 사설은 다 집어치우고,
기억해야할 쿼리는 단 한가지.

show table status where name = '테이블명'

리그캣의 개발놀이터

데이터베이스

해당 테이블의 최신 auto_increment값 가져오기

리그캣 2018. 6. 4. 15:54

만약 게시판을 만든다 치자.

게시판을 create 함과 동시에 파일을 첨부하게 되면 해당 파일은 

게시판의 아이디를 포함하게 된다.

게시판의 아이디가 auto_increment라면 해당 값을 가져와야 한다.

SELECT AUTO_INCREMENT

FROM information_schema.tables

WHERE table_name = 'table name'

AND table_schema = DATABASE( ) ;

해당을 사용하고

INSERT INTO

spring_boardfile

(

b_no,

f_ori_name,

f_sto_name,

f_size,

f_crea_id

)

VALUES

(

(SELECT AUTO_INCREMENT

FROM information_schema.tables

WHERE table_name = 'spring_board'

AND table_schema = DATABASE( ))-1

,

#{f_ori_name},

#{f_sto_name},

#{f_size},

'Admin'

)

나같은 경우에는 다음과 같이 사용하였다

query를 사용하다보면 insert시 다음 auto_increment 값이 필요할 때가 있다.

select LAST_INSERT_ID() 를 사용할 수도 있지만, LAST_INSERT_ID()는 table을 선택할 수 없다.

그래서 아래의 query를 사용하면 다음 auto_increment의 값을 얻을 수 있다.

SELECT AUTO_INCREMENT

FROM information_schema.tables

WHERE table_name = 'table name'

AND table_schema = DATABASE( ) ;

출처: https://wkdgusdn3.tistory.com/entry/데이터베이스-다음-autoincrement-값-받아오기 [장삼의 착한코딩]

query를 사용하다보면 insert시 다음 auto_increment 값이 필요할 때가 있다.

select LAST_INSERT_ID() 를 사용할 수도 있지만, LAST_INSERT_ID()는 table을 선택할 수 없다.

그래서 아래의 query를 사용하면 다음 auto_increment의 값을 얻을 수 있다.

SELECT AUTO_INCREMENT

FROM information_schema.tables

WHERE table_name = 'table name'

AND table_schema = DATABASE( ) ;

MySql 에서 primary key 값이 auto_increment 인 경우, row가 추가 됨에 따라 자동으로 증가된 값이 입력된다.

그럼 row 추가 전에 입력 예정 값은 어떻게 알수 있을까?

버전이 5 이상인 경우

select Auto_increment from information_schema.tables where table_schema = '[db_name]' and table_name = '[table_name]'

버전이 4.5 이상인 경우

show table status from [db_name] like '[table_name]';

그리고....

증가값을 초기화 하는 방법

/* 데이터가 있을때 */

alter table [table_name] auto_increment = 1;


/* 데이터가 없을때 */

truncate table [table_name];

MySQL에서 AUTO INCREMENT를 사용하다보면 INSERT 쿼리를 실행하며 사용된 AUTO INCREMENT 값을 필요로 할 때가 있습니다.

이 때 사용할 수 있는 함수가 LAST_INSERT_ID()라는 함수 입니다.

이 함수를 사용하면 가장 최근에 수행된 AUTO INCREMENT 값을 반환해줍니다.

INSERT INTO TB_TEST(TEST1, TEST2)VALUES('aaaa','bbbb');

SELECT LAST_INSERT_ID();

위와 같이 INSERT 후에 SELECT LAST_INSERT_ID()를 실행하면 바로 전 INSERT 에서 수행된 AUTO INCREMENT 값을 반환 받을 수 있습니다.

코드로 배우는 스프링 웹 프로젝트 책은 오라클을 사용하고 있는데

나는 mysql로 실습을 하고 있다

이유는 mysql은 써봤고 오라클은 한번도 안써봤으므로..

근데 생각보다 오라클하고 mysql이 다른 점이 많아서 ㅠㅠ 뭐 하나 할때마다 애먹고 있다

(덕분에 공부가 되는거 같기도 하궁;)

​

아무튼 공부 중 막힌 것이 오라클에서 nextval을 mysql에서 어떻게 구현하는가!

방법 1. 'SELECT MAX(컬럼명) FROM 테이블명' 으로 얻어오기

​

이게 가장 간단히 생각할 수 있는 방법인 듯!

근데 나는 MAX로 얻어온 값이 반드시 auto_increment의 다음 값이라는 보장이 없다고 생각했돠..

왜냐하면 가장 마지막으로 삽입된 인덱스 값이 예를 들어 11인 경우,

해당 레코드를 삭제해버리면 MAX로 얻어온 값은 10이 되는데

실제로 다음 레코드를 삽입하면 anto_increment로 삽입되는 값은 12가 되기 때문이다

(삭제된 인덱스까지 반영됨)

​

방법 2. information_schema의 TABLES 테이블에서 auto_increment 값 얻어오기

​

auto_increment 값이 데이터베이스에 메타데이터로 저장되어 있지 않을까 하는 생각!

실제로 information_schema.TABLES 테이블에 auto_increment 값이 저장되어 있었다

내 생각엔 이걸 SELECT해서 가져오면 되지 않을까 했는데..

레코드를 아무리 삽입해도 auto_increment 값이 업데이트가 되지 않는 것이었다 ㅜㅜ,,

그 이유를 스택오버플로우에서 찾았다

auto_increment값은 디스크가 아닌 메모리에 저장되기 때문이라고 한다

따라서 올바른 값을 얻어오기 위해서는

ANALIZE TABLE 쿼리를 날려줘야 한다는 것인데,, 성능 문제가 발생할 수 있으니 추천하지 않는다는 것

음.. 그렇구나..^^ 이것도 모르고 열심히 삽질을 했다ㅠㅠ

​

방법3. 'SELECT LAST_INSERT_ID()'을 사용

LAST_INSERT_ID()가 가장 최근에 auto_increment로 삽입된 인덱스를 얻어오는 함수라고 한다

근데 만약에 auto_increment를 사용하는 테이블이 여러개라면.. 내가 원하는 값을 제대로 얻어올 수 있을까 의문이 들었다

사실 LAST_INSERT_ID()가 어떻게 동작하는지 아직 잘 모르겠다...ㅎㅎ;

결론: 어떻게 해야할 지 모르겠다-!^^

​

일단 책 예제에서는 다음 auto_increment 값을 얻어오는 기능이 당장 쓰이지 않는거 같으니

나중에 진짜 필요할 때 생각해보기로;;

(정 구현이 어렵다면 auto_increment 값을 저장하는 테이블을 따로 만들어야 할 것 같다)

​

+

일단 책 예제에서

​

<insert id="insertSelectKey">

​

<selectKey keyProperty="bno" order="BEFORE" resultType="long">

select seq_board.nextval from dual

</selectKey>

​

insert into tbl_board(bno, title, content, writer)

values(#{bno}, #{title}, #{content}, #{writer})

</insert>

​

위와 같이 구현한 것을

​

<insert id="insertSelectKey">

​

insert into tbl_board(title, content, writer)

values(#{title}, #{content}, #{writer})

​

<selectKey keyProperty="bno" order="AFTER" resultType="long">

select LAST_INSERT_ID()

</selectKey>

​

</insert>

​

이렇게 바꿔서 구현했다

책의 예제는 bno값을 얻어오고 insert하는 건데 일단 나는 insert 후에 LAST_INSERT_ID()로 bno값을 얻어왔다

(문제가 생기면.. 그때 수정하자 ^^ㅠ)