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값보다 큰 값이면, 원래부터 존재하지 않았던 데이터로 구분하면 되지 않을까?
라고 생각했는데, 다시 생각해보니 틀린 생각.
key field | field 1 | ... | key field n |
1 | data | ... | data |
2 | data | ... | data |
3 | data | ... | data |
... | |||
98 | data | ... | data |
99 | data | ... | data |
100 | data | ... | data |
위와 같을때, "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( ) ;
출처: //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값을 얻어왔다
(문제가 생기면.. 그때 수정하자 ^^ㅠ)