H2数据库-使用SQL重新排序列 [英] H2 Database - Reorder columns using SQL

问题描述

我有一个H2数据库，其中有1600万个条目，并且没有主键.我使用以下语句成功添加了自动递增的主键:

I have a H2 database with 16 million entries and no primary key. I successfully added an auto-incrementing primary key using the following statements:

ALTER TABLE
    PUBLIC.ADDRESSES ADD ID BIGINT AUTO_INCREMENT;
ALTER TABLE
    PUBLIC.ADDRESSES ADD PRIMARY KEY (ID)

现在的问题是，列顺序是STREET, HOUSENUMBER, ..., ID，但是我希望ID是表的第一列.似乎有一个相应的ALTER TABLE语句MySQL(请参见此处)，但我无法使其适应H2.

Now the problem is, that the column order is STREET, HOUSENUMBER, ..., ID, but I would like ID to be the first column of the table. It looks like there is a corresponding ALTER TABLE statement MySQL (see here), but I'm unable to adapt it to H2.

长话短说:如何将列顺序更改为ID, STREET, HOUSENUMBER ...?有没有类似的解决方案:

Long story short: How can I change the column order to ID, STREET, HOUSENUMBER ...? Is there a solution similar to:

ALTER TABLE "ADDRESSES" MODIFY COLUMN "ID" BEFORE "STREET";

我们非常感谢您的帮助.

Any help is kindly appreciated.

推荐答案

H2当前不支持对列进行重新排序.您将需要运行多个语句:

H2 does not currently support re-ordering columns. You would need to run multiple statements:

• 首先，重命名该列，然后在正确的位置添加一个名称正确的新列(alter table add支持定位)，最后删除旧列.

• First, rename the column, then add a new column with the right name at the right position (alter table add supports positioning), and finally drop the old column.

或者，也许更优雅一些，先使用rename table，然后使用create table ... as select.

Or, probably more elegant, use rename table and then create table ... as select.

这篇关于H2数据库-使用SQL重新排序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！