如何向MONTH_YEAR表插入2010-2040年所有月度首日的日期记录

以下是两种常用的实现方式，可根据你使用的数据库类型按需选择：

方法1：纯SQL语句实现（无需额外脚本，适配大多数主流数据库） #

MySQL 代码 #

INSERT INTO MONTH_YEAR (date_column)
SELECT DATE_FORMAT(
    DATE_ADD('2010-01-01', INTERVAL (t4 * 1000 + t3 * 100 + t2 * 10 + t1) MONTH),
    '%d-%m-%Y'
) AS month_first_day
FROM 
    (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
    (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
    (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
    (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t4
WHERE DATE_ADD('2010-01-01', INTERVAL (t4 * 1000 + t3 * 100 + t2 * 10 + t1) MONTH) <= '2040-12-01';

PostgreSQL 代码 #

INSERT INTO MONTH_YEAR (date_column)
SELECT TO_CHAR(generate_series, 'DD-MM-YYYY') AS month_first_day
FROM generate_series('2010-01-01'::date, '2040-12-01'::date, '1 month'::interval);

Oracle 代码 #

INSERT INTO MONTH_YEAR (date_column)
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2010-01-01', 'YYYY-MM-DD'), LEVEL - 1), 'DD-MM-YYYY') AS month_first_day
FROM DUAL
CONNECT BY ADD_MONTHS(TO_DATE('2010-01-01', 'YYYY-MM-DD'), LEVEL - 1) <= TO_DATE('2040-12-01', 'YYYY-MM-DD');

方法2：Python脚本实现（适配所有数据库，灵活性更高） #

# 可替换为对应数据库的驱动，比如psycopg2 for PostgreSQL、cx_Oracle for Oracle
import pymysql
from datetime import datetime
from dateutil.relativedelta import relativedelta

# 按需修改为你的数据库连接配置
conn = pymysql.connect(
    host='数据库地址',
    user='用户名',
    password='密码',
    database='库名'
)
cursor = conn.cursor()

start_date = datetime(2010, 1, 1)
end_date = datetime(2040, 12, 1)
current_date = start_date
params = []

# 批量生成所有月度首日
while current_date <= end_date:
    params.append((current_date.strftime('%d-%m-%Y'),))
    current_date += relativedelta(months=1)

# 执行批量插入
cursor.executemany("INSERT INTO MONTH_YEAR (date_column) VALUES (%s)", params)
conn.commit()

cursor.close()
conn.close()

注意事项 #

• 上述代码中的date_column请替换为你MONTH_YEAR表中实际存储日期的字段名
• 如果你的日期字段是DATE类型而非字符串类型，可以去掉格式化步骤，直接插入原生日期值即可，不需要转成DD-MM-YYYY格式的字符串

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