业务场景中使用 SQL 实现快速数据更新与插入

一、业务背景

在气象数据处理系统中，我们经常需要对分钟级的降水数据进行更新和插入操作。具体场景如下：

• 数据源会定期发送分钟级的降水数据，包括降水值（PRECA）和质控码（PRECA_QC2）。

• 如果系统中已存在对应时间点的数据，则需要更新降水值和质控码。

• 如果系统中不存在对应时间点的数据，则需要插入新的记录。

二、SQL 实现方法

为了高效地实现上述需求，我们采用INSERT INTO ... ON DUPLICATE KEY UPDATE语法。这种方法可以在尝试插入数据时，自动检测是否存在冲突（如主键或唯一键冲突），并在冲突发生时执行更新操作。

1.数据表结构

假设数据表名为pre_YPREC00_N01，其结构如下：

• pre_time：时间戳，主键或唯一键，格式为YYYY-MM-DD HH:MM:SS。

• PRECA：降水值，类型为浮点数。

• PRECA_QC2：质控码，类型为整数。

2.SQL 语句构造

插入并更新的 SQL 语句

INSERT INTO pre_YPREC00_N01 (pre_time, PRECA, PRECA_QC2)
VALUES
('2025-05-23 13:21:00', 0.1, 4),
('2025-05-23 13:22:00', 0.1, 4),
('2025-05-23 13:23:00', 0.1, 4),
('2025-05-23 13:24:00', 0.1, 4),
('2025-05-23 13:25:00', 0.1, 4),
('2025-05-23 13:26:00', 0.1, 4),
('2025-05-23 13:31:00', 0.1, 4),
('2025-05-23 13:32:00', 0.1, 4),
('2025-05-23 13:33:00', 0.1, 4),
('2025-05-23 13:34:00', 0.1, 4),
('2025-05-23 13:35:00', 0.1, 4),
('2025-05-23 13:36:00', 0.1, 4)
ON DUPLICATE KEY UPDATE
PRECA = VALUES(PRECA),
PRECA_QC2 = VALUES(PRECA_QC2);

3.代码实现

以下是使用 C++和 MySQL 的示例代码，展示如何动态生成上述 SQL 语句并执行：

#include <iostream>
#include <string>
#include <vector>
#include <mysql/mysql.h>// 假设 request 是一个包含分钟级数据的对象
struct MinuteData {std::string minute; // 时间戳，格式为 YYYY-MM-DD HH:MM:SSstd::string v;      // 降水值std::string qc;     // 质控码
};struct Request {std::vector<MinuteData> minutelist;
};void SetRainDataInDB(const Request& request, const std::string& strTableName) {int totalUpdates = request.minutelist.size();std::string strSqlBase = "INSERT INTO pre_" + strTableName + " (pre_time, PRECA, PRECA_QC2) VALUES ";std::string strSqlValues;for (size_t i = 0; i < totalUpdates; i++) {if (request.minutelist[i].v.empty()) {continue;}std::cout << "打印 订正的 [" << request.minutelist[i].minute << "] 分钟的 降水数据 [" << request.minutelist[i].v << "] 质控值 是[" << request.minutelist[i].qc << "]" << std::endl;strSqlValues += "('" + request.minutelist[i].minute + "', " + request.minutelist[i].v + ", 4), ";}// 去掉最后一个逗号if (!strSqlValues.empty()) {strSqlValues.pop_back();strSqlValues.pop_back(); // 去掉多余的逗号和空格}std::string strSqlOnDuplicateKeyUpdate = " ON DUPLICATE KEY UPDATE PRECA = VALUES(PRECA), PRECA_QC2 = VALUES(PRECA_QC2)";std::string strFullSQL = strSqlBase + strSqlValues + strSqlOnDuplicateKeyUpdate;std::cout << "SetRainDataInDB SQL: [" << strFullSQL << "]" << std::endl;// 连接数据库并执行 SQL 语句MYSQL* conn = mysql_init(NULL);if (mysql_real_connect(conn, "localhost", "username", "password", "database_name", 0, NULL, 0)) {if (mysql_query(conn, strFullSQL.c_str())) {std::cerr << "SQL 执行失败: " << mysql_error(conn) << std::endl;} else {std::cout << "SQL 执行成功" << std::endl;}mysql_close(conn);} else {std::cerr << "数据库连接失败: " << mysql_error(conn) << std::endl;}
}int main() {Request request;request.minutelist.push_back({"2025-05-23 13:21:00", "0.1", "4"});request.minutelist.push_back({"2025-05-23 13:22:00", "0.1", "4"});request.minutelist.push_back({"2025-05-23 13:23:00", "0.1", "4"});// 添加更多分钟级数据...SetRainDataInDB(request, "YPREC00_N01");return 0;
}

三、使用步骤

• 准备数据：
• 确保你有一个包含分钟级降水数据的数据源，数据格式应包括时间戳、降水值和质控码。

• 配置数据库：
• 确保数据库中存在目标表（如pre_YPREC00_N01），并且pre_time列是主键或唯一键。

• 运行代码：
• 将上述代码编译并运行，确保数据库连接信息正确。
• 代码将自动构造并执行 SQL 语句，实现数据的插入或更新。

四、注意事项

• 性能优化：
• 如果数据量较大，建议分批处理数据，避免一次性插入过多记录导致性能问题。

• 错误处理：
• 在实际应用中，应添加更详细的错误处理逻辑，确保系统在遇到异常时能够正确响应。

• 安全性：
• 避免 SQL 注入风险，确保输入数据经过适当的验证和转义。

通过上述方法，你可以快速实现气象数据的插入和更新操作，提高数据处理的效率和准确性。