今天抽空学习了用swoole实现订单的延时处理(自动取消订单,还原库存等操作),顺便分享出来供大家参考。

一、业务场景:当客户下单在指定的时间内如果没有付款,那我们需要将这笔订单取消掉,比如好的处理方法是运用延时取消,很多人首先想到的当然是crontab,这个也行,不过这里我们运用swoole的异步毫秒定时器来实现,同样也不会影响到当前程序的运行,具体可以参考:https://wiki.swoole.com/wiki/page/319.html

二、说明,order_status为1时代表客户下单确定,为2时代表客户已付款,为0时代表订单已取消(正是swoole来做的)

三、举例说明,库存表csdn_product_stock产品ID为1的产品库存数量为20,产品ID为2的库存数量为40,然后客户下单一笔产品ID1减10,产品ID2减20,所以库存表只够2次下单,例子中10秒后自动还原库存,如下图:
图解:
1、第一次下完单产品ID1库存从20减到了10,产品ID2库存从40减到了20;
2、第二次下完单产品ID1的库存为0了,产品ID2的库存也为0了,
3、第三次下单时,程序提示Out of stock;
4、过了10秒钟(每个订单下单后往后推10秒),客户两次下单,由于没有付款(csdn_order表的order_status为1),产品1和产品2的库存被还原了(csdn_order表的order_status变为0),客户又可以继续下单了

演示图片

1、所需要sql数据库表

DROP TABLE IF EXISTS `csdn_order`;
CREATE TABLE `csdn_order` (
  `order_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_amount` float(10,2) unsigned NOT NULL DEFAULT '0.00',
  `user_name` varchar(64) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `order_status` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `date_created` datetime NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `csdn_order_detail`;
CREATE TABLE `csdn_order_detail` (
  `detail_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int(10) unsigned NOT NULL,
  `product_id` int(10) NOT NULL,
  `product_price` float(10,2) NOT NULL,
  `product_number` smallint(4) unsigned NOT NULL DEFAULT '0',
  `date_created` datetime NOT NULL,
  PRIMARY KEY (`detail_id`),
  KEY `idx_order_id` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `csdn_product_stock`;
CREATE TABLE `csdn_product_stock` (
  `auto_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(10) NOT NULL,
  `product_stock_number` int(10) unsigned NOT NULL,
  `date_modified` datetime NOT NULL,
  PRIMARY KEY (`auto_id`),
  KEY `idx_product_id` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `csdn_product_stock` VALUES ('1', '1', '20', '2019-04-25 14:10:19');
INSERT INTO `csdn_product_stock` VALUES ('2', '2', '40', '2019-04-25 14:10:19');

下面贴出来纯手工PHP,很多同学用了原生PHP,就不会运用到框架里去,其实都一样的,不要想得那么复杂就是了。只要一点就是你用多了,你就会这样觉得咯。

配置文件config.php ,这个在框架的话,基本上都是配置好了。

<?php
/**
 * Created by ZhengNiu.
 * User: 77103
 * Date: 2019/4/25
 * Time: 14:07
 */
$dbHost = "127.0.0.1";
$dbUser = "root";
$dbPassword = "123456";
$dbName = "test";

swoole都是用在linux系统里的,这里的host你可以自己搭建虚拟主机,也可以网上购买属于自己的服务器

订单提交的文件order_submit.php,这里对订单生成,同时扣除库存的一系列操作。

<?php
/**
 * Created by ZhengNiu.
 * User: 77103
 * Date: 2019/4/25
 * Time: 14:09
 */
require("config.php");
try {
    $pdo = new PDO(
        "mysql:host=" . $dbHost . ";dbname=" . $dbName,
        $dbUser,
        $dbPassword,
        array(PDO::ATTR_PERSISTENT => true)//持久连接缓存可以避免每次脚本需要与数据库回话时建立一个新连接的开销,从而让 web 应用程序更快。
    );
    $pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, 0);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);//异常错误抛出

    $orderInfo = array(
        'order_amount' => 10.92,
        'user_name' => 'yusan',
        'order_status' => 1,
        'date_created' => 'now()',
        'product_lit' => array(
            0 => array(
                'product_id' => 1,
                'product_price' => 5.00,
                'product_number' => 10,
                'date_created' => 'now()'
            ),
            1 => array(
                'product_id' => 2,
                'product_price' => 5.92,
                'product_number' => 20,
                'date_created' => 'now()'
            )
        )
    );

    try{
        $pdo->beginTransaction();//开启事务处理

        $sql = 'insert into csdn_order 
                (order_amount, user_name, order_status, date_created) 
                values 
                (:orderAmount, :userName, :orderStatus, now())';
        $stmt = $pdo->prepare($sql);
        $affectedRows = $stmt->execute(array(':orderAmount' => $orderInfo['order_amount'], ':userName' => $orderInfo['user_name'], ':orderStatus' => $orderInfo['order_status']));
        $orderId = $pdo->lastInsertId();
        if(!$affectedRows) {
            throw new PDOException("Failure to submit order!");
        }
        foreach($orderInfo['product_lit'] as $productInfo) {

            $sqlProductDetail = 'insert into csdn_order_detail (order_id, product_id, product_price, product_number, date_created) values (:orderId, :productId, :productPrice, :productNumber, now())';
            $stmtProductDetail = $pdo->prepare($sqlProductDetail);
            $stmtProductDetail->execute(array(':orderId' => $orderId, ':productId' =>  $productInfo['product_id'], ':productPrice' => $productInfo['product_price'], ':productNumber' => $productInfo['product_number']));

            $sqlCheck = "select product_stock_number from csdn_product_stock where product_id=:productId";
            $stmtCheck = $pdo->prepare($sqlCheck);
            $stmtCheck->execute(array(':productId' => $productInfo['product_id']));
            $rowCheck = $stmtCheck->fetch(PDO::FETCH_ASSOC);
            if($rowCheck['product_stock_number'] < $productInfo['product_number']) {
                throw new PDOException("Out of stock, Failure to submit order!");
            }

            $sqlProductStock = 'update csdn_product_stock set product_stock_number=product_stock_number-:productNumber, date_modified=now() where product_id=:productId';
            $stmtProductStock = $pdo->prepare($sqlProductStock);
            $stmtProductStock->execute(array(':productNumber' => $productInfo['product_number'], ':productId' => $productInfo['product_id']));
            $affectedRowsProductStock = $stmtProductStock->rowCount();

            //库存没有正常扣除,失败,库存表里的product_stock_number设置了为非负数
            //如果库存不足时,sql异常:SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in '(`test`.`csdn_product_stock`.`product_stock_number` - 20)'
            if($affectedRowsProductStock <= 0) {
                throw new PDOException("Out of stock, Failure to submit order!");
            }
        }
        echo "Successful, Order Id is:" . $orderId .",Order Amount is:" . $orderInfo['order_amount'] . "。";
        $pdo->commit();//提交事务
        //exec("php order_cancel.php -a" . $orderId . " &");
        pclose(popen('php order_cancel.php -a ' . $orderId . ' &', 'w'));
        //system("php order_cancel.php -a" . $orderId . " &", $phpResult);
        //echo $phpResult;
    }catch(PDOException $e){
        echo $e->getMessage();
        $pdo->rollback();
    }
    $pdo = null;
} catch (PDOException $e) {
    echo $e->getMessage();
}
?>

