MariaDB 10.3 sequence(序列),mariadb10.3
运行环境:centos 7.5+ MariaDB 10.3.8
序列是创建产生顺序值得对象,使用create sequence语句创建。
sequence是MySQL自带的auto_increment 的备份方案用于可以自主控制多少序列数值;
此外序列的cache values 某些时候比使用auto_increment访问速度更快;
还有一个好处是可以访问所使用序列的最后一个值,解决了函数 LAST_INSERT_ID()的限制。
相关的语法:
CREATE SEQUENCE 创建序列
SHOW CREATE SEQUENCE 查看序列
ALTER SEQUENCE 修改序列
DROP SEQUENCE 删除序列
NEXT VALUE FOR 查询序列的下一个值
PREVIOUS VALUE FOR 查询序列的前一个值
SETVAL() 设置序列的值。
--创建序列:
MariaDB [wuhan]> create sequence seq start with 1000 minvalue 1000 maxvalue 9999 increment by 2 cache 1000 cycle;
Query OK, 0 rows affected (0.004 sec)
MariaDB [wuhan]> show create sequence seq;
+-------+-----------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------+
| seq | CREATE SEQUENCE `seq` start with 1000 minvalue 1000 maxvalue 9999 increment by 2 cache 1000 cycle ENGINE=InnoDB |
+-------+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.003 sec)
--序列的下一个值和上一个的查询:
MariaDB [wuhan]> select nextval(seq),next value for seq as seq_nextval,lastval(seq),previous value for seq as seq_preval ;
+--------------+-------------+--------------+------------+
| nextval(seq) | seq_nextval | lastval(seq) | seq_preval |
+--------------+-------------+--------------+------------+
| 1514 | 1516 | 1516 | 1516 |
+--------------+-------------+--------------+------------+
1 row in set (0.000 sec)
--序列的修改:
MariaDB [wuhan]> ALTER SEQUENCE seq restart 1200;
Query OK, 0 rows affected (0.003 sec)
MariaDB [wuhan]> select nextval(seq),next value for seq as seq_nextval,lastval(seq),previous value for seq as seq_preval ;
+--------------+-------------+--------------+------------+
| nextval(seq) | seq_nextval | lastval(seq) | seq_preval |
+--------------+-------------+--------------+------------+
| 1200 | 1202 | 1202 | 1202 |
+--------------+-------------+--------------+------------+
1 row in set (0.001 sec)
--setval:设置序列的下一个返回值,主要setval()设置的序列必须是增加,反之则返回NULL值。
MariaDB [wuhan]> select setval(seq,1300);
+------------------+
| setval(seq,1300) |
+------------------+
| 1300 |
+------------------+
1 row in set (0.000 sec)
MariaDB [wuhan]> select nextval(seq),next value for seq as seq_nextval,lastval(seq),previous value for seq as seq_preval ;
+--------------+-------------+--------------+------------+
| nextval(seq) | seq_nextval | lastval(seq) | seq_preval |
+--------------+-------------+--------------+------------+
| 1302 | 1304 | 1304 | 1304 |
+--------------+-------------+--------------+------------+
1 row in set (0.000 sec)
MariaDB [wuhan]> select setval(seq,1200);
+------------------+
| setval(seq,1200) |
+------------------+
| NULL |
+------------------+
1 row in set (0.000 sec)
序列已经增加到1300了此时设置为1200 则比之前的序列值小返回为NULL。
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。