PostgreSQL中高效插入日期区间与房间组合数据的方法

嘿，这个批量生成酒店预订数据的需求太戳痛点了——手动插30+房间的月度/年度数据简直是折磨！我给你分享两个实用方案，不管用纯SQL还是简单脚本都能轻松搞定。

问题背景 #

我正在为模拟酒店项目搭建数据库，需要为每个入住日期（check_in）和退房日期（check_out）插入对应房间数据。少量示例数据操作无难度，但对于拥有30间以上客房的酒店而言，手动操作会极为繁琐。请问如何将房间列表批量插入到如一个月甚至一年的日期区间中？

示例数据表 #

booking表

booking_id | check_in   | check_out  | guest_id | room_id
-----------|------------|------------|----------|--------
1          | '05/10/2018'| 05/11/2018 | null     | 101
2          | '05/10/2018'| 05/11/2018 | null     | 102
3          | '05/10/2018'| 05/11/2018 | null     | 103
4          | '05/11/2018'| 05/12/2018 | null     | 101
5          | '05/11/2018'| 05/12/2018 | null     | 102
6          | '05/11/2018'| 05/12/2018 | null     | 103

room表

room_id | price
--------|------
101     | $68
102     | $68
103     | $90

guest表

guest_id | name
---------|-------------
32       | tony stark
33       | iron man
34       | robert downey

方案1：用SQL递归CTE批量生成（纯数据库操作） #

如果你的数据库支持递归CTE（比如MySQL 8.0+、PostgreSQL、SQL Server），这是最直接的方式——不用写外部脚本，直接在数据库里生成日期范围，再和房间表关联插入。

示例代码（MySQL版本）

假设我们要生成2018-05-10到2018-05-31期间，每天所有房间的预订记录（和示例一样，入住1天，check_out为次日）：

-- 第一步：用递归CTE生成日期范围
WITH RECURSIVE date_range AS (
    -- 起始日期
    SELECT '2018-05-10' AS check_in, DATE_ADD('2018-05-10', INTERVAL 1 DAY) AS check_out
    UNION ALL
    -- 递归生成后续日期，直到结束日
    SELECT DATE_ADD(check_in, INTERVAL 1 DAY), DATE_ADD(check_out, INTERVAL 1 DAY)
    FROM date_range
    WHERE check_in < '2018-05-31'
)
-- 第二步：关联房间表，批量插入到booking表
INSERT INTO booking (check_in, check_out, guest_id, room_id)
SELECT dr.check_in, dr.check_out, NULL, r.room_id
FROM date_range dr
CROSS JOIN room r
-- 可选：避免重复插入已存在的记录
WHERE NOT EXISTS (
    SELECT 1 FROM booking 
    WHERE check_in = dr.check_in AND room_id = r.room_id
);

适配其他数据库

• PostgreSQL：把DATE_ADD(xxx, INTERVAL 1 DAY)改成xxx + INTERVAL '1 day'
• SQL Server：用DATEADD(day, 1, xxx)替换日期函数

方案2：用Python脚本批量生成（灵活度更高） #

如果你的数据库版本不支持递归CTE，或者需要更灵活的逻辑（比如随机分配客人、设置不同入住时长），用Python脚本会更方便。

示例代码

import mysql.connector
from datetime import datetime, timedelta
import random

# 数据库连接信息（替换成你的配置）
db_config = {
    'user': 'your_username',
    'password': 'your_password',
    'host': 'localhost',
    'database': 'hotel_db'
}

# 配置日期范围和入住时长（这里默认1天）
start_date = datetime(2018, 5, 10)
end_date = datetime(2018, 5, 31)
stay_days = 1

# 连接数据库，获取所有房间ID和客人ID
conn = mysql.connector.connect(**db_config)
cursor = conn.cursor()

# 获取房间列表
cursor.execute("SELECT room_id FROM room")
room_ids = [row[0] for row in cursor.fetchall()]

# 可选：获取客人列表，用于随机分配
cursor.execute("SELECT guest_id FROM guest")
guest_ids = [row[0] for row in cursor.fetchall()]

# 生成批量插入数据
insert_records = []
current_date = start_date
while current_date <= end_date:
    check_in = current_date.strftime('%Y-%m-%d')
    check_out = (current_date + timedelta(days=stay_days)).strftime('%Y-%m-%d')
    
    for room_id in room_ids:
        # 可选：随机分配客人，否则用NULL
        selected_guest = random.choice(guest_ids) if guest_ids else None
        insert_records.append((check_in, check_out, selected_guest, room_id))
    
    current_date += timedelta(days=1)

# 执行批量插入
cursor.executemany(
    "INSERT INTO booking (check_in, check_out, guest_id, room_id) VALUES (%s, %s, %s, %s)",
    insert_records
)
conn.commit()

# 关闭连接
cursor.close()
conn.close()
print(f"成功插入{len(insert_records)}条预订记录！")

小提示 #

1. 如果需要生成多日入住的记录，可以调整stay_days变量，或者在循环里随机生成1-3天的入住时长。
2. 批量插入前建议先备份数据库，或者在测试环境验证数据格式。
3. 如果数据量极大（比如一年30+房间），可以分批次插入，避免一次性占用过多数据库资源。

内容的提问来源于stack exchange，提问作者Wolf_Tru