SQL实例之四

最近要完成一个自动计算销售价格并更新到销售价格表的需求。具体描述如下:

产品中记录成本价 和 重量,现在要监控这两个变量,一旦这两个变量发生改变,就要按照公式重新计算价格,然后把新的价格写入产品价格表。计算价格的过程需要多个变量,其中产品对应不同类目,会有不同的收费费率,所有需要把所有SKU和类目对应起来,这里需要手动完成这个对应过程(记录到新表)。

首先要解决的是:如何监控成本价 和 重量的变化?
解决方案是使用MySQL的触发器,这个还算简单,因为我只需要监控变化,价格计算过程还是需要调用程序完成的,MySQL的触发器是无法发出调用外部程序的指令的,所以我决定采用最简单的方式:只要数据有变化就写入到一张监控表中(product_trigger),通过计划任务定时扫描这个表执行整个过程。

产品删除时,并不需要去删除对应的价格,所以对于删除产品时,我并不做监控。但是在产品新增和更新时添加了触发器:

DROP TRIGGER IF EXISTS `product_insert`;
CREATE DEFINER=`root`@`localhost` TRIGGER `product_insert` AFTER INSERT ON `product` FOR EACH ROW 
BEGIN 
insert into product_trigger(pid,sku,oprice,oweight,nprice,nweight,type,issync) 
values(NEW.id,0,0,NEW.price,NEW.weight,1,0);
END 

DROP TRIGGER IF EXISTS `product_update`;
CREATE DEFINER=`root`@`localhost` TRIGGER `product_insert` AFTER INSERT ON `product` FOR EACH ROW 
BEGIN 
insert into product_trigger(pid,sku,oprice,oweight,nprice,nweight,type,issync) 
values(NEW.id,OLD.price,OLD.weight,NEW.price,NEW.weight,2,0);
END 

产品添加时,type为1,为1表示是新插入的,那么就直接使用nprice和nweight来计算价格(不需要检测价格和重量是否变化)。当是产品更新时,type为2,为2表示是更新,要检查价格和重量是否变化了,变化了才去更新价格。如果对应的产品价格更新,对应的issync就是1,如果失败了就是2,默认去扫描issync为0的记录。

这个触发表SQL:

CREATE TABLE IF NOT EXISTS `product_trigger` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL DEFAULT '0',
  `oprice` decimal(10,2) NOT NULL DEFAULT '0.00',
  `oweight` int(10) NOT NULL DEFAULT '0',
  `nprice` decimal(10,2) NOT NULL DEFAULT '0.00',
  `nweight` int(10) NOT NULL DEFAULT '0',
  `type` int(3) NOT NULL DEFAULT '0',
  `issync` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

然后首先是一段主程序框架:

$allUpdate = $db->fetchAll("SELECT * FROM product_trigger
WHERE issync<1 ORDER BY id");
foreach($allUpdate as $itm){
    if(($itm['type']=1) ||  (($itm['type']==2) && ($itm['oprice'] != $itm['nprice'] || $itm['oweight'] != $itm['nweight']))){
        //需要计算价格
        ......
    }else{
        //不需要操作的记录 可能是更新其它字段导致的写入
        $db->update('product_trigger',array('issync'=>1),"id=".$itm['id']);
    }
    //主要获取产品ID
}

查询中的ORDER BY id是为了解决当有多次更新时,可以确保它是安装顺序进行的。当是更新操作时,只有价格和重量变化了才操作。

计算价格这段程序,有一个变量需要确定,就是产品对应的类名,使用如下SQL查询:

SELECT p.id,p.sku,p.is_var,pv.sku,pr.sku FROM (product p LEFT JOIN product_var pv ON p.id = pv.pid) JOIN product_rate pr ON (pr.sku = if(p.is_var,pv.sku,p.sku)) 
WHERE p.id = PID

这里的PID就是主程序循环获取的产品ID,表product_rate结构:

--
-- 表的结构 `product_rate`
--

CREATE TABLE IF NOT EXISTS `product_rate` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sku` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `platform` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `site` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `rate` decimal(10,4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;

--
-- 转存表中的数据 `product_rate`
--

INSERT INTO `product_rate` (`id`, `sku`, `platform`, `site`, `rate`) VALUES
(1, '00302', 'eBay', 'US', '1.0000');

注意这个表记录的是最小SKU对应的平台站点费率。在可以确定费率之后主程序继续完善如下:

$allUpdate = $db->fetchAll("SELECT * FROM product_trigger
WHERE issync<1 ORDER BY id");
foreach($allUpdate as $itm){
    $del = true;
    if(($itm['type']=1) ||  (($itm['type']==2) && ($itm['oprice'] != $itm['nprice'] || $itm['oweight'] != $itm['nweight']))){
        //需要计算价格
        $reslt = $db->fetchAll("SELECT p.id,if(p.is_var,pv.sku,p.sku) as sku,pr.platform, pr.site, pr.rate FROM (product p LEFT JOIN product_var pv ON p.id = pv.pid) WHERE p.id = ".$itm['pid']);
        if(!empty($reslt)){
             // 循环每个SKU
             foreach($reslt as $rat){
                 // 最小SKU
                 $sku = $rat['sku']; 

                 // 这里初始化所有参数*****

                 $rateTable = $db->fetchAll("SELECT * FROM product_rate WHERE platform='eBay' sku = '".$sku); 
                 if(!$rateTable){
                     ////////////////////////////
                     // 对应费率
                     $rate = $rat['rate'];
                     // 平台
                     $platform = $rat['platform'];
                     // 站点
                     $site = $rat['site'];
                     /////////////////////////////
                     // 开始计算 写入价格表逻辑
                 }else{
                     // 对应费率
                     $rate = 0.05;
                     // 平台
                     $platform = 'SMT';
                     // 站点
                     $site = 'All';
                     // 开始计算 写入价格表逻辑
                 }
                 
             }
        }
    }

    if($del)
        //更新
        $db->update('product_trigger',array('issync'=>1),"id=".$itm['id']);
    }
}

原本想着这段程序会稍微复杂,要搞多次JOIN,实际上根本没有JOIN的机会。