英文:
What Liquibase XML element to use to reset AUTO INCREMENT counter for a column in MySQL?
问题
在MySQL中,您可以使用以下类型的SQL来重置表的AUTO INCREMENT计数器:
ALTER TABLE my_table AUTO_INCREMENT = 200;
在Liquibase中要使用的相应XML元素是什么?
我正在编写一个changeSet
,用于将新数据加载到现有的表中,我希望自动递增的主键从1
开始重新计数,如下所示:
<changeSet id="reload-data" author="neek">
<delete tableName="my_table"></delete>
<!-- 在这里重置auto_increment计数器 -->
<loadData tableName="my_table"
file="data/fresh_data.csv" separator=","/>
<rollback></rollback>
</changeSet>
这可以加载数据,但id
列的值将继续从最后已知的id
值开始,这是正常的,因为我还没有重置表的AUTO_INCREMENT
。
您可以使用原始的sql
元素,例如:
<sql>ALTER TABLE my_table AUTO_INCREMENT = 1</sql>
但我担心对于其他数据库,语法可能会不同,并且最好由特定的Liquibase命令处理。
也许我们可以使用addAutoIncrement
https://docs.liquibase.com/change-types/add-auto-increment.html 来重置已经是自动增量的列?这似乎有点过于重-handed(重量级)。
英文:
In MySQL you would use this kind of SQL to reset the AUTO INCREMENT counter for a table:
ALTER TABLE my_table AUTO_INCREMENT = 200;
What is the corresponding XML element to use in Liquibase?
I'm writing a changeSet
that loads fresh data into an existing table, and I'd like the auto-increment primary key to start counting from 1
again. i.e.:
<changeSet id="reload-data" author="neek">
<delete tableName="my_table"></delete>
<!-- want to reset the auto_increment counter here -->
<loadData tableName="my_table"
file="data/fresh_data.csv" separator=","/>
<rollback></rollback>
</changeSet>
This loads the data OK but the id
column continues its value from the last known id
, which is normal because I haven't reset the AUTO_INCREMENT
for the table.
I can use a raw sql
element, i.e.:
<sql>ALTER TABLE my_table AUTO_INCREMENT = 1</sql>
But I worry the syntax would be different for other databases and best handled by a specific Liquibase command.
Perhaps we could use addAutoIncrement
https://docs.liquibase.com/change-types/add-auto-increment.html on the column, which already is auto increment, to reset it? Seems a bit heavy handed.
答案1
得分: 1
没有Liquibase更改用于重置自动增量。
正如您提到的,您始终可以使用<sql>
更改。
如果您必须使用Liquibase更改(假设您没有与该列相关的任何外键,因为您想要重置自动增量),作为一个不太好的解决方法,您可以尝试删除自动增量的列,然后重新创建它。
<!-- 删除主键 -->
<changeSet id="foo1" author="bar">
<dropColumn tableName="table_name" columnName="auto_incremented_column"/>
</changeSet>
<!-- 重新创建自动增量列 -->
<changeSet id="foo2" author="bar">
<addColumn tableName="table_name">
<column name="auto_incremented_column" autoIncrement="true" type="bigserial">
<constraints primaryKey="true"/>
</column>
</addColumn>
</changeSet>
请注意,这只是一种解决方法,如果您的表中有与该列相关的外键,可能需要额外的步骤来处理这些外键。
英文:
There's no Liquibase change for resetting autoIncrement.
As you mentioned, you can always use <sql>
change.
If you have to use Liquibase changes (assuming you don't have any foreign keys related to that column, since you want to reset autoIncrement), as a not very good workaround you may try dropping the autoIncremented column, and then recreating it.
<!-- drop primary key -->
<changeSet id="foo1" author="bar">
<dropColumn tableName="table_name" columnName="auto_incremented_column"/>
</changeSet>
<!-- recreate autoIncremented column -->
<changeSet id="foo2" author="bar">
<addColumn tableName="table_name">
<column name="auto_incremented_column" autoIncrement="true" type="bigserial">
<constraints primaryKey="true"/>
</column>
</addColumn>
</changeSet>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论