一、业务场景&＃xff1a;当客户下单在指定的时间内如果没有付款&＃xff0c;那我们需要将这笔订单取消掉&＃xff0c;比如好的处理方法是运用延时取消&＃xff0c;这里我们用到了swoole&＃xff0c;运用swoole的异步毫秒定时器不会影响到当前程序的运行&＃xff0c;具体参考&＃xff1a;https://wiki.swoole.com/wiki/page/319.html
二、说明&＃xff0c;order_status为1时代表客户下单确定&＃xff0c;为2时代表客户已付款&＃xff0c;为0时代表订单已取消(正是swoole来做的)&＃xff0c;下面的代表我没有用框架&＃xff0c;比较纯的PHP代表方便理解和应用
三、举例说明&＃xff0c;库存表csdn_product_stock产品ID为1的产品库存数量为20&＃xff0c;产品ID为2的库存数量为40&＃xff0c;然后客户下单一笔产品ID1减10&＃xff0c;产品ID2减20&＃xff0c;所以库存表只够2次下单&＃xff0c;例子中10秒后自动还原库存&＃xff0c;如下图&＃xff1a;
图解&＃xff1a;1、第一次下完单产品ID1库存从20减到了10&＃xff0c;产品ID2库存从40减到了20&＃xff1b;2、第二次下完单产品ID的库存为0了&＃xff0c;产品ID2的库存也为0了&＃xff0c;3、第三次下单时&＃xff0c;程序提示Out of stock&＃xff1b;4、过了10秒钟(每个订单下单后往后推10秒)&＃xff0c;客户两次下单&＃xff0c;由于没有付款(csdn_order表的order_status为1)&＃xff0c;产品1和产品2的库存被还原了(csdn_order表的order_status变为0)&＃xff0c;客户又可以继续下单了
1、所需要sql数据库表
DROP TABLE IF EXISTS &＃96;csdn_order&＃96;;
CREATE TABLE &＃96;csdn_order&＃96; (&＃96;order_id&＃96; int(10) unsigned NOT NULL AUTO_INCREMENT,&＃96;order_amount&＃96; float(10,2) unsigned NOT NULL DEFAULT &＃39;0.00&＃39;,&＃96;user_name&＃96; varchar(64) CHARACTER SET latin1 NOT NULL DEFAULT &＃39;&＃39;,&＃96;order_status&＃96; tinyint(2) unsigned NOT NULL DEFAULT &＃39;0&＃39;,&＃96;date_created&＃96; datetime NOT NULL,PRIMARY KEY (&＃96;order_id&＃96;)
) ENGINE&＃61;InnoDB AUTO_INCREMENT&＃61;0 DEFAULT CHARSET&＃61;utf8;DROP TABLE IF EXISTS &＃96;csdn_order_detail&＃96;;
CREATE TABLE &＃96;csdn_order_detail&＃96; (&＃96;detail_id&＃96; int(10) unsigned NOT NULL AUTO_INCREMENT,&＃96;order_id&＃96; int(10) unsigned NOT NULL,&＃96;product_id&＃96; int(10) NOT NULL,&＃96;product_price&＃96; float(10,2) NOT NULL,&＃96;product_number&＃96; smallint(4) unsigned NOT NULL DEFAULT &＃39;0&＃39;,&＃96;date_created&＃96; datetime NOT NULL,PRIMARY KEY (&＃96;detail_id&＃96;),KEY &＃96;idx_order_id&＃96; (&＃96;order_id&＃96;)
) ENGINE&＃61;InnoDB AUTO_INCREMENT&＃61;0 DEFAULT CHARSET&＃61;utf8;DROP TABLE IF EXISTS &＃96;csdn_product_stock&＃96;;
CREATE TABLE &＃96;csdn_product_stock&＃96; (&＃96;auto_id&＃96; int(10) unsigned NOT NULL AUTO_INCREMENT,&＃96;product_id&＃96; int(10) NOT NULL,&＃96;product_stock_number&＃96; int(10) unsigned NOT NULL,&＃96;date_modified&＃96; datetime NOT NULL,PRIMARY KEY (&＃96;auto_id&＃96;),KEY &＃96;idx_product_id&＃96; (&＃96;product_id&＃96;)
) ENGINE&＃61;InnoDB AUTO_INCREMENT&＃61;3 DEFAULT CHARSET&＃61;utf8;INSERT INTO &＃96;csdn_product_stock&＃96; VALUES (&＃39;1&＃39;, &＃39;1&＃39;, &＃39;20&＃39;, &＃39;2018-09-13 19:36:19&＃39;);
INSERT INTO &＃96;csdn_product_stock&＃96; VALUES (&＃39;2&＃39;, &＃39;2&＃39;, &＃39;40&＃39;, &＃39;2018-09-13 19:36:19&＃39;);
2、数据库配置信息&＃xff1a;config.php
$dbHost &＃61; "192.168.0.110";
$dbUser &＃61; "root";
$dbPassword &＃61; "123456";
$dbName &＃61; "test123";
?>
3、order_submit.php&＃xff0c;生成订单
require("config.php");
try {$pdo &＃61; new PDO("mysql:host&＃61;" . $dbHost . ";dbname&＃61;" . $dbName, $dbUser, $dbPassword, array(PDO::ATTR_PERSISTENT &＃61;> true));$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, 1);$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);$orderInfo &＃61; array(&＃39;order_amount&＃39; &＃61;> 10.92,&＃39;user_name&＃39; &＃61;> &＃39;yusan&＃39;,&＃39;order_status&＃39; &＃61;> 1,&＃39;date_created&＃39; &＃61;> &＃39;now()&＃39;,&＃39;product_lit&＃39; &＃61;> array(0 &＃61;> array(&＃39;product_id&＃39; &＃61;> 1,&＃39;product_price&＃39; &＃61;> 5.00,&＃39;product_number&＃39; &＃61;> 10,&＃39;date_created&＃39; &＃61;> &＃39;now()&＃39;),1 &＃61;> array(&＃39;product_id&＃39; &＃61;> 2,&＃39;product_price&＃39; &＃61;> 5.92,&＃39;product_number&＃39; &＃61;> 20,&＃39;date_created&＃39; &＃61;> &＃39;now()&＃39;)));try{$pdo->beginTransaction();//开启事务处理$sql &＃61; &＃39;insert into csdn_order (order_amount, user_name, order_status, date_created) values (:orderAmount, :userName, :orderStatus, now())&＃39;;$stmt &＃61; $pdo->prepare($sql); $affectedRows &＃61; $stmt->execute(array(&＃39;:orderAmount&＃39; &＃61;> $orderInfo[&＃39;order_amount&＃39;], &＃39;:userName&＃39; &＃61;> $orderInfo[&＃39;user_name&＃39;], &＃39;:orderStatus&＃39; &＃61;> $orderInfo[&＃39;order_status&＃39;]));$orderId &＃61; $pdo->lastInsertId();if(!$affectedRows) {throw new PDOException("Failure to submit order!");}foreach($orderInfo[&＃39;product_lit&＃39;] as $productInfo) {$sqlProductDetail &＃61; &＃39;insert into csdn_order_detail (order_id, product_id, product_price, product_number, date_created) values (:orderId, :productId, :productPrice, :productNumber, now())&＃39;;$stmtProductDetail &＃61; $pdo->prepare($sqlProductDetail); $stmtProductDetail->execute(array(&＃39;:orderId&＃39; &＃61;> $orderId, &＃39;:productId&＃39; &＃61;> $productInfo[&＃39;product_id&＃39;], &＃39;:productPrice&＃39; &＃61;> $productInfo[&＃39;product_price&＃39;], &＃39;:productNumber&＃39; &＃61;> $productInfo[&＃39;product_number&＃39;]));$sqlCheck &＃61; "select product_stock_number from csdn_product_stock where product_id&＃61;:productId"; $stmtCheck &＃61; $pdo->prepare($sqlCheck); $stmtCheck->execute(array(&＃39;:productId&＃39; &＃61;> $productInfo[&＃39;product_id&＃39;])); $rowCheck &＃61; $stmtCheck->fetch(PDO::FETCH_ASSOC);if($rowCheck[&＃39;product_stock_number&＃39;] <$productInfo[&＃39;product_number&＃39;]) {throw new PDOException("Out of stock, Failure to submit order!");}$sqlProductStock &＃61; &＃39;update csdn_product_stock set product_stock_number&＃61;product_stock_number-:productNumber, date_modified&＃61;now() where product_id&＃61;:productId&＃39;;$stmtProductStock &＃61; $pdo->prepare($sqlProductStock); $stmtProductStock->execute(array(&＃39;:productNumber&＃39; &＃61;> $productInfo[&＃39;product_number&＃39;], &＃39;:productId&＃39; &＃61;> $productInfo[&＃39;product_id&＃39;]));$affectedRowsProductStock &＃61; $stmtProductStock->rowCount();//库存没有正常扣除&＃xff0c;失败&＃xff0c;库存表里的product_stock_number设置了为非负数//如果库存不足时&＃xff0c;sql异常&＃xff1a;SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in &＃39;(&＃96;test&＃96;.&＃96;csdn_product_stock&＃96;.&＃96;product_stock_number&＃96; - 20)&＃39;if($affectedRowsProductStock <&＃61; 0) {throw new PDOException("Out of stock, Failure to submit order!");}}echo "Successful, Order Id is&＃xff1a;" . $orderId ."&＃xff0c;Order Amount is&＃xff1a;" . $orderInfo[&＃39;order_amount&＃39;] . "。";$pdo->commit();//提交事务//exec("php order_cancel.php -a" . $orderId . " &");pclose(popen(&＃39;php order_cancel.php -a &＃39; . $orderId . &＃39; &&＃39;, &＃39;w&＃39;));//system("php order_cancel.php -a" . $orderId . " &", $phpResult);//echo $phpResult;}catch(PDOException $e){echo $e->getMessage();$pdo->rollback();}$pdo &＃61; null;
} catch (PDOException $e) {echo $e->getMessage();
}
?>
4、order_cancel.php&＃xff0c;这个方法主要就是做订单自动取消&＃xff0c;并还原库存的业务处理
require("config.php");
$queryString &＃61; getopt(&＃39;a:&＃39;);
$userParams &＃61; array($queryString);
appendLog(date("Y-m-d H:i:s") . "t" . $queryString[&＃39;a&＃39;] . "t" . "start");try {$pdo &＃61; new PDO("mysql:host&＃61;" . $dbHost . ";dbname&＃61;" . $dbName, $dbUser, $dbPassword, array(PDO::ATTR_PERSISTENT &＃61;> true));$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, 0);$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);swoole_timer_after(10000, function ($queryString) {global $queryString, $pdo;try{$pdo->beginTransaction();//开启事务处理$orderId &＃61; $queryString[&＃39;a&＃39;]; $sql &＃61; "select order_status from csdn_order where order_id&＃61;:orderId"; $stmt &＃61; $pdo->prepare($sql); $stmt->execute(array(&＃39;:orderId&＃39; &＃61;> $orderId)); $row &＃61; $stmt->fetch(PDO::FETCH_ASSOC);//$row[&＃39;order_status&＃39;] &＃61;&＃61;&＃61; "1"代表已下单&＃xff0c;但未付款&＃xff0c;我们还原库存只针对未付款的订单if(isset($row[&＃39;order_status&＃39;]) && $row[&＃39;order_status&＃39;] &＃61;&＃61;&＃61; "1") {$sqlOrderDetail &＃61; "select product_id, product_number from csdn_order_detail where order_id&＃61;:orderId"; $stmtOrderDetail &＃61; $pdo->prepare($sqlOrderDetail); $stmtOrderDetail->execute(array(&＃39;:orderId&＃39; &＃61;> $orderId)); while($rowOrderDetail &＃61; $stmtOrderDetail->fetch(PDO::FETCH_ASSOC)) {$sqlRestoreStock &＃61; "update csdn_product_stock set product_stock_number&＃61;product_stock_number &＃43; :productNumber, date_modified&＃61;now() where product_id&＃61;:productId"; $stmtRestoreStock &＃61; $pdo->prepare($sqlRestoreStock);$stmtRestoreStock->execute(array(&＃39;:productNumber&＃39; &＃61;> $rowOrderDetail[&＃39;product_number&＃39;], &＃39;:productId&＃39; &＃61;> $rowOrderDetail[&＃39;product_id&＃39;]));}$sqlRestoreOrder &＃61; "update csdn_order set order_status&＃61;:orderStatus where order_id&＃61;:orderId"; $stmtRestoreOrder &＃61; $pdo->prepare($sqlRestoreOrder);$stmtRestoreOrder->execute(array(&＃39;:orderStatus&＃39; &＃61;> 0, &＃39;:orderId&＃39; &＃61;> $orderId));}$pdo->commit();//提交事务}catch(PDOException $e){echo $e->getMessage();$pdo->rollback();}$pdo &＃61; null;appendLog(date("Y-m-d H:i:s") . "t" . $queryString[&＃39;a&＃39;] . "t" . "endt" . json_encode($queryString));}, $pdo);} catch (PDOException $e) {echo $e->getMessage();
}
function appendLog($str) {$dir &＃61; &＃39;log.txt&＃39;;$fh &＃61; fopen($dir, "a");fwrite($fh, $str . "n");fclose($fh);
}
?>
以上内容希望帮助到大家&＃xff0c;很多PHPer在进阶的时候总会遇到一些问题和瓶颈&＃xff0c;业务代码写多了没有方向感&＃xff0c;不知道该从那里入手去提升&＃xff0c;对此我整理了一些资料&＃xff0c;包括但不限于&＃xff1a;分布式架构、高可扩展、高性能、高并发、服务器性能调优、TP6&＃xff0c;laravel&＃xff0c;YII2&＃xff0c;Redis&＃xff0c;Swoole、Swoft、Kafka、Mysql优化、shell脚本、Docker、微服务、Nginx等多个知识点高级进阶干货需要的可以免费分享给大家&＃xff0c;需要请戳这里链接 或 者关注咱们下面的知乎专栏
PHP架构师圈子zhuanlan.zhihu.com
来源链接&＃xff1a;https://blog.csdn.net/WanTianwen/article/details/82693677
京公网安备 11010802041100号