1 of 100

Reference

Access the comprehensive MariaDB Server reference. Find detailed documentation on SQL syntax, data types, functions, system variables, and other technical specifications.

SQL Structure

Explore the SQL structure in MariaDB Server. This section provides a fundamental overview of SQL syntax, components, and concepts that form the basis for interacting with your database.

SQL Language Structure

Explore the SQL language structure in MariaDB Server. This section provides fundamental concepts, syntax rules, and common elements that form the building blocks of SQL queries and commands.

Binary Literals

Binary literals can be written in one of the following formats: b'value', B'value' or 0bvalue, where value is a string composed by 0 and 1 digits.

Binary literals are interpreted as binary strings, and are convenient to represent VARBINARY, BINARY or BIT values.

To convert a binary literal into an integer, just add 0.

Examples

Printing the value as a binary string:

Converting the same value into a number:

Date and Time Literals

Standard syntaxes

MariaDB supports the SQL standard and ODBC syntaxes for , , and literals.

SQL standard syntax:

DATE 'string'

Hexadecimal Literals

Hexadecimal literals can be written using any of the following syntaxes:

x'value'
X'value' (SQL standard)
0xvalue

Identifier Case Sensitivity

Whether objects are case sensitive or not is partly determined by the underlying operating system. Unix-based systems are case sensitive, Windows is not, while Mac OS X is usually case insensitive by default, but devices can be configured as case sensitive using Disk Utility.

Database, table, table aliases, and names are affected by the system's case sensitivity, while index, column, column aliases, , and names are never case sensitive.

Log file group names are case sensitive.

In some cases, the table exists but that you are referring to it incorrectly:

Identifier Qualifiers

Qualifiers are used within SQL statements to reference data structures, such as databases, tables, or columns. For example, typically a SELECT query contains references to some columns and at least one table.

Qualifiers can be composed by one or more identifiers, where the initial parts affect the context within which the final identifier is interpreted:

For a database, only the database identifier needs to be specified.
For objects which are contained in a database (like tables, views, functions, etc.) the database identifier can be specified. If no database is specified, the current database is assumed (see USE and DATABASE() for more details). If there is no default database and no database is specified, an error is issued.
For column names, the table and the database are generally obvious from the context of the statement. It is however possible to specify the table identifier, or the database identifier plus the table identifier.
An identifier is fully-qualified if it contains all possible qualifiers, for example, the following column is fully qualified: db_name.tbl_name.col_name.

If a qualifier is composed by more than one identifier, a dot (.) must be used as a separator. All identifiers can be quoted individually. Extra spacing (including new lines and tabs) is allowed.

All the following examples are valid:

db_name.tbl_name.col_name
tbl_name
db_name.tbl_name.col_name

If a table identifier is prefixed with a dot (.), the default database is assumed. This syntax is supported for ODBC compliance, but has no practical effect on MariaDB. These qualifiers are equivalent:

tbl_name
. tbl_name
.tbl_name
. tbl_name

For DML statements, it is possible to specify a list of the partitions using the PARTITION clause. See for details.

Numeric Literals

Numeric literals are written as a sequence of digits from 0 to 9. Initial zeros are ignored. A sign can always precede the digits, but it is optional for positive numbers. In decimal numbers, the integer part and the decimal part are divided with a dot (.).

If the integer part is zero, it can be omitted, but the literal must begin with a dot.

The notation with exponent can be used. The exponent is preceded by an E or e character. The exponent can be preceded by a sign and must be an integer. A number N with an exponent part X, is calculated as N * POW(10, X).

Boolean Literals

In MariaDB, FALSE is a synonym of 0 and TRUE is a synonym of 1. These constants are case insensitive, so TRUE, True, and true are equivalent.

These terms are not synonyms of 0 and 1 when used with the IS operator. So, for example, 10 IS TRUE returns 1, while 10 = TRUE returns 0 (because 1 != 10).

The IS operator accepts a third constant exists: UNKNOWN. It is always a synonym of .

TRUE and FALSE are , while UNKNOWN is not.

Table Value Constructors

Syntax

