如何在MySQL数据库中存储Java Instant [英] How to store a Java Instant in a MySQL database
问题描述
使用Java Date对象，最简单的方法是将它们存储为MySql DateTime对象(在UTC中).切换到Instant时，此方法将不再起作用，因为MySQL DateTime不提供存储纳秒的精度.仅仅将它们截断可能会导致新创建的Instant对象与从数据库读取的对象之间的意外比较结果.
BigDecimal时间戳不适合我作为一种优雅的解决方案:手动编写选择查询变得更加困难，因为您必须在所有地方转换时间戳以使其可读，并且与Instant相比，Java的处理有些笨拙甚至Long值.
去这里最好的方法是什么?可能不是varchar，对吧?
截断为微秒
显然，我们不能将Instant的纳秒分辨率压缩为MySQL数据类型DateTime和Timestamp的微秒分辨率.
Instant时忽略纳秒，将值截断为微秒.建议您尝试一下实验，或者检查符合JDBC 4.2及更高版本的驱动程序的源代码.Instant instant = Instant.now().with( ChronoField.NANO_OF_SECOND , 123_456_789L ) ; //Set the fractional second to a spefic number of nanoseconds.
myPreparedStatement.setObject( … , instant ) ;
…和…
Instant instant2 = myResultSet.getObject( … , Instant.class ) ;
然后比较.
Boolean result = instant.equals( instant2 ) ;
System.out.println( "instant: " + instant + " equals instant2: = " + instant2 + " is: " + result ) ;
您明智地担心从数据库中提取的值与原始值不匹配.一种解决方案(如果您的业务问题可以接受的话)是将原始数据中的所有纳秒级转换为几微秒级.我通常推荐这种方法.
Instant instant = Instant().now().truncatedTo( ChronoUnit.MICROSECONDS ) ;
当前，这种方法就足够了，因为您不太可能在数据中包含任何纳秒级的数据.据我所知，当今的主流计算机不支持能够捕获纳秒级的硬件时钟.
从纪元开始计数
如果您承担不起丢失任何可能存在的纳秒数据的风险，请使用从纪元开始计数".
我通常建议不要将日期时间作为从参考日期开始的计数.但是在将基于纳秒的值存储在数据库(如MySQL和Postgres)中，除了基于微秒的值之外，您还有其他选择.
存储一对整数
我建议不要使用自1970-01-01T00:00Z这样的纪元以来的极大量的纳秒，而是建议使用Instant类的内部方法:使用对个数字.
将整个秒作为整数存储在数据库中.在第二列中，将小数秒中的纳秒数存储为整数.
您可以轻松地将这些数字从Instant对象提取/注入到Instant对象.仅涉及简单的64位long数字.不需要BigDecimal或BigInteger.我想您可能可以对两个数字中的至少一个使用32位整数列.但是我会选择64位整数列类型，以简化操作并与java.time.Instant类的long对直接兼容.
long seconds = instant.getEpochSecond() ;
long nanos = instant.getNano() ;
…和…
Instant instant = Instant.ofEpochSecond( seconds , nanos ) ;
按时间顺序进行排序时，您需要进行多级排序，首先在整个秒数列上进行排序，然后在nanos秒的列上进行第二次排序.
With Java Date objects the easiest way to go was to store them as MySql DateTime objects (in UTC). With the switch to Instant this approach won't work anymore because MySQL DateTime does not offer the precision to store nanoseconds. Just truncating them could lead to unexpected comparison results between a newly created Instant objects and the ones read from the database.
BigDecimal timestamps don't strike me as an elegant solution: writing select queries manually becomes more difficult because you have to convert the timestamp everywhere to make it readable, and the handling in Java is somewhat clunky compared to Instant or even Long values.
What's the best way to go here? Probably not varchar, right?
Truncate to microseconds
Obviously we cannot squeeze the nanoseconds resolution of an Instant into the microseconds resolution of the MySQL data types DateTime and Timestamp.
I imagine the JDBC driver is built to ignore the nanoseconds when receiving an Instant, truncating the value to microseconds. I suggest you try an experiment to see, and perhaps examine source code of your driver that complies with JDBC 4.2 and later.
Instant instant = Instant.now().with( ChronoField.NANO_OF_SECOND , 123_456_789L ) ; //Set the fractional second to a spefic number of nanoseconds.
myPreparedStatement.setObject( … , instant ) ;
…and…
Instant instant2 = myResultSet.getObject( … , Instant.class ) ;
Then compare.
Boolean result = instant.equals( instant2 ) ;
System.out.println( "instant: " + instant + " equals instant2: = " + instant2 + " is: " + result ) ;
You wisely are concerned about values drawn from the database not matching the original value. One solution, if acceptable to your business problem, is to truncate any nanoseconds to microseconds in your original data. I recommend this approach generally.
Instant instant = Instant().now().truncatedTo( ChronoUnit.MICROSECONDS ) ;
Currently this approach should suffice as you are unlikely to have any nanoseconds in your data. Mainstream computers today do not sport hardware clocks capable of capturing nanoseconds, as far as I know.
Count from epoch
If you cannot afford to lose any nanosecond data that may be present, use a count-from-epoch.
I usually recommend against tracking date-time as a count from an epoch reference date. But you have few other choices in storing your nanosecond-based values in a database such as MySQL and Postgres limited to microsecond-based values.
Store pair of integers
Rather than using the extremely large number of nanoseconds since an epoch such as 1970-01-01T00:00Z, I suggest following the approach taken by the internals of the Instant class: Use a pair of numbers.
Store a number of whole seconds as an integer in your database. In a second column store as an integer the number of nanoseconds in the fractional second.
You can easily extract/inject these numbers from/to an Instant object. Only simple 64-bit long numbers are involved; no need for BigDecimal or BigInteger. I suppose you might be able to use a 32-bit integer column for at least one of the two numbers. But I would choose 64-bit integer column types for simplicity and for direct compatibility with the java.time.Instant class’ pair of longs.
long seconds = instant.getEpochSecond() ;
long nanos = instant.getNano() ;
…and…
Instant instant = Instant.ofEpochSecond( seconds , nanos ) ;
When sorting chronologically, you'll need to do a multi-level sort, sorting first on the whole seconds column and then sorting secondarily on the nanos fraction-of-second column.
这篇关于如何在MySQL数据库中存储Java Instant的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
