Add column location

{问题}

是否可以在Nuodb表中的现有列之间添加新列?  

例如:

create table employees
(Id bigint generated always as identity primary key NOT NULL,
Name char(36) NOT NULL,
City char(36) NOT NULL,
Location varchar(255) NOT NULL);

在“名称”和“城市”之间添加了“Mobile”列如何?

{问题}

 

{回答}

此时Nuodb不直接支持添加列位置。   NOODB中没有语法支持此类命令:

ALTER表员工在名称后添加列移动varchar(30);

此外,没有对现有表中的特定位置插入的新列没有语法。性能方面,表中列的位置在NOODB中没有差异。

将列添加到现有表中时,它始终在上一个现有列后添加:

SQL.> alter table employees add column Mobile varchar(30);
SQL.> show table employees Tables named EMPLOYEES Found table EMPLOYEES in schema USER Fields:
ID bigint
Nullable: No
Generator: EMPLOYEES$IDENTITY_SEQUENCE Generated Always
NAME char(36)
Nullable: No
CITY char(36)
Nullable: No
LOCATION varchar(255)
Nullable: No
MOBILE varchar(30)
Primary Index: EMPLOYEES..PRIMARY_KEY on field: ID

 

但是,有一个流行的解决方法,良好地解决了这个问题:

1.创建一个新表,使用所需的列的确切顺序。

2.将数据从原始表中复制到新表。

3.放下原始表。

4.将新表重命名为原始表。

SQL.> create table employees2
 (Id bigint generated always as identity primary key NOT NULL,
 Name char(36) NOT NULL,
 Mobile varchar(30),
 City char(36) NOT NULL,
 Location varchar(255) NOT NULL);
SQL.> show table employees2 Tables named EMPLOYEES2 Found table EMPLOYEES2 in schema USER Fields:
ID bigint
Nullable: No
Generator: EMPLOYEES2$IDENTITY_SEQUENCE Generated Always
NAME char(36)
Nullable: No
MOBILE varchar(30)
CITY char(36)
Nullable: No
LOCATION varchar(255)
Nullable: No
Primary Index: EMPLOYEES2..PRIMARY_KEY on field: ID
SQL.> insert into employees2(NAME,MOBILE,CITY,LOCATION) select NAME,MOBILE,CITY,LOCATION from employees;

SQL.> select * from employees2; ID NAME MOBILE CITY LOCATION
--- ----- ------- -------- --------- 1 Paul <null> New York USA
2 David <null> London Europe
SQL.> drop table employees;

SQL.> rename table employees2 to employees;

SQL.> select * from employees; ID NAME MOBILE CITY LOCATION
--- ----- ------- -------- --------- 1 Paul <null> New York USA
2 David <null> London Europe

 

您可能会找到更多信息和示例“alter table” 这里.

 

{回答}

Have more questions? 提交申请

注释