All pages
Powered by GitBook
1 of 93

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:

SELECT 0b1000001;
+-----------+
| 0b1000001 |
+-----------+
| A         |
+-----------+

Converting the same value into a number:

SELECT 0b1000001+0;
+-------------+
| 0b1000001+0 |
+-------------+
|          65 |
+-------------+

See Also

  • BIN()

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

Date and Time Literals

Standard syntaxes

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

SQL standard syntax:

  • DATE 'string'

  • TIME 'string'

  • TIMESTAMP 'string'

ODBC syntax:

  • {d 'string'}

  • {t 'string'}

  • {ts 'string'}

The timestamp literals are treated as DATETIME literals, because in MariaDB the range of DATETIME is closer to the TIMESTAMP range in the SQL standard.

string is a string in a proper format, as explained below.

DATE literals

A DATE string is a string in one of the following formats: 'YYYY-MM-DD' or 'YY-MM-DD'. Note that any punctuation character can be used as delimiter. All delimiters must consist of 1 character. Different delimiters can be used in the same string. Delimiters are optional (but if one delimiter is used, all delimiters must be used).

A DATE literal can also be an integer, in one of the following formats: YYYYMMDD or YYMMDD.

All the following DATE literals are valid, and they all represent the same value:

'19940101'
'940101'
'1994-01-01'
'94/01/01'
'1994-01/01'
'94:01!01'
19940101
940101

DATETIME literals

A DATETIME string is a string in one of the following formats: 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS'. Note that any punctuation character can be used as delimiter for the date part and for the time part. All delimiters must consist of 1 character. Different delimiters can be used in the same string. The hours, minutes and seconds parts can consist of one character. For this reason, delimiters are mandatory for DATETIME literals.

The delimiter between the date part and the time part can be a T or any sequence of space characters (including tabs, new lines and carriage returns).

A DATETIME literal can also be a number, in one of the following formats: YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD or YYMMDD. In this case, all the time subparts must consist of 2 digits.

All the following DATE literals are valid, and they all represent the same value:

'1994-01-01T12:30:03'
'1994/01/01\n\t 12+30+03'
'1994/01\\01\n\t 12+30-03'
'1994-01-01 12:30:3'

TIME literals

A TIME string is a string in one of the following formats: 'D HH:MM:SS', 'HH:MM:SS, 'D HH:MM', 'HH:MM', 'D HH', or 'SS'. D is a value from 0 to 34 which represents days. : is the only allowed delimiter for TIME literals. Delimiters are mandatory, with an exception: the 'HHMMSS' format is allowed. When delimiters are used, each part of the literal can consist of one character.

A TIME literal can also be a number in one of the following formats: HHMMSS, MMSS, or SS.

The following literals are equivalent:

'09:05:00'
'9:05:0'
'9:5:0'
'090500'

2-digit years

The year part in DATE and DATETIME literals is determined as follows:

  • 70 - 99 = 1970 - 1999

  • 00 - 69 = 2000 - 2069

Microseconds

DATETIME and TIME literals can have an optional microseconds part. For both string and numeric forms, it is expressed as a decimal part. Up to 6 decimal digits are allowed. Examples:

'12:30:00.123456'
123000.123456

See Microseconds in MariaDB for details.

Date and time literals and the SQL_MODE

Unless the SQL_MODE NO_ZERO_DATE flag is set, some special values are allowed: the '0000-00-00' DATE, the '00:00:00' TIME, and the 0000-00-00 00:00:00 DATETIME.

If the ALLOW_INVALID_DATES flag is set, the invalid dates (for example, 30th February) are allowed. If not, if the NO_ZERO_DATE is set, an error is produced; otherwise, a zero-date is returned.

Unless the NO_ZERO_IN_DATE flag is set, each subpart of a date or time value (years, hours...) can be set to 0.

See also

  • Date and time units

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

Hexadecimal Literals

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

  • x'value'

  • X'value' (SQL standard)

  • 0xvalue (ODBC)

value is a sequence of hexadecimal digits (from 0 to 9 and from A to F). The case of the digits does not matter. With the first two syntaxes, value must consist of an even number of digits. With the last syntax, digits can be even, and they are treated as if they had an extra 0 at the beginning.

Normally, hexadecimal literals are interpreted as binary string, where each pair of digits represents a character. When used in a numeric context, they are interpreted as integers. (See the example below). In no case can a hexadecimal literal be a decimal number.

The first two syntaxes; X'value' and x'value, follow the SQL standard, and behave as a string in all contexts in MariaDB. The latter syntax, 0xvalue, is a MySQL/MariaDB extension for hex hybrids and behaves as a string or as a number depending on context. MySQL treats all syntaxes the same, so there may be different results in MariaDB and MySQL (see below).

Examples

Representing the a character with the three syntaxes explained above:

SELECT x'61', X'61', 0x61;
+-------+-------+------+
| x'61' | X'61' | 0x61 |
+-------+-------+------+
| a     | a     | a    |
+-------+-------+------+

Hexadecimal literals in a numeric context:

SELECT 0 + 0xF, -0xF;
+---------+------+
| 0 + 0xF | -0xF |
+---------+------+
|      15 |  -15 |
+---------+------+

Fun With Types

CREATE TABLE t1 (a INT, b VARCHAR(10));
INSERT INTO t1 VALUES (0x31, 0x61),(COALESCE(0x31), COALESCE(0x61));

SELECT * FROM t1;
+------+------+
| a    | b    |
+------+------+
|   49 | a    |
|    1 | a    |
+------+------+

The reason for the differing results above is that when 0x31 is inserted directly to the column, it's treated as a number, while when 0x31 is passed to COALESCE(), it's treated as a string, because:

  • HEX values have a string data type by default.

  • COALESCE() has the same data type as the argument.

Differences Between MariaDB and MySQL

SELECT x'0a'+0;
+---------+
| x'0a'+0 |
+---------+
|       0 |
+---------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1292): Truncated incorrect DOUBLE value: '\x0A'

SELECT X'0a'+0;
+---------+
| X'0a'+0 |
+---------+
|       0 |
+---------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1292): Truncated incorrect DOUBLE value: '\x0A'

SELECT 0x0a+0;
+--------+
| 0x0a+0 |
+--------+
|     10 |
+--------+

In MySQL (up until at least MySQL 8.0.26):

SELECT x'0a'+0;
+---------+
| x'0a'+0 |
+---------+
|      10 |
+---------+

SELECT X'0a'+0;
+---------+
| X'0a'+0 |
+---------+
|      10 |
+---------+

SELECT 0x0a+0;
+--------+
| 0x0a+0 |
+--------+
|     10 |
+--------+

See Also

  • HEX()

  • UNHEX()

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

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 trigger names are affected by the system's case sensitivity, while index, column, column aliases, stored routine, and event 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:

  • Because MariaDB uses directories and files to store databases and tables, database and table names are case-sensitive if they are located on a file system that has case-sensitive file names.

  • Even for file systems that are not case-sensitive, such as on Windows, all references to a given table within a query must use the same lettercase.

The lower_case_table_names server system variable plays a key role. It determines whether table names, aliases, and database names are compared in a case-sensitive manner. If set to 0 (the default on Unix-based systems), table names and aliases, and database names are compared in a case-sensitive manner. If set to 1 (the default on Windows), names are stored in lowercase and not compared in a case-sensitive manner. If set to 2 (the default on Mac OS X), names are stored as declared, but compared in lowercase.

It is thus possible to make Unix-based systems behave like Windows and ignore case-sensitivity. The reverse is not true before Windows 10, as the underlying Windows filesystem could not support this. It is possible since Windows 10, although case insensitivity is still the default operating system setting.

Even on case-insensitive systems, you are required to use the same case consistently within the same statement. The following statement fails, as it refers to the table name in a different case.

SELECT * FROM a_table WHERE A_table.id>10;

For a full list of identifiers naming rules, see Identifier Names.

Please note that lower_case_table_names is a database initialization parameter. This means that, along with innodb_page_size, this variable must be set before running mariadb-install-db, and will not change the behavior of servers unless applied before the creation of core system databases.

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

Identifier Names

Databases, tables, indexes, columns, aliases, views, stored routines, triggers, events, variables, partitions, tablespaces, savepoints, labels, users, roles, are collectively known as identifiers, and have certain rules for naming.

Identifiers may be quoted using the backtick character (`). Quoting is optional for identifiers that don't contain special characters, or for identifiers that are not reserved words. If the ANSI_QUOTES SQL_MODE flag is set, double quotes (") can also be used to quote identifiers. If the MSSQL flag is set, square brackets ([ and ]) can be used for quoting.

Even when using reserved words as names, fully qualified names do not need to be quoted. For example, test.select has only one possible meaning, so it is correctly parsed even without quotes.

Unquoted

The following characters are valid, and allow identifiers to be unquoted:

  • ASCII: [0-9,a-z,A-Z$_] (numerals 0-9, basic Latin letters, both lowercase and uppercase, dollar sign, underscore)

  • Extended: U+0080 .. U+FFFF

Quoted

The following characters are valid, but identifiers using them must be quoted:

  • ASCII: U+0001 .. U+007F (full Unicode Basic Multilingual Plane (BMP) except for U+0000)

  • Extended: U+0080 .. U+FFFF

  • Identifier quotes can themselves be used as part of an identifier, as long as they are quoted.

Further Rules

There are a number of other rules for identifiers:

  • Identifiers are stored as Unicode (UTF-8)

  • Identifiers may or may not be case-sensitive. See Indentifier Case-sensitivity.

  • Database, table and column names can't end with space characters

  • Identifier names may begin with a numeral, but can't only contain numerals unless quoted.

  • An identifier starting with a numeral, followed by an 'e', may be parsed as a floating point number, and needs to be quoted.

  • Identifiers are not permitted to contain the ASCII NUL character (U+0000) and supplementary characters (U+10000 and higher).

  • Names such as 5e6, 9e are not prohibited, but it's strongly recommended not to use them, as they could lead to ambiguity in certain contexts, being treated as a number or expression.

  • User variables cannot be used as part of an identifier, or as an identifier in an SQL statement.

Quote Character

The regular quote character is the backtick character - ```, but if the ANSI_QUOTES SQL_MODE option is specified, a regular double quote - " may be used as well.

The backtick character can be used as part of an identifier. In that case the identifier needs to be quoted. The quote character can be the backtick, but in that case, the backtick in the name must be escaped with another backtick.

Maximum Length

  • Databases, tables, columns, indexes, constraints, stored routines, triggers, events, views, tablespaces, servers and log file groups have a maximum length of 64 characters.

  • Compound statement labels have a maximum length of 16 characters.

  • Aliases have a maximum length of 256 characters, except for column aliases in CREATE VIEW statements, which are checked against the maximum column length of 64 characters (not the maximum alias length of 256 characters).

  • Users have a maximum length of 80 characters.

  • Roles have a maximum length of 128 characters.

  • Multi-byte characters do not count extra towards the character limit.

Multiple Identifiers

MariaDB allows the column name to be used on its own if the reference will be unambiguous, or the table name to be used with the column name, or all three of the database, table and column names. A period is used to separate the identifiers, and the period can be surrounded by spaces.

Examples

Using the period to separate identifiers:

CREATE TABLE t1 (i int);

INSERT INTO t1(i) VALUES (10);

SELECT i FROM t1;
+------+
| i    |
+------+
|   10 |
+------+

SELECT t1.i FROM t1;
+------+
| i    |
+------+
|   10 |
+------+

SELECT test.t1.i FROM t1;
+------+
| i    |
+------+
|   10 |
+------+

The period can be separated by spaces:

SELECT test . t1 . i FROM t1;
+------+
| i    |
+------+
|   10 |
+------+

Resolving ambiguity:

CREATE TABLE t2 (i int);

SELECT i FROM t1 LEFT JOIN t2 ON t1.i=t2.i;
ERROR 1052 (23000): Column 'i' in field list is ambiguous

SELECT t1.i FROM t1 LEFT JOIN t2 ON t1.i=t2.i;
+------+
| i    |
+------+
|   10 |
+------+

Creating a table with characters that require quoting:

CREATE TABLE 123% (i int);
ERROR 1064 (42000): You have an error in your SQL syntax; 
  check the manual that corresponds to your MariaDB server version for the right syntax 
  to use near '123% (i int)' at line 1

CREATE TABLE `123%` (i int);
Query OK, 0 rows affected (0.85 sec)

CREATE TABLE `TABLE` (i int);
Query OK, 0 rows affected (0.36 sec)

Using double quotes as a quoting character:

CREATE TABLE "SELECT" (i int);
ERROR 1064 (42000): You have an error in your SQL syntax; 
  check the manual that corresponds to your MariaDB server version for the right syntax 
  to use near '"SELECT" (i int)' at line 1

SET sql_mode='ANSI_QUOTES';
Query OK, 0 rows affected (0.03 sec)

CREATE TABLE "SELECT" (i int);
Query OK, 0 rows affected (0.46 sec)

Using an identifier quote as part of an identifier name:

SHOW VARIABLES LIKE 'sql_mode';
+---------------+-------------+
| Variable_name | Value       |
+---------------+-------------+
| sql_mode      | ANSI_QUOTES |
+---------------+-------------+

CREATE TABLE "fg`d" (i int);
Query OK, 0 rows affected (0.34 sec)

Creating the table named * (Unicode number: U+002A) requires quoting.

CREATE TABLE `*` (a INT);

Floating point ambiguity:

CREATE TABLE 8984444cce5d (x INT);
Query OK, 0 rows affected (0.38 sec)

CREATE TABLE 8981e56cce5d (x INT);
ERROR 1064 (42000): You have an error in your SQL syntax; 
  check the manual that corresponds to your MariaDB server version for the right syntax 
  to use near '8981e56cce5d (x INT)' at line 1

CREATE TABLE `8981e56cce5d` (x INT);
Query OK, 0 rows affected (0.39 sec)

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

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

  • db_name . tbl_name

  • db_name. tbl_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 Partition Pruning and Selection for details.

See Also

  • Identifier Names

  • USE

  • DATABASE()

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

Identifier to File Name Mapping

Some identifiers map to a file name on the filesystem. Databases each have their own directory, while, depending on the storage engine, table names and index names may map to a file name.

Not all characters that are allowed in table names can be used in file names. Every filesystem has its own rules of what characters can be used in file names. To let the user create tables using all characters allowed in the SQL Standard and to not depend on whatever particular filesystem a particular database resides, MariaDB encodes "potentially unsafe" characters in the table name to derive the corresponding file name.

This is implemented using a special character set. MariaDB converts a table name to the "filename" character set to get the file name for this table. And it converts the file name from the "filename" character set to, for example, utf8 to get the table name for this file name.

The conversion rules are as follows: if the identifier is made up only of basic Latin numbers, letters and/or the underscore character, the encoding matches the name (see however Identifier Case Sensitivity). Otherwise they are encoded according to the following table:

Code Range
Pattern
Number
Used
Unused
Blocks

00C0..017F

[@][0..4][g..z]

5*20= 100

97

3

Latin-1 Supplement + Latin Extended-A

0370..03FF

[@][5..9][g..z]

5*20= 100

88

12

Greek and Coptic

0400..052F

[@][g..z][0..6]

20*7= 140

137

3

Cyrillic + Cyrillic Supplement

0530..058F

[@][g..z][7..8]

20*2= 40

38

2

Armenian

2160..217F

[@][g..z][9]

20*1= 20

16

4

Number Forms

0180..02AF

[@][g..z][a..k]

20*11=220

203

17

Latin Extended-B + IPA Extensions

1E00..1EFF

[@][g..z][l..r]

20*7= 140

136

4

Latin Extended Additional

1F00..1FFF

[@][g..z][s..z]

20*8= 160

144

16

Greek Extended

.... ....

[@][a..f][g..z]

6*20= 120

0

120

RESERVED

24B6..24E9

[@][@][a..z]

26

26

0

Enclosed Alphanumerics

FF21..FF5A

[@][a..z][@]

26

26

0

Halfwidth and Fullwidth forms

Code Range values are UCS-2.

All of this encoding happens transparently at the filesystem level with one exception.

Examples

Find the file name for a table with a non-Latin1 name:

SELECT CAST(CONVERT("this_is_таблица" USING filename) AS BINARY);
+------------------------------------------------------------------+
| CAST(CONVERT("this_is_таблица" USING filename) AS BINARY)        |
+------------------------------------------------------------------+
| this_is_@y0@g0@h0@r0@o0@i1@g0                                    |
+------------------------------------------------------------------+

Find the table name for a file name:

SELECT CONVERT(_filename "this_is_@y0@g0@h0@r0@o0@i1@g0" USING utf8);
+---------------------------------------------------------------+
| CONVERT(_filename "this_is_@y0@g0@h0@r0@o0@i1@g0" USING utf8) |
+---------------------------------------------------------------+
| this_is_таблица                                               |
+---------------------------------------------------------------+

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

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).

In some cases, adding zeroes at the end of a decimal number can increment the precision of the expression where the number is used. For example, PI() by default returns a number with 6 decimal digits. But the PI()+0.0000000000 expression (with 10 zeroes) returns a number with 10 decimal digits.

Hexadecimal literals are interpreted as numbers when used in numeric contexts.

Examples

10
+10
-10

All these literals are equivalent:

0.1
.1
+0.1
+.1

With exponents:

0.2E3 -- 0.2 * POW(10, 3) = 200
.2e3
.2e+2
1.1e-10 -- 0.00000000011
-1.1e10 -- -11000000000

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

Reserved Words

The following is a list of all reserved words in MariaDB.

Reserved words cannot be used as Identifiers, unless they are quoted.

The definitive list of reserved words for each version can be found by examining the sql/lex.h and sql/sql_yacc.yy files.

Reserved Words

Keyword

ACCESSIBLE

ADD

ALL

ALTER

ANALYZE

AND

AS

ASC

ASENSITIVE

BEFORE

BETWEEN

BIGINT

BINARY

BLOB

BOTH

BY

CALL

CASCADE

CASE

CHANGE

CHAR

CHARACTER

CHECK

COLLATE

COLUMN

CONDITION

CONSTRAINT

CONTINUE

CONVERT

CREATE

CROSS

CURRENT_DATE

CURRENT_ROLE

CURRENT_TIME

CURRENT_TIMESTAMP

CURRENT_USER

CURSOR

DATABASE

DATABASES

DAY_HOUR

DAY_MICROSECOND

DAY_MINUTE

DAY_SECOND

DEC

DECIMAL

DECLARE

DEFAULT

DELAYED

DELETE

DELETE_DOMAIN_ID

DESC

DESCRIBE

DETERMINISTIC

DISTINCT

DISTINCTROW

DIV

DO_DOMAIN_IDS

DOUBLE

DROP

DUAL

EACH

ELSE

ELSEIF

ENCLOSED

ESCAPED

EXCEPT

EXISTS

EXIT

EXPLAIN

FALSE

FETCH

FLOAT

FLOAT4

FLOAT8

FOR

FORCE

FOREIGN

FROM

FULLTEXT

GENERAL

GRANT

GROUP

HAVING

HIGH_PRIORITY

HOUR_MICROSECOND

HOUR_MINUTE

HOUR_SECOND

IF

IGNORE

IGNORE_DOMAIN_IDS

IGNORE_SERVER_IDS

IN

INDEX

INFILE

INNER

INOUT

INSENSITIVE

INSERT

INT

INT1

INT2

INT3

INT4

INT8

INTEGER

INTERSECT

INTERVAL

INTO

IS

ITERATE

JOIN

KEY

KEYS

KILL

LEADING

LEAVE

LEFT

LIKE

LIMIT

LINEAR

LINES

LOAD

LOCALTIME

LOCALTIMESTAMP

LOCK

LONG

LONGBLOB

LONGTEXT

LOOP

LOW_PRIORITY

MASTER_HEARTBEAT_PERIOD

MASTER_SSL_VERIFY_SERVER_CERT

MATCH

MAXVALUE

MEDIUMBLOB

MEDIUMINT

MEDIUMTEXT

MIDDLEINT

MINUTE_MICROSECOND

MINUTE_SECOND

MOD

MODIFIES

NATURAL

NOT

NO_WRITE_TO_BINLOG

NULL

NUMERIC

OFFSET (> 10.6)

ON

OPTIMIZE

OPTION

OPTIONALLY

OR

ORDER

OUT

OUTER

OUTFILE

OVER

PAGE_CHECKSUM

PARSE_VCOL_EXPR

PARTITION

PRECISION

PRIMARY

PROCEDURE

PURGE

RANGE

READ

READS

READ_WRITE

REAL

RECURSIVE

REF_SYSTEM_ID

REFERENCES

REGEXP

RELEASE

RENAME

REPEAT

REPLACE

REQUIRE

RESIGNAL

RESTRICT

RETURN

RETURNING

REVOKE

RIGHT

RLIKE

ROW_NUMBER (> 10.7)

ROWS

SCHEMA

SCHEMAS

SECOND_MICROSECOND

SELECT

SENSITIVE

SEPARATOR

SET

SHOW

SIGNAL

SLOW

SMALLINT

SPATIAL

SPECIFIC

SQL

SQLEXCEPTION

SQLSTATE

SQLWARNING

SQL_BIG_RESULT

SQL_CALC_FOUND_ROWS

SQL_SMALL_RESULT

SSL

STARTING

STATS_AUTO_RECALC

STATS_PERSISTENT

STATS_SAMPLE_PAGES

STRAIGHT_JOIN

TABLE

TERMINATED

THEN

TINYBLOB

TINYINT

TINYTEXT

TO

TRAILING

TRIGGER

TRUE

UNDO

