使用Hibernate进行Spring Boot:自动生成具有可保留几分之一秒时间的列的数据库架构 [英] Spring Boot with Hibernate: Auto-generating database schema with columns which can holds fractions of seconds

问题描述

我有一个spring-boot项目，在这里我需要以微秒为单位保存实体的最后修改时间-实际上，这是一个保留实体版本的审核属性.在我的开发环境中，我使用MySQL数据库，而MySQL的时间类型最多支持6位小数.因此，当手动创建模式时，我可以用timestamp(6)或datetime(6)定义相应的列.

I have a spring-boot project where I need to save the last modified time of entities with microseconds accuracy - actually, an audit property where entity version is kept. In my development environment, I use a MySQL database and MySQL's temporal types support fractions up to 6 digits. So I can define the respective column with timestamp(6) or datetime(6) when the schema is manually created.

但是我希望使用spring.jpa.hibernate.ddl-auto=update属性自动生成架构.我知道在生产环境中这不是一个好习惯，但是这个项目将成为许多其他项目的模板或种子.因此，我需要将其保留为通用形式，以使其不依赖于下划线数据库系统.

But I want the schema to be auto-generated with spring.jpa.hibernate.ddl-auto=update property. I know it's not a good practice in a production environment, but this project is going to be a template or seed for many other projects. So I need to keep it in generic form so that it does not depend on the underline database system.

对于MySQL，我可以使用@Column批注的columnDefinition参数使它正常工作，如下所示.

For MySQL, I can get it to work using columnDefinition argument of @Column annotation like below.

@Version
@Column(columnDefinition= "datetime(6)")
Timestamp version;

但是，这在datetime(6)不是有效的时间类型的其他数据库中将不起作用.

But this won't work in other databases where datetime(6) is not a valid temporal type.

我尝试自动生成特定的数据库模式设置scale(@Column(scale= 6))参数，但是它忽略了小数部分会生成类型为datetime而不是datetime(6)的列.

I tried auto-generating the particular database schema setting scale (@Column(scale= 6)) argument, but it just neglects the fraction part generates a column of type datetime, not datetime(6).

我很好奇是否有办法做到这一点，尽管我不知道.

I'm curious if there is a way to do that, though I'm not aware.

你们能给我一些建议吗?

Can you guys give me some input on this?

@crizzis在他的评论中告诉我，这是使用正确的 hibernate.dialect 的问题.因此，使用 org.hibernate.dialect.MySQL57Dialect ，我能够自动生成 datetime(6)列.然后，我尝试用scale参数控制小数部分中的位数，但没有成功，它总是有6位数字.

@crizzis in his comment taught me that it is a matter of using correct hibernate.dialect. So, with org.hibernate.dialect.MySQL57Dialect I was able to auto-generate datetime(6) columns. Then I tried to control the number of digits in fraction part with scale argument without no success, it always have 6 digits.

现在，我想知道是否可以在@Column批注内不使用columnDefinition自变量.

Now I'd like to know if it is possible without using columnDefinition argument inside @Column annotation.

推荐答案

MySql57Dialect(和/或MySql57InnoDbDialect，取决于Hibernate的版本)，TIMESTAMP SQL类型已经映射到TIMESTAMP(6)数据库列类型:

MySql57Dialect (and/or MySql57InnoDbDialect, depending on the version of Hibernate), the TIMESTAMP SQL type is already mapped to a TIMESTAMP(6) database column type:

registerColumnType( Types.TIMESTAMP, "datetime(6)" );

这意味着默认的列定义应支持微秒精度.最好的做法是让Hibernate生成架构而不覆盖列定义.对于大多数其他数据库，它将正常降级为简单的TIMESTAMP.

This means the default column definition should support microsecond precision. The best course of action is to let Hibernate generate the schema without overriding the column definition. For most other databases, it will degrade gracefully to a simple TIMESTAMP.

如果将来想切换到也支持微秒精度的另一个数据库，请查找相关的Dialect；它可能会包含对该功能的支持，如果不支持，您可以随时对其进行自定义.

If you want to switch to another database in the future which also supports microsecond precision, look up the relevant Dialect; it will likely include support for the feature, and if not, you can always customize it.

然后我尝试用scale参数控制小数部分的位数，但没有成功，它总是有6位数.

Then I tried to control the number of digits in fraction part with scale argument without no success, it always have 6 digits.

这是因为在上面的registerColumnType调用中，TIMESTAMP的精度固定为6.如果希望对其进行自定义，请推出自己的自定义方言(从MySql57Dialect扩展)，然后用

This is because in the registerColumnType call above, the precision of the TIMESTAMP is fixed at 6. If you want to be able to customize it, roll out your own custom dialect (extending from MySql57Dialect) and override the definition with:

registerColumnType(Types.TIMESTAMP, 6, "timestamp($l)"); //l for length, p for precicion, s for scale

然后您将能够使用length，precision或scale覆盖列长度(取决于括号内的内容).请注意，该方法的第二个参数定义了此特定数据类型的最大可能列长度，因此，例如纳秒精度，您应该将其更改为9.

You will then be able to override the column length using either length, precision, or scale (depending on what you put inside the brackets). Note that the second argument to the method defines the maximum possible column length for this particular datatype, so if you want e.g. nanosecond precision, you should change it to 9.

这篇关于使用Hibernate进行Spring Boot:自动生成具有可保留几分之一秒时间的列的数据库架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！