mariadb10.3.31序列

==作者：YB-Chi==

[toc]

案例

创建序列

1	CREATE SEQUENCE test_seq START WITH 1 MINVALUE 1 MAXVALUE 16777215 INCREMENT BY 1 CYCLE;

创建表,主键使用序列值

CREATE TABLE `test` (
  `id` mediumint(8) UNSIGNED NOT NULL DEFAULT nextval(`tsoc`.`test_seq`) COMMENT '序列自增主键',
	`name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

文档

CREATE SEQUENCE

MariaDB starting with 10.3

CREATE SEQUENCE was introduced in MariaDB 10.3.

CREATE [OR REPLACE] [TEMPORARY] SEQUENCE [IF NOT EXISTS] sequence_name
[ INCREMENT [ BY | = ] increment ]
[ MINVALUE [=] minvalue | NO MINVALUE | NOMINVALUE ]
[ MAXVALUE [=] maxvalue | NO MAXVALUE | NOMAXVALUE ]
[ START [ WITH | = ] start ] 
[ CACHE [=] cache | NOCACHE ] [ CYCLE | NOCYCLE] 
[table_options]

参数

Option	Default value	Description
INCREMENT	1	Increment to use for values. May be negative. Setting an increment of 0 causes the sequence to use the value of the auto_increment_increment system variable at the time of creation, which is always a positive number. (see MDEV-16035).
MINVALUE	1 if INCREMENT > 0 and -9223372036854775807 if INCREMENT < 0	Minimum value for the sequence
MAXVALUE	9223372036854775806 if INCREMENT > 0 and -1 if INCREMENT < 0	Max value for sequence
START	MINVALUE if INCREMENT > 0 and MAX_VALUE if INCREMENT< 0	First value that the sequence will generate
CACHE	1000	Number of values that should be cached. 0 if no CACHE. The underlying table will be updated first time a new sequence number is generated and each time the cache runs out.应该缓存的值的数量。如果没有缓存，则为0。底层表将在第一次生成新序列号和每次缓存用完时更新。

If CYCLE is used then the sequence should start again from MINVALUE after it has run out of values. Default value is NOCYCLE.

如果使用CYCLE，则序列应该在耗尽值后从MINVALUE重新开始。默认值为NOCYCLE。