Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
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:
Converting the same value into a number:
This page is licensed: CC BY-SA / Gnu FDL
SELECT 0b1000001;
+-----------+
| 0b1000001 |
+-----------+
| A |
+-----------+SELECT 0b1000001+0;
+-------------+
| 0b1000001+0 |
+-------------+
| 65 |
+-------------+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 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.
For a full list of identifiers naming rules, see .
This page is licensed: CC BY-SA / Gnu FDL
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.
A DATE string is a string in one of the following formats: 'YYYY-MM-DD' or 'YY-MM-DD'. Note that any punctuation character can be used as delimiter. All delimiters must consist of 1 character. Different delimiters can be used in the same string. Delimiters are optional (but if one delimiter is used, all delimiters must be used).
A DATE literal can also be an integer, in one of the following formats: YYYYMMDD or YYMMDD.
All the following DATE literals are valid, and they all represent the same value:
A DATETIME string is a string in one of the following formats: 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS'. Note that any punctuation character can be used as delimiter for the date part and for the time part. All delimiters must consist of 1 character. Different delimiters can be used in the same string. The hours, minutes and seconds parts can consist of one character. For this reason, delimiters are mandatory for DATETIME literals.
The delimiter between the date part and the time part can be a T or any sequence of space characters (including tabs, new lines and carriage returns).
A DATETIME literal can also be a number, in one of the following formats: YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD or YYMMDD. In this case, all the time subparts must consist of 2 digits.
All the following DATE literals are valid, and they all represent the same value:
A TIME string is a string in one of the following formats: 'D HH:MM:SS', 'HH:MM:SS, 'D HH:MM', 'HH:MM', 'D HH', or 'SS'. D is a value from 0 to 34 which represents days. : is the only allowed delimiter for TIME literals. Delimiters are mandatory, with an exception: the 'HHMMSS' format is allowed. When delimiters are used, each part of the literal can consist of one character.
A TIME literal can also be a number in one of the following formats: HHMMSS, MMSS, or SS.
The following literals are equivalent:
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:
See Microseconds in MariaDB for details.
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
'19940101'
'940101'
'1994-01-01'
'94/01/01'
'1994-01/01'
'94:01!01'
19940101
940101'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''09:05:00'
'9:05:0'
'9:5:0'
'090500''12:30:00.123456'
123000.123456SELECT * FROM a_table WHERE A_table.id>10;In MariaDB, FALSE is a synonym of 0 and TRUE is a synonym of 1. These constants are case insensitive, so TRUE, True, and true are equivalent.
These terms are not synonyms of 0 and 1 when used with the IS operator. So, for example, 10 IS TRUE returns 1, while 10 = TRUE returns 0 (because 1 != 10).
The IS operator accepts a third constant exists: UNKNOWN. It is always a synonym of .
TRUE and FALSE are , while UNKNOWN is not.
type
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).
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 , 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 and 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
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.
All these literals are equivalent:
With exponents:
This page is licensed: CC BY-SA / Gnu FDL
10
+10
-100.1
.1
+0.1
+.10.2E3 -- 0.2 * POW(10, 3) = 200
.2e3
.2e+2
1.1e-10 -- 0.00000000011
-1.1e10 -- -11000000000Hexadecimal literals can be written using any of the following syntaxes:
x'value'
X'value' (SQL standard)
0xvalue (ODBC)
value is a sequence of hexadecimal digits (from 0 to 9 and from A to F). The case of the digits does not matter. With the first two syntaxes, value must consist of an even number of digits. With the last syntax, digits can be even, and they are treated as if they had an extra 0 at the beginning.
Normally, hexadecimal literals are interpreted as binary string, where each pair of digits represents a character. When used in a numeric context, they are interpreted as integers. (See the example below). In no case can a hexadecimal literal be a decimal number.
The first two syntaxes; X'value' and x'value, follow the SQL standard, and behave as a string in all contexts in MariaDB. The latter syntax, 0xvalue, is a MySQL/MariaDB extension for hex hybrids and behaves as a string or as a number depending on context. MySQL treats all syntaxes the same, so there may be different results in MariaDB and MySQL (see below).
Representing the a character with the three syntaxes explained above:
Hexadecimal literals in a numeric context:
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 , 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.
In MySQL (up until at least MySQL 8.0.26):
This page is licensed: CC BY-SA / Gnu FDL
Using TVC's with UNION operations:
Using TVCs with a CREATE VIEW statement:
Using TVC with an ORDER BY clause:
Using TVC with LIMIT clause:
This page is licensed: CC BY-SA / Gnu FDL
VALUES ( row_value[, row_value...]), (...)...SELECT x'61', X'61', 0x61;
+-------+-------+------+
| x'61' | X'61' | 0x61 |
+-------+-------+------+
| a | a | a |
+-------+-------+------+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 |
+------+------+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 |
+--------+SELECT x'0a'+0;
+---------+
| x'0a'+0 |
+---------+
| 10 |
+---------+
SELECT X'0a'+0;
+---------+
| X'0a'+0 |
+---------+
| 10 |
+---------+
SELECT 0x0a+0;
+--------+
| 0x0a+0 |
+--------+
| 10 |
+--------+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 |
+------+------+CREATE VIEW v1 AS VALUES (7, 9), (9, 10);
SELECT * FROM v1;
+---+----+
| 7 | 9 |
+---+----+
| 7 | 9 |
| 9 | 10 |
+---+----+SELECT * FROM test.t1
UNION
VALUES (10, 20), (30, 40), (50, 60), (70, 80)
ORDER BY val1 DESC;SELECT * FROM test.t1
UNION
VALUES (10, 20), (30, 40), (50, 60), (70, 80)
LIMIT 2 OFFSET 4;
+------+------+
| val1 | val2 |
+------+------+
| 30 | 40 |
| 50 | 60 |
+------+------+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 .
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():
If a variable has not been used yet, its value is NULL:
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:
Another common use is to include a counter in a query:
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.
This page is licensed: CC BY-SA / Gnu FDL
SET @str = CAST(123 AS CHAR(5));SELECT @x IS NULL;
+------------+
| @x IS NULL |
+------------+
| 1 |
+------------+@sql = 'DELETE FROM my_table WHERE c>1;';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;SET @var = 0;
SELECT a, b, c, (@var:=@var+1) AS counter FROM my_table;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)Some identifiers map to a file name on the filesystem. Databases each have their own directory, while, depending on the storage engine, table names and index names may map to a file name.
Not all characters that are allowed in table names can be used in file names. Every filesystem has its own rules of what characters can be used in file names. To let the user create tables using all characters allowed in the SQL Standard and to not depend on whatever particular filesystem a particular database resides, MariaDB encodes "potentially unsafe" characters in the table name to derive the corresponding file name.
This is implemented using a special character set. MariaDB converts a table name to the "filename" character set to get the file name for this table. And it converts the file name from the "filename" character set to, for example, utf8 to get the table name for this file name.
The conversion rules are as follows: if the identifier is made up only of basic Latin numbers, letters and/or the underscore character, the encoding matches the name (see however Identifier Case Sensitivity). Otherwise they are encoded according to the following table:
Code Range 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:
Find the table name for a file name:
This page is licensed: CC BY-SA / Gnu FDL
Strings are sequences of characters and enclosed with quotes.
The syntax is:
For example:
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 \ (backslash character) is used to escape characters (unless the SQL_MODE hasn't been set to NO_BACKSLASH_ESCAPES):
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:
That is now a valid string, and if displayed, will appear without the backslash.
Another way to escape the quoting character is repeating it twice:
There are other escape sequences:
Escaping the % and _ characters can be necessary when using the 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
[_charset_name]'string' [COLLATE collation_name]'The MariaDB Foundation'
_utf8 'Foundation' COLLATE utf8_unicode_ci;'The ' 'MariaDB ' 'Foundation''The MariaDB Foundation''MariaDB's new features''MariaDB\'s new features'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
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
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.
\0
ASCII NUL (0x00).
'
Single quote (“'”).
"
Double quote (“"”).
\b
Backspace.
Newline, or linefeed,.
Carriage return.
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 |
+------------------------------------------------------------------+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_таблица |
+---------------------------------------------------------------+SELECT 'MariaDB\'s new features';
+------------------------+
| MariaDB's new features |
+------------------------+
| MariaDB's new features |
+------------------------+SELECT 'I''m here', """Double""";
+----------+----------+
| I'm here | "Double" |
+----------+----------+
| I'm here | "Double" |
+----------+----------+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.
Some keywords are exceptions for historical reasons, and are permitted as unquoted identifiers. These include:
In , there are a number of extra reserved words:
If the IGNORE_SPACE flag is set, function names become reserved words.
This page is licensed: CC BY-SA / Gnu FDL
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
SYSTEM
SYSTEM_TIME
VERSIONING
WITHOUT
ACCESSIBLE
ADD
ALL
ALTER
ANALYZE
AND
AS
ASC
ASENSITIVE
BEFORE
BETWEEN
BIGINT
BINARY
ACTION
NO
BODY
ELSIF
GOTO
HISTORY
MINUS (> 10.6.0)
OTHERS
PACKAGE
PERIOD
RAISE
ROWNUM
ROWTYPE
BLOB
SYSDATE
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 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 .
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.
The regular quote character is the backtick character - ```, but if the ANSI_QUOTES 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 have a maximum length of 16 characters.
Aliases have a maximum length of 256 characters, except for column aliases in 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.
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:
The period can be separated by spaces:
Resolving ambiguity:
Creating a table with characters that require quoting:
Using double quotes as a quoting character:
Using an identifier quote as part of an identifier name:
Creating the table named * (Unicode number: U+002A) requires quoting.
Floating point ambiguity:
This page is licensed: CC BY-SA / Gnu FDL
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.
Roles have a maximum length of 128 characters.
Multi-byte characters do not count extra towards the character limit.
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 |
+------+SELECT test . t1 . i FROM t1;
+------+
| i |
+------+
| 10 |
+------+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 |
+------+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)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)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)CREATE TABLE `*` (a INT);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)