336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.


[MySql] auto_increment 값 알아오기


컬럼타입이 auto_increment이면 insert 시 값을 지정하지 않아도 알아서 자동으로 증가된 값을 사용한다. 그러나 문제는 PK로 사용한 경우 insert 후 그 값을 알 수가 없다는 것이다. 그래서 MySQL에서는 가장 최근에 사용된 auto_increment값을 알수 있게 해주는 함수를 제공한다.


LAST_INSERT_ID()

이 함수는 connection기반으로 가장 최근에 사용된 auto_increment 값을 리턴한다. 사용할 대는 아래와 같이 간단하게.


select LAST_INSERT_ID();


이런 식으로 사용하면 된다.


참고로 auto_increment를 사용할 때 임의로 시작값을 설정하기 위한 것..

alter table "tbl_name" auto_increment = xxx


=================================================


3.6.9. Using AUTO_INCREMENT

The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:


CREATE TABLE animals (


             id MEDIUMINT NOT NULL AUTO_INCREMENT,


             name CHAR(30) NOT NULL,


             PRIMARY KEY (id)


             );


INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'),


                                  ('lax'),('whale'),('ostrich');


SELECT * FROM animals;

Which returns:


+----+---------+


| id | name    |


+----+---------+


|  1 | dog     |


|  2 | cat     |


|  3 | penguin |


|  4 | lax     |


|  5 | whale   |


|  6 | ostrich |


+----+---------+

You can retrieve the most recent AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. These functions are connection-specific, so their return value is not affected by another connection also doing inserts.


Note: For a multiple-row insert, LAST_INSERT_ID()/mysql_insert_id() actually returns the AUTO_INCREMENT key from the first of the inserted rows. This allows multiple-row inserts to be reproduced correctly on other servers in a replication setup.


For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column)+1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.


CREATE TABLE animals (


             grp ENUM('fish','mammal','bird') NOT NULL,


             id MEDIUMINT NOT NULL AUTO_INCREMENT,


             name CHAR(30) NOT NULL,


             PRIMARY KEY (grp,id)


             );


INSERT INTO animals (grp,name) VALUES('mammal','dog'),('mammal','cat'),


                  ('bird','penguin'),('fish','lax'),('mammal','whale'),


                  ('bird','ostrich');SELECT * FROM animals ORDER BY grp,id;

Which returns:


+--------+----+---------+


| grp    | id | name    |


+--------+----+---------+


| fish   |  1 | lax     |


| mammal |  1 | dog     |


| mammal |  2 | cat     |


| mammal |  3 | whale   |


| bird   |  1 | penguin |


| bird   |  2 | ostrich |


+--------+----+---------+

Note that in this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused.)


If the AUTO_INCREMENT column is part of multiple indexes, MySQL will generate sequence values using the index that begins with the AUTO_INCREMENT column, if there is one. For example, if the animals table contained indexes PRIMARY KEY (grp, id) and INDEX (id), MySQL would ignore the PRIMARY KEY for generating sequence values. As a result, the table would contain a single sequence, not a sequence per grp value. 


==================================================================



LAST_INSERT_ID()

LAST_INSERT_ID(expr)

Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column.

mysql> SELECT LAST_INSERT_ID();        -> 195

커넥트 단워로 값이 유지됨으로 다른 사용자에 의해서 값이 바뀌지 않습니다.

The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions. The value of LAST_INSERT_ID() is not changed if you update the AUTO_INCREMENT column of a row with a non-magic value (that is, a value that is not NULL and not 0). If you insert many rows at the same time with an insert statement, LAST_INSERT_ID() returns the value for the first inserted row. The reason for this is to make it possible to easily reproduce the same INSERT statement against some other server. If you use INSERT IGNORE and the record is ignored, the AUTO_INCREMENT counter still is incremented and LAST_INSERT_ID() returns the new value. 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:

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);

Use the table to generate sequence numbers like this:

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

The UPDATE statement increments the sequence counter and causes the next call to LAST_INSERT_ID() to return the updated value. The SELECT statement retrieves that value. The mysql_insert_id() C API function can also be used to get the value. See section 21.2.3.33 mysql_insert_id().

You can generate sequence

'IT > MySQL' 카테고리의 다른 글

mysql event 조회 확인  (0) 2015.06.12
MYSQL Data type  (0) 2015.06.01
Mysql 날짜 함수  (0) 2015.04.24
Mysql 반올림, 올림, 버림.  (0) 2015.04.20
MYSQL 에서 SP exit. 중간에 나가기.  (0) 2015.04.15
Posted by 당양부부34

블로그 이미지
주요 토렌트를 블로깅하고 있습니다. 토렌트 순위 등은 다른 사이트를 찾아보세요. 주요 웹툰 순위도 게재했어요 경제를 좋아하는 일산의 행복한 프로그래머입니다.
당양부부34
Yesterday
Today
Total

달력

 « |  » 2024.3
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31

최근에 올라온 글

최근에 달린 댓글

최근에 받은 트랙백

글 보관함