How to create sequences in MariaDB Server?

Question

How to create sequences in MariaDB Server?

Navigation

#1 by (2 votes)

2

Within database managers as they are:

Oracle

PostgreSQL

SQL Server

We have the possibility to create sequences, which help us for example to generate the incremental auto key that distinguishes each of the records of a table in a database, as an alternative to the use of AUTO_INCREMENT

For example in PostgreSQL , we do it in the following way

CREATE SEQUENCE id
START WITH 1
INCREMENT BY 1;

For example in SQL Server we can achieve it as follows

CREATE SEQUENCE id as BIGINT
START WITH 1
INCREMENT BY 1;

And the Oracle 11g for example, we can do it as follows

CREATE SEQUENCE id
START WITH 1
INCREMENT BY 1;

To the sequence we indicate a name, later we indicate it the   entire value in which it will start and finally the value with which   the initial value will be increased.

If for example we want to use that value in a record now

sql mariadb

asked by element 18.09.2018 в 01:33

source

1 answer

How can I save the numbers of a notebook in an array in java and then order those numbers [duplicated] How to add the dynamically generated checkbox values, jquery?

score 2 · Accepted Answer

In mariaDB since version 10.3 there is the possibility of natively using the creation of sequences; through the following example you can see how to achieve it

First let's create the sequence table

CREATE SEQUENCE id
START WITH 1
INCREMENT BY 1;

If for example we want to see the structure of the table id, newly created; just execute the following in the console

MariaDB [blog]> describe id;
+-----------------------+---------------------+------+-----+---------+-------+
| Field                 | Type                | Null | Key | Default | Extra |
+-----------------------+---------------------+------+-----+---------+-------+
| next_not_cached_value | bigint(21)          | NO   |     | NULL    |       |
| minimum_value         | bigint(21)          | NO   |     | NULL    |       |
| maximum_value         | bigint(21)          | NO   |     | NULL    |       |
| start_value           | bigint(21)          | NO   |     | NULL    |       |
| increment             | bigint(21)          | NO   |     | NULL    |       |
| cache_size            | bigint(21) unsigned | NO   |     | NULL    |       |
| cycle_option          | tinyint(1) unsigned | NO   |     | NULL    |       |
| cycle_count           | bigint(21)          | NO   |     | NULL    |       |
+-----------------------+---------------------+------+-----+---------+-------+

From the above table we can notice important details such as the fact that the default numerical value created is of type BIGINT

So that as we have indicated that it is going to start the numbering in 1 and in one more it is going to increase, it allows us to generate the progressive number that can be associated as a primary key to a table

We will then create a new example table in the mariaDB manager

MariaDB [blog]> CREATE TABLE demo(
    -> id BIGINT NOT NULL,
    -> name VARCHAR(30),
    -> PRIMARY KEY(id));

Finally, unlike when declaring a primary key of type auto_increment it is not necessary to declare it in the statement insert , when we use a sequence if it is necessary to write the name of the column; as the following example

MariaDB [blog]> INSERT INTO demo(id, name)
    -> VALUES
    -> (NEXT VALUE FOR id, 'alfa');

As can be seen from the previous sentence, to insert the dynamic value generated by the sequence, we invoke the name of the same through NEXT VALUE FOR and at the end the name of the sequence id

Finally to be able to visualize the result of our previous sentence, we execute a% co_of regular% on the table and we obtain the following

MariaDB [blog]> SELECT * FROM demo;
+----+------+
| id | name |
+----+------+
|  1 | alfa |
+----+------+

Extra configurations:

Optionally you can configure the following parameters to a sequence within the mariaDB manager:

SELECT = You can set it 1

minvalue = Depending on the type of data you choose if it is maxvalue or INT you must check to place a cap that respects the limits of those data types

BIGINT = By default you have the option no cycle, otherwise, once the minimum value starts and the maximum limit is reached, the counter will restart and the numbering will start again (provided that the limit of the data type possible)