mysql教程 LAST_INSERT_ID() mysql_insert_id 獲取insert數據的主鍵值自動編號
我們講了關於mysql LAST_INSERT_ID() mysql_insert_id 獲取insert數據的主鍵值自動編號那麼來分析一下它們的區別吧
mysql_insert_id ()是直接獲取當前session的insert_id，而LAST_INSERT_ID()是SQL函數，需要通過執行SQL來獲得，如：SELECT LAST_INSERT_ID();顯然這兩個效果是一樣的，但執行一次SQL要耗費很多資源，不如直接通過mysql_insert_id()獲得內存中的值快得多
$tablename ='test';
$conn = mysql_connect('127.0.0.1','root','12346');
$result = mysql_query ( 'UPDATE ' . $tablename . ' SET id=LAST_INSERT_ID(id+1)', $conn );
if ($result === false) {
mysql_query ( 'CREATE TABLE ' . $tablename . ' (id INT NOT NULL)', $dbh );
$rs = mysql_query ( 'SELECT COUNT(*) FROM ' . $tablename . ' LIMIT 1', $dbh );
if (mysql_result ( $rs, 0 ) == 0) {
mysql_query ( 'INSERT INTO ' . $tablename . ' VALUES (' . ($start_index - 1) . ')', $dbh );
}
mysql_query ( 'UPDATE ' . $tablename . ' SET id=LAST_INSERT_ID(id+1)', $dbh );
}
$return = mysql_insert_id ( $dbh );
//下面接著來看一個last_inert_id實例
$sql="insert into table (name1,name2,...) values('www.bKjia.c0m','bKjia.c0m'...)";
mysql_query($conn,$sql);
//找出最後一次插入記錄的id
$select="select last_insert_id() ";
$result=mysql_query($db_link,$select);
$rs = mysql_fetch_array( $result );
$last_id=$rs[0];
//再來看一個官方的實例
mysql> USE test;
Database changed
mysql> CREATE TABLE t (
-> id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> name VARCHAR(10) NOT NULL
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO t VALUES (NULL, 'Bob');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM t;
+----+------+
| id | name |
+----+------+
| 1 | Bob |
+----+------+
1 row in set (0.01 sec)
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO t VALUES
-> (NULL, 'Mary'), (NULL, 'Jane'), (NULL, 'Lisa');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t;
+----+------+
| id | name |
+----+------+
| 1 | Bob |
| 2 | Mary |
| 3 | Jane |
| 4 | Lisa |
+----+------+
4 rows in set (0.01 sec)
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)
總結
mysql_insert_id() 將 MySQL 內部的 C API 函數 mysql_insert_id() 的返回值轉換成 long（PHP 中命名為 int）。如果 AUTO_INCREMENT 的列的類型是 BIGINT，則 mysql_insert_id() 返回的值將不正確。可以在 SQL 查詢中用 MySQL 內部的 SQL 函數 LAST_INSERT_ID() 來替代。
本站原創轉載注明來源www.bKjia.c0m
