煤矿数据处理：日数据与月数据的协同更新（Mysql）

项目需求

该项目围绕煤矿数据处理展开，主要需求可归纳为数据存储与更新两方面：

• 数据存储：
  • 建立两个MySQL数据库表，daily_data用于存储每日煤矿车次重量数据，包含id（自增主键）、date（日期）、coal_mine（煤矿名称）、weight（重量）字段；monthly_data用于存储每月各煤矿销量汇总数据，包含id（自增主键）、year_month（年月）、coal_mine（煤矿名称）、total_weight（总重量）字段，并在year_month和coal_mine字段上建立联合唯一索引 。
• 数据更新：在PHP程序中，当有新的日数据（包含日期、煤矿名称、重量）插入daily_data表时，需要同步更新monthly_data表。先获取日数据中的日期并提取年月信息，然后使用INSERT INTO... ON DUPLICATE KEY UPDATE语句更新monthly_data表，若对应年月和煤矿的记录已存在，则增加其总重量；若不存在，则插入新记录。

核心代码

主要在数据库连接、SQL 语句执行以及性能优化方面进行了改进，使用预处理语句提高安全性和性能，在月数据处理上使用 INSERT INTO... ON DUPLICATE KEY UPDATE 语句简化操作。同时，在 monthly_data 表的 year_month 和 coal_mine 字段上建立联合索引以加快查询速度。

首先，创建数据库表并添加索引：

-- 创建每日数据表格
CREATE TABLE daily_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL,
    coal_mine VARCHAR(255) NOT NULL,
    weight DECIMAL(10, 2) NOT NULL
);

-- 创建每月数据表格
CREATE TABLE monthly_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    year_month VARCHAR(7) NOT NULL,
    coal_mine VARCHAR(255) NOT NULL,
    total_weight DECIMAL(10, 2) NOT NULL,
    UNIQUE KEY idx_year_month_coal_mine (year_month, coal_mine)
);

然后实现插入日数据并同步更新月数据：

<?php
// 数据库连接配置
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

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

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

// 模拟插入的日数据
$date = '2025-01-27';
$coal_mine = '煤矿A';
$weight = 150;

// 插入日数据，使用预处理语句
$sql_insert_daily = "INSERT INTO daily_data (date, coal_mine, weight) VALUES (?,?,?)";
$stmt_insert_daily = $conn->prepare($sql_insert_daily);
$stmt_insert_daily->bind_param("ssd", $date, $coal_mine, $weight);
if ($stmt_insert_daily->execute()) {
    // 获取年月格式数据
    $year_month = date('Y-m', strtotime($date));

    // 更新月数据，使用 INSERT INTO... ON DUPLICATE KEY UPDATE 语句
    $sql_update_monthly = "INSERT INTO monthly_data (year_month, coal_mine, total_weight) 
                           VALUES (?,?,?) 
                           ON DUPLICATE KEY UPDATE total_weight = total_weight +?";
    $stmt_update_monthly = $conn->prepare($sql_update_monthly);
    $stmt_update_monthly->bind_param("sss", $year_month, $coal_mine, $weight, $weight);
    if ($stmt_update_monthly->execute()) {
        echo "日数据插入成功，月数据已同步更新。";
    } else {
        echo "更新月数据时出错: ". $stmt_update_monthly->error;
    }
    $stmt_update_monthly->close();
} else {
    echo "插入日数据时出错: ". $stmt_insert_daily->error;
}
$stmt_insert_daily->close();
// 关闭连接
$conn->close();
?>

上述代码通过使用预处理语句和优化后的 SQL 操作，提高了代码的安全性和执行效率，同时在月数据处理上减少了数据库交互次数。

利弊分析

插入日数据时同步更新月数据是可行的。

优点

• 数据实时性高：能确保月数据随着日数据的插入及时更新，使用者随时获取的月数据都是最新、最准确的，不存在数据延迟问题，为决策等提供及时的数据支持。
• 操作相对简单：在插入日数据的同时更新月数据，逻辑较为直接，代码实现相对容易理解和维护，不需要额外单独编写复杂的定时任务或批量处理程序来专门更新月数据。
• 减少数据不一致性：将日数据插入和月数据更新放在一个操作流程中，可降低因各种原因（如定时任务失败、数据处理顺序问题等）导致日数据和月数据不一致的风险，保持数据的完整性和一致性。

缺点

• 性能开销大：每次插入日数据都要更新月数据，若日数据插入频繁，会增加数据库的写入操作次数，对数据库性能产生影响，可能导致数据库负载过高，影响系统整体响应速度。
• 事务处理复杂：若插入日数据和更新月数据在一个事务中，事务失败时回滚操作复杂，可能需要同时回滚日数据插入和月数据更新，增加了事务管理的复杂性和潜在的错误风险。
• 代码耦合度高：插入日数据和更新月数据的逻辑紧密耦合在一起，若后续业务逻辑有变化，比如月数据的统计方式改变，可能需要同时修改插入日数据和更新月数据的代码，维护成本增加，代码的可扩展性和可维护性降低。

@漏刻有时