mysql date storage&查询性能与php [英] mysql date storage & query performance with php

问题描述

我有一个来自WW2的历史记录数据库，目前每个记录的事件的日期都存储在一个整数字段中作为YYMMDDHHMM。这样可以使ORDER BY等简单的任务，或者在一段时间内搜索所有事件都非常容易。然而，如果有人想要在战争的任何一年的12月1日发生的所有事件都有点痛苦，并且当向访问者显示记录时，字符串必须被分解成5个变量以进行格式化，从而使得相当难看的PHP代码。 （好吧，至少当我写的时候，哈哈）

如果我们把这个字符串分成5个，有没有人知道性能后果离散列？或者如果我们保留这些搜索的原始列，那么对于更多的列（年，月，日，小时，分钟）也是有用的，并附加了5列，以便于数据显示？

这是一个明显的重复数据，在同一个表中也不少，哪种是一种规范化，不，不是吗？但是似乎使得PHP方面的事情变得更加容易...

谢谢，

• A

解决方案

为什么不使用MySQL的 datetime 字段和大量的函数，使日期相关的查询快速吗？

I've got a database of historical records from WW2 and currently each recorded event's date is stored in one integer field as YYMMDDHHMM. This makes simple tasks like ORDER BY, or searching for all events within a certain time period extremely easy. However, if somebody wants all events that occurred on December 1st in any year of the war that's a bit of a pain, and when displaying records to the visitor that string must be broken up into 5 variables for formatting purposes making for rather ugly PHP code. (well, at least when I write it, ha)

Does anyone have an idea of the performance consequences if we broke that string into 5 discrete columns? Or if we kept the original column for those searches it is useful for and appended 5 more columns (Year, Month, Day, Hour, Minute) for ease of data display?

This is an obvious duplication of data, and in the same table no less, which is kind of a normalization no-no is it not? But it would seem to make the PHP side of things much easier...

Thank you kindly,

• A

解决方案

Why not use MySQL's datetime field and the vast array of functions that make date-related queries a snap?

这篇关于mysql date storage&查询性能与php的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！