存储日期 i 火车时刻表 MYSQL
我在 MYSQL 中创建了一个火车时刻表数据库。每天有数千条路线。但除了少数例外，大多数工作日的路线都是相似的，但周末的路线有所不同。
此时我基本上每天午夜都会更新 SQL 表，以获取接下来 24 小时的出发时间。然而，这是非常不方便的。所以我需要一种方法在表中存储日期，这样我就不必每天都这样做。
我尝试创建一个单独的表，在其中存储每个路线编号的日期（路线编号每天都会重置），但这使我的查询速度如此之慢以至于无法使用。这是否意味着我必须将出发和到达时间存储为日期时间？在这种情况下，包含路由的主表将有数百万个条目。
或者还有别的办法吗？
我的路由表如下所示：
StnCode (referenced in seperate Station table) DepTime ArrTime Routenumber legNumber
I have created a train schedule database in MYSQL. There are several thousand routes for each day. But with a few exceptions most of the routes are similar for every working day, but differ on weekends.
At this time I basically update my SQL tables at midnight each day, to get the departures for the next 24 hours. This is however very inconvenient. So I need a way to store dates in my tables so I don't have to do this every day.
I tried to create a separate table where I stored dates for each routenumber (routenumbers are resetted each day), but this made my query so slow that it was impossible to use. Does this mean I would have to store my departure and arrival times as datetimes? In that case the main table containing routes would have several million entries.
Or is there another way?
My routetable looks like this:
StnCode (referenced in seperate Station table) DepTime ArrTime Routenumber legNumber
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
你是如何存储日期的？单个日期/时间字段？这肯定是最紧凑的表示，但也是最难索引和扫描的，特别是如果您正在执行以下类型的查询：
这样的构造将需要全表扫描来分解每个日期字段并提取月/日。对于这种情况，最好进行一些非名词化，并将日期时间拆分为单独的年/月/日/小时/分钟字段，并将索引放入其中。维护起来比较麻烦，但也会极大地加快特定时间部分的查询速度。
How were you storing the dates? A single date/time field? That'd certainly be the most compact representation, but also the most difficult to index and scan, especially if you're doing queries of the following type:
Such a construct would require a full table scan to tear apart each date field and extract the month/day. For such a case, it'd be better to denomalize a bit and split the datetime into seperate year/month/day/hour/minute fields and place indeces onto them. Bit more of a hassle to maintain, but would also speed up querying by specific time parts immensely.
您可以按日期（周日、周一、周二等）存储日程，而不是按日期存储日程。这将消除存储路线日期的情况。您可以将路线视为预先确定的，因此它们是固定的。由于列车数量约为8000列（旅客列车），并且天数固定（7），路线为（50-1000），每个表如，1, 1A，如铁路书籍中发布的，
这将避免将大量火车时刻表组合存储到数据库中，因为每个日期都会转换为工作日之一，并且我们不会丢失任何数据。
您可以创建一个表来存储日期，该表将最多 7 天。
我建议以这样的方式对数据库进行建模，即每个站点都是一个接触点，而不是站点 ID....
并且可以在设计中引入hub的概念，识别3-4个车站，它们是同一个城市的....
每个车站都是一个接触点，有上车点、HALT等设施支持POINT等...
因为，并非所有车站都是所有火车的登机点。 .
设施是在不同车站可用的设施...
并非所有列车都可使用所有设施...，
例如：Kazipet 是一个车站，也是一个交汇处...但是对于少数火车，在几条路线上，
他们经过车站，也停在车站，但不允许新乘客在车站上车。
但是，它将允许在反向路线上进行相同的操作...
Instead of storing Schedules in terms of dates, you can store them against day (Sun, Mon, Tue, etc). This will eliminate storing the dates for routes. You can treat the routes as predetermined, and thus they are fixed. As the number of trains are around 8000(passenger trains) and days are fixed (7), routes are (50-1000), each table like, 1, 1A, AS PUBLISHED IN RAILWAY BOOKS,
This will avoid storing huge combinations of train schedules into the db since every date is translated into one of the weekdays and we are not missing any data.
You can create a table for storing days which will have at most 7 days.
I would suggest to model the database in such a way, that each station is a touch point, and not as station id....
and you can introduce the hub concept in the design, to identify 3-4 stations, which are of the same city....
each station is a touch point, where it is supported by facilities like,boarding point,HALT POINT etc...
cause, not all the stations are boarding points for all the trains..
facilities are the ones which are available at different stations...
all facilities are not available for all the trains...,
ex: Kazipet is a station, which is also a junction...but for FEW TRAINS,ON few routes,
they pass thru the station, and it also halts at the station, but, it will not allow new passengers to board at the station(s).
But, it will allow the same on reverse routes...