VALUES ( row_value[, row_value...]), (...)...

Description

In Unions, Views, and subqueries, a Table Value Constructor (TVC) allows you to inject arbitrary values into the result set. The given values must have the same number of columns as the result set, otherwise it returns Error 1222.

Examples

Using TVC's with operations:

Using TVCs with a statement:

Using TVC with an clause:

Using TVC with clause:

_{This page is licensed: CC BY-SA / Gnu FDL}

Joins, Subqueries, and Set

Explore joins, subqueries, and set operations in MariaDB SQL. This section details how to combine data from multiple tables, nest queries, and perform set-based operations for complex data retrieval.

SQL (Structured Query Language) is a highly potent language used in the realm of databases and data analytics. It allows us to create, manipulate, and retrieve data stored in relational databases. While simple SQL commands can handle straightforward tasks, one must delve into advanced aspects such as multiple joins, subqueries, and set operations to unravel the full potential of SQL.

Multiple Joins

Joins are fundamental to SQL, as they enable us to combine rows from two or more tables based on related columns. Several types of joins exist, including INNER JOIN, LEFT JOIN, RIGHT JOIN

Geometry

Explore geometric data types and functions in MariaDB Server. This section details how to store, query, and manipulate spatial data, enabling geospatial applications within your database.

Geometry Hierarchy

Description

Geometry is the base class. It is an abstract class. The instantiable subclasses of Geometry are restricted to zero-, one-, and two-dimensional geometric objects that exist in two-dimensional coordinate space. All instantiable geometry classes are defined so that valid instances of a geometry class are topologically closed (that is, all defined geometries include their boundary).

The base Geometry class has subclasses for Point, Curve, Surface, and GeometryCollection:

represents zero-dimensional objects.

GIS Resources

GIS stands for Geographic Information System.

SPATIAL INDEX

Description

On MyISAM, Aria and InnoDB tables, MariaDB can create spatial indexes (an R-tree index) using syntax similar to that for creating regular indexes, but extended with the SPATIAL keyword. Columns in spatial indexes must be declared NOT NULL.

Spatial indexes can be created when the table is created, or added after the fact:

CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g));

ALTER TABLE geom ADD SPATIAL INDEX(g);

SPATIAL INDEX creates an R-tree index. For storage engines that support non-spatial indexing of spatial columns, the engine creates a B-tree index. A B-tree index on spatial values is useful for exact-value lookups, but not for range scans.

For more information on indexing spatial columns, see .

To drop spatial indexes, use or :

Data-at-Rest Encryption

If is set to full_crc32 or strict_full_crc32, and if the table does not use , InnoDB spatial indexes are encrypted if the table is encrypted.

See for more information.

_{This page is licensed: GPLv2, originally from}

NoSQL

Explore MariaDB Server's NoSQL capabilities. This section details how to store and query schemaless data, including JSON, and how to integrate with other NoSQL data sources, enhancing flexibility.

Dynamic Columns Dynamic Column API HANDLER HandlerSocket JSON Functions Cassandra Storage Engine CONNECT

HANDLER

Explore the HANDLER statement in MariaDB Server for direct table access. This section details how to bypass the SQL optimizer for low-level row operations, useful for specific NoSQL-like interactions.

HANDLER for MEMORY Tables

This article explains how to use efficiently with tables.

If you want to scan a table for different key values, not just search for exact key values, you should create your keys with USING BTREE:

In the above table, a is a key that only supports exact matches (=) while b is a key that you can use to scan the table in key order, starting from start or from a given key value.

The limitations for HANDLER READ with MEMORY|HEAP tables are:

HandlerSocket

Explore HandlerSocket for MariaDB Server. This plugin enables high-performance NoSQL-like access directly to InnoDB tables, bypassing SQL parsing for fast key-value operations.

HandlerSocket Client Libraries

In order to make use of the HandlerSocket plugin in your applications, you will need to use the appropriate client library. The following client libraries are available:

