[MySql] auto_increment 값 알아오기
네이년에서는 copy&paste가 안되니 직접 쓸 수밖에..ㅡ.ㅡ;;;
=======================================
컬럼타입이 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) 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();
TheUPDATEstatement increments the sequence counter and causes the next call toLAST_INSERT_ID()to return the updated value. TheSELECTstatement retrieves that value. Themysql_insert_id()C API function can also be used to get the value. See section 21.2.3.33mysql_insert_id().
LAST_INSERT_ID(), but the utility of using the function this way is that the ID value is maintained in the server as the last automatically generated value. It is multi-user safe because multiple clients can issue the UPDATE statement and get their own sequence value with the SELECT statement (or mysql_insert_id()), without affecting or being affected by other clients that generate their own sequence values. Note that mysql_insert_id() is only updated after INSERT and UPDATE statements, so you cannot use the C API function to retrieve the value for LAST_INSERT_ID(expr) after executing other SQL statements like SELECT or SET. 