UNION

UNIQUE

UNLOCK

UNSIGNED

UPDATE

USAGE

USE

USING

UTC_DATE

UTC_TIME

UTC_TIMESTAMP

VALUES

VARBINARY

VARCHAR

VARCHARACTER

VARYING

VECTOR (> 11.6)

WHEN

WHERE

WHILE

WINDOW

WITH

WRITE

XOR

YEAR_MONTH

ZEROFILL

Exceptions

Some keywords are exceptions for historical reasons, and are permitted as unquoted identifiers. These include:

Keyword

ACTION

BIT

DATE

ENUM

NO

TEXT

TIME

TIMESTAMP

Oracle Mode

In Oracle mode, there are a number of extra reserved words:

Keyword

BODY

ELSIF

GOTO

HISTORY

MINUS (> 10.6.0)

OTHERS

PACKAGE

PERIOD

RAISE

ROWNUM

ROWTYPE

SYSDATE

SYSTEM

SYSTEM_TIME

VERSIONING

WITHOUT

Function Names

If the IGNORE_SPACE SQL_MODE flag is set, function names become reserved words.

See Also

  • Information Schema KEYWORDS Table

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

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 NULL.

TRUE and FALSE are reserved words, while UNKNOWN is not.

See Also

  • BOOLEAN type

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

String Literals

Strings are sequences of characters and enclosed with quotes.

The syntax is:

[_charset_name]'string' [COLLATE collation_name]

For example:

'The MariaDB Foundation'
_utf8 'Foundation' COLLATE utf8_unicode_ci;

Strings can either be enclosed in single quotes or in double quotes (the same character must be used to both open and close the string).

The ANSI SQL-standard does not permit double quotes for enclosing strings, and although MariaDB does by default, if the MariaDB server has enabled the ANSI_QUOTES_SQL SQL_MODE, double quotes will be treated as being used for identifiers instead of strings.

Strings that are next to each other are automatically concatenated. The following are equivalent:

'The ' 'MariaDB ' 'Foundation'
'The MariaDB Foundation'

The \ (backslash character) is used to escape characters (unless the SQL_MODE hasn't been set to NO_BACKSLASH_ESCAPES):

'MariaDB's new features'

That is not a valid string because of the single quote in the middle of the string, which is treated as if it closes the string, but is actually meant as part of the string, an apostrophe. The backslash character helps in situations like this:

'MariaDB\'s new features'

That is now a valid string, and if displayed, will appear without the backslash.

SELECT 'MariaDB\'s new features';
+------------------------+
| MariaDB's new features |
+------------------------+
| MariaDB's new features |
+------------------------+

Another way to escape the quoting character is repeating it twice:

SELECT 'I''m here', """Double""";
+----------+----------+
| I'm here | "Double" |
+----------+----------+
| I'm here | "Double" |
+----------+----------+

Escape Sequences

There are other escape sequences:

Escape sequence
Character

\0

ASCII NUL (0x00).

'

Single quote (“'”).

"

Double quote (“"”).

\b

Backspace.

Newline, or linefeed,.

Carriage return.

Tab.

\Z

ASCII 26 (Control+Z). See note following the table.

\

Backslash (“\”).

%

“%” character. See note following the table.

_

A “_” character. See note following the table.

Escaping the % and _ characters can be necessary when using the LIKE operator, which treats them as special characters.

The ASCII 26 character (\Z) needs to be escaped when included in a batch file which needs to be executed in Windows. The reason is that ASCII 26, in Windows, is the end of file (EOF).

Backslash (\), if not used as an escape character, must always be escaped. When followed by a character that is not in the above table, backslashes will simply be ignored.

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

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 UNION operations:

CREATE TABLE test.t1 (val1 INT, val2 INT);
INSERT INTO test.t1 VALUES(5, 8), (3, 4), (1, 2);

SELECT * FROM test.t1
UNION
VALUES (70, 90), (100, 110);

+------+------+
| val1 | val2 |
+------+------+
|    5 |    8 | 
|    3 |    4 |
|    1 |    2 |
|   70 |   90 |
|  100 |  110 |
+------+------+

Using TVCs with a CREATE VIEW statement:

CREATE VIEW v1 AS VALUES (7, 9), (9, 10);

SELECT * FROM v1;
+---+----+
| 7 |  9 |
+---+----+
| 7 |  9 |
| 9 | 10 |
+---+----+

Using TVC with an ORDER BY clause:

SELECT * FROM test.t1
UNION
VALUES (10, 20), (30, 40), (50, 60), (70, 80)
ORDER BY val1 DESC;

Using TVC with LIMIT clause:

SELECT * FROM test.t1
UNION
VALUES (10, 20), (30, 40), (50, 60), (70, 80)
LIMIT 2 OFFSET 4;

+------+------+
| val1 | val2 |
+------+------+
|   30 |   40 | 
|   50 |   60 |
+------+------+

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

User-Defined Variables

User-defined variables are variables which can be created by the user and exist in the session. This means that no one can access user-defined variables that have been set by another user, and when the session is closed these variables expire. However, these variables can be shared between several queries and stored programs.

User-defined variables names must be preceded by a single at character (@). While it is safe to use a reserved word as a user-variable name, the only allowed characters are ASCII letters, digits, dollar sign ($), underscore (_) and dot (.). If other characters are used, the name can be quoted in one of the following ways:

  • @var_name

  • @'var_name'

  • @"var_name"

These characters can be escaped as usual.

User-variables names are case insensitive.

User-defined variables cannot be declared. They can be read even if no value has been set yet; in that case, they are NULL. To set a value for a user-defined variable you can use:

  • SET statement;

  • := operator within a SQL statement;

  • SELECT ... INTO.

Since user-defined variables type cannot be declared, the only way to force their type is using CAST() or CONVERT():

SET @str = CAST(123 AS CHAR(5));

If a variable has not been used yet, its value is NULL:

SELECT @x IS NULL;
+------------+
| @x IS NULL |
+------------+
|          1 |
+------------+

It is unsafe to read a user-defined variable and set its value in the same statement (unless the command is SET), because the order of these actions is undefined.

User-defined variables can be used in most MariaDB's statements and clauses which accept an SQL expression. However there are some exceptions, like the LIMIT clause.

They must be used to PREPARE a prepared statement:

@sql = 'DELETE FROM my_table WHERE c>1;';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Another common use is to include a counter in a query:

SET @var = 0;
SELECT a, b, c, (@var:=@var+1) AS counter FROM my_table;

Viewing

User-defined variables can be viewed by either querying the USER_VARIABLES, or by running SHOW USER_VARIABLES.

Flushing User-Defined Variables

User-defined variables are reset and the Information Schema table emptied with the FLUSH USER_VARIABLES statement.

Examples

SET @v1 = 0;
SET @v2 = 'abc';
SET @v3 = CAST(123 AS CHAR(5));

SHOW USER_VARIABLES;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| v3            | 123   |
| v2            | abc   |
| v1            | 0     |
+---------------+-------+

SELECT * FROM information_schema.USER_VARIABLES ORDER BY VARIABLE_NAME;
+---------------+----------------+---------------+--------------------+
| VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_TYPE | CHARACTER_SET_NAME |
+---------------+----------------+---------------+--------------------+
| v1            | 0              | INT           | latin1             |
| v2            | abc            | VARCHAR       | utf8               |
| v3            | 123            | VARCHAR       | utf8               |
+---------------+----------------+---------------+--------------------+

FLUSH USER_VARIABLES;

SELECT * FROM information_schema.USER_VARIABLES ORDER BY VARIABLE_NAME;
Empty set (0.000 sec)

See Also

  • DECLARE VARIABLE

  • Performance Schema user_variables_by_thread Table

  • Information Schema USER_VARIABLES Table

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, and FULL JOIN.

In real-world scenarios, finding database schemas with multiple tables linked through relationships is common. In such cases, we often need to write SQL queries with multiple joins to fetch the required data.

For instance, consider a database for a bookstore with three tables: Books, Authors, and Publishers. If we want to get a list of books along with their author names and publishers, you would require multiple joins.

SELECT Books.title, Authors.name, Publishers.name
FROM Books
INNER JOIN Authors ON Books.author_id = Authors.id
INNER JOIN Publishers ON Books.publisher_id = Publishers.id;

In that query, we used two INNER JOIN operations to combine data from the three tables.

Subqueries

A subquery, also known as a nested query or inner query, is a query embedded within another SQL query. The result of the subquery can be used in the outer query. Subqueries can be used in SELECT, INSERT, UPDATE, and DELETE statements and also in conjunction with WHERE and HAVING clauses.

Subqueries can prove powerful in many scenarios. For example, suppose we want to find books that are above the average price in our bookstore database. We could achieve this using a subquery.

SELECT title, price
FROM Books
WHERE price > (SELECT AVG(price) FROM Books);

In this query, the subquery calculates the average book price, which the outer query then uses to filter books that are priced above average.

Set Operations

SQL also provides several set operations to combine rows from two or more tables, including UNION, UNION ALL, INTERSECT, and EXCEPT. These operations are instrumental when dealing with large databases where data is spread across multiple tables and we need to perform set-based operations.

For example, suppose we have two tables, Books_2019 and Books_2020, listing the books sold in the respective years. If we want to find books sold in both years, we can use the INTERSECT operation.

SELECT title FROM Books_2019
INTERSECT
SELECT title FROM Books_2020;

In the above query, INTERSECT returns the common book titles sold in both 2019 and 2020.

Conclusion

Advanced SQL queries that involve multiple joins, subqueries, and set operations can be daunting at first glance. However, these incredibly powerful tools in our SQL toolkit enable us to craft complex data retrieval and manipulation commands. To truly excel in data analytics, mastering these aspects of SQL is not only beneficial but essential.

Remember, practice is the key to mastering these advanced SQL concepts, like any other technical skill. Write queries, solve problems, make mistakes, and learn from them. Over time, you'll find yourself not only comfortable but proficient in creating intricate SQL queries.

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

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:

  • Point represents zero-dimensional objects.

  • Curve represents one-dimensional objects, and has a subclass LineString, with sub-subclasses Line and LinearRing.

  • Surface is designed for two-dimensional objects and has a subclass Polygon.

  • GeometryCollection has specialized zero-, one-, and two-dimensional collection classes named MultiPoint, MultiLineString, and MultiPolygon for modeling geometries corresponding to collections of Points, LineStrings, and Polygons, respectively. MultiCurve and MultiSurface are introduced as abstract superclasses that generalize the collection interfaces to handle Curves and Surfaces.

Geometry, Curve, Surface, MultiCurve, and MultiSurface are defined as non-instantiable classes. They define a common set of methods for their subclasses and are included for extensibility.

Point, LineString, Polygon, GeometryCollection, MultiPoint,MultiLineString, and MultiPolygon are instantiable classes.

This page is licensed: GPLv2, originally from fill_help_tables.sql

Geometry Types

Description

MariaDB provides a standard way of creating spatial columns for geometry types, for example, with CREATE TABLE or ALTER TABLE. Spatial columns are supported for MyISAM, InnoDB and ARCHIVE tables. See also SPATIAL INDEX.

The basic geometry type is GEOMETRY, but the type can be more specific. The following types are supported:

  • POINT

  • LINESTRING

  • POLYGON

  • MULTIPOINT

  • MULTILINESTRING

  • MULTIPOLYGON

  • GEOMETRYCOLLECTION

  • GEOMETRY

Examples

Note: For clarity, only one type is listed per table in the examples below, but a table row can contain multiple types.

CREATE TABLE object (shapeA POLYGON, shapeB LINESTRING);
INSERT INTO geometry_example VALUES
  (Point(0, 0)),
  (ST_PolygonFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))')),
  (ST_LineFromText('LINESTRING(0 0, 1 1, 2 2)')),
  (ST_MultiLineStringFromText(
    'MULTILINESTRING((0 1, 0 2, 0 3),
    (1 0, 2 0, 3 0))')),
  (ST_MultiPointFromText('MULTIPOINT(0 0, 1 1, 0 1, 1 0)')),
  (ST_MultiPolygonFromText(
    'MULTIPOLYGON(((0 40, 0 20, 6 30, 12 20, 12 40, 0 40),
    (15 40, 15 20, 25 20, 30 25, 30 35, 25 40, 15 40)))'));
SELECT ST_AsWKT(g) FROM geometry_example;

+-----------------------------------------------------------------------------------------------+
| ST_AsWKT(g)                                                                                   |
+-----------------------------------------------------------------------------------------------+
| POINT(0 0)                                                                                    |
| POLYGON((0 0,0 1,1 1,1 0,0 0))                                                                |
| LINESTRING(0 0,1 1,2 2)                                                                       |
| MULTILINESTRING((0 1,0 2,0 3),(1 0,2 0,3 0))                                                  |
| MULTIPOINT(0 0,1 1,0 1,1 0)                                                                   |
| MULTIPOLYGON(((0 40,0 20,6 30,12 20,12 40,0 40),(15 40,15 20,25 20,30 25,30 35,25 40,15 40))) |
+-----------------------------------------------------------------------------------------------+

POINT

CREATE TABLE gis_point  (g POINT);
SHOW FIELDS FROM gis_point;
INSERT INTO gis_point VALUES
    (PointFromText('POINT(10 10)')),
    (PointFromText('POINT(20 10)')),
    (PointFromText('POINT(20 20)')),
    (PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))));
<</sql>>

=== [[linestring|LINESTRING]]
<<code lang=sql inline=false wrap=true>>
CREATE TABLE gis_line  (g LINESTRING);
SHOW FIELDS FROM gis_line;
INSERT INTO gis_line VALUES
    (LineFromText('LINESTRING(0 0,0 10,10 0)')),
    (LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
    (LineStringFromWKB(AsWKB(LineString(Point(10, 10), Point(40, 10)))));

POLYGON

CREATE TABLE gis_polygon   (g POLYGON);
SHOW FIELDS FROM gis_polygon;
INSERT INTO gis_polygon VALUES
    (PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),
    (PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')),
    (PolyFromWKB(AsWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0))))));

MULTIPOINT

CREATE TABLE gis_multi_point (g MULTIPOINT);
SHOW FIELDS FROM gis_multi_point;
INSERT INTO gis_multi_point VALUES
    (MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
    (MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
    (MPointFromWKB(AsWKB(MultiPoint(Point(3, 6), Point(4, 10)))));

MULTILINESTRING

CREATE TABLE gis_multi_line (g MULTILINESTRING);
SHOW FIELDS FROM gis_multi_line;
INSERT INTO gis_multi_line VALUES
    (MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),
    (MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),
    (MLineFromWKB(AsWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7))))));

MULTIPOLYGON

CREATE TABLE gis_multi_polygon  (g MULTIPOLYGON);
SHOW FIELDS FROM gis_multi_polygon;
INSERT INTO gis_multi_polygon VALUES
    (MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')),
    (MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')),
    (MPolyFromWKB(AsWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3)))))));

GEOMETRYCOLLECTION

CREATE TABLE gis_geometrycollection  (g GEOMETRYCOLLECTION);
SHOW FIELDS FROM gis_geometrycollection;
INSERT INTO gis_geometrycollection VALUES
    (GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
    (GeometryFromWKB(AsWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))))),
    (GeomFromText('GeometryCollection()')),
    (GeomFromText('GeometryCollection EMPTY'));

GEOMETRY

CREATE TABLE gis_geometry (g GEOMETRY);
SHOW FIELDS FROM gis_geometry;
INSERT INTO gis_geometry SELECT * FROM gis_point;
INSERT INTO gis_geometry SELECT * FROM gis_line;
INSERT INTO gis_geometry SELECT * FROM gis_polygon;
INSERT INTO gis_geometry SELECT * FROM gis_multi_point;
INSERT INTO gis_geometry SELECT * FROM gis_multi_line;
INSERT INTO gis_geometry SELECT * FROM gis_multi_polygon;
INSERT INTO gis_geometry SELECT * FROM gis_geometrycollection;

This page is licensed: GPLv2, originally from fill_help_tables.sql

GIS Features

GIS stands for Geographic Information System.

MySQL operates on spatial data based on the OpenGIS standards, particularly the OpenGIS SFS (Simple feature access, SQL option).

Initial support was based on version 05-134 of the standard. MariaDB implements a subset of the 'SQL with Geometry Types' environment proposed by the OGC. And the SQL environment was extended with a set of geometry types.

MariaDB supports spatial extensions to operate on spatial features. These features are available for Aria, MyISAM, InnoDB, NDB, and ARCHIVE tables.

For spatial columns, Aria and MyISAM supports both SPATIAL and non-SPATIAL indexes. Other storage engines support non-SPATIAL indexes.

We aim at meeting the OpenGIS requirements. One thing missed in previous MariaDB versions is that the functions which check spatial relations didn't consider the actual shape of an object, instead they operate only on their bounding rectangles. These legacy functions have been left as they are and new, properly-working functions are named with an 'ST_' prefix, in accordance with the latest OpenGIS requirements. Also, operations over geometry features were added.

Spatial Operators

Spatial operators produce new geometries.

Name
Description

ST_UNION(A, B)

union of A and B

ST_INTERSECTION(A, B)

intersection of A and B

ST_SYMDIFFERENCE(A, B)

symdifference, notintersecting parts of A and B

ST_BUFFER(A, radius)

returns the shape of the area that lies in 'radius' distance from the shape A.

Predicates

Predicates return a boolean result of the relationship.

Name
Description

ST_INTERSECTS(A, B)

if A and B have an intersection

ST_CROSSES(A, B)

if A and B cross

ST_EQUALS(A, B)

if A and B are equal

ST_WITHIN(A, B)

if A lies within B

ST_CONTAINS(A,B)

if B lies within A

ST_DISJOINT(A,B)

if A and B have no intersection

ST_TOUCHES(A,B)

if A touches B

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

GIS Resources

GIS stands for Geographic Information System.

Further reading:

  • OGC Simple Feature Access - the Open Geospatial Consortium's OpenGIS Simple Features Specifications For SQL.

  • Geo/Spatial Search with MySQL - a presentation by Alexander Rubin, from the MySQL Conference in 2006.

There are no differences between GIS in stable versions of MariaDB and GIS in MySQL. There are, however, some extensions and enhancements being worked on.

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

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);
CREATE SPATIAL INDEX sp_index ON geom (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 CREATE INDEX.

To drop spatial indexes, use ALTER TABLE or DROP INDEX:

ALTER TABLE geom DROP INDEX g;
DROP INDEX sp_index ON geom;

Data-at-Rest Encryption

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

See MDEV-12026 for more information.

This page is licensed: GPLv2, originally from fill_help_tables.sql

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 ColumnsDynamic Column APIHANDLERHandlerSocketJSON FunctionsCassandra Storage EngineCONNECT

Dynamic Columns

Overview

Dynamic columns allow one to store different sets of columns for each row in a table. It works by storing a set of columns in a blob and having a small set of functions to manipulate it.

Dynamic columns should be used when it is not possible to use regular columns.

A typical use case is when one needs to store items that may have many different attributes (like size, color, weight, etc), and the set of possible attributes is very large and/or unknown in advance. In that case, attributes can be put into dynamic columns.

Dynamic Columns Basics

The table should have a blob column which will be used as storage for dynamic columns:

main
CREATE TABLE assets (
  item_name VARCHAR(32) PRIMARY KEY, -- A common attribute for all items
  dynamic_cols  BLOB  -- Dynamic columns will be stored here
);

Once created, you can access dynamic columns via dynamic column functions.

Insert a row with two dynamic columns: color=blue, size=XL:

INSERT INTO assets VALUES 
  ('MariaDB T-shirt', COLUMN_CREATE('color', 'blue', 'size', 'XL'));

Insert another row with dynamic columns: color=black, price=500:

INSERT INTO assets VALUES
  ('Thinkpad Laptop', COLUMN_CREATE('color', 'black', 'price', 500));

Select the dynamic column 'color' for all items:

SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) 
  AS color FROM assets;
+-----------------+-------+
| item_name       | color |
+-----------------+-------+
| MariaDB T-shirt | blue  |
| Thinkpad Laptop | black |
+-----------------+-------+

It is possible to add and remove dynamic columns from a row:

-- Remove a column:
UPDATE assets SET dynamic_cols=COLUMN_DELETE(dynamic_cols, "price") 
WHERE COLUMN_GET(dynamic_cols, 'color' AS CHAR)='black'; 

-- Add a column:
UPDATE assets SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '3 years')
WHERE item_name='Thinkpad Laptop';

You can also list all columns, or get them together with their values in JSON format:

SELECT item_name, column_list(dynamic_cols) FROM assets;
+-----------------+---------------------------+
| item_name       | column_list(dynamic_cols) |
+-----------------+---------------------------+
| MariaDB T-shirt | `size`,`color`            |
| Thinkpad Laptop | `color`,`warranty`        |
+-----------------+---------------------------+

SELECT item_name, COLUMN_JSON(dynamic_cols) FROM assets;
+-----------------+----------------------------------------+
| item_name       | COLUMN_JSON(dynamic_cols)              |
+-----------------+----------------------------------------+
| MariaDB T-shirt | {"size":"XL","color":"blue"}           |
| Thinkpad Laptop | {"color":"black","warranty":"3 years"} |
+-----------------+----------------------------------------+

Dynamic Columns Reference

This is a complete reference of dynamic columns in MariaDB.

Column can be referred to by name (column_name in the following code blocks). This is the preferred method. If you need support for old (< 10.0) MariaDB versions, you have to refer to columns by number. In that case, replace column_name with column_nr. This method is not recommended.

Dynamic Columns Functions

COLUMN_CREATE

