将 Lazada 爬取结果落地 MySQL：PHP 数据持久化实战

http://<亿牛云用户名>:<密码>@tunnel.16yun.cn:<端口>

CREATE TABLE lazada_products (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  item_id       VARCHAR(32)  NOT NULL COMMENT 'Lazada 商品ID',
  title         VARCHAR(512) NOT NULL DEFAULT '',
  seller_name   VARCHAR(128) NOT NULL DEFAULT '',
  price         DECIMAL(12,2) NOT NULL DEFAULT 0,
  original_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  currency      CHAR(3)      NOT NULL DEFAULT 'USD',
  rating        DECIMAL(3,2) NOT NULL DEFAULT 0,
  review_count  INT UNSIGNED NOT NULL DEFAULT 0,
  stock         INT          NOT NULL DEFAULT 0,
  category      VARCHAR(128) NOT NULL DEFAULT '',
  image_url     VARCHAR(1024) NOT NULL DEFAULT '',
  url           VARCHAR(1024) NOT NULL DEFAULT '',
  country       CHAR(2)      NOT NULL DEFAULT 'SG' COMMENT '站点国家',
  last_crawled  DATETIME     NOT NULL,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_item (item_id, country)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE lazada_price_history (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  item_id     VARCHAR(32) NOT NULL,
  price       DECIMAL(12,2) NOT NULL,
  crawled_at  DATETIME NOT NULL,
  KEY idx_item (item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

composer require guzzlehttp/guzzle symfony/dom-crawler symfony/css-selector

<?php
require 'vendor/autoload.php';

use GuzzleHttp\Client;
use Symfony\Component\DomCrawler\Crawler;

$proxy = sprintf(
    'http://%s:%s@tunnel.16yun.cn:3100',
    getenv('YINIU_USER'),
    getenv('YINIU_PASS')
);

$client = new Client([
    'proxy' => $proxy,
    'timeout' => 15,
    'headers' => [
        'User-Agent' => 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36',
        'Accept-Language' => 'en-US,en;q=0.9',
    ],
]);

function fetchProduct(Client $client, string $url): array
{
    $html = $client->get($url)->getBody()->getContents();
    $crawler = new Crawler($html);

    $script = $crawler->filter('script:contains("__INITIAL_STATE__")')->first()->text();
    preg_match('/__INITIAL_STATE__\s*=\s*(\{.*?\});/', $script, $m);
    $data = json_decode($m[1], true);

    $p = $data['mods']['pageProduct'] ?? [];
    return [
        'item_id'     => $p['itemId'] ?? '',
        'title'       => $p['title'] ?? '',
        'price'       => ($p['price'] ?? 0) / 100,   // Lazada 金额以最小货币单位存储，需除以 100
        'seller_name' => $p['sellerName'] ?? '',
        'rating'      => $p['ratingScore'] ?? 0,
        'review_count'=> $p['review'] ?? 0,
        'stock'       => $p['stock'] ?? 0,
        'image_url'   => $p['image'] ?? '',
        'url'         => $url,
    ];
}

<?php
$pdo = new PDO(
    'mysql:host=127.0.0.1;dbname=lazada;charset=utf8mb4',
    getenv('DB_USER'), getenv('DB_PASS'),
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

function persist(PDO $pdo, array $row, string $country)
{
    $now = date('Y-m-d H:i:s');
    $sql = "INSERT INTO lazada_products
      (item_id, title, seller_name, price, currency, rating, review_count, stock, image_url, url, country, last_crawled)
      VALUES (?,?,?,?, 'USD', ?,?,?,?,?,?,?)
      ON DUPLICATE KEY UPDATE
        title=VALUES(title), price=VALUES(price), rating=VALUES(rating),
        review_count=VALUES(review_count), stock=VALUES(stock),
        last_crawled=VALUES(last_crawled)";
    $pdo->prepare($sql)->execute([
        $row['item_id'], $row['title'], $row['seller_name'],
        $row['price'], $row['rating'], $row['review_count'],
        $row['stock'], $row['image_url'], $row['url'], $country, $now
    ]);

    // 仅当价格相对上次发生变化时记录历史，避免历史表被重复值撑大
    $prev = $pdo->prepare("SELECT price FROM lazada_price_history WHERE item_id = ? ORDER BY crawled_at DESC LIMIT 1");
    $prev->execute([$row['item_id']]);
    if ($prev->fetchColumn() != $row['price']) {
        $pdo->prepare("INSERT INTO lazada_price_history (item_id, price, crawled_at) VALUES (?,?,?)")
            ->execute([$row['item_id'], $row['price'], $now]);
    }
}