DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

How does AI transform chaos engineering from an experiment into a critical capability? Learn how to effectively operationalize the chaos.

Data quality isn't just a technical issue: It impacts an organization's compliance, operational efficiency, and customer satisfaction.

Are you a front-end or full-stack developer frustrated by front-end distractions? Learn to move forward with tooling and clear boundaries.

Developer Experience: Demand to support engineering teams has risen, and there is a shift from traditional DevOps to workflow improvements.

Related

  • Leveraging "INSERT INTO ... RETURNING": Practical Scenarios
  • Using the PostgreSQL Pager With MariaDB Xpand
  • Distributed SQL: An Alternative to Database Sharding
  • Introduction to Data Replication With MariaDB Using Docker Containers

Trending

  • Converting List to String in Terraform
  • How to Marry MDC With Spring Integration
  • How to Use Testcontainers With ScyllaDB
  • Kung Fu Commands: Shifu Teaches Po the Command Pattern with Java Functional Interfaces
  1. DZone
  2. Data Engineering
  3. Databases
  4. Sequences Support in MariaDB Server 10.3

Sequences Support in MariaDB Server 10.3

Read this article in order to learn more about how to create a sequence and how to create a table to test the usage of sequences.

By 
Rasmus Johansson user avatar
Rasmus Johansson
·
Jun. 14, 18 · Analysis
Likes (1)
Comment
Save
Tweet
Share
10.6K Views

Join the DZone community and get the full member experience.

Join For Free

In the SQL standard, SQL:2003 sequences are defined. The idea of sequences is to have a way of requesting unique values on demand. The typical use case for sequences is to have a unique identifier that can be used on multiple tables. In addition, it might be useful in some cases to have an identifier before an actual row is inserted. With the normal way of having an automatically incrementing identifier, the identifier value will only be available after insert of the row, and the identifier will only be unique inside its own table. The implementation of sequences in MariaDB Server 10.3 follows the standard and includes compatibility with the way Oracle does sequences introduced in Oracle Database Server on top of the standard.

To create a sequence, a create statement is used:

CREATE SEQUENCE Seq1_1  
  START WITH 1  
  INCREMENT BY 1;

This creates a sequence that starts at 1 and is incremented with 1 every time a value is requested from the sequence. In this example, both START WITH and INCREMENT BY could have been left out since their default values are 1. The sequence will be visible among the tables in the database, i.e. if you run SHOW TABLES, it will be there. You can use DESCRIBE on the sequence to see what columns it has.

To test out the usage of sequences, let’s create a table.

CREATE TABLE Observation (
  Id int(11) NOT NULL,
  Place varchar(50) NOT NULL,
  BirdId int(11) NOT NULL,
  PRIMARY KEY (Id)
)

Since I want to use sequences this time, I did not put AUTO_INCREMENT on the ID column. Instead, I’ll ask for the next value from the sequence in the INSERT statements:

INSERT INTO Observation (Id, Place, BirdId) VALUES (NEXT VALUE FOR Seq1_1, 'Helsinki', 10);
INSERT INTO Observation (Id, Place, BirdId) VALUES (NEXT VALUE FOR Seq1_1, 'Espoo', 10);
INSERT INTO Observation (Id, Place, BirdId) VALUES (NEXT VALUE FOR Seq1_1, 'Kirkkonummi', 10);
INSERT INTO Observation (Id, Place, BirdId) VALUES (NEXT VALUE FOR Seq1_1, 'Hanko', 10);

"A bird flying west from Helsinki and being observed in cities along the way." In the INSERT statements, there is a call to the sequence: NEXT VALUE FOR Seq1_1. It will retrieve the next value from the sequence. Instead of having the NEXT VALUE FOR in each INSERT statement, it could have been the default value of the column in this way:

ALTER TABLE Observation MODIFY Id int(11) NOT NULL DEFAULT NEXT VALUE FOR Seq1_1;

Running a SELECT over the Observation table will look like this:

SELECT * FROM Observation;
+----+-------------+--------+
| Id | Place       | BirdId |
+----+-------------+--------+
|  1 | Helsinki    |     10 |
|  2 | Espoo       |     10 |
|  3 | Kirkkonummi |     10 |
|  4 | Hanko       |     10 |
+----+-------------+--------+
4 rows in set (0.001 sec)

As we can see, the ID column has been populated with numbers that start from 1 and are incremented with 1 as defined in the sequence’s CREATE statement. To get the last retrieved number from the sequence, PREVIOUS VALUE is used:

SELECT PREVIOUS VALUE FOR Seq1_1;
+---------------------------+
| PREVIOUS VALUE FOR Seq1_1 |
+---------------------------+
|                         4 |
+---------------------------+
1 row in set (0.001 sec)

Another useful option for sequences is CYCLE, which means that we start from the beginning after reaching a certain value. For example, if there are 4 phases in a process that are done sequentially and then start from the beginning, we could create a sequence to always be able to retrieve the number of the next phase.

CREATE SEQUENCE Seq1_1_c4  
  START WITH 1  
  INCREMENT BY 1
  MAXVALUE = 4
  CYCLE;

The sequence starts at 1 and is incremented with 1 every time the next value is requested. But when it reaches 4 (MAXVALUE), it will restart from 1 (CYCLE).

It’s also possible to set the next value of a sequence, to ALTER a sequence, or use sequences in Oracle mode with Oracle specific syntax. To switch to Oracle mode use:

SET SQL_MODE=ORACLE;

After that, you can retrieve the next value of a sequence in Oracle style:

SELECT Seq1_1.nextval;

Read more about sequences in the MariaDB documentation. Get MariaDB Server 10.3 as part of the MariaDB TX 3.0 download — now available.

Database MariaDB sql

Published at DZone with permission of Rasmus Johansson, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Leveraging "INSERT INTO ... RETURNING": Practical Scenarios
  • Using the PostgreSQL Pager With MariaDB Xpand
  • Distributed SQL: An Alternative to Database Sharding
  • Introduction to Data Replication With MariaDB Using Docker Containers

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • [email protected]

Let's be friends: