likes
comments
collection
share

人大金仓数据库KingbaseES 兼容MYSQL自增列介绍

作者站长头像
站长
· 阅读数 9

关键字：

自增列、人大金仓、KingbaseES

什么是自增列

Mysql可以通过auto_increment关键字，实现列数据自增，一般用于单表的唯一标识列，常见的有主键，每张表最多只能有一个自增列。

自增列支持哪些数据类型

自增列的类型必须是整型或浮点型，其中整型包括；tinyint, smallint, mediumint, int, bigint，serial列（表现同 bigint auto_increment）以及对应的无符号类型，浮点型包括：float和double，除此之外的类型均不支持。

自增列支持类型

值域

Tinyint

1-127

smallint

1- 32767

mediumint

1-8388607

int

1-2147483647

Bigint/ serial

1-9223372036854775807

float

1-16777216

double

1-9007199254740992

自增列有哪些限制

  1. 每张表中只能有唯一一个自增列。
  2. 自增列必须是主键、唯一键。
  3. 不能为自增列指定非空默认值。
  4. 不能在自增列上创建check约束。
  5. 可以在自增列上添加NOT NULL、NULL以及外键约束，自增列和它们没有相关性。

在表上定义自增列有还以下行为：

  1. 当指定自增列属性AUTO_INCREMENT时，总是会隐式添加NOT NULL约束。如果自增列是唯一键或唯一键中的一列时，自增列上最终的NULL约束状态取决于NULL、NOT NULL和AUTO_INCREMENT三者中在SQL语句中排在最后的子句。
  2. 可以在自增列上创建外键，其DML行为与普通列相同，即当自增列值违反外键约束时报错。

自增列语法

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT

EXISTS ] table_name ( [

{ column_name data_type [ INVISIBLE | VISIBLE ] [ COLLATE collation ] [ column_constraint [ ... ] ]

| table_constraint

| LIKE source_table [ like_option ... ] }

[, ... ]

] )

[ INHERITS ( parent_table [, ... ] ) ]

[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [

COLLATE collation ] [ opclass ] [, ... ] ) [ partition_extented_spec ] ]

[ USING method ]

[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS | WITH ROWID]

[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]

[ WITH ( ONCOMMIT = value ) ]

[ TABLESPACE tablespace_name ]

[ { ENABLE | DISABLE } [ VALIDATE | NOVALIDATE ] constraint [ ... ] ]

[ ENCRYPTED [BY tablekey ]]

[ AUTO_INCREMENT [=] value ]

column_constraint:=

[ CONSTRAINT constraint_name ]

{ NOT NULL |

NULL |

CHECK ( expression ) [ NO INHERIT ] |

DEFAULT default_expr |

GENERATED ALWAYS AS ( generation_expr ) STORED |

GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |

IDENTITY [ ( NumericOnly , NumericOnly ) ] |

UNIQUE index_parameters |

PRIMARY KEY index_parameters |

AUTO_INCREMENT |

REFERENCES reftable [ ( refcolumn ) ]

[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]

[ ON DELETE referential_action ]

[ ON UPDATE referential_action ]

}

[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

[ { ENABLE | DISABLE } [ VALIDATE | NOVALIDATE ] ]

使用说明：

  1. 在同一列上，生成列属性GENERATED ALWAYS AS ( generation_expr ) STORED和标识列属性GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY，均不可以与自增列属性AUTO_INCREMENT联合使用，否则语句执行报错。
  2. 每张表中只能定义唯一一个AUTO_INCREMENT自增列，否则语句执行报错。
  3. 自增列必须是主键或唯一键，如果主键或唯一键由多列组成，自增列必须是其中的第一列，否则语句执行报错。
  4. 不能为自增列指定非空默认值，否则语句执行报错。
  5. check约束也不能包含自增列，否则语句执行报错。
  6. 可以在自增列上添加NOT NULL、NULL以及外键约束。
  7. 当指定自增列属性AUTO_INCREMENT时，总是会隐式添加NOT NULL约束。如果自增列是唯一键或唯一键中的一列时，自增列上最终的NULL约束状态取决于NULL、NOT NULL和AUTO_INCREMENT三者中在SQL语句中排在最后的子句。
  8. 表属性AUTO_INCREMENT为自增列初始值，由“AUTO_INCREMENT [=] value”子句设置，若省略该子句，则自增列初始默认为1。value的值域为自增列序列的值域。当value小于0时语句执行报错。当value值为小数时，value值被截断取整。当value等于0或小于自增列序列的当前值时，该值被忽略。当value大于自增列序列的最大值时，value被截断为自增列序列的最大值。
  9. 支持在分区表上定义自增列，自增列可以作为分区键的一部分。不支持直接向包含自增列的分区表分区直接插入数据。

兼容MYSQL自增列使用

示例1：

drop table if exists t1;

create table t1(c1 serial, c2 varchar(10));

\dS+ T1;

insert into t1 values(1,'a');

insert into t1 values(null,'b');

insert into t1(c2) values('c');

select * from t1;

示例2:

set sql_mode= ONLY_FULL_GROUP_BY,ANSI_QUOTES；

show sql_mode;

drop table if exists t2;

create table t2(c1 int ,c2 int auto_increment primary key);

insert into t2(c2) values(0);

insert into t2(c2) values(0);

insert into t2(c2) values(0);

select * from t2;

示例3：

Set sql_mode=ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,ANSI_QUOTES;

show sql_mode;

create table t2(c1 int ,c2 int auto_increment primary key);

insert into t2(c2) values(0);

insert into t2(c2) values(0);

insert into t2(c2) values(0);

select * from t2;

示例4：

drop table if exists t1;

create table t1(c1 bigint auto_increment unique, c2 varchar(10)) auto_increment=5;

insert into t1 values(1,'a');

insert into t1 values(null,'b');

insert into t1(c2) values('b');

select * from t1;

示例5：

set auto_increment_offset=3;

set auto_increment_increment=10;

show auto_increment_offset;

show auto_increment_increment;

drop table if exists t1;

create table t1(id serial,c2 varchar(10));

insert into t1(c2) values('a');

insert into t1(c2) values('b');

insert into t1(c2) values('c');

select * from t1;

参考资料

转载自:https://juejin.cn/post/7343137522053693467
评论
请登录