MySQL 中基于两表数据实现插入或更新操作-php教程-PHP中文网

MySQL 中基于两表数据实现插入或更新操作

霞舞

发布： 2025-08-20 17:22:14

原创

594人浏览过

mysql 中基于两表数据实现插入或更新操作

本文旨在提供一种利用 MySQL 实现数据表间插入或更新记录的方案。通过结合 IFNULL 函数和 INSERT ON DUPLICATE KEY UPDATE 语句，我们能够高效地将一个表中的数据合并到另一个表中，并处理目标表中已存在或不存在相应记录的情况，从而简化数据同步和更新的流程。

场景描述

假设我们有两个表：parts 和 magazzino。

parts 表包含 codice (代码), pezzi (数量), durata (持续时间) 等字段。
magazzino 表包含 codiceM (代码), pezziM (数量), durataM (持续时间) 等字段。

目标是将 parts 表中的数据合并到 magazzino 表中，具体逻辑如下：

如果 parts 表中的 codice 在 magazzino 表中不存在对应的 codiceM，则将 parts 表中的 codice、pezzi 和 durata 插入到 magazzino 表中。
如果 parts 表中的 codice 在 magazzino 表中已存在对应的 codiceM，则将 parts 表中的 pezzi 和 durata 分别加到 magazzino 表中对应记录的 pezziM 和 durataM 字段上，并更新 magazzino 表。

解决方案

1. 使用 SELECT 语句计算需要插入或更新的数据

首先，使用 SELECT 语句从两个表中检索数据，并使用 IFNULL 函数处理 magazzino 表中可能不存在对应记录的情况：

SELECT
    p.codice,
    IFNULL(p.pezzi, 0) + IFNULL(m.pezziM, 0) AS updatedPezzi,
    IFNULL(p.durata, 0) + IFNULL(m.durataM, 0) AS updatedDurata
FROM
    parts AS p
LEFT JOIN
    magazzino AS m ON p.codice = m.codiceM;

登录后复制

这个 SELECT 语句会返回一个结果集，包含 codice、updatedPezzi 和 updatedDurata 三列。updatedPezzi 和 updatedDurata 分别是 parts 表中的 pezzi 和 durata 与 magazzino 表中对应记录的 pezziM 和 durataM 的和。如果 magazzino 表中不存在对应的记录，则 IFNULL 函数会返回 0，确保计算结果的正确性。

可以使用 WHERE 子句添加条件，例如只计算特定行的结果。

腾讯智影-AI数字人

基于AI数字人能力，实现7*24小时AI数字人直播带货，低成本实现直播业务快速增增，全天智能在线直播

查看详情

2. 使用 INSERT ON DUPLICATE KEY UPDATE 语句插入或更新数据

接下来，使用 INSERT ON DUPLICATE KEY UPDATE 语句将 SELECT 语句的结果插入或更新到 magazzino 表中。要使用此语句，magazzino 表的 codiceM 字段需要定义为 UNIQUE 索引。

INSERT INTO magazzino (codiceM, pezziM, durataM)
SELECT
    p.codice,
    IFNULL(p.pezzi, 0) + IFNULL(m.pezziM, 0) AS updatedPezzi,
    IFNULL(p.durata, 0) + IFNULL(m.durataM, 0) AS updatedDurata
FROM
    parts AS p
LEFT JOIN
    magazzino AS m ON p.codice = m.codiceM
ON DUPLICATE KEY UPDATE
    pezziM = VALUES(pezziM),
    durataM = VALUES(durataM);

登录后复制

这条语句首先尝试将 SELECT 语句的结果插入到 magazzino 表中。如果 codiceM 已经存在于 magazzino 表中（由于 codiceM 是 UNIQUE 索引），则会执行 ON DUPLICATE KEY UPDATE 子句，更新 pezziM 和 durataM 字段。 VALUES(pezziM) 和 VALUES(durataM) 分别表示要插入的新值。

PHP 代码示例

以下是一个使用 PHP 执行上述 SQL 语句的示例：

<?php

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "provaMagazzino";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检测连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

$sql = "
INSERT INTO magazzino (codiceM, pezziM, durataM)
SELECT
    p.codice,
    IFNULL(p.pezzi, 0) + IFNULL(m.pezziM, 0) AS updatedPezzi,
    IFNULL(p.durata, 0) + IFNULL(m.durataM, 0) AS updatedDurata
FROM
    parts AS p
LEFT JOIN
    magazzino AS m ON p.codice = m.codiceM
ON DUPLICATE KEY UPDATE
    pezziM = VALUES(pezziM),
    durataM = VALUES(durataM);
";

if ($conn->query($sql) === TRUE) {
    echo "记录更新成功";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();

?>

登录后复制

这段代码首先连接到 MySQL 数据库，然后执行 INSERT ON DUPLICATE KEY UPDATE 语句。如果执行成功，则输出 "记录更新成功"，否则输出错误信息。