PHP 获取自增长字段数值 (MySQL的last_insert_id())

摘要:最近和朋友打算搞个网站,试水阶段不想投入太多资金,同时要有现成的开源代码可参考,所以PHP是首选了,一个虚拟主机一年才400块,1G空间还和MySQL共享空间,真便宜啊~~~     以前一直用.net开发,习惯了强类型语言的严谨,突然使用动态的PHP,还有些不太适应,言归正传吧。     电商网站最核心的订单的处理,如何在大并发时候产生连续的订单编号是一个关键。我们首先想到的自然是自

最近和朋友打算搞个网站,试水阶段不想投入太多资金,同时要有现成的开源代码可参考,所以PHP是首选了,一个虚拟主机一年才400块,1G空间还和MySQL共享空间,真便宜啊~~~

timg (14).jpg

以前一直用.net开发,习惯了强类型语言的严谨,突然使用动态的PHP,还有些不太适应,言归正传吧。

电商网站最核心的订单的处理,如何在大并发时候产生连续的订单编号是一个关键。我们首先想到的自然是自增长字段,我们如何获得这个自增长字段呢?

SQL Server数据库可以使用存储过程,获取Scope内插入数据的Identity数值,MySQL如何实现呢?
查阅资料后,我发现PHP已经封装了MySQL的last_insert_id() API,经测试在独立连接的情况下,完全没有问题。此函数返回的是当前连接最后一次插入数据的值(与表无关),那么如果是持久连接会是怎么样呢?还是用代码来测试一下吧,事实胜于雄辩。

1、立即结束的页面

<?php $pdo = new PDO(\'mysql:dbname=test;host=localhost;\', \'root\', \'\'); $pdo->setAttribute(PDO::ATTR_PERSISTENT, true); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $pdo->exec("INSERT INTO testtable (custname) values (\'insert1\')"); echo \'Last insert id is \' . $pdo->lastInsertId(); ?>

2、休眠10秒的页面

<?php $pdo = new PDO(\'mysql:dbname=test;host=localhost;\', \'root\', \'\'); $pdo->setAttribute(PDO::ATTR_PERSISTENT, true); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $pdo->exec("INSERT INTO testtable (custname) values (\'insert2\')"); sleep(10); echo \'Last insert id is \' . $pdo->lastInsertId(); ?> 执行页面2后,不停舒心页面1,ID不断增加,待页面2执行完毕后查看,页面2获取的ID并非随着页面1增加,也就是说在持久连接的情况下,Last_Insert_ID是有效的!

查看MySQL的连接情况:

mysql> show processlist;
+----+------+----------------+------+---------+------+-------+------------------
+
| Id | User | Host | db | Command | Time | State | Info
|
+----+------+----------------+------+---------+------+-------+------------------
+
| 1 | root | localhost:2206 | NULL | Query | 0 | NULL | show processlist
|
| 3 | root | localhost:2214 | test | Sleep | 2539 | | NULL
|
| 9 | root | localhost:2225 | test | Sleep | 8764 | | NULL
|
| 10 | root | localhost:2227 | test | Sleep | 2537 | | NULL
|
+----+------+----------------+------+---------+------+-------+------------------
+
4 rows in set (0.19 sec)

持久连接已经启用。

反思API的说明,我们可以认为在一个页面执行完毕(或者说期中数据库操作部分)前,其所获得的数据连接时没有返回到连接池中的,因此PDO::lastInsertId()也是仍然是当前连接,而不会返回成其他连接的数据。

希望高手批评指正。

补充,看到一篇转载的文章,作为本文补充阅读:

http://my.oschina.net/fz04003/blog/63327

PHP官网的Note:

如果想使用持久连接,必须在传递给 PDO 构造函数的驱动选项数组中设置PDO::ATTR_PERSISTENT。如果是在对象初始化之后用PDO::setAttribute()设置此属性,则驱动程序将不会使用持久连接。

所以以上测试是基于单次连接,并非持久链接,再次消毒!

 

<?php $DBOptions = array( PDO::ATTR_PERSISTENT => true, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION ); $pdo = new PDO(\'mysql:dbname=test;host=localhost\', \'root\', \'\', $DBOptions); // $pdo->setAttribute(PDO::ATTR_PERSISTENT, true); // $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $pdo->exec("insert into testid (owner) values(\'2\')"); sleep(5); $lastid = $pdo->lastInsertId(); echo \'Inserted ID is \' . $lastid; $pdo->exec("insert into saveid (owner, oid) values (\'2\', \'$lastid\')");

 

使用apache ab 进行休眠与非休眠两个进程测试:
D:xamppapachebinab.exe -n 5000 -c 5 http://localhost/PHPTest/PDOTest/Insert1.php

Server Software: Apache/2.2.11
Server Hostname: localhost
Server Port: 80


Document Path: /PHPTest/PDOTest/Insert2.php
Document Length: 20 bytes


Concurrency Level: 5
Time taken for tests: 20.213 seconds
Complete requests: 20
Failed requests: 0
Write errors: 0
Total transferred: 4840 bytes
HTML transferred: 400 bytes
Requests per second: 0.99 [#/sec] (mean)
Time per request: 5053.260 [ms] (mean)
Time per request: 1010.652 [ms] (mean, across all concurrent requests)
Transfer rate: 0.23 [Kbytes/sec] received


Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.4 0 1
Processing: 5024 5047 9.8 5048 5067
Waiting: 5023 5046 10.0 5047 5067
Total: 5024 5047 9.8 5049 5067


Percentage of the requests served within a certain time (ms)
50% 5049
66% 5051
75% 5052
80% 5052
90% 5067
95% 5067
98% 5067
99% 5067
100% 5067 (longest request)

MySQL进程
mysql> show processlist;
+----+------+-----------------+------+---------+------+---------------+---------
-------------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+----+------+-----------------+------+---------+------+---------------+---------
-------------------------------+
| 1 | root | localhost:18353 | test | Sleep | 10 | | NULL
|
| 2 | root | localhost:19964 | NULL | Query | 0 | NULL | show pro
cesslist |
| 3 | root | localhost:19968 | test | Sleep | 459 | | NULL
|
| 4 | root | localhost:20016 | test | Query | 0 | freeing items | insert i
nto testid (owner) values(\'1\') |
| 5 | root | localhost:20020 | test | Sleep | 2 | | NULL
|
| 6 | root | localhost:20023 | test | Query | 0 | freeing items | insert i
nto testid (owner) values(\'1\') |
| 7 | root | localhost:20025 | test | Sleep | 2 | | NULL
|
| 8 | root | localhost:20056 | test | Sleep | 2 | | NULL
|
| 9 | root | localhost:20058 | test | Sleep | 2 | | NULL
|
| 10 | root | localhost:20061 | test | Sleep | 0 | | NULL
|
| 11 | root | localhost:20193 | test | Query | 0 | freeing items | insert i
nto testid (owner) values(\'1\') |
| 12 | root | localhost:20594 | test | Query | 0 | freeing items | insert i
nto testid (owner) values(\'1\') |
| 13 | root | localhost:20614 | test | Sleep | 2 | | NULL
|
| 14 | root | localhost:21587 | test | Query | 0 | freeing items | insert i
nto testid (owner) values(\'1\') |
+----+------+-----------------+------+---------+------+---------------+---------
-------------------------------+
14 rows in set (0.00 sec)

测试结果,从数据库中查看返回数据正常,没有重复的ID。持久连接可行!

freeing items
The thread has executed a command. This state is usually followed by cleaning up.