mysql - 从现有时间戳记创建索引小时列 [英] mysql - create indexed hour column from existing timestamp
问题描述
我有一个updatetime列,它是一个时间戳(2011-02-01 09:00:51)。出于性能目的,我需要基于时间戳的小时创建一个索引列'updatetime_hour'。
例如,如果'updatetime'是2011-02-01 09:00:51then'updatetime_hour'将为9。
我试图在mysql做这一切,虽然PHP也是一个选项。
code> UPDATE yourtable SET updatetime_hour = HOUR(updatetime);
不要在高峰时段运行,这将需要一段时间。你甚至可以以更小的批量运行它 - 使 updatetime_hour
可空,并继续运行,直到你得到0行受影响:
UPDATE yourtable SET updatetime_hour = HOUR(updatetime)
WHERE updatetime_hour IS NULL LIMIT 1000;
I have a column 'updatetime' that is a timestamp ("2011-02-01 09:00:51"). For performance purposes I need to create an indexed column 'updatetime_hour' based on the hour of the timestamp.
So for example if 'updatetime' was "2011-02-01 09:00:51" then 'updatetime_hour' would be "9".
I'm trying to do this all in mysql though PHP is an option as well. 60k+ existing rows.
Thoughts?
UPDATE yourtable SET updatetime_hour=HOUR(updatetime);
Don't run this in peak hours, it will take a while. You could even run it in smaller batches - make updatetime_hour
nullable and continue running this, until you get "0 rows affected":
UPDATE yourtable SET updatetime_hour=HOUR(updatetime)
WHERE updatetime_hour IS NULL LIMIT 1000;
这篇关于mysql - 从现有时间戳记创建索引小时列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!