COLUMN_CREATE(column_name, value [as type], [column_name, value 
  [as type]]...);

Returns a dynamic columns blob that stores the specified columns with values. The return value is suitable for

  • storing in a table,

  • further modification with other dynamic columns functions.

The as type part allows one to specify the value type. In most cases, this is redundant because MariaDB will be able to deduce the type of the value. Explicit type specification may be needed when the type of the value is not apparent. For example, a literal '2012-12-01' has a CHAR type by default, one will need to specify '2012-12-01' AS DATE to have it stored as a date. See the Datatypes section for further details. Note also MDEV-597.

Typical usage:

INSERT INTO tbl SET dyncol_blob=COLUMN_CREATE("column_name", "value");

COLUMN_ADD

COLUMN_ADD(dyncol_blob, column_name, value [as type], 
  [column_name, value [as type]]...);

Adds or updates dynamic columns.

  • dyncol_blob must be either a valid dynamic columns blob (for example, COLUMN_CREATE returns such blob), or an empty string.

  • column_name specifies the name of the column to be added. If dyncol_blob already has a column with this name, it will be overwritten.

  • value specifies the new value for the column. Passing a NULL value will cause the column to be deleted.

  • as type is optional. See the datatypes section for a discussion about types.

The return value is a dynamic column blob after the modifications.

Typical usage:

UPDATE t1 SET dyncol_blob=COLUMN_ADD(dyncol_blob, "column_name", "value") 
  WHERE id=1;

Note: COLUMN_ADD() is a regular function (just likeCONCAT()), hence, in order to update the value in the table you have to use the UPDATE ... SET dynamic_col=COLUMN_ADD(dynamic_col, ....) pattern.

COLUMN_GET

COLUMN_GET(dyncol_blob, column_name as type);

Retrieves the value of a dynamic column by its name. If no column with the given name exists, NULL is returned.

column_name as type requires that one specify the datatype of the dynamic column they are reading.

This may seem counter-intuitive: Why would you need to specify which datatype they're retrieving? Can't the dynamic columns system figure the datatype from the data being stored?

The answer is: SQL is a statically-typed language. The SQL interpreter needs to know the datatypes of all expressions before the query is run (for example, when one is using prepared statements and runs"select COLUMN_GET(...)", the prepared statement API requires the server to inform the client about the datatype of the column being read before the query is executed and the server can see what datatype the column actually has).

See the Datatypes section for more information about datatypes.

COLUMN_DELETE

COLUMN_DELETE(dyncol_blob, column_name, column_name...);

Deletes a dynamic column with the specified name. Multiple names can be given.

The return value is a dynamic column blob after the modification.

COLUMN_EXISTS

COLUMN_EXISTS(dyncol_blob, column_name);

Checks if a column with name column_name exists in dyncol_blob. If yes, return 1, otherwise return 0.

COLUMN_LIST

COLUMN_LIST(dyncol_blob);

Returns a comma-separated list of column names. The names are quoted with backticks.

SELECT column_list(column_create('col1','val1','col2','val2'));
+---------------------------------------------------------+
| column_list(column_create('col1','val1','col2','val2')) |
+---------------------------------------------------------+
| `col1`,`col2`                                           |
+---------------------------------------------------------+

COLUMN_CHECK

COLUMN_CHECK(dyncol_blob);

Checks if dyncol_blob is a valid packed dynamic columns blob. A return value of 1 means the blob is valid, a return value of 0 means it is not.

Rationale: Normally, you work with valid dynamic column blobs. Functions likeCOLUMN_CREATE, COLUMN_ADD, COLUMN_DELETE always return valid dynamic column blobs. However, if a dynamic column blob is accidentally truncated, or transcoded from one character set to another, it is corrupted. This function can be used to check if a value in a blob field is a valid dynamic column blob.

It is possible that a truncation cuts a Dynamic Column "clearly" so that COLUMN_CHECK does not notice the corruption, but, in any case of truncation, a warning is issued during value storing.

COLUMN_JSON

COLUMN_JSON(dyncol_blob);

Returns a JSON representation of data in dyncol_blob :

SELECT item_name, COLUMN_JSON(dynamic_cols) FROM assets;
+-----------------+----------------------------------------+
| item_name       | COLUMN_JSON(dynamic_cols)              |
+-----------------+----------------------------------------+
| MariaDB T-shirt | {"size":"XL","color":"blue"}           |
| Thinkpad Laptop | {"color":"black","warranty":"3 years"} |
+-----------------+----------------------------------------+

Limitation: COLUMN_JSON decodes nested dynamic columns at a nesting level of not more than 10 levels deep. Dynamic columns that are nested deeper than 10 levels are shown as a BINARY string, without encoding.

Nesting Dynamic Columns

It is possible to use nested dynamic columns by putting one dynamic column blob inside another. The COLUMN_JSON function will display nested columns.

SET @tmp= column_create('parent_column', 
  column_create('child_column', 12345));
Query OK, 0 rows affected (0.00 sec)

SELECT column_json(@tmp);
+------------------------------------------+
| column_json(@tmp)                        |
+------------------------------------------+
| {"parent_column":{"child_column":12345}} |
+------------------------------------------+

SELECT column_get(column_get(@tmp, 'parent_column' AS char), 
  'child_column' AS int);
+------------------------------------------------------------------------------+
| column_get(column_get(@tmp, 'parent_column' as char), 'child_column' as int) |
+------------------------------------------------------------------------------+
|                                                                        12345 |
+------------------------------------------------------------------------------+

If you are trying to get a nested dynamic column as a string, use AS BINARY as the last argument of COLUMN_GET . Otherwise, problems with character set conversion and illegal symbols are possible:

SELECT column_json( column_get(
  column_create('test1', 
    column_create('key1','value1','key2','value2','key3','value3')),
  'test1' AS BINARY));

Datatypes

In SQL, one needs to define the type of each column in a table. Dynamic columns do not provide any way to declare a type in advance ("whenever there is a column 'weight', it should be integer" is not possible). However, each particular dynamic column value is stored together with its datatype.

The set of possible datatypes is mostly the same as that used by the CAST and CONVERT functions. However, note that there are currently some differences - see MDEV-597.

Type
Dynamic column internal type
Description

BINARY[(N)]

DYN_COL_STRING

(variable length string with binary charset)

CHAR[(N)]

DYN_COL_STRING

(variable length string with charset)

DATE

DYN_COL_DATE

(date - 3 bytes)

DATETIME[(D)]

DYN_COL_DATETIME

(date and time (with microseconds) - 9 bytes)

DECIMAL[(M[,D])]

DYN_COL_DECIMAL

(variable length binary decimal representation with MariaDB limitation)

DOUBLE[(M,D)]

DYN_COL_DOUBLE

(64 bit double-precision floating point)

INTEGER

DYN_COL_INT

(variable length, up to 64 bit signed integer)

SIGNED [INTEGER]

DYN_COL_INT

(variable length, up to 64 bit signed integer)

TIME[(D)]

DYN_COL_TIME

(time (with microseconds, may be negative) - 6 bytes)

UNSIGNED [INTEGER]

DYN_COL_UINT

(variable length, up to 64bit unsigned integer)

A Note About Lengths

If you're running queries without specifying a maximum length (i.e. using #as CHAR#, not as CHAR(n)), MariaDB reports the maximum length of the result set column to be53,6870,911 (bytes or characters?). This may cause excessive memory usage in some client libraries, because they try to pre-allocate a buffer of maximum result set width. If you suspect you're hitting this problem, use CHAR(n) whenever you're using COLUMN_GET in the select list.

SELECT COLUMN_GET(blob, 'colname' as CHAR) ...

Client-side API

It is also possible to create or parse dynamic columns blobs on the client side. libmysql client library now includes an API for writing/reading dynamic column blobs. See dynamic-columns-api for details.

Limitations

Description
Limit

Max number of columns

65535

Maximum total length of packed dynamic column

max_allowed_packet (1G)

See Also

  • Dynamic Columns

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

Dynamic Column API

This page describes the client-side API for reading and writing Dynamic Columns blobs.

Normally, you should use Dynamic column functions which are run inside the MariaDB server and allow one to access Dynamic Columns content without any client-side libraries.

If you need to read/write dynamic column blobs on the client for some reason, this API enables that.

Where to get it

The API is a part of libmysql C client library. In order to use it, you need to include this header file and link against libmysql:

#include <mysql/ma_dyncol.h>

Data structures

DYNAMIC_COLUMN

DYNAMIC_COLUMN represents a packed dynamic column blob. It is essentially a string-with-length and is defined as follows:

/* A generic-purpose arbitrary-length string defined in MySQL Client API */
typedef struct st_dynamic_string
{
  char *str;
  size_t length,max_length,alloc_increment;
} DYNAMIC_STRING;

...

typedef DYNAMIC_STRING DYNAMIC_COLUMN;

DYNAMIC_COLUMN_VALUE

Dynamic columns blobs store {name, value} pairs. The DYNAMIC_COLUMN_VALUE structure is used to represent the value in accessible form.

struct st_dynamic_column_value
{
  DYNAMIC_COLUMN_TYPE type;
  union
  {
    long long long_value;
    unsigned long long ulong_value;
    double double_value;
    struct {
      MYSQL_LEX_STRING value;
      CHARSET_INFO *charset;
    } string;
    struct {
      decimal_digit_t buffer[DECIMAL_BUFF_LENGTH];
      decimal_t value;
    } decimal;
    MYSQL_TIME time_value;
  } x;
};
typedef struct st_dynamic_column_value DYNAMIC_COLUMN_VALUE;

Every value has a type, which is determined by the type member.

Type
Structure field

DYN_COL_NULL

-

DYN_COL_INT

value.x.long_value

DYN_COL_UINT

value.x.ulong_value

DYN_COL_DOUBLE

value.x.double_value

DYN_COL_STRING

value.x.string.value, value.x.string.charset

DYN_COL_DECIMAL

value.x.decimal.value

DYN_COL_DATETIME

value.x.time_value

DYN_COL_DATE

value.x.time_value

DYN_COL_TIME

value.x.time_value

DYN_COL_DYNCOL

value.x.string.value

Notes

  • Values with type DYN_COL_NULL do not ever occur in dynamic columns blobs.

  • Type DYN_COL_DYNCOL means that the value is a packed dynamic blob. This is how nested dynamic columns are done.

  • Before storing a value to value.x.decimal.value, you must call mariadb_dyncol_prepare_decimal() to initialize the space for storage.

enum_dyncol_func_result

enum enum_dyncol_func_result is used as return value.

Value
Name
Comments

0

ER_DYNCOL_OK

OK

0

ER_DYNCOL_NO

(the same as ER_DYNCOL_OK but for functions which return a YES/NO)

1

ER_DYNCOL_YES

YES response or success

2

ER_DYNCOL_TRUNCATED

Operation succeeded but the data was truncated

-1

ER_DYNCOL_FORMAT

Wrong format of the encoded string

-2

ER_DYNCOL_LIMIT

A limit of implementation reached

-3

ER_DYNCOL_RESOURCE

Out of resources

-4

ER_DYNCOL_DATA

Incorrect input data

-5

ER_DYNCOL_UNKNOWN_CHARSET

Unknown character set

Result codes that are less than zero represent error conditions.

Function reference

Functions come in pairs:

  • xxx_num() operates on the old (pre-MariaDB-10.0.1) dynamic column blob format, where columns were identified by numbers.

  • xxx_named() can operate on both old or new data format. If it modifies the blob, it converts it to the new data format.

You should use xxx_named() functions, unless you need to keep the data compatible with MariaDB versions before 10.0.1.

mariadb_dyncol_init

First, define mariadb_dyncol_init(A) memset((A), 0, sizeof(*(A))). It is the correct initialization for an empty packed dynamic blob.

mariadb_dyncol_free

Copy where str is IN. Packed dynamic blob which memory should be freed.

void mariadb_dyncol_free(DYNAMIC_COLUMN *str);

mariadb_dyncol_create_many

Create a packed dynamic blob from arrays of values and names.

enum enum_dyncol_func_result
mariadb_dyncol_create_many(DYNAMIC_COLUMN *str,
                           uint column_count,
                           uint *column_numbers,
                           DYNAMIC_COLUMN_VALUE *values,
                           my_bool new_string);
enum enum_dyncol_func_result
mariadb_dyncol_create_many_named(DYNAMIC_COLUMN *str,
                                 uint column_count,
                                 MYSQL_LEX_STRING *column_keys,
                                 DYNAMIC_COLUMN_VALUE *values,
                                 my_bool new_string);

Here are the names and values:

Name
Value
Comments

str

OUT

Packed dynamic blob will be put here

column_count

IN

Number of columns

column_numbers

IN

Column numbers array (old format)

column_keys

IN

Column names array (new format)

values

IN

Column values array

new_string

IN

If TRUE then the str will be reinitialized (not freed) before usage

mariadb_dyncol_update_many

Add or update columns in a dynamic columns blob. To delete a column, update its value to a "non-value" of type DYN_COL_NULL :

enum enum_dyncol_func_result
mariadb_dyncol_update_many(DYNAMIC_COLUMN *str,
                           uint column_count,
                           uint *column_numbers,
                           DYNAMIC_COLUMN_VALUE *values);
enum enum_dyncol_func_result
mariadb_dyncol_update_many_named(DYNAMIC_COLUMN *str,
                                 uint column_count,
                                 MYSQL_LEX_STRING *column_keys,
                                 DYNAMIC_COLUMN_VALUE *values);
Name
Value
Comments

str

IN/OUT

Dynamic columns blob to be modified

column_count

IN

Number of columns in following arrays

column_numbers

IN

Column numbers array (old format)

column_keys

IN

Column names array (new format)

values

IN

Column values array

mariadb_dyncol_exists

Check if column with given names exist in the blob:

enum enum_dyncol_func_result
mariadb_dyncol_exists(DYNAMIC_COLUMN *str, uint column_number);
enum enum_dyncol_func_result
mariadb_dyncol_exists_named(DYNAMIC_COLUMN *str, MYSQL_LEX_STRING *column_key);
Name
Value
Comments

str

IN

Packed dynamic columns string

column_number

IN

Column number (old format)

column_key

IN

Column name (new format)

The function returns YES, or NO or Error code.

mariadb_dyncol_column_count

Get the number of columns in a dynamic column blob:

enum enum_dyncol_func_result
mariadb_dyncol_column_count(DYNAMIC_COLUMN *str, uint *column_count);
Name
Value
Comments

str

IN

Packed dynamic columns string

column_count

OUT

Number of not NULL columns in the dynamic columns string

mariadb_dyncol_list

List columns in a dynamic column blob:

enum enum_dyncol_func_result
mariadb_dyncol_list(DYNAMIC_COLUMN *str, uint *column_count, uint **column_numbers);
enum enum_dyncol_func_result
mariadb_dyncol_list_named(DYNAMIC_COLUMN *str, uint *column_count,
                          MYSQL_LEX_STRING **column_keys);

str

IN

Packed dynamic columns string

column_count

OUT

Number of columns in following arrays

column_numbers

OUT

Column numbers array (old format). Caller should free this array

column_keys

OUT

Column names array (new format). Caller should free this array

mariadb_dyncol_get

Get a value of one column:

enum enum_dyncol_func_result
mariadb_dyncol_get(DYNAMIC_COLUMN *org, uint column_number,
                   DYNAMIC_COLUMN_VALUE *value);
enum enum_dyncol_func_result
mariadb_dyncol_get_named(DYNAMIC_COLUMN *str, MYSQL_LEX_STRING *column_key,
                         DYNAMIC_COLUMN_VALUE *value);
Name
Value
Comments

str

IN

Packed dynamic columns string

column_number

IN

Column numbers array (old format)

column_key

IN

Column names array (new format)

value

OUT

Value of the column

If the column is not found, NULL is returned as the value of the column.

mariadb_dyncol_unpack

Get the value of all columns:

enum enum_dyncol_func_result
mariadb_dyncol_unpack(DYNAMIC_COLUMN *str,
                      uint *column_count,
                      MYSQL_LEX_STRING **column_keys,
                      DYNAMIC_COLUMN_VALUE **values);
Name
Value
Comments

str

IN

Packed dynamic columns string to unpack

column_count

OUT

Number of columns in following arrays

column_keys

OUT

Column names array (should be free by caller)

values

OUT

Values of the columns array (should be free by caller)

mariadb_dyncol_has_names

Check whether the dynamic columns blob uses the new data format (the one where columns are identified by names):

my_bool mariadb_dyncol_has_names(DYNAMIC_COLUMN *str);
Name
Value
Comments

str

IN

Packed dynamic columns string

mariadb_dyncol_check

Check whether the dynamic column blob has the correct data format:

enum enum_dyncol_func_result
mariadb_dyncol_check(DYNAMIC_COLUMN *str);
Name
Value
Comments

str

IN

Packed dynamic columns string

mariadb_dyncol_json

Get contents of a dynamic columns blob in a JSON form:

enum enum_dyncol_func_result
mariadb_dyncol_json(DYNAMIC_COLUMN *str, DYNAMIC_STRING *json);
Name
Value
Comments

str

IN

Packed dynamic columns string

json

OUT

JSON representation

mariadb_dyncol_val_TYPE

Get the dynamic column value as one of the base types:

enum enum_dyncol_func_result
mariadb_dyncol_val_str(DYNAMIC_STRING *str, DYNAMIC_COLUMN_VALUE *val,
                       CHARSET_INFO *cs, my_bool quote);
enum enum_dyncol_func_result
mariadb_dyncol_val_long(longlong *ll, DYNAMIC_COLUMN_VALUE *val);
enum enum_dyncol_func_result
mariadb_dyncol_val_double(double *dbl, DYNAMIC_COLUMN_VALUE *val);
Name
Value
Comments

str or ll or dbl

OUT

value of the column

val

IN

Value

mariadb_dyncol_prepare_decimal

Initialize DYNAMIC_COLUMN_VALUE before setting the value of value.x.decimal.value:

void mariadb_dyncol_prepare_decimal(DYNAMIC_COLUMN_VALUE *value);
Name
Value
Comments

value

OUT

Value of the column

This function links value.x.decimal.value to value.x.decimal.buffer.

mariadb_dyncol_value_init

Initialize a DYNAMIC_COLUMN_VALUE structure to a safe default:

#define mariadb_dyncol_value_init(V) (V)->type= DYN_COL_NULL

mariadb_dyncol_column_cmp_named

Compare two column names (column names are compared with memcmp()):

int mariadb_dyncol_column_cmp_named(const MYSQL_LEX_STRING *s1,
                                    const MYSQL_LEX_STRING *s2);

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

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

Syntax

