1. 功能描述

1. 实现类似联表的商品顺序，支持操作：

   1. 往顺序尾部添加一个商品
   2. 取商品往头部添加
   3. 取商品往尾部添加

   4. 删除任意商品

      2. 开发

      2.1 背景

      2.2 思路

      方案1：构建一个链表

      方案2：数据库级联设计

      优势：先后顺序硬关联，有明确的先后
      劣势：需要数据库花费额外的资源来维护这个关系，当出现并发的情况时，就gg，可能破坏这个硬关联，没了先后，导致这个先后链被截断

      方案3：数据库增加排序字段

      优势：通过 order by 排序，先后顺序也时明确的
      劣势：出现并发问题时，可能有会出现顺序先后同样为3，4，5的，而 order by 时，先后关系可能无法预料（注：可以与业务方确定再 order by 时增加主键id来明确先后关系）

      2.3 落地开发

      方案2：

      CREATE TABLE `commodity_promote_goods_sequence` (
      `goods_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商品id',
      `pre_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '前驱id'
      UNIQUE KEY `goods_id` (`goods_id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品顺序';

      <?
      class GoodsList
      {
      /**
      * 获取商品顺序
      * @return array
      */
      public function goodsSort()
      {
        $sorted = [];
        $indexBy = 'pre_id';
          // 读取表内所有数据
        $dao = new CommodityPromoteGoodsSequenceDao();
        $list = $dao->listGoodsInfo($indexBy);
        if (!$list) {
            return [];
        }
        $sorted[$list[0]['goods_id']] = $list[0];
        while (count($sorted) < count($list)) {
            $value = $list[end($sorted)['goods_id']];
            $key = $value['goods_id'];
            if (count($sorted) == 1) {
                $sorted[$list[0]['goods_id']]['back_id'] = $key;
            }
            $sorted[$key] = $value;
            $sorted[$key]['back_id'] = $list[$key]['goods_id'] ?? '';
        }
        // 整合数据
        foreach ($sorted as $k => $item) {
            // 商品顺序后一个商品id
            $sorted[$k]['back_id'] = $item['back_id'] ?? '';
        }
        return $sorted;
      }
      /**
      * 修改商品顺序
      * @param int $goodsId
      * @param int $to
      * @return bool
      * @throws Exception
      */
      public function modifyGoodsSort($goodsId, $to)
      {
        if (!$goodsId) {
            return true;
        }
        $delGoodsIds = $updateSort = [];
        switch ($to) {
            case self::$goodsTo['top'] :
                if ($this->allGoodsList[$goodsId]['pre_id'] == 0) {
                    break;
                }
                // 1. 置顶
                $updateSort[] = [
                    'goods_id' => $goodsId,
                    'pre_id' => 0,
                ];
                // 2. 接中间
                if (!empty($this->allGoodsList[$goodsId]['back_id'])) {
                    $tmpGoods = $this->allGoodsList[$goodsId]['back_id'];
                    $tmpPreId = $this->allGoodsList[$goodsId]['pre_id'];
                    $updateSort[] = [
                        'goods_id' => $tmpGoods,
                        'pre_id' => $tmpPreId,
                    ];
                }
                // 3. 改第二元素
                $rawFirstGoodsId = reset($this->allGoodsList)['goods_id'];
                $updateSort[] = [
                    'goods_id' => $rawFirstGoodsId,
                    'pre_id' => $goodsId,
                ];
                break;
            case self::$goodsTo['tail'] :
                (end($this->allGoodsList)['goods_id'] != $goodsId) && $updateSort[] = [
                    'goods_id' => $goodsId,
                    'pre_id' => end($this->allGoodsList)['goods_id'],
                ];
                (!empty($this->allGoodsList[$goodsId]['back_id'])) && $updateSort[] = [
                    'goods_id' => $this->allGoodsList[$goodsId]['back_id'],
                    'pre_id' => $this->allGoodsList[$goodsId]['pre_id'],
                ];
                break;
            case self::$goodsTo['del'] :
                $delGoodsIds = [$goodsId];
                (!empty($this->allGoodsList[$goodsId]['back_id'])) && $updateSort[] = [
                    'goods_id' => $this->allGoodsList[$goodsId]['back_id'],
                    'pre_id' => $this->allGoodsList[$goodsId]['pre_id'],
                ];
                break;
        }
        if (!$updateSort && !$delGoodsIds) {
            return true;
        }
        $ok = true;
        $delGoodsIds && $ok = CommodityPromoteGoodsSequenceDao::getInstance()->delByGoodsIds($delGoodsIds);
        if (!$ok) {
            return false;
        }
        $updateSort && $ok = CommodityPromoteGoodsSequenceDao::getInstance()->batchUpdateByGoodsId($updateSort);
        if (!$ok) {
            return false;
        }
        // 更新商品表
        $this->allGoodsList = $this->goodsSort();
        return true;
      }
      }

      方案3:

      CREATE TABLE `commodity_promote_goods_sequence` (
      `goods_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商品id',
      `sort` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
      UNIQUE KEY `goods_id` (`goods_id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品顺序';