在MySQL中存储微秒：哪个变通方法？ [英] Storing microseconds in MySQL: which workaround?

问题描述

我们正在使用MySQL支持编写一个科学工具。问题是，对于我们的datetime字段，我们需要微秒的精度，MySQL目前不支持。我在这里看到至少两个解决方法：

• 使用decimal（）列类型，其中整数部分与某些时间点对应的秒数（我怀疑UNIX时代将会做，因为我们必须存储60年代和50年代的测量）。


• 使用两个整数列，一个秒，另一个是微秒。 >
  

最流行的查询是选择与时间间隔对应的列（即dt_record> time1和dt_record< time2）。

在大型表（数百万行）的情况下，这些方法中哪一种（或者另一种）可能会提供更好的性能？

解决方案

如果你说最受欢迎的查询是时间基础，那么我会推荐使用一个列，将时间存储在你的第一个选项中。

您可以选择自己的应用程序，并从那里工作。

这应该是sim补充在搜索时间间隔时需要写入的查询。

还可以看看 10.3.1。 DATETIME，DATE和TIMESTAMP类型

然而，微秒不能将
存储到任何时间的列中数据
类型。任何微秒部分是
被丢弃。将TIME或
DATETIME值转换为数字形式（对于
示例，通过添加+0）将生成一个
双重值，微秒部分
为.000000

we're writing a scientific tool with MySQL support. The problem is, we need microsecond precision for our datetime fields, which MySQL doesn't currently support. I see at least two workarounds here:

• Using a decimal() column type, with integer part corresponding to seconds since some point in time (I doubt that UNIX epoch will do, since we have to store measurements taken in 60's and 50's).
• Using two integer columns, one for seconds, the other one for microseconds.

The most popular query is selecting columns corresponding to a time interval (i.e. dt_record > time1 and dt_record < time2).

Which one of these methods (or perhaps another one) is likely to provide better performance in the case of large tables (millions of rows)?

解决方案

If you say that the most popular queries are time base, I would recomend going with a single column that stores the time as in your first option.

You could pick your own epoch for the application, and work from there.

This should simplify the queries that needs to be written when searching for the time intervals.

Also have a look at 10.3.1. The DATETIME, DATE, and TIMESTAMP Types

However, microseconds cannot be stored into a column of any temporal data type. Any microseconds part is discarded. Conversion of TIME or DATETIME values to numeric form (for example, by adding +0) results in a double value with a microseconds part of .000000

这篇关于在MySQL中存储微秒：哪个变通方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！