MariaDB 10.3 深入理解序列，mariadb10.3

MariaDB 10.3 深入理解序列，mariadb10.3

在MariaDB 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/