Mariadb 简明教程
MariaDB - Sequences
在版本 10.0.3 中,MariaDB 引入了一种名为序列的存储引擎。它的临时解决方案为操作生成一个整数序列,然后终止它。序列包含按降序或升序排列的正整数,并使用起始、结束和增量值。
In version 10.0.3, MariaDB introduced a storage engine known as sequence. Its ad hoc generates an integer sequence for operations, and then it terminates. The sequence contains positive integers in descending or ascending order, and uses a starting, ending, and increment value.
由于其虚拟(未写入磁盘)性质,它不允许在多个查询中使用,只允许在其原始查询中使用。但是,可以通过 ALTER 命令将序列表转换为标准表。如果删除转换后的表,则序列表仍然存在。序列也不能产生负数或在最小值/最大值处旋转。
It does not allow use in multiple queries, only in its original query because of its virtual (not written to disk) nature. However, sequence tables can be converted to standard tables through an ALTER command. If a converted table is deleted, the sequence table still exists. Sequences also cannot produce negative numbers or rotate at the minimum/maximum.
Installing the Sequence Engine
使用序列需要安装序列引擎,MariaDB 将其作为插件而不是二进制文件进行分发。使用以下命令安装它 −
Using sequences requires installing the sequence engine, which MariaDB distributes as a plugin rather than binary. Install it with the following command −
INSTALL SONAME "ha_sequence";
安装后,验证它 −
After installation, verify it −
SHOW ENGINES\G
请记住,在安装引擎后,您无法使用序列语法创建名称为标准表的表,但您可以创建带有序列语法名称的临时表。
Remember that after engine installation, you cannot create a standard table with a name that uses sequence syntax, but you can create a temporary table with a sequence-syntax name.
Creating Sequence
有两种序列创建方法 −
There are two methods of sequence creation −
-
Create a table and use the AUTO_INCREMENT attribute to define a column as auto-increment.
-
Use an existing database and use a sequence SELECT query to produce a sequence. The query uses seq_ [FROM] to[TO] or seq_[FROM]to[TO]_step_STEP syntax.
最佳做法更偏好使用第二种方法。查看下面提供的一个序列创建示例 −
Best practices prefer the use of the second method. Review an example of a sequence creation given below −
SELECT * FROM seq_77_to_99;
序列有许多用途 −
Sequences have many uses −
-
Locate missing values within a column to protect against related issues in operations −
SELECT myseq.seq FROM seq_22_to_28 myseq LEFT JOIN table1 t ON myseq.seq
= x.y WHERE x.y IS NULL;
-
Construct a combination of values −
SELECT x1.seq, x2.seq FROM seq_5_to_9 x1 JOIN seq_5_to_9 x2 ORDER BY 5, 6;
-
Find multiples of a number −
SELECT seq FROM seq_3_to_100_step_4;
-
Construct a date sequence for use in applications like booking systems.
-
Construct a time sequence.