MariaDB 10.3 序列
时间：2021-07-01 10:21:17
帮助过：7人阅读
10.3版本中sequence是特殊的表，和表使用相同的namespace，因此表和序列的名字不能相同。
MariaDB [wuhan]> select version();
+--------------------+
| version()
|
+--------------------+
| 10.3.
8-MariaDB
-log |
+--------------------+
1 row
in set (
0.000 sec)
MariaDB [wuhan]> create sequence s;
Query OK, 0 rows affected (
0.477 sec)
MariaDB [wuhan]> show
create sequence s;
+-------+--------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------+
| s
| CREATE SEQUENCE `s` start
with 1 minvalue
1 maxvalue
9223372036854775806 increment
by 1 cache
1000 nocycle ENGINE
=InnoDB
|
+-------+--------------------------------------------------------------------------------------------------------------------------+
1 row
in set (
0.075 sec)
MariaDB [wuhan]> show
create table s\G
*************************** 1. row
***************************
Table: s
Create Table:
CREATE TABLE `s` (
`next_not_cached_value` bigint(
21)
NOT NULL,
`minimum_value` bigint(
21)
NOT NULL,
`maximum_value` bigint(
21)
NOT NULL,
`start_value` bigint(
21)
NOT NULL COMMENT
‘start value when sequences is created or value if RESTART is used‘,
`increment` bigint(
21)
NOT NULL COMMENT
‘increment value‘,
`cache_size` bigint(
21) unsigned
NOT NULL,
`cycle_option` tinyint(
1) unsigned
NOT NULL COMMENT
‘0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed‘,
`cycle_count` bigint(
21)
NOT NULL COMMENT
‘How many cycles have been done‘
) ENGINE=InnoDB SEQUENCE
=1
1 row
in set (
0.000 sec)
MariaDB [wuhan]> select * from s\G
*************************** 1. row
***************************
next_not_cached_value: 1
minimum_value: 1
maximum_value: 9223372036854775806
start_value: 1
increment: 1
cache_size: 1000
cycle_option: 0
cycle_count: 0
1 row
in set (
0.001 sec)
cycle_count每次递增。
可以看到序列是一种特殊的表，对这种表不能update和delete，不能将表名和序列名命名为一样的，否则报错。
MariaDB [wuhan]> update s
set increment
=2 ;
ERROR 1031 (HY000): Storage engine SEQUENCE
of the
table `wuhan`.`s` doesn
‘t have this option
MariaDB [wuhan]> delete from s where increment=1;
ERROR 1031 (HY000): Storage engine SEQUENCE of the table `wuhan`.`s` doesn‘t have this
option
MariaDB [wuhan]> create table s(a
int);
ERROR 1050 (42S01):
Table ‘s‘ already
exists
但是对表的重命名、删除操作生效：
MariaDB [wuhan]> alter table s rename
to ss;
Query OK, 0 rows affected (
0.291 sec)
MariaDB [wuhan]> rename
table ss
to s;
Query OK, 0 rows affected (
0.022 sec)
MariaDB [wuhan]> drop table s;
Query OK, 0 rows affected (
0.076 sec)
对序列进行FLUSH TABLE操作将会关闭序列，生成的下一个序列值将会根据序列对象生成，实际上将会丢弃缓存cached 值。
MariaDB [wuhan]> create sequence s;
Query OK, 0 rows affected (
0.024 sec)
MariaDB [wuhan]> select nextval(s),lastval(s);
+------------+------------+
| nextval(s)
| lastval(s)
|
+------------+------------+
| 1 | 1 |
+------------+------------+
1 row
in set (
0.059 sec)
MariaDB [wuhan]> select nextval(s),lastval(s);
+------------+------------+
| nextval(s)
| lastval(s)
|
+------------+------------+
| 2 | 2 |
+------------+------------+
1 row
in set (
0.001 sec)
MariaDB [wuhan]> flush
table s;
Query OK, 0 rows affected (
0.001 sec)
MariaDB [wuhan]> select nextval(s),lastval(s);
+------------+------------+
| nextval(s)
| lastval(s)
|
+------------+------------+
| 1001 | 1001 |
+------------+------------+
1 row
in set (
0.001 sec)
MariaDB [wuhan]> flush
table s;
Query OK, 0 rows affected (
0.000 sec)
MariaDB [wuhan]> select nextval(s),lastval(s);
+------------+------------+
| nextval(s)
| lastval(s)
|
+------------+------------+
| 2001 | 2001 |
+------------+------------+
1 row
in set (
0.001 sec)
缺点：
由于在MariaDB中在很多应用场景中序列对象充当普通的表，将会受到lock table操作的影响。但是在其他DBMS中则不会受影响，比如oracle中的序列。
序列兼容性：
MariaDB兼容ANSI SQL和oracle 序列的语法,oracle 语法的序列需要将SQL_MODE设置为SQL_MODE=oracle.
序列备份：
由于序列是只有一行数据的常规表，因此mysqldump可以很好的支持。
序列和复制：
若在master-master复制或者galera复制中使用序列，需要设置 INCREMENT
=0，以告知sequence对象使用
auto_increment_increment和 auto_increment_offset对每个服务器产生唯一的序列值。
转自：
https://mariadb.com/kb/en/library/sequence-overview/
MariaDB 10.3 序列
标签：使用 sequence art div mysqldump pac inno 设置 code
