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.
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 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
.
Printing the value as a binary string:
SELECT 0b1000001;
+-----------+
| 0b1000001 |
+-----------+
| A |
+-----------+
Converting the same value into a number:
SELECT 0b1000001+0;
+-------------+
| 0b1000001+0 |
+-------------+
| 65 |
+-------------+
This page is licensed: CC BY-SA / Gnu FDL
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
literalsA 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
literalsA 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
literalsA 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'
The year part in DATE
and DATETIME
literals is determined as follows:
70
- 99
= 1970
- 1999
00
- 69
= 2000
- 2069
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.
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
.
This page is licensed: CC BY-SA / Gnu FDL
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, 0x
value
, 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).
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 |
+---------+------+
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.
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 |
+--------+
This page is licensed: CC BY-SA / Gnu FDL
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.
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.
This page is licensed: CC BY-SA / Gnu FDL
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.
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
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.
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.
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.
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.
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.
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
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.
This page is licensed: CC BY-SA / Gnu FDL
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:
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.
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 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.
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
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.
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
Some keywords are exceptions for historical reasons, and are permitted as unquoted identifiers. These include:
In Oracle mode, there are a number of extra reserved words:
BODY
ELSIF
GOTO
HISTORY
MINUS (> 10.6.0)
OTHERS
PACKAGE
PERIOD
RAISE
ROWNUM
ROWTYPE
SYSDATE
SYSTEM
SYSTEM_TIME
VERSIONING
WITHOUT
If the IGNORE_SPACE
SQL_MODE flag is set, function names become reserved words.
This page is licensed: CC BY-SA / Gnu FDL
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.
BOOLEAN type
This page is licensed: CC BY-SA / Gnu FDL
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" |
+----------+----------+
There are other escape sequences:
\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
VALUES ( row_value[, row_value...]), (...)...
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.
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 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:
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;
User-defined variables can be viewed by either querying the USER_VARIABLES, or by running SHOW USER_VARIABLES
.
User-defined variables are reset and the Information Schema table emptied with the FLUSH USER_VARIABLES statement.
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)
This page is licensed: CC BY-SA / Gnu FDL
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.
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.
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.
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.
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
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 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
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:
GEOMETRY
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))) |
+-----------------------------------------------------------------------------------------------+
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)))));
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))))));
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)))));
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))))));
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)))))));
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'));
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 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 produce new geometries.
union of A and B
intersection of A and B
symdifference, notintersecting parts of A and B
returns the shape of the area that lies in 'radius' distance from the shape A.
Predicates return a boolean result of the relationship.
if A and B have an intersection
if A and B cross
if A and B are equal
if A lies within B
if B lies within A
if A and B have no intersection
if A touches B
This page is licensed: CC BY-SA / Gnu FDL
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
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;
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
Explore MariaDB Server's NoSQL capabilities. This section details how to store and query schemaless data, including JSON, and how to integrate with other NoSQL data sources, enhancing flexibility.
Dynamic columns 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.
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"} |
+-----------------+----------------------------------------+
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.
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(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(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(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(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(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(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(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.
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));
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.
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)
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)
UNSIGNED [INTEGER]
DYN_COL_UINT
(variable length, up to 64bit unsigned integer)
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) ...
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.
Max number of columns
65535
Maximum total length of packed dynamic column
max_allowed_packet (1G)
This page is licensed: CC BY-SA / Gnu FDL
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.
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>
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 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.
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
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 enum_dyncol_func_result
is used as return value.
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.
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.
First, define mariadb_dyncol_init(A) memset((A), 0, sizeof(*(A))). It is the correct initialization for an empty packed dynamic blob.
Copy where str is IN. Packed dynamic blob which memory should be freed.
void mariadb_dyncol_free(DYNAMIC_COLUMN *str);
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:
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
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);
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
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);
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.
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);
str
IN
Packed dynamic columns string
column_count
OUT
Number of not NULL columns in the dynamic columns string
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
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);
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.
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);
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)
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);
str
IN
Packed dynamic columns string
Check whether the dynamic column blob has the correct data format:
enum enum_dyncol_func_result
mariadb_dyncol_check(DYNAMIC_COLUMN *str);
str
IN
Packed dynamic columns string
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);
str
IN
Packed dynamic columns string
json
OUT
JSON representation
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);
str or ll or dbl
OUT
value of the column
val
IN
Value
Initialize DYNAMIC_COLUMN_VALUE
before setting the value of value.x.decimal.value
:
void mariadb_dyncol_prepare_decimal(DYNAMIC_COLUMN_VALUE *value);
value
OUT
Value of the column
This function links value.x.decimal.value
to value.x.decimal.buffer
.
Initialize a DYNAMIC_COLUMN_VALUE
structure to a safe default:
#define mariadb_dyncol_value_init(V) (V)->type= DYN_COL_NULL
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
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 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
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.
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.
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 ... ]
You can scan a table in row order by doing this:
HANDLER tbl_name READ FIRST [ LIMIT ... ]
HANDLER tbl_name READ NEXT [ LIMIT ... ]
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.
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.
HANDLER ... READ
also reads the data of invisible columns.
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.
If you do an ALTER TABLE, all your HANDLER
s 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 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.
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 HANDLER
s 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
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:
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.
READ NEXT
gives an error 1031 if the tables changed since last read. This limitation can be lifted in the future.
READ NEXT
gives an error 1031 if the table was truncated since last READ
call.
See also the limitations listed in HANDLER commands.
This page is licensed: CC BY-SA / Gnu FDL
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.
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
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)
JavaScript
node-handlersocket
Scala
Haskell
HandlerSocket-Haskell-Client
This page is licensed: CC BY-SA / Gnu FDL
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
Some resources and documentation about HandlerSocket.
The home of HandlerSocket is here.
The story of 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
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.
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.
- (unary minus), bitwise not (unary bit inversion)
||
(string concatenation)
^ (bitwise XOR)
& (bitwise AND)
| (bitwise OR)
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.
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
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.
N % M
Modulo operator. Returns the remainder of N
divided by M
. See also MOD.
SELECT 1042 % 50;
+-----------+
| 1042 % 50 |
+-----------+
| 42 |
+-----------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
-
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.
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 |
+---------+
This page is licensed: CC BY-SA / Gnu FDL
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.
var_name := expr
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.
SELECT @x := 10;
+----------+
| @x := 10 |
+----------+
| 10 |
+----------+
SELECT @x, @y := @x;
+------+----------+
| @x | @y := @x |
+------+----------+
| 10 | 10 |
+------+----------+
This page is licensed: CC BY-SA / Gnu FDL
identifier = expr
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.
UPDATE table_name SET x = 2 WHERE x > 100;
SET @x = 1, @y := 2;
This page is licensed: CC BY-SA / Gnu FDL
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.
expr BETWEEN min AND max
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.
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 |
+------------+---------------------+---------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
COALESCE(value,...)
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.
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 |
+------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
left_expr = right_expr
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.
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 |
+-------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
>=
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;
SELECT 2 >= 2;
+--------+
| 2 >= 2 |
+--------+
| 1 |
+--------+
SELECT 'A' >= 'a';
+------------+
| 'A' >= 'a' |
+------------+
| 1 |
+------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
>
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;
SELECT 2 > 2;
+-------+
| 2 > 2 |
+-------+
| 0 |
+-------+
SELECT 'b' > 'a';
+-----------+
| 'b' > 'a' |
+-----------+
| 1 |
+-----------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
GREATEST(value1,value2,...)
With two or more arguments, returns the largest (maximum-valued) argument. The arguments are compared using the same rules as for LEAST().
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
expr IN (value,...)
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
.
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 |
+-------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
INTERVAL(N0,N1,N2,N3,...)
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.
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
Tests whether a value is not NULL
. See also NULL Values in MariaDB.
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 |
+---------------+---------------+------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
IS NOT boolean_value
Tests a value against a boolean value, where boolean_value
can be TRUE
, FALSE
, or UNKNOWN
.
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
Tests whether a value is NULL
. See also NULL Values in MariaDB.
SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
+-----------+-----------+--------------+
| 1 IS NULL | 0 IS NULL | NULL IS NULL |
+-----------+-----------+--------------+
| 0 | 0 | 1 |
+-----------+-----------+--------------+
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 |
+-----------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
IS boolean_value
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
.
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 |
+----------+-----------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
ISNULL(expr)
If expr
is NULL
, ISNULL()
returns 1
, otherwise it returns 0.
See also NULL Values in MariaDB.
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(value1,value2,...)
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
.
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
<=
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;
SELECT 0.1 <= 2;
+----------+
| 0.1 <= 2 |
+----------+
| 1 |
+----------+
SELECT 'a'<='A';
+----------+
| 'a'<='A' |
+----------+
| 1 |
+----------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
<
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;
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 |
+---------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
expr NOT BETWEEN min AND max
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.
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
<>, !=
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;
SELECT '.01' <> '0.01';
+-----------------+
| '.01' <> '0.01' |
+-----------------+
| 1 |
+-----------------+
SELECT .01 <> '0.01';
+---------------+
| .01 <> '0.01' |
+---------------+
| 0 |
+---------------+
SELECT 'zapp' <> 'zappp';
+-------------------+
| 'zapp' <> 'zappp' |
+-------------------+
| 1 |
+-------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
expr NOT IN (value,...)
This is the same as NOT
(expr
IN (value,...)).
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
<=>
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.
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 |
+-------+-------------+----------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
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.
AND, &&
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.
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 |
+-----------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
NOT, !
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.
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 |
+-------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
OR, ||
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.
In Oracle mode, ||
ignores null.
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 |
+-----------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
XOR
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.
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 |
+---------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
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.
This page is about sequence objects. For details about the storage engine, see Sequence Storage Engine.
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().
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
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 |
+------------+
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 |
+---+------+
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 |
+---------------+
The DROP SEQUENCE statement is used to drop a sequence, for example:
DROP SEQUENCE s;
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.
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.
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
.
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.
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.
DROP TABLE sequence_name. This is allowed mainly to get old tools like mariadb-dump to work with sequence tables.
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).
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.
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.
SETVAL(). Set next value for the sequence.
This page is licensed: CC BY-SA / Gnu FDL
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.
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.
ALTER SEQUENCE
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;
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 |
+-------+--------------------------------------------------------------------------------------------------------------+
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.
SETVAL(). Set next value for the sequence.
This page is licensed: CC BY-SA / Gnu FDL
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 SEQUENCE
s using the Oracle syntax.
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.
The AS
option is not available.
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
.
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
.
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
.
First value the sequence will generate. Default MINVALUE
if INCREMENT
> 0
, and MAX_VALUE
if INCREMENT
< 0
.
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.
If CYCLE
is used, then the sequence should start again from MINVALUE
after it has run out of values. Default value is NOCYCLE
.
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
.
MariaDB supports Atomic DDL and CREATE SEQUENCE
is atomic.
MariaDB does not support Atomic DDL and CREATE SEQUENCE
is atomic.
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
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
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 |
+---+------+
SETVAL(). Set next value for the sequence.
This page is licensed: CC BY-SA / Gnu FDL
DROP [TEMPORARY] SEQUENCE [IF EXISTS] [/*COMMENT TO SAVE*/]
sequence_name [, sequence_name] ...
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.
DROP SEQUENCE
only removes sequences, not tables. However, DROP TABLE can remove both sequences and tables.
This page is licensed: CC BY-SA / Gnu FDL
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
is a synonym for PREVIOUS VALUE for sequence_name.
This page is licensed: CC BY-SA / Gnu FDL
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.
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 SEQUENCE
s 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.
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 |
+------------+
SETVAL(). Set next value for the sequence.
This page is licensed: CC BY-SA / Gnu FDL
NEXTVAL
is a synonym for NEXT VALUE for sequence_name.
This page is licensed: CC BY-SA / Gnu FDL
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.
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.
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 |
+------------+
SETVAL(). Set next value for the sequence.
Error 4084: Sequence has run out
This page is licensed: CC BY-SA / Gnu FDL
SETVAL(sequence_name, next_value, [is_used, [round]])
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.
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 |
+-------------+
Error 4084: Sequence has run out
This page is licensed: CC BY-SA / Gnu FDL
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.
Bitemporal tables are tables that use versioning both at the system and application-time period levels.
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
This page is licensed: CC BY-SA / Gnu FDL
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 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.
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;
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`
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 |
+------+----------------------------+----------------------------+
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)
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';
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.
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.
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
);
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
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).
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
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
);
A number of system variables are related to system-versioned tables:
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
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
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
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.
MariaDB Temporal Tables (video)
This page is licensed: CC BY-SA / Gnu FDL
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.
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.
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
);
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]'));
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 |
+----+
There are a number of system variables used for vectors. See Vector System Variables.
MariaDB Vector is integrated in several frameworks, see Vector Framework Integrations.
This page is licensed: CC BY-SA / Gnu FDL
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.
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
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
MariaDB Vector has integrations in several frameworks.
LangChain, MariaDB Vector Store - Python
LangChain.js, MariaDB Vector Store - Node.js
LlamaIndex, MariaDB Vector Store - Python
VectorDBBench - benchmarking for vector databases
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
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(v, s)
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.
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 |
+-----------------------------------------------------------------------+
This page is licensed: CC BY-SA / Gnu FDL
VEC_DISTANCE_EUCLIDEAN(v, s)
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.
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 |
+----+
This page is licensed: CC BY-SA / Gnu FDL
VEC_FromText(s)
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).
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(v)
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.
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.
Error 4201: Invalid binary vector format
This page is licensed: CC BY-SA / Gnu FDL
VEC_DISTANCE(v, s)
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
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 |
+----+
This page is licensed: CC BY-SA / Gnu FDL