HANDLER tbl_name OPEN [ [AS] alias]
HANDLER tbl_name READ index_name { = | >= | <= | < } (value1,value2,...)
    [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST }
    [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ { FIRST | NEXT }
    [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name CLOSE

Description

The HANDLER statement provides direct access to table storage engine interfaces for key lookups and key or table scans. It is available for at least Aria, Memory, MyISAM and InnoDB tables (and should work with most 'normal' storage engines, but not with system tables, MERGE or views).

HANDLER ... OPEN opens a table, allowing it to be accessible to subsequent HANDLER ... READ statements. The table can either be opened using an alias, or a table name. If opened with an alias, references to this table by further HANDLER statements must use this alias, and not the table name. If opened with a table name qualified by database name, further references to this table must use the unqualified table name. For example, if a table is opened with db1.t1, further references must use t1.

The table object is only closed when HANDLER ... CLOSE is called by the session, or the session closes, and is not shared by other sessions.

Prepared statements work with HANDLER READ, which gives a much higher performance (50% speedup) as there is no parsing and all data is transformed in binary (without conversions to text, as with the normal protocol).

The HANDLER command does not work with partitioned tables.

Key Lookup

A key lookup is started with:

HANDLER tbl_name READ index_name { = | >= | <= | < }  (value,value) [LIMIT...]

The values stands for the value of each of the key columns. For most key types, except for HASH keys in MEMORY storage engine, you can use a prefix subset of its columns.

If you are using LIMIT, then in case of >= or > then there is an implicit NEXT implied, while if you are using <= or < then there is an implicit PREV implied.

After the initial read, you can use the following to scan rows in key order:

HANDLER tbl_name READ index_name NEXT [ LIMIT ... ]
or
HANDLER tbl_name READ index_name PREV [ LIMIT ... ]

Note that the row order is not defined for keys with duplicated values, and will vary from engine to engine.

Key Scans

You can scan a table in key order by doing this:

HANDLER tbl_name READ index_name FIRST [ LIMIT ... ]
HANDLER tbl_name READ index_name NEXT  [ LIMIT ... ]

Alternatively, if the handler supports backwards key scans (which most do), you can use this:

HANDLER tbl_name READ index_name LAST [ LIMIT ... ]
HANDLER tbl_name READ index_name PREV [ LIMIT ... ]

Table Scans

You can scan a table in row order by doing this:

HANDLER tbl_name READ FIRST [ LIMIT ... ]
HANDLER tbl_name READ NEXT  [ LIMIT ... ]

Limitations

As this is a direct interface to the storage engine, some limitations may apply for what you can do and what happens if the table changes. Here are some of the common limitations.

Finding 'Old Rows'

HANDLER READ is not transaction-safe, consistent or atomic. It's okay for the storage engine to return rows that existed when you started the scan, but that were later deleted. This can happen as the storage engine may cache rows as part of the scan from a previous read.

You may also find rows committed since the scan originally started.

Invisible Columns

HANDLER ... READ also reads the data of invisible columns.

System-Versioned Tables

HANDLER ... READ reads everything from system-versioned tables, and so includes row_start and row_end fields, as well as all rows that have since been deleted or changed, including when history partitions are used.

Other Limitations

  • If you do an ALTER TABLE, all your HANDLERs for that table are automatically closed.

  • If you do an ALTER TABLE for a table that is used by some other connection with HANDLER, the ALTER TABLE query waits for the HANDLER to be closed.

  • For HASH keys, you must use all key parts when searching for a row.

  • For HASH keys, you can't do a key scan of all values. You can only find all rows with the same key value.

  • While each HANDLER READ command is atomic, if you do a scan in many steps, some engines may give you error 1020 if the table changed between the commands. Please refer to the specific engine handler page if this happens.

Error Codes

  • Error 1031 (ER_ILLEGAL_HA) Table storage engine for 't1' doesn't have this option

    • If you get this for HANDLER OPEN it means the storage engine doesn't support HANDLER calls.

    • If you get this for HANDLER READ , it means you are trying to use an incomplete HASH key.

  • Error 1020 (ER_CHECKREAD) Record has changed since last read in table '...'

    • This means that the table changed between two reads, and the handler can't handle this case for the given scan.

Examples

CREATE TABLE t1 (f1 INT);

INSERT INTO t1 VALUES (1),(2),(3);

HANDLER t1 OPEN;

HANDLER t1 READ NEXT;
+------+
| f1   |
+------+
|    1 |
+------+

HANDLER t1 READ NEXT;
+------+
| f1   |
+------+
|    2 |
+------+

In the previous example, the HANDLER was opened with the t1 table name. Since HANDLERs use unqualified table names, trying to access another table with this same name, even though it's in another database, will result in ambiguity. An alias needs to be used to avoid the ambiguity, resulting in Error 1066: Not unique table/alias:

CREATE DATABASE db_new;

CREATE TABLE db_new.t1 (id INT);

INSERT INTO db_new.t1 VALUES (4),(5),(6);

HANDLER db_new.t1 OPEN;
ERROR 1066 (42000): Not unique table/alias: 't1'

HANDLER db_new.t1 OPEN AS db_new_t1;

HANDLER db_new_t1 READ NEXT LIMIT 3;
+------+
| id   |
+------+
|    4 |
|    5 |
|    6 |
+------+

This page is licensed: GPLv2, originally from fill_help_tables.sql

HANDLER for MEMORY Tables

This article explains how to use HANDLER commands efficiently with MEMORY/HEAP 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:

CREATE TABLE t1 (a INT, b INT, KEY(a), KEY b USING BTREE (b)) ENGINE=memory;

In the above table, a is a HASH key that only supports exact matches (=) while b is a BTREE 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:

Limitations for HASH keys

  • You must use all key parts when searching for a row.

  • You can't do a key scan of all values. You can only find all rows with the same key value.

  • READ NEXT gives an error 1031 if the tables changed since last read.

Limitations for BTREE keys

  • READ NEXT gives an error 1031 if the tables changed since last read. This limitation can be lifted in the future.

Limitations for table scans

  • READ NEXT gives an error 1031 if the table was truncated since last READ call.

See also

See also the limitations listed in HANDLER commands.

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

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 Installation

After MariaDB is installed, use the INSTALL PLUGIN command (as the root user) to install the HandlerSocket plugin. This command only needs to be run once, like so:

INSTALL PLUGIN handlersocket SONAME 'handlersocket.so';

After installing the plugin, SHOW PROCESSLIST shows you first need to configure some settings. All HandlerSocket configuration options are placed in the [mysqld] section of your my.cnf file.

At least the handlersocket_address, handlersocket_port and handlersocket_port_wr options need to be set:

handlersocket_address="127.0.0.1"
handlersocket_port="9998"
handlersocket_port_wr="9999"

After updating the configuration options, restart MariaDB.

On the client side, to make use of the plugin you will need to install the appropriate client library (that is, libhsclient for C++ applications and perl-Net-HandlerSocket for perl applications).

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

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

    • Net_HandlerSocket

    • HSPHP

    • php-ext-handlersocketi

  • Java

    • hs4j

    • handlersocketforjava

  • Python

    • python-handler-socket

    • pyhandlersocket

  • Ruby

    • ruby-handlersocket

    • handlersocket

  • JavaScript

    • node-handlersocket

  • Scala

    • hs2client

  • Haskell

    • HandlerSocket-Haskell-Client

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

HandlerSocket Configuration Options

The HandlerSocket plugin has the following options.

See also the Full list of MariaDB options, system and status variables.

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

  • Type: IP Address

  • Default Value: Empty, previously 0.0.0.0

handlersocket_backlog

  • Description: Specify the listen backlog length.

  • Command line: --handlersocket-backlog="value"

  • Scope: Global

  • Dynamic: No

  • Type: number

  • Range: 5 to 1000000

  • Default Value: 32768

handlersocket_epoll

  • Description: Specify whether to use epoll for I/O multiplexing.

  • Command line: --handlersocket-epoll="value"

  • Scope: Global

  • Dynamic: No

  • Type: number

  • Valid values:

    • Min: 0

    • Max: 1

  • Default Value: 1

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

  • Default Value: Empty

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

  • Default Value: Empty

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

  • Type: number

  • Default Value: Empty, previously 9998

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

  • Type: number

  • Default Value: Empty, previously 9999

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

  • Type: number

  • Range: 0 to 1677216

  • Default Value: 0

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

  • Type: number

  • Range: 0 to 1677216

  • Default Value: 0 (possibly 4096)

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

  • Type: number

  • Range: 0 to 1677216

  • Default Value: 0

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

  • Type: number

  • Range: 1 to 3000

  • Default Value: 16

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

  • Type: number

  • Range: 1 to 3000

  • Default Value: 1

handlersocket_timeout

  • Description: Specify the socket timeout in seconds.

  • Command line: --handlersocket-timeout="value"

  • Scope: Global

  • Dynamic: No

  • Type: number

  • Range: 30 to 3600

  • Default Value: 300

handlersocket_verbose

  • Description: Specify the logging verbosity.

  • Command line: --handlersocket-verbose="value"

  • Scope: Global

  • Dynamic: No

  • Type: number

  • Valid values:

    • Min: 0

    • Max: 10000

  • Default Value: 10

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

  • Type: number

  • Range: 0 to 3600

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.

  • HandlerSocket plugin for MySQL presentation by Akira Higuchi of DeNA - June 29 2010 - DeNA Technology Seminar

  • HandlerSocket plugin for MySQL 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.

Operator Precedence

Understand operator precedence in MariaDB Server SQL. This section details the order in which operators are evaluated within expressions, crucial for writing accurate and predictable queries.

The precedence is the order in which the SQL operators are evaluated.

The following list shows the SQL operator precedence. Operators that appear first in the list have a higher precedence. Operators which are listed together have the same precedence.

  • INTERVAL

  • BINARY, COLLATE

  • !

  • - (unary minus), bitwise not (unary bit inversion)

  • || (string concatenation)

  • ^ (bitwise XOR)

  • *, /, DIV, %, MOD (multiplication, division, modulo)

  • -, + (subtraction, addition)

  • <<, >>

  • & (bitwise AND)

  • | (bitwise OR)

  • LIKE, REGEXP, IN

  • BETWEEN

  • = (comparison), <=>, >=, >, <=, <, <>, !=, IS

  • NOT

  • &&, AND

  • XOR

  • || (logical or), OR

  • = (assignment), :=

Functions precedence is always higher than operators precedence.

If the HIGH_NOT_PRECEDENCE SQL_MODE is set, NOT has the same precedence as !.

The || operator's precedence, as well as its meaning, depends on the PIPES_AS_CONCAT SQL_MODE flag: if it is on, || can be used to concatenate strings (like the CONCAT() function) and has a higher precedence.

The = operator's precedence depends on the context - it is higher when = is used as a comparison operator.

Parentheses can be used to modify the operators precedence in an expression.

Short-Circuit Evaluation

The AND, OR, && and || operators support short-circuit evaluation. This means that, in some cases, the expression on the right of those operators is not evaluated, because its result cannot affect the result. In the following cases, short-circuit evaluation is used and x() is not evaluated:

  • FALSE AND x()

  • FALSE && x()

  • TRUE OR x()

  • TRUE || x()

  • NULL BETWEEN x() AND x()

Note however that the short-circuit evaluation does not apply to NULL AND x(). Also, BETWEEN's right operands are not evaluated if the left operand is NULL, but in all other cases all the operands are evaluated.

This is a speed optimization. Also, since functions can have side-effects, this behavior can be used to choose whether execute them or not using a concise syntax:

SELECT some_function() OR log_error();

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

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.

Modulo Operator (%)

Syntax

N % M

Description

Modulo operator. Returns the remainder of N divided by M. See also MOD.

Examples

SELECT 1042 % 50;
+-----------+
| 1042 % 50 |
+-----------+
|        42 |
+-----------+

See Also

  • Operator Precedence

This page is licensed: GPLv2, originally from fill_help_tables.sql

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 BIGINT precision. If either integer is unsigned, the result is also an unsigned integer, unless the NO_UNSIGNED_SUBTRACTION SQL_MODE is enabled, in which case the result is always signed.

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

Examples

SELECT 96-9;
+------+
| 96-9 |
+------+
|   87 |
+------+

SELECT 15-17;
+-------+
| 15-17 |
+-------+
|    -2 |
+-------+

SELECT 3.66 + 1.333;
+--------------+
| 3.66 + 1.333 |
+--------------+
|        4.993 |
+--------------+

Unary minus:

SELECT - (3+5);
+---------+
| - (3+5) |
+---------+
|      -8 |
+---------+

See Also

  • Type Conversion

  • Addition Operator (+)

  • Multiplication Operator (*)

  • Division Operator (/)

  • Operator Precedence

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

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

var_name := expr

Description

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

Unlike the = operator, := can always be used to assign a value to a variable.

This operator works with both user-defined variables and local variables.

When assigning the same value to several variables, LAST_VALUE() can be useful.

Examples

SELECT @x := 10;
+----------+
| @x := 10 |
+----------+
|       10 |
+----------+

SELECT @x, @y := @x;
+------+----------+
| @x   | @y := @x |
+------+----------+
|   10 |       10 |
+------+----------+

See Also

  • Operator Precedence

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

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 := assignment operator, the = assignment operator cannot be used in all contexts, and is only valid as part of a SET statement, or the SET clause of an UPDATE statement

This operator works with both user-defined variables and local variables.

Examples

UPDATE table_name SET x = 2 WHERE x > 100;
SET @x = 1, @y := 2;

See Also

  • Operator Precedence

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

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.

BETWEEN AND

Syntax

expr BETWEEN min AND max

Description

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type. Otherwise type conversion takes place according to the rules described at Type Conversion, but applied to all the three arguments.

Examples

SELECT 1 BETWEEN 2 AND 3;
+-------------------+
| 1 BETWEEN 2 AND 3 |
+-------------------+
|                 0 |
+-------------------+
SELECT 'b' BETWEEN 'a' AND 'c';
+-------------------------+
| 'b' BETWEEN 'a' AND 'c' |
+-------------------------+
|                       1 |
+-------------------------+
SELECT 2 BETWEEN 2 AND '3';
+---------------------+
| 2 BETWEEN 2 AND '3' |
+---------------------+
|                   1 |
+---------------------+
SELECT 2 BETWEEN 2 AND 'x-3';
+-----------------------+
| 2 BETWEEN 2 AND 'x-3' |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1292): Truncated incorrect DOUBLE value: 'x-3'

NULL:

SELECT 1 BETWEEN 1 AND NULL;
+----------------------+
| 1 BETWEEN 1 AND NULL |
+----------------------+
|                 NULL |
+----------------------+

DATE, DATETIME and TIMESTAMP examples. Omitting the time component compares against 00:00, so later times on the same date are not returned:

CREATE TABLE `x` (
  a date ,
  b datetime,
  c timestamp
)

INSERT INTO x VALUES 
 ('2018-11-11', '2018-11-11 05:15', '2018-11-11 05:15'), 
 ('2018-11-12', '2018-11-12 05:15', '2018-11-12 05:15'); 

SELECT * FROM x WHERE a BETWEEN '2018-11-11' AND '2018-11-12';
+------------+---------------------+---------------------+
| a          | b                   | c                   |
+------------+---------------------+---------------------+
| 2018-11-11 | 2018-11-11 05:15:00 | 2018-11-11 05:15:00 |
| 2018-11-12 | 2018-11-12 05:15:00 | 2018-11-12 05:15:00 |
+------------+---------------------+---------------------+

SELECT * FROM x WHERE b BETWEEN '2018-11-11' AND '2018-11-12';
+------------+---------------------+---------------------+
| a          | b                   | c                   |
+------------+---------------------+---------------------+
| 2018-11-11 | 2018-11-11 05:15:00 | 2018-11-11 05:15:00 |
+------------+---------------------+---------------------+

SELECT * FROM x WHERE c BETWEEN '2018-11-11' AND '2018-11-12';
+------------+---------------------+---------------------+
| a          | b                   | c                   |
+------------+---------------------+---------------------+
| 2018-11-11 | 2018-11-11 05:15:00 | 2018-11-11 05:15:00 |
+------------+---------------------+---------------------+

See Also

  • Operator Precedence

This page is licensed: GPLv2, originally from fill_help_tables.sql

COALESCE

Syntax

COALESCE(value,...)

Description

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values. At least one parameter must be passed.

The function is useful when substituting a default value for null values when displaying data.

See also NULL Values in MariaDB.

Examples

SELECT COALESCE(NULL,1);
+------------------+
| COALESCE(NULL,1) |
+------------------+
|                1 |
+------------------+
SELECT COALESCE(NULL,NULL,NULL);
+--------------------------+
| COALESCE(NULL,NULL,NULL) |
+--------------------------+
|                     NULL |
+--------------------------+

When two arguments are given, COALESCE() is the same as IFNULL():

SET @a=NULL, @b=1;

SELECT COALESCE(@a, @b), IFNULL(@a, @b);
+------------------+----------------+
| COALESCE(@a, @b) | IFNULL(@a, @b) |
+------------------+----------------+
|                1 |              1 |
+------------------+----------------+

Hex type confusion:

CREATE TABLE t1 (a INT, b VARCHAR(10));
INSERT INTO t1 VALUES (0x31, 0x61),(COALESCE(0x31), COALESCE(0x61));

SELECT * FROM t1;
+------+------+
| a    | b    |
+------+------+
|   49 | a    |
|    1 | a    |
+------+------+

The reason for the differing results above is that when 0x31 is inserted directly to the column, it's treated as a number (see Hexadecimal Literals), while when 0x31 is passed to COALESCE(), it's treated as a string, because:

  • HEX values have a string data type by default.

  • COALESCE() has the same data type as the argument.

Substituting zero for NULL (in this case when the aggregate function returns NULL after finding no rows):

SELECT SUM(score) FROM student;
+------------+
| SUM(score) |
+------------+
|       NULL |
+------------+

SELECT COALESCE(SUM(score),0) FROM student;
+------------------------+
| COALESCE(SUM(score),0) |
+------------------------+
|                      0 |
+------------------------+

See also

  • NULL values

  • IS NULL operator

  • IS NOT NULL operator

  • IFNULL function

  • NULLIF function

  • CONNECT data types

This page is licensed: GPLv2, originally from fill_help_tables.sql

=

Syntax

left_expr = right_expr

Description

Equal operator. Evaluates both SQL expressions and returns 1 if they are equal, 0 if they are not equal, or NULL if either expression is NULL. If the expressions return different data types (for example, a number and a string), a type conversion is performed.

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

SELECT (t1.a, t1.b) = (t2.x, t2.y) FROM t1 INNER JOIN t2;

SELECT (t1.a = t2.x) AND (t1.b = t2.y) FROM t1 INNER JOIN t2;

To perform a NULL-safe comparison, use the <=> operator.

= can also be used as an assignment operator.

Examples

SELECT 1 = 0;
+-------+
| 1 = 0 |
+-------+
|     0 |
+-------+

SELECT '0' = 0;
+---------+
| '0' = 0 |
+---------+
|       1 |
+---------+

SELECT '0.0' = 0;
+-----------+
| '0.0' = 0 |
+-----------+
|         1 |
+-----------+

SELECT '0.01' = 0;
+------------+
| '0.01' = 0 |
+------------+
|          0 |
+------------+

SELECT '.01' = 0.01;
+--------------+
| '.01' = 0.01 |
+--------------+
|            1 |
+--------------+

SELECT (5 * 2) = CONCAT('1', '0');
+----------------------------+
| (5 * 2) = CONCAT('1', '0') |
+----------------------------+
|                          1 |
+----------------------------+

SELECT 1 = NULL;
+----------+
| 1 = NULL |
+----------+
|     NULL |
+----------+

SELECT NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
|        NULL |
+-------------+

See Also

  • Operator Precedence

This page is licensed: GPLv2, originally from fill_help_tables.sql

>=

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 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:

SELECT (t1.a, t1.b) >= (t2.x, t2.y) 
FROM t1 INNER JOIN t2;

SELECT (t1.a > t2.x) OR ((t1.a = t2.x) AND (t1.b >= t2.y))
FROM t1 INNER JOIN t2;

Examples

SELECT 2 >= 2;
+--------+
| 2 >= 2 |
+--------+
|      1 |
+--------+

SELECT 'A' >= 'a';
+------------+
| 'A' >= 'a' |
+------------+
|          1 |
+------------+

See Also

  • Operator Precedence

This page is licensed: GPLv2, originally from fill_help_tables.sql

>

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:

SELECT (t1.a, t1.b) > (t2.x, t2.y) 
FROM t1 INNER JOIN t2;

SELECT (t1.a > t2.x) OR ((t1.a = t2.x) AND (t1.b > t2.y))
FROM t1 INNER JOIN t2;

Examples

SELECT 2 > 2;
+-------+
| 2 > 2 |
+-------+
|     0 |
+-------+

SELECT 'b' > 'a';
+-----------+
| 'b' > 'a' |
+-----------+
|         1 |
+-----------+

See Also

  • Operator Precedence

This page is licensed: GPLv2, originally from fill_help_tables.sql

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

SELECT GREATEST(2,0);
+---------------+
| GREATEST(2,0) |
+---------------+
|             2 |
+---------------+
SELECT GREATEST(34.0,3.0,5.0,767.0);
+------------------------------+
| GREATEST(34.0,3.0,5.0,767.0) |
+------------------------------+
|                        767.0 |
+------------------------------+
SELECT GREATEST('B','A','C');
+-----------------------+
| GREATEST('B','A','C') |
+-----------------------+
| C                     |
+-----------------------+

This page is licensed: GPLv2, originally from fill_help_tables.sql

IN

Syntax

expr IN (value,...)

Description

Returns 1 if expr is equal to any of the values in the IN list, else returns 0. If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search. This means IN is very quick if the IN value list consists entirely of constants. Otherwise, type conversion takes place according to the rules described at Type Conversion, but applied to all the arguments.

If expr is NULL, IN always returns NULL. If at least one of the values in the list is NULL, and one of the comparisons is true, the result is 1. If at least one of the values in the list is NULL and none of the comparisons is true, the result is NULL.

Examples

SELECT 2 IN (0,3,5,7);
+----------------+
| 2 IN (0,3,5,7) |
+----------------+
|              0 |
+----------------+
SELECT 'wefwf' IN ('wee','wefwf','weg');
+----------------------------------+
| 'wefwf' IN ('wee','wefwf','weg') |
+----------------------------------+
|                                1 |
+----------------------------------+

Type conversion:

SELECT 1 IN ('1', '2', '3');
+----------------------+
| 1 IN ('1', '2', '3') |
+----------------------+
|                    1 |
+----------------------+
SELECT NULL IN (1, 2, 3);
+-------------------+
| NULL IN (1, 2, 3) |
+-------------------+
|              NULL |
+-------------------+

SELECT 1 IN (1, 2, NULL);
+-------------------+
| 1 IN (1, 2, NULL) |
+-------------------+
|                 1 |
+-------------------+

SELECT 5 IN (1, 2, NULL);
+-------------------+
| 5 IN (1, 2, NULL) |
+-------------------+
|              NULL |
+-------------------+

See Also

  • Conversion of Big IN Predicates Into Subqueries

  • Operator Precedence

This page is licensed: GPLv2, originally from fill_help_tables.sql

INTERVAL

Syntax

INTERVAL(N0,N1,N2,N3,...)

Description

Returns the index of the last argument that is less than or equal to the first argument, or is NULL.

Returns 0 if N0 < N1, 1 if N1 <= N0 < N2, 2 if N2 <= N0 < N3 and so on or -1 if N0 is NULL. All arguments are treated as integers. It is required that N1 <= N2 <= N3 <= ... <= Nn for this function to work correctly because a fast binary search is used.

Examples

SELECT INTERVAL(22, 24, 26, 28);
+--------------------------+
| INTERVAL(22, 24, 26, 28) |
+--------------------------+
|                        0 |
+--------------------------+
SELECT INTERVAL(22, 22, 22, 22, 23);
+------------------------------+
| INTERVAL(22, 22, 22, 22, 23) |
+------------------------------+
|                            3 |
+------------------------------+
SELECT INTERVAL(25, 24, 26, 28);
+--------------------------+
| interval(25, 24, 26, 28) |
+--------------------------+
|                        1 |
+--------------------------+
SELECT INTERVAL(27, 24, 26, 28);
+--------------------------+
| interval(27, 24, 26, 28) |
+--------------------------+
|                        2 |
+--------------------------+
SELECT INTERVAL(27, 25, 26, 27);
+--------------------------+
| interval(27, 25, 26, 27) |
+--------------------------+
|                        3 |
+--------------------------+
SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
+--------------------------------------+
| INTERVAL(23, 1, 15, 17, 30, 44, 200) |
+--------------------------------------+
|                                    3 |
+--------------------------------------+
SELECT INTERVAL(10, 1, 10, 100, 1000);
+--------------------------------+
| INTERVAL(10, 1, 10, 100, 1000) |
+--------------------------------+
|                              2 |
+--------------------------------+
SELECT INTERVAL(22, 23, 30, 44, 200);
+-------------------------------+
| INTERVAL(22, 23, 30, 44, 200) |
+-------------------------------+
|                             0 |
+-------------------------------+
SELECT INTERVAL(10, 2, NULL);
+-----------------------+
| INTERVAL(10, 2, NULL) |
+-----------------------+
|                     2 |
+-----------------------+

This page is licensed: GPLv2, originally from fill_help_tables.sql

IS NOT NULL

Syntax

IS NOT NULL

Description

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

Examples

SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
+---------------+---------------+------------------+
| 1 IS NOT NULL | 0 IS NOT NULL | NULL IS NOT NULL |
+---------------+---------------+------------------+
|             1 |             1 |                0 |
+---------------+---------------+------------------+

See also

  • NULL values

  • IS NULL operator

  • COALESCE function

  • IFNULL function

  • NULLIF function

  • CONNECT data types

This page is licensed: GPLv2, originally from fill_help_tables.sql

IS NOT

Syntax

IS NOT boolean_value

Description

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

Examples

SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;
+------------------+------------------+---------------------+
| 1 IS NOT UNKNOWN | 0 IS NOT UNKNOWN | NULL IS NOT UNKNOWN |
+------------------+------------------+---------------------+
|                1 |                1 |                   0 |
+------------------+------------------+---------------------+
SELECT NULL IS NOT TRUE, NULL IS NOT FALSE;
+------------------+-------------------+
| NULL IS NOT TRUE | NULL IS NOT FALSE |
+------------------+-------------------+
|                1 |                 1 |
+------------------+-------------------+

This page is licensed: GPLv2, originally from fill_help_tables.sql

IS NULL

Syntax

IS NULL

Description

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

Examples

SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
+-----------+-----------+--------------+
| 1 IS NULL | 0 IS NULL | NULL IS NULL |
+-----------+-----------+--------------+
|         0 |         0 |            1 |
+-----------+-----------+--------------+

Compatibility

Some ODBC applications use the syntax auto_increment_field IS NOT NULL to find the latest row that was inserted with an autogenerated key value. If your applications need this, you can set the sql_auto_is_null variable to 1.

SET @@sql_auto_is_null=1;
CREATE TABLE t1 (auto_increment_column INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 WHERE auto_increment_column IS NULL;

+-----------------------+
| auto_increment_column |
+-----------------------+
|                     1 |
+-----------------------+

See also

  • NULL values

  • IS NOT NULL operator

  • COALESCE function

  • IFNULL function

  • NULLIF function

  • CONNECT data types

This page is licensed: GPLv2, originally from fill_help_tables.sql

IS

Syntax

IS boolean_value

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

SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;
+-----------+------------+-----------------+
| 1 IS TRUE | 0 IS FALSE | NULL IS UNKNOWN |
+-----------+------------+-----------------+
|         1 |          1 |               1 |
+-----------+------------+-----------------+

Difference between = and IS TRUE:

SELECT 2 = TRUE, 2 IS TRUE;
+----------+-----------+
| 2 = TRUE | 2 IS TRUE |
+----------+-----------+
|        0 |         1 |
+----------+-----------+

See Also

  • Boolean Literals

  • BOOLEAN Data Type

  • Operator Precedence

This page is licensed: GPLv2, originally from fill_help_tables.sql

ISNULL

Syntax

ISNULL(expr)

Description

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

See also NULL Values in MariaDB.

Examples

SELECT ISNULL(1+1);
+-------------+
| ISNULL(1+1) |
+-------------+
|           0 |
+-------------+

SELECT ISNULL(1/0);
+-------------+
| ISNULL(1/0) |
+-------------+
|           1 |
+-------------+

This page is licensed: GPLv2, originally from fill_help_tables.sql

LEAST

Syntax

LEAST(value1,value2,...)

Description

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

  • If the return value is used in an INTEGER context or all arguments are integer-valued, they are compared as integers.

  • If the return value is used in a REAL context or all arguments are real-valued, they are compared as reals.

  • If any argument is a case-sensitive string, the arguments are compared as case-sensitive strings.

  • In all other cases, the arguments are compared as case-insensitive strings.

LEAST() returns NULL if any argument is NULL.

Examples

SELECT LEAST(2,0);
+------------+
| LEAST(2,0) |
+------------+
|          0 |
+------------+
SELECT LEAST(34.0,3.0,5.0,767.0);
+---------------------------+
| LEAST(34.0,3.0,5.0,767.0) |
+---------------------------+
|                       3.0 |
+---------------------------+
SELECT LEAST('B','A','C');
+--------------------+
| LEAST('B','A','C') |
+--------------------+
| A                  |
+--------------------+

This page is licensed: GPLv2, originally from fill_help_tables.sql

<=

Syntax

<=

Description

Less than or equal operator. Evaluates both SQL expressions and returns 1 if the left value is less than or equal to 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:

SELECT (t1.a, t1.b) <= (t2.x, t2.y) 
FROM t1 INNER JOIN t2;

SELECT (t1.a < t2.x) OR ((t1.a = t2.x) AND (t1.b <= t2.y))
FROM t1 INNER JOIN t2;

Examples

SELECT 0.1 <= 2;
+----------+
| 0.1 <= 2 |
+----------+
|        1 |
+----------+
SELECT 'a'<='A';
+----------+
| 'a'<='A' |
+----------+
|        1 |
+----------+

See Also

  • Operator Precedence

This page is licensed: GPLv2, originally from fill_help_tables.sql

<

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:

SELECT (t1.a, t1.b) < (t2.x, t2.y) 
FROM t1 INNER JOIN t2;

SELECT (t1.a < t2.x) OR ((t1.a = t2.x) AND (t1.b < t2.y))
FROM t1 INNER JOIN t2;

Examples

SELECT 2 < 2;
+-------+
| 2 < 2 |
+-------+
|     0 |
+-------+

Type conversion:

SELECT 3<'4';
+-------+
| 3<'4' |
+-------+
|     1 |
+-------+

Case insensitivity - see Character Sets and Collations:

SELECT 'a'<'A';
+---------+
| 'a'<'A' |
+---------+
|       0 |
+---------+

See Also

  • Operator Precedence

This page is licensed: GPLv2, originally from fill_help_tables.sql

NOT BETWEEN

Syntax

expr NOT BETWEEN min AND max

Description

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

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

Examples

SELECT 1 NOT BETWEEN 2 AND 3;
+-----------------------+
| 1 NOT BETWEEN 2 AND 3 |
+-----------------------+
|                     1 |
+-----------------------+
SELECT 'b' NOT BETWEEN 'a' AND 'c';
+-----------------------------+
| 'b' NOT BETWEEN 'a' AND 'c' |
+-----------------------------+
|                           0 |
+-----------------------------+

NULL:

SELECT 1 NOT BETWEEN 1 AND NULL;
+--------------------------+
| 1 NOT BETWEEN 1 AND NULL |
+--------------------------+
|                     NULL |
+--------------------------+

This page is licensed: GPLv2, originally from fill_help_tables.sql

!=

Syntax

<>, !=

Description

Not equal operator. Evaluates both SQL expressions and returns 1 if they are not equal, and 0 if they are equal, 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:

SELECT (t1.a, t1.b) != (t2.x, t2.y) 
FROM t1 INNER JOIN t2;

SELECT (t1.a != t2.x) OR (t1.b != t2.y)
FROM t1 INNER JOIN t2;

Examples

SELECT '.01' <> '0.01';
+-----------------+
| '.01' <> '0.01' |
+-----------------+
|               1 |
+-----------------+

SELECT .01 <> '0.01';
+---------------+
| .01 <> '0.01' |
+---------------+
|             0 |
+---------------+

SELECT 'zapp' <> 'zappp';
+-------------------+
| 'zapp' <> 'zappp' |
+-------------------+
|                 1 |
+-------------------+

See Also

  • Operator Precedence

This page is licensed: GPLv2, originally from fill_help_tables.sql

NOT IN

Syntax

expr NOT IN (value,...)

Description

This is the same as NOT (expr IN (value,...)).

Examples

SELECT 2 NOT IN (0,3,5,7);
+--------------------+
| 2 NOT IN (0,3,5,7) |
+--------------------+
|                  1 |
+--------------------+
SELECT 'wefwf' NOT IN ('wee','wefwf','weg');
+--------------------------------------+
| 'wefwf' NOT IN ('wee','wefwf','weg') |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
SELECT 1 NOT IN ('1', '2', '3');
+--------------------------+
| 1 NOT IN ('1', '2', '3') |
+--------------------------+
|                        0 |
+--------------------------+

NULL:

SELECT NULL NOT IN (1, 2, 3);
+-----------------------+
| NULL NOT IN (1, 2, 3) |
+-----------------------+
|                  NULL |
+-----------------------+

SELECT 1 NOT IN (1, 2, NULL);
+-----------------------+
| 1 NOT IN (1, 2, NULL) |
+-----------------------+
|                     0 |
+-----------------------+

SELECT 5 NOT IN (1, 2, NULL);
+-----------------------+
| 5 NOT IN (1, 2, NULL) |
+-----------------------+
|                  NULL |
+-----------------------+

This page is licensed: GPLv2, originally from fill_help_tables.sql

<=>

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:

SELECT (t1.a, t1.b) <=> (t2.x, t2.y) 
FROM t1 INNER JOIN t2;

SELECT (t1.a <=> t2.x) AND (t1.b <=> t2.y)
FROM t1 INNER JOIN t2;

See also NULL Values in MariaDB.

Examples

SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
+---------+---------------+------------+
| 1 <=> 1 | NULL <=> NULL | 1 <=> NULL |
+---------+---------------+------------+
|       1 |             1 |          0 |
+---------+---------------+------------+

SELECT 1 = 1, NULL = NULL, 1 = NULL;
+-------+-------------+----------+
| 1 = 1 | NULL = NULL | 1 = NULL |
+-------+-------------+----------+
|     1 |        NULL |     NULL |
+-------+-------------+----------+

See Also

  • Operator Precedence

This page is licensed: GPLv2, originally from fill_help_tables.sql

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

AND, &&

Description

Logical AND. Evaluates to 1 if all operands are non-zero and not NULL, to 0 if one or more operands are 0, otherwise NULL is returned.

For this operator, short-circuit evaluation can be used.

Examples

SELECT 1 && 1;
+--------+
| 1 && 1 |
+--------+
|      1 |
+--------+

SELECT 1 && 0;
+--------+
| 1 && 0 |
+--------+
|      0 |
+--------+

SELECT 1 && NULL;
+-----------+
| 1 && NULL |
+-----------+
|      NULL |
+-----------+

SELECT 0 && NULL;
+-----------+
| 0 && NULL |
+-----------+
|         0 |
+-----------+

SELECT NULL && 0;
+-----------+
| NULL && 0 |
+-----------+
|         0 |
+-----------+

See Also

  • Operator Precedence

This page is licensed: GPLv2, originally from fill_help_tables.sql

!

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 higher precedence. If the HIGH_NOT_PRECEDENCE SQL_MODE flag is set, NOT and ! have the same precedence.

Examples

SELECT NOT 10;
+--------+
| NOT 10 |
+--------+
|      0 |
+--------+

SELECT NOT 0;
+-------+
| NOT 0 |
+-------+
|     1 |
+-------+

SELECT NOT NULL;
+----------+
| NOT NULL |
+----------+
|     NULL |
+----------+

SELECT ! (1+1);
+---------+
| ! (1+1) |
+---------+
|       0 |
+---------+

SELECT ! 1+1;
+-------+
| ! 1+1 |
+-------+
|     1 |
+-------+

See Also

  • Operator Precedence

This page is licensed: GPLv2, originally from fill_help_tables.sql

||

Syntax

OR, ||

Description

Logical OR. When both operands are non-NULL, the result is 1 if any operand is non-zero, and 0 otherwise. With a NULL operand, the result is 1 if the other operand is non-zero, and NULL otherwise. If both operands are NULL, the result is NULL.

For this operator, short-circuit evaluation can be used.

Note that, if the PIPES_AS_CONCAT SQL_MODE is set, || is used as a string concatenation operator. This means that a || b is the same as CONCAT(a,b). See CONCAT() for details.

Oracle Mode

In Oracle mode, || ignores null.

Examples

SELECT 1 || 1;
+--------+
| 1 || 1 |
+--------+
|      1 |
+--------+

SELECT 1 || 0;
+--------+
| 1 || 0 |
+--------+
|      1 |
+--------+

SELECT 0 || 0;
+--------+
| 0 || 0 |
+--------+
|      0 |
+--------+

SELECT 0 || NULL;
+-----------+
| 0 || NULL |
+-----------+
|      NULL |
+-----------+

SELECT 1 || NULL;
+-----------+
| 1 || NULL |
+-----------+
|         1 |
+-----------+

In Oracle mode:

SELECT 0 || NULL;
+-----------+
| 0 || NULL |
+-----------+
| 0         |
+-----------+

See Also

See Also

  • Operator Precedence

  • Oracle mode

This page is licensed: GPLv2, originally from fill_help_tables.sql

XOR

Syntax

XOR

Description

XOR stands for eXclusive OR. Returns NULL if either operand is NULL. For non-NULL operands, evaluates to 1 if an odd number of operands is non-zero, otherwise 0 is returned.

Examples

SELECT 1 XOR 1;
+---------+
| 1 XOR 1 |
+---------+
|       0 |
+---------+

SELECT 1 XOR 0;
+---------+
| 1 XOR 0 |
+---------+
|       1 |
+---------+

SELECT 1 XOR NULL;
+------------+
| 1 XOR NULL |
+------------+
|       NULL |
+------------+

In the following example, the right 1 XOR 1 is evaluated first, and returns 0. Then, 1 XOR 0 is evaluated, and 1 is returned.

SELECT 1 XOR 1 XOR 1;
+---------------+
| 1 XOR 1 XOR 1 |
+---------------+
|             1 |
+---------------+

See Also

  • Operator Precedence

This page is licensed: GPLv2, originally from fill_help_tables.sql

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.

Sequence OverviewCREATE SEQUENCESHOW CREATE SEQUENCEALTER SEQUENCEDROP SEQUENCESEQUENCE FunctionsInformation Schema SEQUENCES TableSHOW TABLES

Sequence Overview

This page is about sequence objects. For details about the storage engine, see Sequence Storage Engine.

Introduction

A sequence is an object that generates a sequence of numeric values, as specified by the CREATE SEQUENCE statement.

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

Creating a Sequence

The CREATE SEQUENCE statement is used to create a sequence. Here is an example of a sequence starting at 100, incrementing by 10 each time:

CREATE SEQUENCE s START WITH 100 INCREMENT BY 10;

The CREATE SEQUENCE statement, along with defaults, can be viewd with the SHOW CREATE SEQUENCE STATEMENT, for example:

SHOW CREATE SEQUENCE s\G
*************************** 1. row ***************************
       Table: s
Create Table: CREATE SEQUENCE `s` start with 100 minvalue 1 maxvalue 9223372036854775806 
  increment by 10 cache 1000 nocycle ENGINE=InnoDB

Using Sequence Objects

To get the next value from a sequence, use

NEXT VALUE FOR sequence_name

or

NEXTVAL(sequence_name)

or in Oracle mode (SQL_MODE=ORACLE)

sequence_name.nextval

For retrieving the last value used by the current connection from a sequence use:

PREVIOUS VALUE FOR sequence_name

or

LASTVAL(sequence_name)

or in Oracle mode (SQL_MODE=ORACLE)

sequence_name.currval

For example:

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|        100 |
+------------+

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|        110 |
+------------+

SELECT LASTVAL(s);
+------------+
| LASTVAL(s) |
+------------+
|        110 |
+------------+

Using Sequences in DEFAULT

Sequences can be used in DEFAULT:

create sequence s1;
create table t1 (a int primary key default (next value for s1), b int);
insert into t1 (b) values (1),(2);
select * from t1;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |    2 |
+---+------+

Changing a Sequence

The ALTER SEQUENCE statement is used for changing sequences. For example, to restart the sequence at another value:

ALTER SEQUENCE s RESTART 50;

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|         50 |
+------------+

The SETVAL function can also be used to set the next value to be returned for a SEQUENCE, for example:

SELECT SETVAL(s, 100);
+----------------+
| SETVAL(s, 100) |
+----------------+
|            100 |
+----------------+

SETVAL can only be used to increase the sequence value. Attempting to set a lower value will fail, returning NULL:

SELECT SETVAL(s, 50);
+---------------+
| SETVAL(s, 50) |
+---------------+
|          NULL |
+---------------+

Dropping a Sequence

The DROP SEQUENCE statement is used to drop a sequence, for example:

DROP SEQUENCE s;

Replication

If one wants to use Sequences in a master-master setup or with Galera one should use INCREMENT=0. This will tell the Sequence to use auto_increment_increment and auto_increment_offset to generate unique values for each server.

Standards Compliance

MariaDB supports both ANSI SQL and Oracle syntax for sequences.

However as SEQUENCE is implemented as a special kind of table, it uses the same namespace as tables. The benefits are that sequences show up in SHOW TABLES, and one can also create a sequence with CREATE TABLE and drop it with DROP TABLE. One can SELECT from it as from any other table. This ensures that all old tools that work with tables should work with sequences.

Since sequence objects act as regular tables in many contexts, they will be affected by LOCK TABLES. This is not the case in other DBMS, such as Oracle, where LOCK TABLE does not affect sequences.

Notes

One of the goals with the Sequence implementation is that all old tools, such as mariadb-dump (previously mysqldump), should work unchanged, while still keeping the normal usage of sequence standard compatibly.

To make this possible, sequence is currently implemented as a table with a few exclusive properties.

The special properties for sequence tables are:

  • A sequence table has always one row.

  • When one creates a sequence, either with CREATE TABLE or CREATE SEQUENCE, one row will be inserted.

  • If one tries to insert into a sequence table, the single row will be updated. This allows mariadb-dump to work but also gives the additional benefit that one can change all properties of a sequence with a single insert. New applications should of course also use ALTER SEQUENCE.

  • UPDATE or DELETE can't be performed on Sequence objects.

  • Doing a select on the sequence shows the current state of the sequence, except the values that are reserved in the cache. The next_value column shows the next value not reserved by the cache.

  • FLUSH TABLES will close the sequence 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.

  • A number of normal table operations work on Sequence tables. See next section.

Table Operations that Work with Sequences

  • SHOW CREATE TABLE sequence_name. This shows the table structure that is behind the SEQUENCE including the field names that can be used with SELECT or even CREATE TABLE.

  • CREATE TABLE sequence-structure ... SEQUENCE=1

  • ALTER TABLE sequence RENAME TO sequence2

  • RENAME TABLE sequence_name TO new_sequence_name

  • DROP TABLE sequence_name. This is allowed mainly to get old tools like mariadb-dump to work with sequence tables.

  • SHOW TABLES

Implementation

Internally, sequence tables are created as a normal table without rollback (the InnoDB, Aria and MySAM engines support this), wrapped by a sequence engine object. This allowed us to create sequences with almost no performance impact for normal tables. (The cost is one 'if' per insert if the binary log is enabled).

Underlying Table Structure

The following example shows the table structure of sequences and how it can be used as a table. (Output of results are slightly edited to make them easier to read)

create sequence t1;
show create sequence t1\G
*************************** 1. row ***************************
  CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806
  increment by 1 cache 1000 nocycle ENGINE=InnoDB

show create table t1\G
*************************** 1. row ***************************
Create Table: CREATE TABLE `t1` (
  `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


select * from t1\G
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

The cycle_count column is incremented every time the sequence wraps around.

Credits

  • Thanks to Jianwe Zhao from Aliyun for his work on SEQUENCE in AliSQL, which gave ideas and inspiration for this work.

  • Thanks to Peter Gulutzan,who helped test and gave useful comments about the implementation.

See Also

  • CREATE SEQUENCE

  • ALTER SEQUENCE

  • DROP SEQUENCE

  • NEXT VALUE FOR

  • PREVIOUS VALUE FOR

  • SETVAL(). Set next value for the sequence.

  • AUTO INCREMENT

  • Sequence Storage Engine

  • Information Schema SEQUENCES Table

  • Error 4084: Sequence has run out

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

ALTER SEQUENCE

Syntax

ALTER SEQUENCE [IF EXISTS] sequence_name
[ INCREMENT [ BY | = ] number ]
[ MINVALUE [=] number | NO MINVALUE | NOMINVALUE ]
[ MAXVALUE [=] number | NO MAXVALUE | NOMAXVALUE ]
[ START [ WITH | = ] number ] [ CACHE [=] number ] [ [ NO ] CYCLE ]
[ RESTART [[WITH | =] number]

ALTER SEQUENCE allows one to change any values for a SEQUENCE created with CREATE SEQUENCE.

The options for ALTER SEQUENCE can be given in any order.

Description

ALTER SEQUENCE changes the parameters of an existing sequence generator. Any parameters not specifically set in the ALTER SEQUENCE command retain their prior settings.

ALTER SEQUENCE requires the ALTER privilege.

Options to ALTER SEQUENCE

Option
Default value
Description

INCREMENT

1

Increment to use for values. May be negative.

MINVALUE

1 if INCREMENT > 0 and -9223372036854775807 if INCREMENT < 0

Minimum value for the sequence.

MAXVALUE

9223372036854775806 if INCREMENT > 0 and -1 if INCREMENT < 0

Max value for sequence.

START

MINVALUE if INCREMENT > 0 and MAX_VALUE if INCREMENT< 0

First value that the sequence will generate.

CACHE

1000

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.

CYCLE

0 (= NO CYCLE)

1 if the sequence should start again from MINVALUE

The optional clause RESTART [ WITH restart ] sets the next value for the sequence. This is equivalent to calling the SETVAL() function with the is_used argument as 0. The specified value will be returned by the next call of nextval. Using RESTART with no restart value is equivalent to supplying the start value that was recorded by CREATE SEQUENCE or last set by ALTER SEQUENCE START WITH.

ALTER SEQUENCE does not allow to change the sequence so that it's inconsistent:

CREATE SEQUENCE s1;
ALTER SEQUENCE s1 MINVALUE 10;
ERROR 4061 (HY000): Sequence 'test.t1' values are conflicting

ALTER SEQUENCE s1 MINVALUE 10 RESTART 10;
ERROR 4061 (HY000): Sequence 'test.t1' values are conflicting

ALTER SEQUENCE s1 MINVALUE 10 START 10 RESTART 10;

INSERT

To allow SEQUENCE objects to be backed up by old tools, like mariadb-dump, one can use SELECT to read the current state of a SEQUENCE object and use an INSERT to update the SEQUENCE object. INSERT is only allowed if all fields are specified:

CREATE SEQUENCE s1;
INSERT INTO s1 VALUES(1000,10,2000,1005,1,1000,0,0);
SELECT * FROM s1;

+------------+-----------+-----------+-------+-----------+-------+-------+-------+
| next_value | min_value | max_value | start | increment | cache | cycle | round |
+------------+-----------+-----------+-------+-----------+-------+-------+-------+
|       1000 |        10 |      2000 |  1005 |         1 |  1000 |     0 |     0 |
+------------+-----------+-----------+-------+-----------+-------+-------+-------+

SHOW CREATE SEQUENCE s1;
+-------+--------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                 |
+-------+--------------------------------------------------------------------------------------------------------------+
| s1    | CREATE SEQUENCE `s1` start with 1005 minvalue 10 maxvalue 2000 increment by 1 cache 1000 nocycle ENGINE=Aria |
+-------+--------------------------------------------------------------------------------------------------------------+

Notes

ALTER SEQUENCE will instantly affect all future SEQUENCE operations. This is in contrast to some other databases where the changes requested by ALTER SEQUENCE will not be seen until the sequence cache has run out.

ALTER SEQUENCE will take a full table lock of the sequence object during its (brief) operation. This ensures that ALTER SEQUENCE is replicated correctly. If you only want to set the next sequence value to a higher value than current, then you should use SETVAL() instead, as this is not blocking.

If you want to change the storage engine or sequence comment, or rename the sequence, you can use ALTER TABLE for this.

See Also

  • Sequence Overview

  • CREATE SEQUENCE

  • DROP SEQUENCE

  • NEXT VALUE FOR

  • PREVIOUS VALUE FOR

  • SETVAL(). Set next value for the sequence.

  • AUTO INCREMENT

  • ALTER TABLE

  • Information Schema SEQUENCES Table

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

CREATE SEQUENCE

Syntax

CREATE [OR REPLACE] [TEMPORARY] SEQUENCE [IF NOT EXISTS] sequence_name
[AS { TINYINT | SMALLINT | |MEDIUMINT | INT | INTEGER | BIGINT } [SIGNED | UNSIGNED]]
[ INCREMENT [ BY | = ] number ]
[ MINVALUE [=] number | NO MINVALUE | NOMINVALUE ]
[ MAXVALUE [=] number | NO MAXVALUE | NOMAXVALUE ]
[ START [ WITH | = ] number ] 
[ CACHE [=] number | NOCACHE ] [ CYCLE | NOCYCLE] 
[table_options](../sql-statements/data-definition/create/create-table.md#table-options)

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 AUTO INCREMENT 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 AUTO INCREMENT. Another benefit is that you can access the last value generated by all used sequences, which solves one of the limitations with LAST_INSERT_ID().

CREATE SEQUENCE requires the CREATE privilege.

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

CREATE Options

AS

INT type, that is, one of TINYINT, SMALLINT, MEDIUMINT, INT, INTEGER, BIGINT. 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 auto_increment_increment system variable at the time of creation, which is always a positive number. (see MDEV-16035). Default 1.

MINVALUE

Minimum value for the sequence. From MariaDB 11.5, 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 MariaDB 11.5, 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. FLUSH TABLES, 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

  • MAXVALUE <= 9223372036854775806 (LONGLONG_MAX-1). From MariaDB 11.5, the parser accepts values beyond this, and converts based on the int type.

  • MINVALUE >= -9223372036854775807 (LONGLONG_MIN+1). From MariaDB 11.5, the parser accepts values beyond this, and converts based on the int type.

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

Atomic DDL

MariaDB supports Atomic DDL and CREATE SEQUENCE is atomic.

MariaDB does not support Atomic DDL and CREATE SEQUENCE is atomic.

Examples

CREATE SEQUENCE s START WITH 100 INCREMENT BY 10;

CREATE SEQUENCE s2 START WITH -100 INCREMENT BY -10;

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

CREATE SEQUENCE s3 START WITH -100 INCREMENT BY 10;
ERROR 4082 (HY000): Sequence 'test.s3' values are conflicting

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

CREATE SEQUENCE s3 START WITH -100 INCREMENT BY 10 MINVALUE=-100 MAXVALUE=1000;

From MariaDB 11.5:

CREATE SEQUENCE s3 AS BIGINT UNSIGNED START WITH 10;

Parser accepting larger or smaller values:

CREATE OR REPLACE SEQUENCE s AS TINYINT SIGNED
  MINVALUE=-999999999999999999999999999999999
  MAXVALUE=999999999999999999999999999999999 
  START WITH 100 INCREMENT BY 10;
Query OK, 0 rows affected, 2 warnings (0.037 sec)

SHOW WARNINGS;
+-------+------+-----------------------------------------------+
| Level | Code | Message                                       |
+-------+------+-----------------------------------------------+
| Note  | 1292 | Truncated incorrect INTEGER value: 'MINVALUE' |
| Note  | 1292 | Truncated incorrect INTEGER value: 'MAXVALUE' |
+-------+------+-----------------------------------------------+

SELECT * FROM INFORMATION_SCHEMA.SEQUENCES\G
*************************** 1. row ***************************
       SEQUENCE_CATALOG: def
        SEQUENCE_SCHEMA: test
          SEQUENCE_NAME: s
              DATA_TYPE: tinyint
      NUMERIC_PRECISION: 8
NUMERIC_PRECISION_RADIX: 2
          NUMERIC_SCALE: 0
            START_VALUE: 100
          MINIMUM_VALUE: -127
          MAXIMUM_VALUE: 126
              INCREMENT: 10
           CYCLE_OPTION: 0

Cache

Flushing the cache:

CREATE OR REPLACE SEQUENCE s START WITH 1 INCREMENT BY 1 MAXVALUE=10 CACHE=5;

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|          1 |
+------------+

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|          2 |
+------------+

FLUSH TABLES s;

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|          6 |
+------------+

FLUSH TABLES s;

SELECT NEXTVAL(s);
ERROR 4084 (HY000): Sequence 'test.s' has run out

Create table with a sequence as a default value

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

CREATE SEQUENCE s1;
CREATE TABLE t1 (a INT PRIMARY KEY DEFAULT nextval(s1), b INT);
INSERT INTO t1 (b) VALUES(1);
SELINT * FROM t1;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
+---+------+

See Also

  • Sequence Overview

  • ALTER SEQUENCE

  • DROP SEQUENCE

  • NEXT VALUE FOR

  • PREVIOUS VALUE FOR

  • SETVAL(). Set next value for the sequence.

  • AUTO INCREMENT

  • SHOW CREATE SEQUENCE

  • Information Schema SEQUENCES Table

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

DROP SEQUENCE

Syntax

DROP [TEMPORARY] SEQUENCE [IF EXISTS] [/*COMMENT TO SAVE*/]
    sequence_name [, sequence_name] ...

Description

DROP SEQUENCE removes one or more sequences created with CREATE SEQUENCE. You must have the DROP privilege for each sequence. MariaDB returns an error indicating by name which non-existing tables it was unable to drop, but it also drops all of the tables in the list that do exist.

Important: When a table is dropped, user privileges on the table are not automatically dropped. See GRANT.

If another connection is using the sequence, a metadata lock is active, and this statement will wait until the lock is released. This is also true for non-transactional tables.

For each referenced sequence, DROP SEQUENCE drops a temporary sequence with that name, if it exists. If it does not exist, and the TEMPORARY keyword is not used, it drops a non-temporary sequence with the same name, if it exists. The TEMPORARY keyword ensures that a non-temporary sequence will not accidentally be dropped.

Use IF EXISTS to prevent an error from occurring for sequences that do not exist. A NOTE is generated for each non-existent sequence when using IF EXISTS. See SHOW WARNINGS.

DROP SEQUENCE requires the DROP privilege.

Notes

DROP SEQUENCE only removes sequences, not tables. However, DROP TABLE can remove both sequences and tables.

See Also

  • Sequence Overview

  • CREATE SEQUENCE

  • ALTER SEQUENCE

  • DROP TABLE

  • Information Schema SEQUENCES Table

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

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 PREVIOUS VALUE for sequence_name.

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

NEXT VALUE for sequence_name

Syntax

NEXT VALUE FOR sequence

or

NEXTVAL(sequence_name)

or in Oracle mode (SQL_MODE=ORACLE)

sequence_name.nextval

NEXT VALUE FOR is ANSI SQL syntax while NEXTVAL() is PostgreSQL syntax.

Description

Generate next value for a SEQUENCE.

  • You can greatly speed up NEXT VALUE by creating the sequence with the CACHE option. If not, every NEXT VALUE usage will cause changes in the stored SEQUENCE table.

  • When using NEXT VALUE the value will be reserved at once and will not be reused, except if the SEQUENCE was created with CYCLE. This means that when you are using SEQUENCEs you have to expect gaps in the generated sequence numbers.

  • If one updates the SEQUENCE with SETVAL() or ALTER SEQUENCE ... RESTART, NEXT VALUE FOR will notice this and start from the next requested value.

  • FLUSH TABLES will close the sequence 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.

  • A server restart (or closing the current connection) also causes a drop of all cached values. The cached sequence numbers are reserved only for the current connection.

  • NEXT VALUE requires the INSERT privilege.

  • You can also use NEXT VALUE FOR sequence for column DEFAULT.

Once the sequence is complete, unless the sequence has been created with the CYCLE attribute (not the default), calling the function will result in Error 4084: Sequence has run out.

Examples

CREATE OR REPLACE SEQUENCE s MAXVALUE=2;

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|          1 |
+------------+

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|          2 |
+------------+

SELECT NEXTVAL(s);
ERROR 4084 (HY000): Sequence 'test.s' has run out

ALTER SEQUENCE s MAXVALUE=2 CYCLE;

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|          1 |
+------------+

See Also

  • Sequence Overview

  • CREATE SEQUENCE

  • ALTER SEQUENCE

  • PREVIOUS VALUE FOR

  • SETVAL(). Set next value for the sequence.

  • AUTO_INCREMENT

  • Information Schema SEQUENCES Table

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

NEXTVAL

NEXTVAL is a synonym for NEXT VALUE for sequence_name.

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

PREVIOUS VALUE FOR sequence_name

Syntax

PREVIOUS VALUE FOR sequence_name

or

LASTVAL(sequence_name)

or in Oracle mode (SQL_MODE=ORACLE)

sequence_name.currval

PREVIOUS VALUE FOR is IBM DB2 syntax while LASTVAL() is PostgreSQL syntax.

Description

Gets the most recent value in the current connection generated from a sequence.

  • If the sequence has not yet been used by the connection, PREVIOUS VALUE FOR returns NULL (the same thing applies with a new connection which doesn't see a last value for an existing sequence).

  • If a SEQUENCE has been dropped and re-created then it's treated as a new SEQUENCE and PREVIOUS VALUE FOR will return NULL.

  • Returns NULL if the sequence is complete.

  • FLUSH TABLES has no effect on PREVIOUS VALUE FOR.

  • Previous values for all used sequences are stored per connection until connection ends.

  • PREVIOUS VALUE FOR requires the SELECT privilege.

Examples

CREATE SEQUENCE s START WITH 100 INCREMENT BY 10;

SELECT PREVIOUS VALUE FOR s;
+----------------------+
| PREVIOUS VALUE FOR s |
+----------------------+
|                 NULL |
+----------------------+

# The function works for sequences only, if the table is used an error is generated
SELECT PREVIOUS VALUE FOR t;
ERROR 4089 (42S02): 'test.t' is not a SEQUENCE

# Call the NEXT VALUE FOR s:
SELECT NEXT VALUE FOR s;
+------------------+
| NEXT VALUE FOR s |
+------------------+
|              100 |
+------------------+

SELECT PREVIOUS VALUE FOR s;
+----------------------+
| PREVIOUS VALUE FOR s |
+----------------------+
|                  100 |
+----------------------+

Now try to start the new connection and check that the last value is still NULL, before updating the value in the new connection after the output of the new connection gets current value (110 in the example below). Note that first connection cannot see this change and the result of last value still remains the same (100 in the example above).

$ .mysql -uroot test -e"SELECT PREVIOUS VALUE FOR s; SELECT NEXT VALUE FOR s; SELECT PREVIOUS VALUE FOR s;"
+----------------------+
| PREVIOUS VALUE FOR s |
+----------------------+
|                 NULL |
+----------------------+
+------------------+
| NEXT VALUE FOR s |
+------------------+
|              110 |
+------------------+
+----------------------+
| PREVIOUS VALUE FOR s |
+----------------------+
|                  110 |
+----------------------+

Returns NULL if the sequence has run out:

CREATE OR REPLACE SEQUENCE s MAXVALUE=2;

SELECT NEXTVAL(s), LASTVAL(s);
+------------+------------+
| NEXTVAL(s) | LASTVAL(s) |
+------------+------------+
|          1 |          1 |
+------------+------------+

SELECT NEXTVAL(s), LASTVAL(s);
+------------+------------+
| NEXTVAL(s) | LASTVAL(s) |
+------------+------------+
|          2 |          2 |
+------------+------------+

SELECT NEXTVAL(s), LASTVAL(s);
ERROR 4084 (HY000): Sequence 'test.s' has run out

SELECT LASTVAL(s);
+------------+
| LASTVAL(s) |
+------------+
|       NULL |
+------------+

See Also

  • Sequence Overview

  • CREATE SEQUENCE

  • ALTER SEQUENCE

  • NEXT VALUE FOR

  • SETVAL(). Set next value for the sequence.

  • AUTO_INCREMENT

  • Information Schema SEQUENCES Table

  • Error 4084: Sequence has run out

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

SETVAL

Syntax

SETVAL(sequence_name, next_value, [is_used, [round]])

Description

Set the next value to be returned for a SEQUENCE.

This function is compatible with PostgreSQL syntax, extended with the round argument.

If the is_used argument is not given or is 1 or true, then the next used value will one after the given value. If is_used is 0 or false then the next generated value will be the given value.

If round is used then it will set the round value (or the internal cycle count, starting at zero) for the sequence. If round is not used, it's assumed to be 0.

next_value must be an integer literal.

For SEQUENCE tables defined with CYCLE (see CREATE SEQUENCE) one should use both next_value and round to define the next value. In this case the current sequence value is defined to be round, next_value.

The result returned by SETVAL() is next_value or NULL if the given next_value and round is smaller than the current value.

SETVAL() will not set the SEQUENCE value to a something that is less than its current value. This is needed to ensure that SETVAL() is replication-safe. If you want to set the SEQUENCE to a smaller number, use ALTER SEQUENCE.

If CYCLE is used, first round and then next_value are compared to see if the value is bigger than the current value.

Internally, in the MariaDB server, SETVAL() is used to inform replicas that a SEQUENCE has changed value. The replica may getSETVAL() statements out of order, but this is ok as only the biggest one will have an effect.

SETVAL requires the INSERT privilege.

Examples

SELECT setval(foo, 42);           -- Next nextval will return 43
SELECT setval(foo, 42, TRUE);     -- Same as above
SELECT setval(foo, 42, FALSE);    -- Next nextval will return 42

SETVAL setting higher and lower values on a sequence with an increment of 10:

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|         50 |
+------------+

SELECT SETVAL(s, 100);
+----------------+
| SETVAL(s, 100) |
+----------------+
|            100 |
+----------------+

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|        110 |
+------------+

SELECT SETVAL(s, 50);
+---------------+
| SETVAL(s, 50) |
+---------------+
|          NULL |
+---------------+

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|        120 |
+------------+

Example demonstrating round:

CREATE OR REPLACE SEQUENCE s1
  START WITH 1
  MINVALUE 1
  MAXVALUE 99
  INCREMENT BY 1 
  CACHE 20 
  CYCLE;

SELECT SETVAL(s1, 99, 1, 0);
+----------------------+
| SETVAL(s1, 99, 1, 0) |
+----------------------+
|                   99 |
+----------------------+

SELECT NEXTVAL(s1);
+-------------+
| NEXTVAL(s1) |
+-------------+
|           1 |
+-------------+

The following statement returns NULL, as the given next_value and round is smaller than the current value:

SELECT SETVAL(s1, 99, 1, 0);
+----------------------+
| SETVAL(s1, 99, 1, 0) |
+----------------------+
|                 NULL |
+----------------------+

SELECT NEXTVAL(s1);
+-------------+
| NEXTVAL(s1) |
+-------------+
|           2 |
+-------------+

Increasing the round from zero to 1 allows next_value to be returned:

SELECT SETVAL(s1, 99, 1, 1);
+----------------------+
| SETVAL(s1, 99, 1, 1) |
+----------------------+
|                   99 |
+----------------------+

SELECT NEXTVAL(s1);
+-------------+
| NEXTVAL(s1) |
+-------------+
|           1 |
+-------------+

See Also

  • Sequence Overview

  • ALTER SEQUENCE

  • CREATE SEQUENCE

  • NEXT VALUE FOR

  • PREVIOUS VALUE FOR

  • Information Schema SEQUENCES Table

  • Error 4084: Sequence has run out

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.

System-Versioned TablesApplication-Time PeriodsBitemporal Tables

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 CREATE TABLE statement with the PERIOD table option.

CREATE TABLE t1(
   name VARCHAR(50), 
   date_1 DATE,
   date_2 DATE,
   PERIOD FOR date_period(date_1, date_2));

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 ALTER TABLE statement now supports syntax for adding and removing time periods from a table. To add a period, use the ADD PERIOD clause:

CREATE OR REPLACE TABLE rooms (
 room_number INT,
 guest_name VARCHAR(255),
 checkin DATE,
 checkout DATE
 );

ALTER TABLE rooms ADD PERIOD FOR p(checkin,checkout);

To remove a period, use the DROP PERIOD clause:

ALTER TABLE rooms DROP PERIOD FOR p;

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

ALTER TABLE rooms ADD PERIOD IF NOT EXISTS FOR p(checkin,checkout);

ALTER TABLE rooms DROP PERIOD IF EXISTS FOR p;

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:

CREATE TABLE t1(
   name VARCHAR(50), 
   date_1 DATE,
   date_2 DATE,
   PERIOD FOR date_period(date_1, date_2));

INSERT INTO t1 (name, date_1, date_2) VALUES
    ('a', '1999-01-01', '2000-01-01'),
    ('b', '1999-01-01', '2018-12-12'),
    ('c', '1999-01-01', '2017-01-01'),
    ('d', '2017-01-01', '2019-01-01');

SELECT * FROM t1;
+------+------------+------------+
| name | date_1     | date_2     |
+------+------------+------------+
| a    | 1999-01-01 | 2000-01-01 |
| b    | 1999-01-01 | 2018-12-12 |
| c    | 1999-01-01 | 2017-01-01 |
| d    | 2017-01-01 | 2019-01-01 |
+------+------------+------------+

Then, run the DELETE FOR PORTION statement:

DELETE FROM t1
FOR PORTION OF date_period
    FROM '2001-01-01' TO '2018-01-01';
Query OK, 3 rows affected (0.028 sec)

SELECT * FROM t1 ORDER BY name;
+------+------------+------------+
| name | date_1     | date_2     |
+------+------------+------------+
| a    | 1999-01-01 | 2000-01-01 |
| b    | 1999-01-01 | 2001-01-01 |
| b    | 2018-01-01 | 2018-12-12 |
| c    | 1999-01-01 | 2001-01-01 |
| d    | 2018-01-01 | 2019-01-01 |
+------+------------+------------+

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.

  • d, with values ranging from 2017 to 2019, where only the lower value falls within the portion to be deleted, has been shrunk to 2018 to 2019.

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 UPDATE 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:

TRUNCATE t1;

INSERT INTO t1 (name, date_1, date_2) VALUES
    ('a', '1999-01-01', '2000-01-01'),
    ('b', '1999-01-01', '2018-12-12'),
    ('c', '1999-01-01', '2017-01-01'),
    ('d', '2017-01-01', '2019-01-01');

SELECT * FROM t1;
+------+------------+------------+
| name | date_1     | date_2     |
+------+------------+------------+
| a    | 1999-01-01 | 2000-01-01 |
| b    | 1999-01-01 | 2018-12-12 |
| c    | 1999-01-01 | 2017-01-01 |
| d    | 2017-01-01 | 2019-01-01 |
+------+------------+------------+

Then run the update:

UPDATE t1 FOR PORTION OF date_period
  FROM '2000-01-01' TO '2018-01-01' 
SET name = CONCAT(name,'_original');

SELECT * FROM t1 ORDER BY name;
+------------+------------+------------+
| name       | date_1     | date_2     |
+------------+------------+------------+
| a          | 1999-01-01 | 2000-01-01 |
| b          | 1999-01-01 | 2000-01-01 |
| b          | 2018-01-01 | 2018-12-12 |
| b_original | 2000-01-01 | 2018-01-01 |
| c          | 1999-01-01 | 2000-01-01 |
| c_original | 2000-01-01 | 2017-01-01 |
| d          | 2018-01-01 | 2019-01-01 |
| d_original | 2017-01-01 | 2018-01-01 |
+------------+------------+------------+
  • 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.

  • d, with values ranging from 2017 to 2019, where only the lower value falls within the portion to be updated, has been shrunk to 2018 to 2019.

  • Original rows affected by the update have "_original" appended to the name.

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:

CREATE OR REPLACE TABLE rooms (
 room_number INT,
 guest_name VARCHAR(255),
 checkin DATE,
 checkout DATE,
 PERIOD FOR p(checkin,checkout)
 );

INSERT INTO rooms VALUES 
 (1, 'Regina', '2020-10-01', '2020-10-03'),
 (2, 'Cochise', '2020-10-02', '2020-10-05'),
 (1, 'Nowell', '2020-10-03', '2020-10-07'),
 (2, 'Eusebius', '2020-10-04', '2020-10-06');

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.

CREATE OR REPLACE TABLE rooms (
 room_number INT,
 guest_name VARCHAR(255),
 checkin DATE,
 checkout DATE,
 PERIOD FOR p(checkin,checkout),
 UNIQUE (room_number, p WITHOUT OVERLAPS)
 );

INSERT INTO rooms VALUES 
 (1, 'Regina', '2020-10-01', '2020-10-03'),
 (2, 'Cochise', '2020-10-02', '2020-10-05'),
 (1, 'Nowell', '2020-10-03', '2020-10-07'),
 (2, 'Eusebius', '2020-10-04', '2020-10-06');
ERROR 1062 (23000): Duplicate entry '2-2020-10-06-2020-10-04' for key 'room_number'

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:

  • INFORMATION_SCHEMA.PERIODS view.

  • INFORMATION_SCHEMA.KEY_PERIOD_USAGE view.

  • Additional columns IS_SYSTEM_TIME_PERIOD_START and IS_SYSTEM_TIME_PERIOD_END in the INFORMATION_SCHEMA.COLUMNS view.

Further Examples

The implicit change from NULL to NOT NULL:

CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  `d1` datetime DEFAULT NULL,
  `d2` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE t2 ADD PERIOD FOR p(d1,d2);

SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
* //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.
* //d//, with values ranging from 2017 to 2019, where only the lower value falls within the portion to be updated, has been *shrunk* to 2018 to 2019. 
* Original rows affected by the update have "_original" appended to the ##name## field.
  `id` int(11) DEFAULT NULL,
  `d1` datetime NOT NULL,
  `d2` datetime NOT NULL,
  PERIOD FOR `p` (`d1`, `d2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

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

CREATE OR REPLACE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  `d1` datetime DEFAULT NULL,
  `d2` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO t2(id) VALUES(1);

ALTER TABLE t2 ADD PERIOD FOR p(d1,d2);
ERROR 1265 (01000): Data truncated for column 'd1' at row 1

See Also

  • System-versioned Tables

  • Bitemporal Tables

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

Bitemporal Tables

Bitemporal tables are tables that use versioning both at the system and application-time period levels.

Using Bitemporal Tables

To create a bitemporal table, use:

CREATE TABLE test.t3 (
   date_1 DATE,
   date_2 DATE,
   row_start TIMESTAMP(6) AS ROW START INVISIBLE,
   row_end TIMESTAMP(6) AS ROW END INVISIBLE,
   PERIOD FOR application_time(date_1, date_2),
   PERIOD FOR system_time(row_start, row_end))
WITH SYSTEM VERSIONING;

Note that, while system_time here is also a time period, it cannot be used in DELETE FOR PORTION or UPDATE FOR PORTION statements:

DELETE FROM test.t3 
FOR PORTION OF system_time 
    FROM '2000-01-01' TO '2018-01-01';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds 
  to your MariaDB server version for the right syntax to use near
  'of system_time from '2000-01-01' to '2018-01-01'' at line 1

See Also

  • System-versioned Tables

  • Application-time Periods

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

System-Versioned Tables

MariaDB supports temporal data tables in the form of system-versioning tables (allowing you to query and operate on historic data, discussed below), application-time periods (allow you to query and operate on a temporal range of data), and bitemporal tables (which combine both system-versioning and application-time periods).

System-Versioned Tables

System-versioned tables store the history of all changes, not only data which is currently applicable. This allows data analysis for any point in time, auditing of changes and comparison of data from different points in time. Typical uses cases are:

  • Forensic analysis & legal requirements to store data for N years.

  • Data analytics (retrospective, trends etc.), e.g. to get your staff information as of one year ago.

  • Point-in-time recovery - recover a table state as of particular point in time.

System-versioned tables were first introduced in the SQL:2011 standard.

Creating a System-Versioned Table

The CREATE TABLE syntax has been extended to permit creating a system-versioned table. To be system-versioned, according to SQL:2011, a table must have two generated columns, a period, and a special table option clause:

CREATE TABLE t(
   x INT,
   start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
   end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
   PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
) WITH SYSTEM VERSIONING;

In MariaDB, you can use a simplified syntax:

CREATE TABLE t (
   x INT
) WITH SYSTEM VERSIONING;

In the latter case, no extra columns will be created, and they won't clutter the output of, say, SELECT * FROM t. The versioning information will still be stored, and it can be accessed via the pseudo-columns ROW_START and ROW_END:

SELECT x, ROW_START, ROW_END FROM t;

Adding or Removing System Versioning To/From a Table

An existing table can be altered to enable system versioning for it.

CREATE TABLE t(
  x INT
);
ALTER TABLE t ADD SYSTEM VERSIONING;
SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `x` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING

Similarly, system versioning can be removed from a table:

ALTER TABLE t DROP SYSTEM VERSIONING;
SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `x` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

You can also add system versioning with all columns created explicitly:

ALTER TABLE t ADD COLUMN ts TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
              ADD COLUMN te TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
              ADD PERIOD FOR SYSTEM_TIME(ts, te),
              ADD SYSTEM VERSIONING;
SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `x` int(11) DEFAULT NULL,
  `ts` timestamp(6) GENERATED ALWAYS AS ROW START,
  `te` timestamp(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (`ts`, `te`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING

MariaDB starting with 11.7

It is possible to convert a versioned table from implicit to explicit row_start/row_end columns. Note that, in order to do any ALTER on a system versioned table, system_versioning_alter_history must be set to KEEP.

CREATE OR REPLACE TABLE t1 (x INT) WITH SYSTEM VERSIONING;

SET system_versioning_alter_history = keep;

ALTER TABLE t1 ADD COLUMN rs TIMESTAMP(6) AS ROW START, 
  ADD COLUMN re TIMESTAMP(6) AS ROW END, ADD PERIOD FOR SYSTEM_TIME (rs,re)

It is not possible to convert a versioned table from implicit to explicit row_start/row_end columns. Doing so results in a duplicate row error:

CREATE OR REPLACE TABLE t1 (x INT) WITH SYSTEM VERSIONING;

SET system_versioning_alter_history = keep;

ALTER TABLE t1 ADD COLUMN rs TIMESTAMP(6) AS ROW START, 
  ADD COLUMN re TIMESTAMP(6) AS ROW END, ADD PERIOD FOR SYSTEM_TIME (rs,re);

ERROR 4134 (HY000): Duplicate ROW START column `rs`

Inserting Data

When data is inserted into a system-versioned table, it is given a row_start value of the current timestamp, and a row_end value of FROM_UNIXTIME (2147483647.999999). The current timestamp can be adjusted by setting the timestamp system variable:

SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2022-10-24 23:09:38 |
+---------------------+
 
INSERT INTO t VALUES(1);
 
SET @@timestamp = UNIX_TIMESTAMP('2033-10-24');

INSERT INTO t VALUES(2);
 
SET @@timestamp = default;

INSERT INTO t VALUES(3);
 
SELECT a,row_start,row_end FROM t;
+------+----------------------------+----------------------------+
| a    | row_start                  | row_end                    |
+------+----------------------------+----------------------------+
|    1 | 2022-10-24 23:09:38.951347 | 2038-01-19 05:14:07.999999 |
|    2 | 2033-10-24 00:00:00.000000 | 2038-01-19 05:14:07.999999 |
|    3 | 2022-10-24 23:09:38.961857 | 2038-01-19 05:14:07.999999 |
+------+----------------------------+----------------------------+

Querying Historical Data

SELECT

To query the historical data one uses the clause FOR SYSTEM_TIME directly after the table name (before the table alias, if any). SQL:2011 provides three syntactic extensions:

  • AS OF is used to see the table as it was at a specific point in time in the past:

SELECT * FROM t FOR SYSTEM_TIME AS OF TIMESTAMP'2016-10-09 08:07:06';
  • BETWEEN start AND end will show all rows that were visible at any point between two specified points in time. It works inclusively, a row visible exactly at start or exactly at end will be shown too.

SELECT * FROM t FOR SYSTEM_TIME BETWEEN (NOW() - INTERVAL 1 YEAR) AND NOW();
  • FROM start TO end will also show all rows that were visible at any point between two specified points in time, including start, but excluding end.

SELECT * FROM t FOR SYSTEM_TIME FROM '2016-01-01 00:00:00' TO '2017-01-01 00:00:00';

Additionally, MariaDB implements a non-standard extension.

  • ALL will show all rows, historical and current.

SELECT * FROM t FOR SYSTEM_TIME ALL;

If the FOR SYSTEM_TIME clause is not used, the table shows the current data. This is usually the same as if you had specified FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP, unless you've adjusted the row_start value:

CREATE OR REPLACE TABLE t (a int) WITH SYSTEM VERSIONING;

SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2022-10-24 23:43:37 |
+---------------------+

INSERT INTO t VALUES (1);

SET @@timestamp = UNIX_TIMESTAMP('2033-03-03');

INSERT INTO t VALUES (2);

DELETE FROM t;

SET @@timestamp = default;


SELECT a, row_start, row_end FROM t FOR SYSTEM_TIME ALL;
+------+----------------------------+----------------------------+
| a    | row_start                  | row_end                    |
+------+----------------------------+----------------------------+
|    1 | 2022-10-24 23:43:37.192725 | 2033-03-03 00:00:00.000000 |
|    2 | 2033-03-03 00:00:00.000000 | 2033-03-03 00:00:00.000000 |
+------+----------------------------+----------------------------+
2 rows in set (0.000 sec)


SELECT a, row_start, row_end FROM t FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP;
+------+----------------------------+----------------------------+
| a    | row_start                  | row_end                    |
+------+----------------------------+----------------------------+
|    1 | 2022-10-24 23:43:37.192725 | 2033-03-03 00:00:00.000000 |
+------+----------------------------+----------------------------+
1 row in set (0.000 sec)


SELECT a, row_start, row_end FROM t;
Empty set (0.001 sec)

If the FOR SYSTEM_TIME clause is not used, the table shows the current data. This is usually the same as if you had specified FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP, unless you've adjusted the row_start value (only possible by setting the secure_timestamp variable):

CREATE OR REPLACE TABLE t (a int) WITH SYSTEM VERSIONING;

SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2022-10-24 23:43:37 |
+---------------------+

INSERT INTO t VALUES (1);

SET @@timestamp = UNIX_TIMESTAMP('2033-03-03');

INSERT INTO t VALUES (2);

DELETE FROM t;

SET @@timestamp = default;


SELECT a, row_start, row_end FROM t FOR SYSTEM_TIME ALL;
+------+----------------------------+----------------------------+
| a    | row_start                  | row_end                    |
+------+----------------------------+----------------------------+
|    1 | 2022-10-24 23:43:37.192725 | 2033-03-03 00:00:00.000000 |
|    2 | 2033-03-03 00:00:00.000000 | 2033-03-03 00:00:00.000000 |
+------+----------------------------+----------------------------+
2 rows in set (0.000 sec)


SELECT a, row_start, row_end FROM t FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP;
+------+----------------------------+----------------------------+
| a    | row_start                  | row_end                    |
+------+----------------------------+----------------------------+
|    1 | 2022-10-24 23:43:37.192725 | 2033-03-03 00:00:00.000000 |
+------+----------------------------+----------------------------+
1 row in set (0.000 sec)


SELECT a, row_start, row_end FROM t;
Empty set (0.001 sec)

Views and Subqueries

When a system-versioned table is used in a view or in a subquery in the from clause, FOR SYSTEM_TIME can be used directly in the view or subquery body, or (non-standard) applied to the whole view when it's being used in a SELECT:

CREATE VIEW v1 AS SELECT * FROM t FOR SYSTEM_TIME AS OF TIMESTAMP'2016-10-09 08:07:06';

Or:

CREATE VIEW v1 AS SELECT * FROM t;
SELECT * FROM v1 FOR SYSTEM_TIME AS OF TIMESTAMP'2016-10-09 08:07:06';

Use in Replication and Binary Logs

Tables that use system-versioning implicitly add the row_end column to the Primary Key. While this is generally not an issue for most use cases, it can lead to problems when re-applying write statements from the binary log or in replication environments, where a primary retries an SQL statement on the replica.

Specifically, these writes include a value on the row_end column containing the timestamp from when the write was initially made. The re-occurrence of the Primary Key with the old system-versioning columns raises an error due to the duplication.

To mitigate this with MariaDB Replication, set the secure_timestamp system variable to YES on the replica. When set, the replica uses its own system clock when applying to the row log, meaning that the primary can retry as many times as needed without causing a conflict. The retries generate new historical rows with new values for the row_start and row_end columns.

Transaction-Precise History in InnoDB

A point in time when a row was inserted or deleted does not necessarily mean that a change became visible at the same moment. With transactional tables, a row might have been inserted in a long transaction, and became visible hours after it was inserted.

For some applications — for example, when doing data analytics on one-year-old data — this distinction does not matter much. For others — forensic analysis — it might be crucial.

MariaDB supports transaction-precise history (only for the InnoDB storage engine) that allows seeing the data exactly as it would've been seen by a new connection doing a SELECT at the specified point in time — rows inserted before that point, but committed after will not be shown.

To use transaction-precise history, InnoDB needs to remember not timestamps, but transaction identifier per row. This is done by creating generated columns as BIGINT UNSIGNED, not TIMESTAMP(6):

CREATE TABLE t(
   x INT,
   start_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW START,
   end_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW END,
   PERIOD FOR SYSTEM_TIME(start_trxid, end_trxid)
) WITH SYSTEM VERSIONING;

These columns must be specified explicitly, but they can be made INVISIBLE to avoid cluttering SELECT * output.

Note that if you are using an engine that does not support system versioning with transaction ids, you will get an error like "start_trxid must be of type TIMESTAMP(6) for system-versioned table t".

When one uses transaction-precise history, one can optionally use transaction identifiers in the FOR SYSTEM_TIME clause:

SELECT * FROM t FOR SYSTEM_TIME AS OF TRANSACTION 12345;

This shows the data, exactly as it was seen by the transaction with the identifier 12345.

Data for this feature is stored in the mysql.transaction_registry table.

Storing the History Separately

When the history is stored together with the current data, it increases the size of the table, so current data queries — table scans and index searches — will take more time, because they will need to skip over historical data. If most queries on that table use only current data, it might make sense to store the history separately, to reduce the overhead from versioning.

This is done by partitioning the table by SYSTEM_TIME. Because of the partition pruning optimization, all current data queries will only access one partition, the one that stores current data.

This example shows how to create such a partitioned table:

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME (
    PARTITION p_hist HISTORY,
    PARTITION p_cur CURRENT
  );

In this example, all history will be stored in the partition p_hist while all current data will be in the partition p_cur. The table must have exactly one current partition and at least one historical partition.

Partitioning by SYSTEM_TIME also supports automatic partition rotation. You can rotate historical partitions by time or by size. This example shows how to rotate partitions by size:

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME LIMIT 100000 (
    PARTITION p0 HISTORY,
    PARTITION p1 HISTORY,
    PARTITION pcur CURRENT
  );

MariaDB starts writing history rows into partition p0, and at the end of the statement that wrote the 100000th row, MariaDB will switch to partition p1. There are only two historical partitions, so when p1 overflows, MariaDB will issue a warning, but will continue writing into it.

Similarly, one can rotate partitions by time:

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME INTERVAL 1 WEEK (
    PARTITION p0 HISTORY,
    PARTITION p1 HISTORY,
    PARTITION p2 HISTORY,
    PARTITION pcur CURRENT
  );

This means that the history for the first week after the table was created will be stored in p0. The history for the second week — in p1, and all later history will go into p2. One can see the exact rotation time for each partition in the INFORMATION_SCHEMA.PARTITIONS table.

It is possible to combine partitioning by SYSTEM_TIME and subpartitions:

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME
    SUBPARTITION BY KEY (x)
    SUBPARTITIONS 4 (
    PARTITION ph HISTORY,
    PARTITION pc CURRENT
  );

Default Partitions

Default Partitions are available from MariaDB 10.5.

Since partitioning by current and historical data is such a typical use case, it is possible to use a simplified statement to do so. Instead of the following statement:

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING 
  PARTITION BY SYSTEM_TIME (
    PARTITION p0 HISTORY,  
    PARTITION pn CURRENT 
);

You can use:

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING 
  PARTITION BY SYSTEM_TIME;

You can also specify the number of partitions, which is useful if you want to rotate history by time, for example:

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING 
  PARTITION BY SYSTEM_TIME 
    INTERVAL 1 MONTH 
    PARTITIONS 12;

Specifying the number of partitions without specifying a rotation condition results in a warning:

CREATE OR REPLACE TABLE t (x INT) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME PARTITIONS 12;
Query OK, 0 rows affected, 1 warning (0.518 sec)

Warning (Code 4115): Maybe missing parameters: no rotation condition for multiple HISTORY partitions.

While specifying only one partition results in an error:

CREATE OR REPLACE TABLE t (x INT) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME PARTITIONS 1;
ERROR 4128 (HY000): Wrong partitions for `t`: must have at least one HISTORY and exactly one last CURRENT

Automatically Creating Partitions

Creating partitions with AUTO is available from MariaDB 10.9.1.

The AUTO keyword can be used to automatically create history partitions:

CREATE TABLE t1 (x INT) WITH SYSTEM VERSIONING
    PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR AUTO;

CREATE TABLE t1 (x INT) WITH SYSTEM VERSIONING
   PARTITION BY SYSTEM_TIME INTERVAL 1 MONTH
   STARTS '2021-01-01 00:00:00' AUTO PARTITIONS 12;

CREATE TABLE t1 (x INT) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME LIMIT 1000 AUTO;

Or with explicit partitions:

CREATE TABLE t1 (x INT) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR AUTO
  (PARTITION p0 HISTORY, PARTITION pn CURRENT);

To disable or enable auto-creation, one can use ALTER TABLE by adding or removing AUTO from the partitioning specification:

CREATE TABLE t1 (x INT) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR AUTO;

# Disables auto-creation:
ALTER TABLE t1 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR;

# Enables auto-creation:
ALTER TABLE t1 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR AUTO;

If the rest of the partitioning specification is identical to CREATE TABLE, no repartitioning will be done (for details see MDEV-27328).

Removing Old History

Because it stores all the history, a system-versioned table might grow very large over time. There are many options to trim down the space and remove the old history.

One can completely drop the versioning from the table and add it back again, this deletes all the history:

ALTER TABLE t DROP SYSTEM VERSIONING;
ALTER TABLE t ADD SYSTEM VERSIONING;

It might be a rather time-consuming operation, though, as the table needs to be rebuilt, possibly twice (depending on the storage engine).

Another option would be to use partitioning and drop some of historical partitions:

ALTER TABLE t DROP PARTITION p0;

You cannot drop a current partition or the only historical partition.

As a third option; you can use a variant of the DELETE statement to prune the history:

DELETE HISTORY FROM t;

Or only old history up to a specific point in time:

DELETE HISTORY FROM t BEFORE SYSTEM_TIME '2016-10-09 08:07:06';

Or to a specific transaction (with BEFORE SYSTEM_TIME TRANSACTION xxx).

To protect the integrity of the history, this statement requires a special DELETE HISTORY privilege.

Currently, using the DELETE HISTORY statement with a BEFORE SYSTEM_TIME greater than the ROW_END of the active records (as a TIMESTAMP, this has a maximum value of '2038-01-19 03:14:07' UTC) results in the historical records being dropped, and the active records being deleted and moved to history. See MDEV-25468.

The TRUNCATE TABLE statement drops all historical records from a system-versioned table.

Historic data is protected from TRUNCATE statements, as per the SQL standard, and an Error 4137 is instead raised:

TRUNCATE t;
ERROR 4137 (HY000): System-versioned tables do not support TRUNCATE TABLE

Excluding Columns From Versioning

Another MariaDB extension allows one to version only a subset of columns in a table. This is useful, for example, if you have a table with user information that should be versioned, but one column is, let's say, a login counter that is incremented often and is not interesting to version. Such a column can be excluded from versioning by declaring it WITHOUT VERSIONING

CREATE TABLE t (
   x INT,
   y INT WITHOUT SYSTEM VERSIONING
) WITH SYSTEM VERSIONING;

A column can also be declared WITH VERSIONING, that will automatically make the table versioned. The statement below is equivalent to the one above:

CREATE TABLE t (
   x INT WITH SYSTEM VERSIONING,
   y INT
);

System Variables

A number of system variables are related to system-versioned tables:

system_versioning_alter_history

  • Description: SQL:2011 does not allow ALTER TABLE on system-versioned tables. When this variable is set to ERROR, an attempt to alter a system-versioned table will result in an error. When this variable is set to KEEP, ALTER TABLE will be allowed, but the history will become incorrect — querying historical data will show the new table structure. This mode is still useful, for example, when adding new columns to a table. Note that if historical data contains or would contain nulls, attempting to ALTER these columns to be NOT NULL will return an error (or warning if strict_mode is not set).

  • Command line: --system-versioning-alter-history=value

  • Scope: Global, Session

  • Dynamic: Yes

  • Type: Enum

  • Default Value: ERROR

  • Valid Values: ERROR, KEEP

system_versioning_asof

  • Description: If set to a specific timestamp value, an implicit FOR SYSTEM_TIME AS OF clause will be applied to all queries. This is useful if one wants to do many queries for history at the specific point in time. Set it to 'DEFAULT' to restore the default behavior. Has no effect on DML, so queries such as INSERT .. SELECT and REPLACE .. SELECT need to state AS OF explicitly.

Note: You need to use quotes around the name 'DEFAULT' when setting the session value, unquoted literal DEFAULT will restore the current global value instead.

  • Command line: None

  • Scope: Global, Session

  • Dynamic: Yes

  • Type: Varchar

  • Default Value: DEFAULT

system_versioning_innodb_algorithm_simple

  • Description: Never fully implemented and removed in the following release.

  • Command line: --system-versioning-innodb-algorithm-simple[={0|1}]

  • Scope: Global, Session

  • Dynamic: Yes

  • Type: Boolean

  • Default Value: ON

  • Introduced: MariaDB 10.3.4

  • Removed: MariaDB 10.3.5

system_versioning_insert_history

  • Description: Allows direct inserts into ROW_START and ROW_END columns if secure_timestamp allows changing timestamp.

  • Command line: --system-versioning-insert-history[={0|1}]

  • Scope: Global, Session

  • Dynamic: Yes

  • Type: Boolean

  • Default Value: OFF

  • Introduced: MariaDB 10.11.0

Limitations

  • Versioning clauses can not be applied to generated (virtual and persistent) columns.

  • mariadb-dump did not read historical rows from versioned tables, and so historical data would not be backed up. Also, a restore of the timestamps would not be possible as they cannot be defined by an insert/a user. From MariaDB 10.11, use the -H or --dump-history options to include the history.

See Also

  • Application-Time Periods

  • Bitemporal Tables

  • mysql.transaction_registry Table

  • MariaDB Temporal Tables (video)

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

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 OverviewCREATE TABLE with VectorsVector System VariablesVector FunctionsVECTORVector Framework Integrations

Vector Overview

Cover

WEBINAR

The Next Generation of MariaDB: Powered by Vector Search

Watch Now

Vectors are available from MariaDB 11.7.

MariaDB Vector is a feature that allows MariaDB Server to perform as a relational vector database. Vectors generated by an AI model can be stored and searched in MariaDB.

The initial implementation uses the modified HNSW algorithm for searching in the vector index (to solve the so-called Approximate Nearest Neighbor problem), and defaults to Euclidean distance. Concurrent reads/writes and all transaction isolation levels are supported.

MariaDB uses int16 for indexes, which gives 15 bits to store the value, rather than 10 bits for float16.

Creating

Vectors can be defined using VECTOR INDEX for the index definition, and using the VECTOR data type in the CREATE TABLE statement.

CREATE TABLE v (
     id INT PRIMARY KEY,
     v VECTOR(5) NOT NULL,
     VECTOR INDEX (v)
);

The distance function used to build the vector index can be euclidean (the default) or cosine. An additional option, M, can be used to configure the vector index. Larger values mean slower SELECT and INSERT statements, larger index size and higher memory consumption but more accurate results. The valid range is from 3 to 200.

CREATE TABLE embeddings (
        doc_id BIGINT UNSIGNED PRIMARY KEY,
        embedding VECTOR(1536) NOT NULL,
        VECTOR INDEX (embedding) M=8 DISTANCE=cosine
);

Inserting

Vector columns store 32-bit IEEE 754 floating point numbers.

INSERT INTO v VALUES 
     (1, x'e360d63ebe554f3fcdbc523f4522193f5236083d'),
     (2, x'f511303f72224a3fdd05fe3eb22a133ffae86a3f'),
     (3,x'f09baa3ea172763f123def3e0c7fe53e288bf33e'),
     (4,x'b97a523f2a193e3eb4f62e3f2d23583e9dd60d3f'),
     (5,x'f7c5df3e984b2b3e65e59d3d7376db3eac63773e'),
     (6,x'de01453ffa486d3f10aa4d3fdd66813c71cb163f'),
     (7,x'76edfc3e4b57243f10f8423fb158713f020bda3e'),
     (8,x'56926c3fdf098d3e2c8c5e3d1ad4953daa9d0b3e'),
     (9,x'7b713f3e5258323f80d1113d673b2b3f66e3583f'),
     (10,x'6ca1d43e9df91b3fe580da3e1c247d3f147cf33e');

Alternatively, you can use VEC_FromText() function:

INSERT INTO v VALUES
  (1,Vec_FromText('[0.418708,0.809902,0.823193,0.598179,0.0332549]')),
  (2,Vec_FromText('[0.687774,0.789588,0.496138,0.57487,0.917617]')),
  (3,Vec_FromText('[0.333221,0.962687,0.467263,0.448235,0.475671]')),
  (4,Vec_FromText('[0.822185,0.185643,0.683452,0.211072,0.554056]')),
  (5,Vec_FromText('[0.437057,0.167281,0.0770977,0.428638,0.241591]')),
  (6,Vec_FromText('[0.76956,0.926895,0.803376,0.0157961,0.589042]')),
  (7,Vec_FromText('[0.493999,0.641957,0.761598,0.94276,0.425865]')),
  (8,Vec_FromText('[0.924108,0.275466,0.0543329,0.0731585,0.136344]')),
  (9,Vec_FromText('[0.186956,0.69666,0.0356002,0.668875,0.84722]')),
  (10,Vec_FromText('[0.415294,0.609278,0.426765,0.988832,0.475556]'));

Querying

For vector indexes built with the euclidean function, VEC_DISTANCE_EUCLIDEAN can be used. It calculates a Euclidean (L2) distance between two points:

SELECT id FROM v ORDER BY 
  VEC_DISTANCE_EUCLIDEAN(v, x'6ca1d43e9df91b3fe580da3e1c247d3f147cf33e');
+----+
| id |
+----+
| 10 |
|  7 |
|  3 |
|  9 |
|  2 |
|  1 |
|  5 |
|  4 |
|  6 |
|  8 |
+----+

Most commonly, this kind of query is done with a limit, for example to return vectors that are closest to a given vector, such as from a user search query, image or a song fragment:

SELECT id FROM v 
  ORDER BY VEC_DISTANCE_EUCLIDEAN(v, x'6ca1d43e9df91b3fe580da3e1c247d3f147cf33e') 
  LIMIT 2;
+----+
| id |
+----+
| 10 |
|  7 |
+----+

For vector indexes built with the cosine function, VEC_DISTANCE_COSINE can be used. It calculates a Cosine distance between two vectors:

SELECT VEC_DISTANCE_COSINE(VEC_FROMTEXT('[1,2,3]'), VEC_FROMTEXT('[3,5,7]'));

The VEC_DISTANCE function is a generic function that behaves either as VEC_DISTANCE_EUCLIDEAN or VEC_DISTANCE_COSINE, depending on the underlying index type:

SELECT id FROM v 
  ORDER BY VEC_DISTANCE(v, x'6ca1d43e9df91b3fe580da3e1c247d3f147cf33e');
+----+
| id |
+----+
| 10 |
|  7 |
|  3 |
|  9 |
|  2 |
|  1 |
|  5 |
|  4 |
|  6 |
|  8 |
+----+

System Variables

There are a number of system variables used for vectors. See Vector System Variables.

Vector Framework Integrations

MariaDB Vector is integrated in several frameworks, see Vector Framework Integrations.

See Also

  • Get to know MariaDB’s Rocket-Fast Native Vector Search - Sergei Golubchyk (video)

  • MariaDB Vector, a new Open Source vector database that you are already familiar by Sergei Golubchik (video)

  • AI first applications with MariaDB Vector - Vicentiu Ciorbaru (video)

  • MariaDB Vector: A storage engine for LLMs - Kaj Arnö and Jonah Harris (video)

  • Try RAG with MariaDB Vector on your own MariaDB data!

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

CREATE TABLE with Vectors

Cover

WEBINAR

The Next Generation of MariaDB: Powered by Vector Search

Watch Now

Vectors are available from MariaDB 11.7.

MariaDB has a dedicated VECTOR(N) data type with a built-in data validation. N is the number of dimensions that all vector values in the column have.

  • Vector indexes are dimensionality-specific.

  • All vectors inserted into an indexed column must match the index's target dimensionality.

  • Inserting vectors with different dimensionalities will result in an error.

Consider the following table:

CREATE TABLE embeddings (
        doc_id BIGINT UNSIGNED PRIMARY KEY,
        embedding VECTOR(1536)
);

To have a fast vector search, you have to index the vector column, creating a VECTOR index:

CREATE TABLE embeddings (
        doc_id BIGINT UNSIGNED PRIMARY KEY,
        embedding VECTOR(1536) NOT NULL,
        VECTOR INDEX (embedding)
);

Note that there can be only one vector index in the table, and the indexed vector column must be NOT NULL.

There are two options that can be used to configure the vector index:

  • M — Larger values mean slower SELECT and INSERT statements, larger index size and higher memory consumption, but more accurate results. The valid range is from 3 to 200.

  • DISTANCE — Distance function to build the vector index for. Searches using a different distance function will not be able to use a vector index. Valid values are cosine and euclidean (the default).

CREATE TABLE embeddings (
        doc_id BIGINT UNSIGNED PRIMARY KEY,
        embedding VECTOR(1536) NOT NULL,
        VECTOR INDEX (embedding) M=8 DISTANCE=cosine
);

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

Vector System Variables

Cover

WEBINAR

The Next Generation of MariaDB: Powered by Vector Search

Watch Now

Vectors are available from MariaDB 11.7.

This page documents system variables related to Vectors.

See Server System Variables for instructions on setting them.

Also see the Full list of MariaDB options, system and status variables.

mhnsw_default_distance

  • Description: Specifies the default distance metric for MHNSW vector indexing. This is used when the DISTANCE option is not explicitly defined during index creation.

  • Command line: --mhnsw-default-distance=val

  • Scope: Global, Session

  • Dynamic: Yes

  • Data Type: enum

  • Default Value: euclidean

  • Valid Values:

    • euclidean Calculates straight-line distance between vectors. Best for spatial data, images, etc, when absolute magnitude matters.

    • cosine Measures directional similarity between vectors. Ideal for text embeddings, semantic search, and when vector magnitude is less important.

  • Introduced: MariaDB 11.7.1

mhnsw_default_m

  • Description: Defines the default value for the M parameter in MHNSW vector indexing. The M parameter controls the number of connections per layer in the graph structure, influencing the balance between search performance and index size.

    • Larger M → Better search accuracy, but larger index size and slower updates and searches.

    • Smaller M → Faster updates and searches, smaller index, but potentially less accurate search.

  • Command line: --mhnsw-default-m=#

  • Scope: Global, Session

  • Dynamic: Yes

  • Data Type: int unsigned

  • Default Value: 6

  • Range: 3 to 200

  • Introduced: MariaDB 11.7.1

mhnsw_ef_search

  • Description: Defines the minimal number of result candidates to look for in the vector index for ORDER BY ... LIMIT N queries. The search will never search for less rows than that, even if LIMIT is smaller. This notably improves the search quality at low LIMIT values, at the expense of search time. Higher values may increase search quality but will also impact query performance.

  • Command line: --mhnsw-ef-search=#

  • Scope: Global, Session

  • Dynamic: Yes

  • Data Type: int unsigned

  • Default Value: 20

  • Range: 1 to 10000

  • Introduced: MariaDB 11.7.1

mhnsw_max_cache_size

  • Description: Upper limit for one MHNSW vector index cache. This limits the amount of memory that can be used for caching the index, ensuring efficient memory utilization.

  • Command line: --mhnsw-max-cache-size=#

  • Scope: Global

  • Dynamic: Yes

  • Data Type: bigint unsigned

  • Default Value: 16777216 (16 MB)

  • Range: 1048576 to 18446744073709551615

  • Introduced: MariaDB 11.7.1

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

Vector Framework Integrations

Cover

WEBINAR

The Next Generation of MariaDB: Powered by Vector Search

Watch Now

Vectors are available from MariaDB 11.7.

MariaDB Vector has integrations in several frameworks.

AI Framework Integrations

  • LangChain, MariaDB Vector Store - Python

  • LangChain.js, MariaDB Vector Store - Node.js

  • LangChain4j, MariaDB Embedding Store - Java

  • LlamaIndex, MariaDB Vector Store - Python

  • MCP (Model Context Protocol), MariaDB MCP server - Python

  • Spring AI, MariaDB Vector Store - Java

  • VectorDBBench - benchmarking for vector databases

Potential Future Integrations

  • AutoGen - agent to agent

  • DSPy - workflow

  • Feast - machine learning (not GenAI)

  • LangGraph - agentic workflow

  • Open WebUI - AI Interface

  • Firebase Studio template for MariaDB Vector - visit link to vote for suggestion

For further alternatives, see Qdrant's list of framework integrations.

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

Vector Functions

Explore vector functions. This section details SQL functions for manipulating and querying vector data types, enabling efficient similarity search and AI/ML applications within your database.

VEC_DISTANCE_COSINE

Cover

WEBINAR

The Next Generation of MariaDB: Powered by Vector Search

Watch Now

Vectors are available from MariaDB 11.7.

Syntax

VEC_DISTANCE_COSINE(v, s)

Description

VEC_Distance_Cosine is an SQL function that calculates a Cosine distance between two vectors.

Vectors must be of the same length. A distance between two vectors of different lengths is not defined, and VEC_Distance_Cosine will return NULL in such cases.

If the vector index was not built for the cosine function (see CREATE TABLE with Vectors), the index is not used — a full table scan is performed instead. The VEC_DISTANCE function is a generic function that behaves either as VEC_DISTANCE_EUCLIDEAN or VEC_DISTANCE_COSINE, depending on the underlying index type.

Example

SELECT VEC_DISTANT_COSINE(vec_fromtext('[1,2,3]'), vec_fromtext('[3,5,7]'));
+-----------------------------------------------------------------------+
| VEC_DISTANT_COSINE(vec_fromtext('[1,2,3]'), vec_fromtext('[3,5,7]')) |
+-----------------------------------------------------------------------+
|                                                   0.00258509695694209 |
+-----------------------------------------------------------------------+

See Also

  • VEC_DISTANCE

  • VEC_DISTANCE_EUCLIDEAN

  • Vector Overview

  • CREATE TABLE with Vectors

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

VEC_DISTANCE_EUCLIDEAN

Cover

WEBINAR

The Next Generation of MariaDB: Powered by Vector Search

Watch Now

Vectors are available from MariaDB 11.7.

Syntax

VEC_DISTANCE_EUCLIDEAN(v, s)

Description

VEC_Distance_Euclidean is an SQL function that calculates a Euclidean (L2) distance between two points.

Vectors must be of the same length, a distance between two vectors of different lengths is not defined and VEC_Distance_Euclidean returns NULL in such cases.

If the vector index was not built for the euclidean function (see CREATE TABLE with Vectors), the index is not used, and a full table scan performed instead. The VEC_DISTANCE function is a generic function that behaves either as VEC_DISTANCE_EUCLIDEAN or VEC_DISTANCE_COSINE, depending on the underlying index type.

Example

INSERT INTO v VALUES 
     (1, x'e360d63ebe554f3fcdbc523f4522193f5236083d'),
     (2, x'f511303f72224a3fdd05fe3eb22a133ffae86a3f'),
     (3,x'f09baa3ea172763f123def3e0c7fe53e288bf33e'),
     (4,x'b97a523f2a193e3eb4f62e3f2d23583e9dd60d3f'),
     (5,x'f7c5df3e984b2b3e65e59d3d7376db3eac63773e'),
     (6,x'de01453ffa486d3f10aa4d3fdd66813c71cb163f'),
     (7,x'76edfc3e4b57243f10f8423fb158713f020bda3e'),
     (8,x'56926c3fdf098d3e2c8c5e3d1ad4953daa9d0b3e'),
     (9,x'7b713f3e5258323f80d1113d673b2b3f66e3583f'),
     (10,x'6ca1d43e9df91b3fe580da3e1c247d3f147cf33e');

SELECT id FROM v 
  ORDER BY VEC_Distance_Euclidean(v, x'6ca1d43e9df91b3fe580da3e1c247d3f147cf33e');
+----+
| id |
+----+
| 10 |
|  7 |
|  3 |
|  9 |
|  2 |
|  1 |
|  5 |
|  4 |
|  6 |
|  8 |
+----+

See Also

  • VEC_DISTANCE

  • VEC_DISTANCE_COSINE

  • Vector Overview

  • CREATE TABLE with Vectors

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

VEC_FromText

Cover

WEBINAR

The Next Generation of MariaDB: Powered by Vector Search

Watch Now

Vectors are available from MariaDB 11.7.

Syntax

VEC_FromText(s)

Description

VEC_FromText converts a text representation of the vector (json array of numbers) to a vector (little-endian IEEE float sequence of bytes, 4 bytes per float).

Example

SELECT HEX(vec_fromtext('[1,2,3]')); 
+------------------------------+
| HEX(vec_fromtext('[1,2,3]')) |
+------------------------------+
| 0000803F0000004000004040     |
+------------------------------+

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

VEC_ToText

Cover

WEBINAR

The Next Generation of MariaDB: Powered by Vector Search

Watch Now

Vectors are available from MariaDB 11.7.

Syntax

VEC_ToText(v)

Description

VEC_ToText converts a binary vector into a json array of numbers (floats). Returns NULL and throws a warning 4201 if given an invalid vector.

Example

SELECT VEC_ToText(x'e360d63ebe554f3fcdbc523f4522193f5236083d');
+---------------------------------------------------------+
| VEC_ToText(x'e360d63ebe554f3fcdbc523f4522193f5236083d') |
+---------------------------------------------------------+
| [0.418708,0.809902,0.823193,0.598179,0.033255]          |
+---------------------------------------------------------+

Invalid vector:

SELECT VEC_ToText(x'aabbcc');
+-----------------------+
| VEC_ToText(x'aabbcc') |
+-----------------------+
| NULL                  |
+-----------------------+
1 row in set, 1 warning (0.000 sec)

Warning (Code 4201): Invalid binary vector format. Must use IEEE standard float 
  representation in little-endian format. Use VEC_FromText() to generate it.

See Also

  • Error 4201: Invalid binary vector format

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

VEC_DISTANCE

Cover

WEBINAR

The Next Generation of MariaDB: Powered by Vector Search

Watch Now

Vectors are available from MariaDB 11.7.

Syntax

VEC_DISTANCE(v, s)

Description

VEC_DISTANCE is a generic function that behaves either as VEC_DISTANCE_EUCLIDEAN, calculating the Euclidean (L2) distance between two points. Or VEC_DISTANCE_COSINE, calculating the Cosine distance between two vectors, depending on the underlying index type.

If the underlying index cannot be determined, an error 4206 is returned:

ERROR 4206 (HY000): Cannot determine distance type for VEC_DISTANCE, index is not found

Example

INSERT INTO v VALUES 
     (1, x'e360d63ebe554f3fcdbc523f4522193f5236083d'),
     (2, x'f511303f72224a3fdd05fe3eb22a133ffae86a3f'),
     (3,x'f09baa3ea172763f123def3e0c7fe53e288bf33e'),
     (4,x'b97a523f2a193e3eb4f62e3f2d23583e9dd60d3f'),
     (5,x'f7c5df3e984b2b3e65e59d3d7376db3eac63773e'),
     (6,x'de01453ffa486d3f10aa4d3fdd66813c71cb163f'),
     (7,x'76edfc3e4b57243f10f8423fb158713f020bda3e'),
     (8,x'56926c3fdf098d3e2c8c5e3d1ad4953daa9d0b3e'),
     (9,x'7b713f3e5258323f80d1113d673b2b3f66e3583f'),
     (10,x'6ca1d43e9df91b3fe580da3e1c247d3f147cf33e');

SELECT id FROM v 
  ORDER BY VEC_DISTANCE(v, x'6ca1d43e9df91b3fe580da3e1c247d3f147cf33e');
+----+
| id |
+----+
| 10 |
|  7 |
|  3 |
|  9 |
|  2 |
|  1 |
|  5 |
|  4 |
|  6 |
|  8 |
+----+

See Also

  • VEC_DISTANCE_COSINE

  • VEC_DISTANCE_EUCLIDEAN

  • Vector Overview

  • CREATE TABLE with Vectors

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