Magento 2.4.4如何将API拉取的数据存入自定义数据表

第一步：补全自定义表字段 #

你当前的db_schema.xml仅配置了自增主键entity_id，没有对应业务字段无法存储实际商品数据，需要先根据乐天API返回的商品属性补充表字段，参考配置如下：

<?xml version="1.0"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <!-- 热门商品表 -->
    <table name="popular_item" resource="default" engine="innodb" comment="Popular Product Table">
        <column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" identity="true" comment="Entity ID"/>
        <column xsi:type="varchar" name="rakuten_item_id" length="255" nullable="false" comment="Rakuten Item ID"/>
        <column xsi:type="varchar" name="item_name" length="1024" nullable="false" comment="Item Name"/>
        <column xsi:type="decimal" name="item_price" scale="2" precision="12" nullable="false" defaultValue="0" comment="Item Price"/>
        <column xsi:type="varchar" name="image_url" length="2048" nullable="true" comment="Item Image Url"/>
        <column xsi:type="varchar" name="item_url" length="2048" nullable="true" comment="Item Detail Url"/>
        <column xsi:type="int" name="rank" unsigned="true" nullable="true" comment="Popular Rank"/>
        <column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" default="CURRENT_TIMESTAMP" comment="Created At"/>
        <column xsi:type="timestamp" name="updated_at" on_update="true" nullable="false" default="CURRENT_TIMESTAMP" comment="Updated At"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="entity_id"/>
        </constraint>
    </table>
    <!-- 搜索商品表 -->
    <table name="search_item" resource="default" engine="innodb" comment="Search Product Table">
        <column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" identity="true" comment="Entity ID"/>
        <column xsi:type="varchar" name="search_keyword" length="255" nullable="false" comment="Search Keyword"/>
        <column xsi:type="varchar" name="rakuten_item_id" length="255" nullable="false" comment="Rakuten Item ID"/>
        <column xsi:type="varchar" name="item_name" length="1024" nullable="false" comment="Item Name"/>
        <column xsi:type="decimal" name="item_price" scale="2" precision="12" nullable="false" defaultValue="0" comment="Item Price"/>
        <column xsi:type="varchar" name="image_url" length="2048" nullable="true" comment="Item Image Url"/>
        <column xsi:type="varchar" name="item_url" length="2048" nullable="true" comment="Item Detail Url"/>
        <column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" default="CURRENT_TIMESTAMP" comment="Created At"/>
        <column xsi:type="timestamp" name="updated_at" on_update="true" nullable="false" default="CURRENT_TIMESTAMP" comment="Updated At"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="entity_id"/>
        </constraint>
    </table>
</schema>

修改完成后执行以下命令让表结构生效：

bin/magento setup:upgrade
bin/magento cache:clean

第二步：创建表对应的ORM模型层 #

Magento 2操作自定义表必须通过标准ORM模型实现，禁止直接拼接原生SQL，避免SQL注入、版本兼容问题。需要为两张表分别创建Model、ResourceModel、Collection三类文件：

• Model类：继承\Magento\Framework\Model\AbstractModel，作为数据实体载体
• ResourceModel类：继承\Magento\Framework\Model\ResourceModel\Db\AbstractDb，负责实际数据库读写操作
• Collection类：继承\Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection，负责数据集合查询

以popular_item表为例，文件路径和代码如下：

1. Model文件 app/code/Vendor/Rakuten/Model/PopularItem.php

<?php
namespace Vendor\Rakuten\Model;

use Magento\Framework\Model\AbstractModel;

class PopularItem extends AbstractModel
{
    protected function _construct()
    {
        $this->_init(ResourceModel\PopularItem::class);
    }
}

2. ResourceModel文件 app/code/Vendor/Rakuten/Model/ResourceModel/PopularItem.php

<?php
namespace Vendor\Rakuten\Model\ResourceModel;

use Magento\Framework\Model\ResourceModel\Db\AbstractDb;

class PopularItem extends AbstractDb
{
    protected function _construct()
    {
        $this->_init('popular_item', 'entity_id');
    }
}

3. Collection文件 app/code/Vendor/Rakuten/Model/ResourceModel/PopularItem/Collection.php

<?php
namespace Vendor\Rakuten\Model\ResourceModel\PopularItem;

use Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection;
use Vendor\Rakuten\Model\PopularItem as Model;
use Vendor\Rakuten\Model\ResourceModel\PopularItem as ResourceModel;

class Collection extends AbstractCollection
{
    protected $_idFieldName = 'entity_id';

    protected function _construct()
    {
        $this->_init(Model::class, ResourceModel::class);
    }
}

search_item表对应的三类文件按照相同逻辑编写，替换对应表名、类名即可，对应Factory类会由Magento自动生成，无需手动创建。

第三步：封装乐天API请求服务类 #

不要在控制器、定时任务中直接编写CURL请求逻辑，单独封装服务类统一处理API鉴权、参数拼接、异常处理、返回格式化：