订单的延时处理order_cancel.php

<?php
/**
 * Created by ZhengNiu.
 * User: 77103
 * Date: 2019/4/25
 * Time: 14:09
 */
require("config.php");
//getopt — 从命令行参数列表中获取选项,解析传入脚本的选项。
$queryString = getopt('a:');
$userParams = array($queryString);
appendLog(date("Y-m-d H:i:s") . "\t" . $queryString['a'] . "\t" . "start");

try {
    $pdo = new PDO(
        "mysql:host=" . $dbHost . ";dbname=" . $dbName,
            $dbUser,
            $dbPassword,
            array(PDO::ATTR_PERSISTENT => 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 = $queryString['a'];
            $sql = "select order_status from csdn_order where order_id=:orderId";
            $stmt = $pdo->prepare($sql);
            $stmt->execute(array(':orderId' => $orderId));
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            //$row['order_status'] === "1"代表已下单,但未付款,我们还原库存只针对未付款的订单
            if(isset($row['order_status']) && $row['order_status'] === "1") {
                $sqlOrderDetail = "select product_id, product_number from csdn_order_detail where order_id=:orderId";
                $stmtOrderDetail = $pdo->prepare($sqlOrderDetail);
                $stmtOrderDetail->execute(array(':orderId' => $orderId));
                while($rowOrderDetail = $stmtOrderDetail->fetch(PDO::FETCH_ASSOC)) {
                    $sqlRestoreStock = "update csdn_product_stock set product_stock_number=product_stock_number + :productNumber, date_modified=now() where product_id=:productId";
                    $stmtRestoreStock = $pdo->prepare($sqlRestoreStock);
                    $stmtRestoreStock->execute(array(':productNumber' => $rowOrderDetail['product_number'], ':productId' => $rowOrderDetail['product_id']));
                }

                $sqlRestoreOrder = "update csdn_order set order_status=:orderStatus where order_id=:orderId";
                $stmtRestoreOrder = $pdo->prepare($sqlRestoreOrder);
                $stmtRestoreOrder->execute(array(':orderStatus' => 0, ':orderId' => $orderId));
            }

            $pdo->commit();//提交事务
        }catch(PDOException $e){
            echo $e->getMessage();
            $pdo->rollback();
        }
        $pdo = null;

        appendLog(date("Y-m-d H:i:s") . "\t" . $queryString['a'] . "\t" . "end\t" . json_encode($queryString));
    }, $pdo);

} catch (PDOException $e) {
    echo $e->getMessage();
}
function appendLog($str) {
    $dir = 'log.txt';
    $fh = fopen($dir, "a");
    fwrite($fh, $str . "\n");
    fclose($fh);
}
?>

完毕!大家也可以把它封装一下融入到框架里。在这里,由于时间问题,我就不再封装了。

Last modification:December 28th, 2019 at 08:50 pm
如果觉得我的文章对你有用,请随意赞赏