C++
- libhsclient (included with the HandlerSocket plugin source)
Perl
- perl-Net-HandlerSocket (included with the HandlerSocket plugin source)
PHP
Java
Python
Ruby
JavaScript
- node-handlersocket
Scala
Haskell
- HandlerSocket-Haskell-Client

_{This page is licensed: CC BY-SA / Gnu FDL}

HandlerSocket External Resources

Some resources and documentation about HandlerSocket.

The home of HandlerSocket is here.
The story of handlersocket can be found here.
Comparison of HANDLER and HandlerSocket can be found here.
presentation by Akira Higuchi of DeNA - June 29 2010 - DeNA Technology Seminar
presentation by Akira Higuchi of DeNA - June 29 2011 - in Japanese

_{This page is licensed: CC BY-SA / Gnu FDL}

Operators

Learn about operators in MariaDB Server SQL. This section details arithmetic, comparison, logical, and bitwise operators used in expressions and conditions for data manipulation and querying.

Arithmetic Operators

Learn about arithmetic operators in MariaDB Server SQL. This section details how to perform mathematical calculations like addition, subtraction, multiplication, and division within your queries.

Addition Operator (+)

Syntax

Description

Addition.

If both operands are integers, the result is calculated with BIGINT precision. If either integer is unsigned, the result is also an unsigned integer.

For real or string operands, the operand with the highest precision determines the result precision.

Examples

Subtraction Operator (-)

Syntax

Description

Subtraction. The operator is also used as the unary minus for changing sign.

If both operands are integers, the result is calculated with precision. If either integer is unsigned, the result is also an unsigned integer, unless the

Multiplication Operator (*)

Syntax

Description

Multiplication operator.

Examples

Modulo Operator (%)

Syntax

Description

Modulo operator. Returns the remainder of N divided by M

Assignment Operators

Learn about assignment operators in MariaDB Server SQL. This section details how to assign values to variables and columns, essential for data manipulation and programmatic logic.

Assignment Operator (:=)

Syntax

Description

Assignment operator for assigning a value. The value on the right is assigned to the variable on left.

Unlike the , :=

Assignment Operator (=)

Syntax

identifier = expr

Description

The equal sign is used as both an assignment operator in certain contexts, and as a comparison operator. When used as assignment operator, the value on the right is assigned to the variable (or column, in some contexts) on the left.

Since its use can be ambiguous, unlike the , the = assignment operator cannot be used in all contexts, and is only valid as part of a statement, or the SET clause of an statement

This operator works with both and .

Examples

Comparison Operators

Learn about comparison operators in MariaDB Server SQL. This section details operators like =, >, <, and LIKE used to compare values in conditions, essential for filtering and joining data.

>=

Syntax

Description

Greater than or equal operator. Evaluates both SQL expressions and returns 1 if the left value is greater than or equal to the right value and 0

>

Syntax

Description

Greater than operator. Evaluates both SQL expressions and returns 1 if the left value is greater than the right value and 0 if it is not, or NULL if either expression is NULL. If the expressions return different data types, (for instance, a number and a string), performs type conversion.

When used in row comparisons these two queries return the same results:

Examples

GREATEST

Syntax

GREATEST(value1,value2,...)

Description

With two or more arguments, returns the largest (maximum-valued) argument. The arguments are compared using the same rules as for LEAST().

Examples

_{This page is licensed: GPLv2, originally from}

IN

Syntax

Description

Returns 1 if

IS NOT NULL

Syntax

IS NOT NULL

Description

Tests whether a value is not NULL. See also NULL Values in MariaDB.

Examples

IS NOT

Syntax

Description

Tests a value against a boolean value, where boolean_value can be TRUE

IS NULL

Syntax

Description

Tests whether a value is NULL. See also .

IS

Syntax

Description

Tests a value against a boolean value, where boolean_value can be TRUE, FALSE, or UNKNOWN.

There is an important difference between using IS TRUE or comparing a value with TRUE using =. When using =, only 1 equals to TRUE. But when using IS TRUE, all values which are logically true (like a number > 1) return TRUE.

Examples

Difference between = and IS TRUE:

ISNULL

Syntax

ISNULL(expr)

Description