• 注入Magento自带的HTTP客户端（\Magento\Framework\HTTP\Client\Curl或Guzzle均可）
• API密钥、接口地址等配置存入系统配置表core_config_data，支持后台配置，禁止硬编码
• 分别封装热门商品拉取、关键词搜索商品拉取方法，返回格式化后的数组数据

参考代码路径app/code/Vendor/Rakuten/Service/RakutenApi.php：

<?php
namespace Vendor\Rakuten\Service;

use Magento\Framework\HTTP\Client\Curl;
use Magento\Framework\App\Config\ScopeConfigInterface;
use Psr\Log\LoggerInterface;

class RakutenApi
{
    protected const POPULAR_ENDPOINT = '乐天热门商品接口地址';
    protected const SEARCH_ENDPOINT = '乐天商品搜索接口地址';

    public function __construct(
        protected Curl $curl,
        protected ScopeConfigInterface $scopeConfig,
        protected LoggerInterface $logger
    ) {
        $this->curl->setOption(CURLOPT_TIMEOUT, 10);
    }

    /**
     * 拉取热门商品列表
     */
    public function getPopularItems(): array
    {
        $appId = $this->scopeConfig->getValue('rakuten/general/app_id');
        try {
            $this->curl->get(self::POPULAR_ENDPOINT . '?applicationId=' . $appId);
            $response = json_decode($this->curl->getBody(), true);
            return $response['Items'] ?? [];
        } catch (\Exception $e) {
            $this->logger->error('乐天热门商品接口请求失败：' . $e->getMessage());
            return [];
        }
    }

    /**
     * 根据关键词拉取搜索商品列表
     */
    public function searchItems(string $keyword): array
    {
        $appId = $this->scopeConfig->getValue('rakuten/general/app_id');
        try {
            $params = http_build_query([
                'applicationId' => $appId,
                'keyword' => $keyword
            ]);
            $this->curl->get(self::SEARCH_ENDPOINT . '?' . $params);
            $response = json_decode($this->curl->getBody(), true);
            return $response['Items'] ?? [];
        } catch (\Exception $e) {
            $this->logger->error('乐天搜索商品接口请求失败：' . $e->getMessage(), ['keyword' => $keyword]);
            return [];
        }
    }
}

第四步：实现数据同步存储逻辑 #

数据同步建议通过Cron定时任务、后台控制台命令、异步队列实现，禁止放在前台用户请求中执行，避免页面超时。核心逻辑如下：

• 全量同步场景：拉取数据前先清空对应表的历史数据
• 增量同步场景：根据乐天商品ID判断数据是否存在，存在则更新，不存在则新增
• 单条数据保存失败要捕获异常，打日志记录，不中断整个同步流程

以Cron定时任务为例，参考代码路径app/code/Vendor/Rakuten/Cron/SyncData.php：

<?php
namespace Vendor\Rakuten\Cron;

use Vendor\Rakuten\Service\RakutenApi;
use Vendor\Rakuten\Model\PopularItemFactory;
use Vendor\Rakuten\Model\ResourceModel\PopularItem\CollectionFactory as PopularCollectionFactory;
use Psr\Log\LoggerInterface;

class SyncData
{
    public function __construct(
        protected RakutenApi $rakutenApi,
        protected PopularItemFactory $popularItemFactory,
        protected PopularCollectionFactory $popularCollectionFactory,
        protected LoggerInterface $logger
    ) {}

    public function execute()
    {
        // 同步热门商品
        $popularItems = $this->rakutenApi->getPopularItems();
        // 全量同步先清空旧数据
        $this->popularCollectionFactory->create()->walk('delete');
        
        foreach ($popularItems as $index => $itemData) {
            try {
                $item = $itemData['Item'] ?? [];
                if (empty($item['itemCode'])) continue;
                
                $popularModel = $this->popularItemFactory->create();
                $popularModel->setData([
                    'rakuten_item_id' => $item['itemCode'],
                    'item_name' => $item['itemName'],
                    'item_price' => $item['itemPrice'],
                    'image_url' => $item['mediumImageUrls'][0]['imageUrl'] ?? '',
                    'item_url' => $item['itemUrl'],
                    'rank' => $index + 1
                ])->save();
            } catch (\Exception $e) {
                $this->logger->error('乐天热门商品保存失败：' . $e->getMessage(), ['item' => $itemData]);
            }
        }

        // 搜索商品同步逻辑和上述逻辑一致，替换对应模型、接口方法即可
    }
}

优化建议 #

• 单批次同步数据量超过1000条时，不要循环调用save()方法，改用ResourceModel的批量插入方法提升性能
• API请求增加重试机制，最多重试2-3次，提升同步成功率
• 同步逻辑加文件锁/缓存锁，避免重复执行导致数据重复写入
• 后台增加手动同步按钮，方便运营人员触发即时同步

内容的提问来源于stack exchange，提问作者kato