H2数据库 - 使用SQL重新排序列

时间：2022-09-11 14:45:13

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:

我有一个H2数据库,有1600万个条目,没有主键。我使用以下语句成功添加了自动递增主键:

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

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.

现在问题是,列顺序是STREET,HOUSENUMBER,...,ID,但我希望ID成为表的第一列。看起来有一个相应的ALTER TABLE语句MySQL(见这里),但我无法将其改编为H2。

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

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

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

Any help is kindly appreciated.

任何帮助都非常感谢。

1 个解决方案

#1

3  

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

H2目前不支持重新排序列。您需要运行多个语句:

  • 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.

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

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

    或者,可能更优雅,使用重命名表,然后创建表...作为选择。

#1

3  

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

H2目前不支持重新排序列。您需要运行多个语句:

  • 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.

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

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

    或者,可能更优雅,使用重命名表,然后创建表...作为选择。

标签：

相关文章