If expr is NULL, ISNULL() returns 1, otherwise it returns 0.

Examples

_{This page is licensed: GPLv2, originally from}

LEAST

Syntax

Description

With two or more arguments, returns the smallest (minimum-valued) argument. The arguments are compared using the following rules:

<

Syntax

Description

Less than operator. Evaluates both SQL expressions and returns 1 if the left value is less than the right value and 0 if it is not, or NULL if either expression is NULL. If the expressions return different data types, (for instance, a number and a string), performs type conversion.

When used in row comparisons, these two queries return the same results:

Examples

Type conversion:

Case insensitivity - see :

NOT BETWEEN

Syntax

expr NOT BETWEEN min AND max

Description

This is the same as NOT (expr min AND max).

Note that the meaning of the alternative form NOT expr BETWEEN min AND max is affected by the HIGH_NOT_PRECEDENCE flag.

Examples

NULL:

_{This page is licensed: GPLv2, originally from}

!=

Syntax

Description

Not equal operator. Evaluates both SQL expressions and returns 1 if they are not equal, and 0

<=>

Syntax

<=>

Description

NULL-safe equal operator. It performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

a <=> b is equivalent to a = b OR (a IS NULL AND b IS NULL).

When used in row comparisons, these two queries return the same results:

Examples

Logical Operators

Learn about logical operators in MariaDB Server SQL. This section details operators like AND, OR, and NOT used to combine or negate conditions, essential for complex filtering and data selection.

&&

Syntax

Description

Logical AND. Evaluates to 1

!

Syntax

NOT, !

Description

Logical NOT. Evaluates to 1 if the operand is 0, to 0 if the operand is non-zero, and NOT NULL returns NULL.

By default, the ! operator has a . If the HIGH_NOT_PRECEDENCE flag is set, NOT and ! have the same precedence.

Examples

XOR

Syntax

Description

XOR stands for eXclusive OR. Returns NULL

Sequences

Learn about sequences in MariaDB Server. This section details how to create and manage sequences for generating unique numbers, often used for primary keys and other auto-incrementing values.

DROP SEQUENCE

Syntax

Description

DROP SEQUENCE removes one or more created with . You must have the DROP

SEQUENCE Functions

Learn about sequence functions in MariaDB Server. This section details SQL functions for retrieving the next or current value from a sequence, crucial for generating unique identifiers.

LASTVAL

LASTVAL is a synonym for .

_{This page is licensed: CC BY-SA / Gnu FDL}

NEXTVAL

NEXTVAL is a synonym for .

_{This page is licensed: CC BY-SA / Gnu FDL}

Temporal Tables

Explore temporal tables in MariaDB Server. This section details how to manage data with system-versioning and application-time periods, enabling historical data tracking and time-aware queries.

Vectors

Explore vector data types. This section details how to store and manage numerical arrays, enabling efficient vector similarity search and machine learning applications within your database.

VECTOR

VECTOR is available from MariaDB 11.7.1.

Syntax

VECTOR(N)

Description

The VECTOR data type was added as part of the feature, which permits MariaDB Server to perform as a relational vector database. N represents the fixed number of dimensions of the vector up to a maximum of 16383. The N dimension will be determined by the embedding algorithm.

Example

SQL Statements

Learn to select data in MariaDB Server using SQL. This section covers various SELECT statement clauses, including WHERE, GROUP BY, and ORDER BY, to retrieve and filter your data effectively.

Comment Syntax

This section details the different ways to add non-executable comments to your SQL statements and script files, including single-line & multi-line comments, and MariaDB-specific conditional comments.

There are three supported comment styles in MariaDB:

From a hash sign (#) to the end of a line:

From a double dash (--) to the end of a line. The space after the two dashes is required (as in MySQL).

Account Management

Learn account management SQL statements for MariaDB Server. This section covers commands like CREATE USER, GRANT, and REVOKE to securely manage user access and privileges within your database.

DROP ROLE

Remove a role from the system. Learn the syntax to delete defined roles and revoke them from any users or roles that currently hold them.

Syntax

Description

The DROP ROLE

HandlerSocket Configuration Options

The HandlerSocket plugin has the following options.

Add the options to the [mysqld] section of your my.cnf file.

`handlersocket_accept_balance`

Description: When set to a value other than zero ('0'), handlersocket will try to balance accepted connections among threads. Default is 0 , but if you use persistent connections (for example if you use client-side connection pooling), a non-zero value is recommended.
Command line: --handlersocket-accept-balance="value"
Scope: Global
Dynamic: No
Type: number
Range: 0 to 10000
Default Value: 0

`handlersocket_address`

Description: Specify the IP address to bind to.
Command line: --handlersocket-address="value"
Scope: Global
Dynamic: No

`handlersocket_backlog`

Description: Specify the listen backlog length.
Command line: --handlersocket-backlog="value"
Scope: Global
Dynamic: No

`handlersocket_epoll`

Description: Specify whether to use epoll for I/O multiplexing.
Command line: --handlersocket-epoll="value"
Scope: Global
Dynamic: No

`handlersocket_plain_secret`

Description: When set, enables plain-text authentication for the listener for read requests, with the value of the option specifying the secret authentication key.
Command line: --handlersocket-plain-secret="value"
Dynamic: No
Type: string

`handlersocket_plain_secret_wr`

Description: When set, enables plain-text authentication for the listener for write requests, with the value of the option specifying the secret authentication key.
Command line: --handlersocket-plain-secret-wr="value"
Dynamic: No
Type: string

`handlersocket_port`

Description: Specify the port to bind to for reads. An empty value disables the listener.
Command line: --handlersocket-port="value"
Scope: Global
Dynamic: No

`handlersocket_port_wr`

Description: Specify the port to bind to for writes. An empty value disables the listener.
Command line: --handlersocket-port-wr="value"
Scope: Global
Dynamic: No

`handlersocket_rcvbuf`

Description: Specify the maximum socket receive buffer (in bytes). If '0' then the system default is used.
Command line: --handlersocket-rcvbuf="value"
Scope: Global
Dynamic: No

`handlersocket_readsize`

Description: Specify the minimum length of the request buffer. Larger values consume available memory but can make handlersocket faster for large requests.
Command line: --handlersocket-readsize="value"
Scope: Global
Dynamic: No

`handlersocket_sndbuf`

Description: Specify the maximum socket send buffer (in bytes). If '0' then the system default is used.
Command line: --handlersocket-sndbuf="value"
Scope: Global
Dynamic: No

`handlersocket_threads`

Description: Specify the number of worker threads for reads. Recommended value = number of CPU cores * 2.
Command line: --handlersocket-threads="value"
Scope: Global
Dynamic: No

`handlersocket_threads_wr`

Description: Specify the number of worker threads for writes. Recommended value = 1.
Command line: --handlersocket-threads-wr="value"
Scope: Global
Dynamic: No

`handlersocket_timeout`

Description: Specify the socket timeout in seconds.
Command line: --handlersocket-timeout="value"
Scope: Global
Dynamic: No

`handlersocket_verbose`

Description: Specify the logging verbosity.
Command line: --handlersocket-verbose="value"
Scope: Global
Dynamic: No

`handlersocket_wrlock_timeout`

Description: The write lock timeout in seconds. When acting on write requests, handlersocket locks an advisory lock named 'handlersocket_wr' and this option sets the timeout for it.
Command line: --handlersocket-wrlock-timeout="value"
Scope: Global
Dynamic: No

_{This page is licensed: CC BY-SA / Gnu FDL}

Application-Time Periods

Extending system-versioned tables, MariaDB supports application-time period tables. Time periods are defined by a range between two temporal columns. The columns must be of the same temporal data type, i.e. DATE, TIMESTAMP or DATETIME (TIME and YEAR are not supported), and of the same width.

Using time periods implicitly defines the two columns as NOT NULL. It also adds a constraint to check whether the first value is less than the second value. The constraint is invisible to SHOW CREATE TABLE statements. The name of this constraint is prefixed by the time period name, to avoid conflict with other constraints.

Creating Tables with Time Periods

To create a table with a time period, use a statement with the PERIOD table option.

This creates a table with a time_period period and populates the table with some basic temporal values.

Examples are available in the MariaDB Server source code, at mysql-test/suite/period/r/create.result.

Adding and Removing Time Periods

The statement now supports syntax for adding and removing time periods from a table. To add a period, use the ADD PERIOD clause:

To remove a period, use the DROP PERIOD clause:

Both ADD PERIOD and DROP PERIOD clauses include an option to handle whether the period already exists:

Deletion by Portion

You can also remove rows that fall within certain time periods.

When MariaDB executes a DELETE FOR PORTION statement, it removes the row:

When the row period falls completely within the delete period, it removes the row.
When the row period overlaps the delete period, it shrinks the row, removing the overlap from the first or second row period value.
When the delete period falls completely within the row period, it splits the row into two rows. The first row runs from the starting row period to the starting delete period. The second runs from the ending delete period to the ending row period.

To test this, first populate the table with some data to operate on:

Then, run the DELETE FOR PORTION statement:

Here:

a is unchanged, as the range falls entirely out of the specified portion to be deleted.
b, with values ranging from 1999 to 2018, is split into two rows, 1999 to 2000 and 2018-01 to 2018-12 (i.e. one extra row has been inserted).
c, with values ranging from 1999 to 2017, where only the upper value falls within the portion to be deleted, has been shrunk to 1999 to 2001.

The DELETE FOR PORTION statement has the following restrictions

The FROM...TO clause must be constant.
Multi-delete is not supported.

If there are DELETE or INSERT triggers, any matched row is deleted, and then one or two rows are inserted. If the record is deleted completely, nothing is inserted.

Updating by Portion

The syntax now supports UPDATE FOR PORTION, which modifies rows based on their occurrence in a range:

To test it, first populate the table with some data:

Then run the update:

a is unchanged, as the range falls entirely out of the specified portion to be updated.
For b, with years ranging from 1999 to 2018, two extra rows are inserted, with ranges 1999-01 to 2000-01 and 2018-01 to 2018-12. The original row's period has been shrunk to years 2000 and 2018, and the name field has got "_original" appended.
c, with values ranging from 1999 to 2017, where only the upper value falls within the portion to be updated, has been shrunk to 1999 to 2001.

The UPDATE FOR PORTION statement has the following limitations:

The operation cannot modify the two temporal columns used by the time period.
The operation cannot reference period values in the SET expression.
FROM...TO expressions must be constant.

WITHOUT OVERLAPS

This clause is available from MariaDB 10.5.3.

WITHOUT OVERLAPS allows to create an index specifying that application time periods should not overlap. An index constrained by WITHOUT OVERLAPS is required to be either a primary key or a unique index.

Take the following example, an application time period table for a booking system:

Our system is not intended to permit overlapping bookings, so the fourth record above should not have been inserted. Using WITHOUT OVERLAPS in a unique index (in this case based on a combination of room number and the application time period) allows to specify this constraint in the table definition.

Information Schema

Information Schema support for application time period tables is available from MariaDB 11.4.

Information Schema contains the following support for application time period tables:

view.
view.
Additional columns IS_SYSTEM_TIME_PERIOD_START and IS_SYSTEM_TIME_PERIOD_END in the view.

Further Examples

The implicit change from NULL to NOT NULL:

Due to this constraint, trying to add a time period where NULL data already exist fails:

CREATE SEQUENCE

Create a sequence generator. This statement initializes a sequence object that produces a series of unique numeric values on demand.

Syntax

The options for CREATE SEQUENCE can be given in any order, optionally followed by table_options.

table_options can be any of the normal table options in CREATE TABLE — the most used ones are ENGINE=... and COMMENT=.

NOMAXVALUE and NOMINVALUE are there to allow one to create SEQUENCEs using the Oracle syntax.

Description

CREATE SEQUENCE creates a sequence that generates new values when called with NEXT VALUE FOR sequence_name. It's an alternative to if you want to have more control of how the numbers are generated. As the SEQUENCE caches values (up to CACHE), it can in some cases be much faster than . Another benefit is that you can access the last value generated by all used sequences, which solves one of the limitations with .

CREATE SEQUENCE requires the .

can be used to drop a sequence, and to change it.

CREATE Options

AS

INT type, that is, one of , , , , , . Can be signed or unsigned. Maximum value is based on the data type. The use of BIGINT UNSIGNED with this option extends the possible maximum value from 9223372036854775806 to 18446744073709551614. Default is BIGINT.

The AS option is not available.

INCREMENT

Increment to use for values. May be negative. Setting an increment of 0 causes the sequence to use the value of the system variable at the time of creation, which is always a positive number. (see ). Default 1.

MINVALUE

Minimum value for the sequence. From , the parser permits much smaller numbers, such as -9999999999999999999999999999, but converts to the minimum permitted for the INT type, with a note. Default 1 if INCREMENT > 0 , and -9223372036854775807 (or based on int type) if INCREMENT < 0.

MAXVALUE

Maximum value for sequence. From , the parser permits much larger numbers, such as 9999999999999999999999999999 used in Oracle examples, but converts to the maximum permitted for the INT type, with a note. Default 9223372036854775806 (or based on int type) if INCREMENT > 0 , and -1 if INCREMENT < 0.

START

First value the sequence will generate. Default MINVALUE if INCREMENT > 0, and MAX_VALUE if INCREMENT< 0.

CACHE / NOCACHE

Number of values that should be cached. 0 if no CACHE. The underlying table will be updated first time a new sequence number is generated and each time the cache runs out. Default 1000. , shutting down the server, etc. will discard the cached values, and the next sequence number generated will be according to what's stored in the Sequence object. In effect, this will discard the cached values.

Note that setting the cache to 1 from 1000 can make inserts to tables using sequences for default values 2x slower and increase the binary log sizes up to 7x.

CYCLE / NOCYCLE

If CYCLE is used, then the sequence should start again from MINVALUE after it has run out of values. Default value is NOCYCLE.

Constraints on Create Arguments

To be able to create a legal sequence, the following must hold:

MAXVALUE >= start
MAXVALUE > MINVALUE
START >= MINVALUE

Note that sequences can't generate the maximum/minimum 64 bit number because of the constraint ofMINVALUE and MAXVALUE.

Atomic DDL

MariaDB supports and CREATE SEQUENCE is atomic.

MariaDB does not support and CREATE SEQUENCE is atomic.

Examples

The following statement fails, as the increment conflicts with the defaults:

The sequence can be created by specifying workable minimum and maximum values:

From :

Parser accepting larger or smaller values:

Cache

Flushing the cache:

Create table with a sequence as a default value

You can use sequences instead of AUTO_INCREMENT to generate values for a table:

Reference

SQL Structure

SQL Language Structure

Binary Literals

Examples

See Also

Date and Time Literals

Standard syntaxes

Hexadecimal Literals

Identifier Case Sensitivity

Identifier Qualifiers

See Also

Numeric Literals

Boolean Literals

See Also

Table Value Constructors

Syntax

Description

Examples

Joins, Subqueries, and Set

Multiple Joins

Geometry

Geometry Hierarchy

Description

GIS Resources

SPATIAL INDEX

Description

Data-at-Rest Encryption

NoSQL

HANDLER

HANDLER for MEMORY Tables

HandlerSocket

HandlerSocket Client Libraries

HandlerSocket External Resources

Operators

Arithmetic Operators

Addition Operator (+)

Syntax

Description

Examples

See Also

Subtraction Operator (-)

Syntax

Description

Multiplication Operator (*)

Syntax

Description

Examples

See Also

Modulo Operator (%)

Syntax

Description

Assignment Operators

Assignment Operator (:=)

Syntax

Description

Assignment Operator (=)

Syntax

Description

Examples

See Also

Comparison Operators

>=

Syntax

Description

>

Syntax

Description

Examples

See Also

GREATEST

Syntax

Description

Examples

IN

Syntax

Description

IS NOT NULL

Syntax

Description