你提供了一个包含多个字段的表结构,其中 `id` 是主键。我们基于这个信息生成 **批量插入或更新(Upsert)语句**,适用于常见的数据库系统。
我们将以 **MySQL** 为例(使用 `ON DUPLICATE KEY UPDATE`),并附上 PostgreSQL 和 SQLite 的等价写法。
---
### ✅ 假设
- 表名为:`ics_declared_work`(你可以根据实际名称修改)
- 所有字段均可更新(除 `id` 外)
- 使用 `id` 作为唯一主键进行判断是否已存在
- 提供的数据是多条记录
---
## ✅ 1. MySQL 批量插入或更新(推荐)
```sql
INSERT INTO ics_declared_work (
id,
area_name,
work_num,
work_name,
team_id,
area_id,
cnpec_user,
create_by,
non_work_time_flag,
work_level,
work_status,
create_date,
management_user,
work_first_time,
h_point_id,
work_code,
company_id,
department_id,
department_name,
work_type,
end_time,
three_new_flag,
team_name,
update_date,
start_time,
safety_user,
great_danger,
company_name,
name,
secluded_flag,
full_name,
date_name
) VALUES
(1, '区域A', 'W001', '焊接作业', 'T001', 101, '张三', 'admin', 0, '高风险', 'IN_PROGRESS', '2025-04-01 08:00:00', '李四', '2025-04-01 07:30:00', NULL, 'WC001', 1, 10, '工程部', '动火作业', '2025-04-01 18:00:00', 1, '焊工一组', '2025-04-01 19:00:00', '2025-04-01 08:00:00', '王五', '重大危险源', 'XX建设公司', '焊接任务1', 1, '焊接作业-焊工一组-20250401', '白天'),
(2, '区域B', 'W002', '高空作业', 'T002', 102, '李四', 'user1', 1, '中风险', 'END', '2025-04-02 08:00:00', '赵六', '2025-04-02 07:45:00', 1001, 'WC002', 1, 11, '安全部', '高处作业', '2025-04-02 17:00:00', 0, '高空组', '2025-04-02 17:30:00', '2025-04-02 08:00:00', '钱七', NULL, 'XX建设公司', '高空任务1', 0, '高空作业-高空组-20250402', '白天')
-- 可继续添加更多行...
ON DUPLICATE KEY UPDATE
area_name = VALUES(area_name),
work_num = VALUES(work_num),
work_name = VALUES(work_name),
team_id = VALUES(team_id),
area_id = VALUES(area_id),
cnpec_user = VALUES(cnpec_user),
create_by = VALUES(create_by),
non_work_time_flag = VALUES(non_work_time_flag),
work_level = VALUES(work_level),
work_status = VALUES(work_status),
create_date = VALUES(create_date),
management_user = VALUES(management_user),
work_first_time = VALUES(work_first_time),
h_point_id = VALUES(h_point_id),
work_code = VALUES(work_code),
company_id = VALUES(company_id),
department_id = VALUES(department_id),
department_name = VALUES(department_name),
work_type = VALUES(work_type),
end_time = VALUES(end_time),
three_new_flag = VALUES(three_new_flag),
team_name = VALUES(team_name),
update_date = VALUES(update_date),
start_time = VALUES(start_time),
safety_user = VALUES(safety_user),
great_danger = VALUES(great_danger),
company_name = VALUES(company_name),
name = VALUES(name),
secluded_flag = VALUES(secluded_flag),
full_name = VALUES(full_name),
date_name = VALUES(date_name);
```
> 🔍 **说明:**
> - `VALUES(col)` 表示本次插入的值
> - 如果某条记录的 `id` 已存在,则执行后面的 `UPDATE`
> - 要求 `id` 字段上有主键或唯一索引
---
## ✅ 2. PostgreSQL 批量 Upsert(使用 `ON CONFLICT`)
```sql
INSERT INTO ics_declared_work (
id,
area_name,
work_num,
work_name,
team_id,
area_id,
cnpec_user,
create_by,
non_work_time_flag,
work_level,
work_status,
create_date,
management_user,
work_first_time,
h_point_id,
work_code,
company_id,
department_id,
department_name,
work_type,
end_time,
three_new_flag,
team_name,
update_date,
start_time,
safety_user,
great_danger,
company_name,
name,
secluded_flag,
full_name,
date_name
) VALUES
(1, '区域A', 'W001', '焊接作业', 'T001', 101, '张三', 'admin', 0, '高风险', 'IN_PROGRESS', '2025-04-01 08:00:00', '李四', '2025-04-01 07:30:00', NULL, 'WC001', 1, 10, '工程部', '动火作业', '2025-04-01 18:00:00', 1, '焊工一组', '2025-04-01 19:00:00', '2025-04-01 08:00:00', '王五', '重大危险源', 'XX建设公司', '焊接任务1', 1, '焊接作业-焊工一组-20250401', '白天'),
(2, '区域B', 'W002', '高空作业', 'T002', 102, '李四', 'user1', 1, '中风险', 'END', '2025-04-02 08:00:00', '赵六', '2025-04-02 07:45:00', 1001, 'WC002', 1, 11, '安全部', '高处作业', '2025-04-02 17:00:00', 0, '高空组', '2025-04-02 17:30:00', '2025-04-02 08:00:00', '钱七', NULL, 'XX建设公司', '高空任务1', 0, '高空作业-高空组-20250402', '白天')
ON CONFLICT (id) DO UPDATE SET
area_name = EXCLUDED.area_name,
work_num = EXCLUDED.work_num,
work_name = EXCLUDED.work_name,
team_id = EXCLUDED.team_id,
area_id = EXCLUDED.area_id,
cnpec_user = EXCLUDED.cnpec_user,
create_by = EXCLUDED.create_by,
non_work_time_flag = EXCLUDED.non_work_time_flag,
work_level = EXCLUDED.work_level,
work_status = EXCLUDED.work_status,
create_date = EXCLUDED.create_date,
management_user = EXCLUDED.management_user,
work_first_time = EXCLUDED.work_first_time,
h_point_id = EXCLUDED.h_point_id,
work_code = EXCLUDED.work_code,
company_id = EXCLUDED.company_id,
department_id = EXCLUDED.department_id,
department_name = EXCLUDED.department_name,
work_type = EXCLUDED.work_type,
end_time = EXCLUDED.end_time,
three_new_flag = EXCLUDED.three_new_flag,
team_name = EXCLUDED.team_name,
update_date = EXCLUDED.update_date,
start_time = EXCLUDED.start_time,
safety_user = EXCLUDED.safety_user,
great_danger = EXCLUDED.great_danger,
company_name = EXCLUDED.company_name,
name = EXCLUDED.name,
secluded_flag = EXCLUDED.secluded_flag,
full_name = EXCLUDED.full_name,
date_name = EXCLUDED.date_name;
```
> ⚠️ 注意:PostgreSQL 使用 `EXCLUDED` 代表新插入的行
---
## ✅ 3. SQLite 批量 Upsert(使用 `REPLACE INTO` 或 `INSERT OR REPLACE`)
如果你的数据完全由 `id` 决定唯一性,可以用:
```sql
INSERT OR REPLACE INTO ics_declared_work (
id,
area_name,
work_num,
work_name,
team_id,
area_id,
cnpec_user,
create_by,
non_work_time_flag,
work_level,
work_status,
create_date,
management_user,
work_first_time,
h_point_id,
work_code,
company_id,
department_id,
department_name,
work_type,
end_time,
three_new_flag,
team_name,
update_date,
start_time,
safety_user,
great_danger,
company_name,
name,
secluded_flag,
full_name,
date_name
) VALUES
(1, '区域A', 'W001', '焊接作业', 'T001', 101, '张三', 'admin', 0, '高风险', 'IN_PROGRESS', '2025-04-01 08:00:00', '李四', '2025-04-01 07:30:00', NULL, 'WC001', 1, 10, '工程部', '动火作业', '2025-04-01 18:00:00', 1, '焊工一组', '2025-04-01 19:00:00', '2025-04-01 08:00:00', '王五', '重大危险源', 'XX建设公司', '焊接任务1', 1, '焊接作业-焊工一组-20250401', '白天'),
(2, '区域B', 'W002', '高空作业', 'T002', 102, '李四', 'user1', 1, '中风险', 'END', '2025-04-02 08:00:00', '赵六', '2025-04-02 07:45:00', 1001, 'WC002', 1, 11, '安全部', '高处作业', '2025-04-02 17:00:00', 0, '高空组', '2025-04-02 17:30:00', '2025-04-02 08:00:00', '钱七', NULL, 'XX建设公司', '高空任务1', 0, '高空作业-高空组-20250402', '白天');
```
> ❗注意:`INSERT OR REPLACE` 会先删除再插入,如果有关联外键需谨慎。
---
## ✅ 动态生成工具建议(用于程序中)
如果你是在 Java / Python 中动态生成这类 SQL,可以这样做:
### Python 示例(构建 VALUES 部分)
```python
fields = [
"id", "area_name", "work_num", "work_name", "team_id", "area_id", "cnpec_user",
"create_by", "non_work_time_flag", "work_level", "work_status", "create_date",
"management_user", "work_first_time", "h_point_id", "work_code", "company_id",
"department_id", "department_name", "work_type", "end_time", "three_new_flag",
"team_name", "update_date", "start_time", "safety_user", "great_danger",
"company_name", "name", "secluded_flag", "full_name", "date_name"
]
def gen_value_placeholders(data_list):
placeholders = []
for row in data_list:
ph = "(" + ", ".join(f"'{v}'" if v is not None else "NULL" for v in row) + ")"
placeholders.append(ph)
return ",\n".join(placeholders)
# 示例数据
data = [
(1, '区域A', 'W001', ...),
(2, '区域B', 'W002', ...)
]
values_sql = gen_value_placeholders(data)
```
然后拼接到完整 SQL 中。
---