MySQL FlightOperatedby航班表跨天起降时间存储及插入实现方案

首先需要明确你现有写法的两个核心错误：

• 主键设计不合理：当前主键设置为(2-let ID, flight #)，意味着同一个航司的同一个航班号只能存入1条记录，无法支持同一个航班每周多个执飞班次的存储需求，会触发主键冲突报错
• 字段值不匹配类型：departing time和arriving time是timestamp类型，要求传入完整的年-月-日 时:分:秒格式的时间值，你写入的每周重复的描述类字符串不符合字段类型要求，无法插入成功

1. 最小改动适配现有表（仅测试单条数据用） #

如果不需要调整表结构，仅测试插入1条指定日期的跨天航班，直接给时间字段传入符合格式要求的完整时间即可，跨天场景只需要将到达时间设为起飞日期的次日即可：

INSERT INTO `FlightOperatedby` (`2-let ID`, `flight #`, `departing airport`, `arriving airport`, `departing time`, `arriving time`)
VALUES ('AA', 1, 'JFK', 'DEL', '2024-05-20 06:00:00', '2024-05-21 08:00:00');

注意：该方案下同一个航班号只能存1条记录，无法支持多班次存储需求

2. 优化表结构适配真实业务 #

针对真实航班运营场景，建议先调整表结构，解决主键冲突问题，同时替换timestamp为datetime避免2038年时间上限问题：

DROP TABLE IF EXISTS `FlightOperatedby`;
CREATE TABLE `FlightOperatedby` (
  `2-let ID` char(2) NOT NULL DEFAULT '' COMMENT '航司二字码',
  `flight #` int NOT NULL DEFAULT 0 COMMENT '航班号',
  `departing airport` char(3) NOT NULL COMMENT '出发机场IATA码',
  `arriving airport` char(3) NOT NULL COMMENT '到达机场IATA码',
  `departing time` datetime NOT NULL COMMENT '出发当地时间',
  `arriving time` datetime NOT NULL COMMENT '到达当地时间',
  PRIMARY KEY (`2-let ID`, `flight #`, `departing time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

调整后可批量插入多班次跨天航班，不会触发主键冲突：

INSERT INTO `FlightOperatedby` (`2-let ID`, `flight #`, `departing airport`, `arriving airport`, `departing time`, `arriving time`)
VALUES 
-- 周一出发周二到达
('AA', 1, 'JFK', 'DEL', '2024-05-20 06:00:00', '2024-05-21 08:00:00'),
-- 周三出发周四到达
('AA', 1, 'JFK', 'DEL', '2024-05-22 06:00:00', '2024-05-23 08:00:00'),
-- 周六出发周日到达
('AA', 1, 'JFK', 'DEL', '2024-05-25 06:00:00', '2024-05-26 08:00:00');

补充说明 #

如果需要存储航班的周期性排班规则（比如每周一、三、六飞），建议单独新增一张航班排班配置表存储规则，实际执飞的航班记录仍然存在FlightOperatedby表中即可。

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