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.111cn.net','111cn.net'...)";
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.111cn.net
