使 ActiveRecord/Rails 使用实际的 mysql TIMESTAMP 列

2022-01-13 00:00:00 timestamp ruby-on-rails mysql activerecord

Rails 的 :timestamp 列类型所在；它实际上只是 :datetime 的别名.

Rails' :timestamp column type lies; it's actually just an alias for :datetime.

我正在使用 mysql，我想使用实际的 unix-timestamp TIMESTAMP 列.

I'm using mysql, and I want to use actual unix-timestamp TIMESTAMP columns.

a) 除了使用 SQL 来创建列之外，还有什么好的设置方法吗?

a) Is there a nice way to set this, other than just making the column using SQL?

b) ActiveRecord 会正确处理它吗(例如，在必要时转换为 Time，接受 unix 时间戳 Integer 作为输入等)?我应该在哪里处理哪些问题?

b) Will ActiveRecord cope with it properly (e.g. converting to Time when necessary, accepting a unix timestamp Integer as input, etc)? What gotchas should I expect to have to handle, and where?

为什么:

  1. 速度.这是一个非常活跃的表，它聚合了已经使用 unix 时间戳的外部数据源.转换为 datetime(或者甚至首先转换为一个 db 字符串，它经过 2 个 gsubs)占用了大部分的导入时间.否则我可能只是在做一个非常便宜的 Integer#to_s 调用.

  1. Speed. This is for an extremely active table that's aggregating outside data sources that already use unix timestamps. Converting to datetime (or even converting first to a db string, which goes through 2 gsubs) uses up the majority of its import time. I could otherwise be doing just a dirt cheap Integer#to_s call.

时区.我不想要他们.我希望它与时区无关地存储；处理时区是一件痛苦的事情，并且与我的需求完全无关，除非是在个人用户显示之前的最后阶段.数据本身不需要知道它记录在哪个时区.

Timezones. I don't want 'em. I want it stored timezone-agnostically; dealing with timezones is a pain and is completely irrelevant to my needs except at the very final stage before individual user display. The data itself has no need to know what timezone it was recorded in.

尺寸.这是一张大桌子.TIMESTAMP 是 DATETIME 大小的一半.

Size. It's a large table. TIMESTAMP is half the size of DATETIME.

是的，我仍然会在代码中进行 updated_at 计算，而不是 mysql.那部分不是瓶颈.

Yes, I would still be doing updated_at calculations in code, not mysql. That part isn't a bottleneck.

为什么你的为什么不"是错误的(先发制人地表明我不是在问愚蠢的原因:-P):

Why your 'why not' is wrong (preëmptively, to show I'm not asking for noobish reasons :-P):

  1. "但 TIMESTAMP 自动更新": 这只是默认情况，可以很容易地关闭.
  2. 我实际上没有使用 Rails，只是使用 ActiveRecord.
  3. 是的，这是基于实际分析数据；我不是早期优化.ActiveRecord::ConnectionAdapters::AbstractMysqlAdapter#quote(在 Quoting#quoted_date [如果通过 Time] 或 Mysql2Adapter#quote_string [if preconverting to_s(:db)]) 实际上是我的爬虫中最消耗 CPU 的部分.我想摆脱它.
  1. "But TIMESTAMP auto updates": That's only true by default, and can be easily switched off.
  2. I'm actually not using Rails, just ActiveRecord.
  3. Yes, this is based on actual profiling data; I am not early optimizing. ActiveRecord::ConnectionAdapters::AbstractMysqlAdapter#quote (in Quoting#quoted_date [if passing Time] or Mysql2Adapter#quote_string [if preconverting to_s(:db)]) is actually the most CPU-consuming section of my scraper. I want rid of it.

推荐答案

这可行(只是在类型定义中添加了空白字符，所以 :timestamp 不会覆盖它):

this works (just added whitespace character to type definition, so :timestamp doesn't override it):

     t.add_column :sometable, :created_at, 'timestamp '

相关文章