Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Explore data types in MariaDB Server. This section details the various numeric, string, date/time, and spatial data types available, crucial for designing efficient database schemas.
Store numerical data efficiently. This section details integer, fixed-point, and floating-point types like INT, DECIMAL, and FLOAT.
Store text and binary data. This section covers character types like CHAR, VARCHAR, and TEXT, as well as binary types like BLOB and BINARY.
Store temporal values. This section covers data types for dates, times, and timestamps, including DATE, DATETIME, TIMESTAMP, TIME, and YEAR.
Discover how MariaDB supports internationalization and localization, enabling databases to store and process data in multiple languages.
Learn about character sets in MariaDB Server. This section details how different character sets and collations impact string storage, comparison, and sorting within your database.
Declare row-based variables. This PL/SQL compatibility feature allows declaring variables that match the structure of a table row or cursor.
This is special declaration only available inside a stored procedure.
CREATE TABLE rowtypeof_table(
descr VARCHAR(20),
val INT
);INSERT INTO rowtypeof_table VALUES ('Life', 42);DELIMITER $$
CREATE PROCEDURE rowtypeof_proc()
BEGIN
DECLARE rec1 ROW TYPE OF rowtypeof_table;
SELECT * INTO rec1 FROM rowtypeof_table;
SELECT rec1.descr, rec1.val;
END;
$$
DELIMITER ;This page is: Copyright © 2025 MariaDB. All rights reserved.
Alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. This shorthand data type is often used to define primary keys.
Synonym for TINYINT(1). This type is commonly used to represent boolean values, where 0 is considered false and non-zero values are true.
See TINYINT.
CREATE TABLE bool_example (
example BOOL
) DEFAULT CHARSET=latin1;SHOW CREATE TABLE bool_example\G*************************** 1. row ***************************
Table: bool_example
Create Table: CREATE TABLE `bool_example` (
`example` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is: Copyright © 2025 MariaDB. All rights reserved.
Synonyms for DECIMAL. These keywords declare fixed-point numbers, which store exact numeric data with a defined precision and scale.
DEC[(M[,D])] [SIGNED | UNSIGNED | ZEROFILL]
NUMERIC[(M[,D])] [SIGNED | UNSIGNED | ZEROFILL]
FIXED[(M[,D])] [SIGNED | UNSIGNED | ZEROFILL]These types are synonyms for DECIMAL. The FIXED synonym is available for compatibility with other database systems.
This page is licensed: GPLv2, originally from
Synonym for DECIMAL. This keyword creates a fixed-point column with exact precision, suitable for financial calculations.
See DECIMAL.
CREATE TABLE dec_example (
example DEC
);SHOW CREATE TABLE dec_example\G*************************** 1. row ***************************
Table: dec_example
Create Table: CREATE TABLE `dec_example` (
`example` decimal(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is: Copyright © 2025 MariaDB. All rights reserved.
Synonym for FLOAT. This keyword declares a single-precision floating-point column.
See FLOAT.
CREATE TABLE float4_example (
example FLOAT4
);SHOW CREATE TABLE float4_example\G*************************** 1. row ***************************
Table: float4_example
Create Table: CREATE TABLE `float4_example` (
`example` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is: Copyright © 2025 MariaDB. All rights reserved.
Synonym for DOUBLE. This keyword declares a double-precision floating-point column.
See DOUBLE.
CREATE TABLE float8_example (
example FLOAT8
);SHOW CREATE TABLE float8_example\G
*************************** 1. row ***************************
Table: float8_example
Create Table: CREATE TABLE `float8_example` (
`example` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is: Copyright © 2025 MariaDB. All rights reserved.
Synonym for SMALLINT. This type uses 2 bytes of storage.
INT2 is a synonym for SMALLINT.
CREATE TABLE t1 (x INT2);
DESC t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| x | smallint(6) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+CREATE TABLE int2_example (
example INT2
);SHOW CREATE TABLE int2_example\G
*************************** 1. row ***************************
Table: int2_example
Create Table: CREATE TABLE `int2_example` (
`example` smallint(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is licensed: CC BY-SA / Gnu FDL
Synonym for INT. This keyword declares a standard 4-byte integer column.
INTEGER[(M)] [SIGNED | UNSIGNED | ZEROFILL]This type is a synonym for INT.
This page is licensed: GPLv2, originally from
Synonym for MEDIUMINT. This keyword refers to the 3-byte integer type.
See MEDIUMINT.
CREATE TABLE middleint_example (
example MIDDLEINT
);SHOW CREATE TABLE middleint_example\G*************************** 1. row ***************************
Table: middleint_example
Create Table: CREATE TABLE `middleint_example` (
`example` mediumint(9) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is: Copyright © 2025 MariaDB. All rights reserved.
Oracle-compatible synonym for DECIMAL. This type is used for fixed-point arithmetic.
NUMBER[(M[,D])] [SIGNED | UNSIGNED | ZEROFILL]In , NUMBER is a synonym for DECIMAL.
Number_Example
SET sql_mode='oracle';CREATE TABLE number_example (
example NUMBER
);SHOW CREATE TABLE number_example\GThis page is licensed: CC BY-SA / Gnu FDL
Synonym for DOUBLE. In standard SQL mode, REAL is a double-precision floating-point number.
See DOUBLE.
CREATE TABLE real_example (
example REAL
);SHOW CREATE TABLE real_example\G*************************** 1. row ***************************
Table: real_example
Create Table: CREATE TABLE `real_example` (
`example` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is: Copyright © 2025 MariaDB. All rights reserved.
Fixed-length character string type. CHARACTER columns store strings of a specified length (0 to 255), padding with spaces if necessary.
This is a synonym for CHAR.
CREATE TABLE character_example (
example CHARACTER
);SHOW CREATE TABLE character_example\G*************************** 1. row ***************************
Table: character_example
Create Table: CREATE TABLE `character_example` (
`example` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is: Copyright © 2025 MariaDB. All rights reserved.
This is a synonym for VARCHAR.
CREATE TABLE char_varying_example (
example CHAR VARYING(32)
);SHOW CREATE TABLE char_varying_example\G*************************** 1. row ***************************
Table: char_varying_example
Create Table: CREATE TABLE `char_varying_example` (
`example` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is: Copyright © 2025 MariaDB. All rights reserved.
Explanation of floating-point precision issues. This page details why FLOAT and DOUBLE types are approximate and how rounding errors occur.
Due to their nature, not all floating-point numbers can be stored with exact precision. Hardware architecture, the CPU or even the compiler version and optimization level may affect the precision.
If you are comparing or with numeric decimals, it is not safe to use the operator.
Sometimes, changing a floating-point number from single-precision (FLOAT) to double-precision (DOUBLE) will fix the problem.
In the following query, f1, f2 and f3 have seemingly identical values across each row, but due to floating point accuracy, the results may be unexpected.
CALL rowtypeof_proc();
+------------+----------+
| rec1.descr | rec1.val |
+------------+----------+
| Life | 42 |
+------------+----------+*************************** 1. row ***************************
Table: number_example
Create Table: CREATE TABLE "number_example" (
"example" double DEFAULT NULL
)CREATE TABLE fixed_example (
example FIXED
);SHOW CREATE TABLE fixed_example\G
*************************** 1. row ***************************
Table: fixed_example
Create Table: CREATE TABLE `fixed_example` (
`example` decimal(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is licensed: CC BY-SA / Gnu FDL
CREATE TABLE fpn (id INT, f1 FLOAT, f2 DOUBLE, f3 DECIMAL (10,3));
INSERT INTO fpn VALUES (1,2,2,2),(2,0.1,0.1,0.1);
SELECT * FROM fpn WHERE f1*f1 = f2*f2;
+------+------+------+-------+
| id | f1 | f2 | f3 |
+------+------+------+-------+
| 1 | 2 | 2 | 2.000 |
+------+------+------+-------+SELECT f1*f1, f2*f2, f3*f3 FROM fpn;
+----------------------+----------------------+----------+
| f1*f1 | f2*f2 | f3*f3 |
+----------------------+----------------------+----------+
| 4 | 4 | 4.000000 |
| 0.010000000298023226 | 0.010000000000000002 | 0.010000 |
+----------------------+----------------------+----------+CREATE TABLE national_character_example (
example NATIONAL CHARACTER(32)
);SHOW CREATE TABLE national_character_example\G*************************** 1. row ***************************
Table: national_character_example
Create Table: CREATE TABLE `national_character_example` (
`example` char(32) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE TABLE long_char_varying_example (
example LONG CHAR VARYING
);SHOW CREATE TABLE long_char_varying_example\G*************************** 1. row ***************************
Table: long_char_varying_example
Create Table: CREATE TABLE `long_char_varying_example` (
`example` mediumtext DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE TABLE national_character_varying_example (
example NATIONAL CHARACTER VARYING(32)
);SHOW CREATE TABLE national_character_varying_example\G*************************** 1. row ***************************
Table: national_character_varying_example
Create Table: CREATE TABLE `national_character_varying_example` (
`example` varchar(32) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE TABLE national_char_varying_example (
example NATIONAL CHAR VARYING(32)
);SHOW CREATE TABLE national_char_varying_example\G*************************** 1. row ***************************
Table: national_char_varying_example
Create Table: CREATE TABLE `national_char_varying_example` (
`example` varchar(32) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE TABLE varcharacter_example (
example VARCHARACTER(32)
);SHOW CREATE TABLE varcharacter_example\G
*************************** 1. row ***************************
Table: varcharacter_example
Create Table: CREATE TABLE `varcharacter_example` (
`example` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE TABLE national_varcharacter_example (
example NATIONAL VARCHARACTER(32)
);SHOW CREATE TABLE national_varcharacter_example\G*************************** 1. row ***************************
Table: national_varcharacter_example
Create Table: CREATE TABLE `national_varcharacter_example` (
`example` varchar(32) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE TABLE nchar_varchar_example (
example NCHAR VARCHAR(32)
);SHOW CREATE TABLE nchar_varchar_example\G*************************** 1. row ***************************
Table: nchar_varchar_example
Create Table: CREATE TABLE `nchar_varchar_example` (
`example` varchar(32) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE TABLE nchar_example (
example NCHAR(32)
);SHOW CREATE TABLE nchar_example\G*************************** 1. row ***************************
Table: nchar_example
Create Table: CREATE TABLE `nchar_example` (
`example` char(32) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE TABLE long_character_varying_example (
example LONG CHARACTER VARYING
);SHOW CREATE TABLE long_character_varying_example\G*************************** 1. row ***************************
Table: long_character_varying_example
Create Table: CREATE TABLE `long_character_varying_example` (
`example` mediumtext DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1SET sql_mode='oracle';
CREATE TABLE varchar2_example (
example VARCHAR2(32)
);SHOW CREATE TABLE varchar2_example\G*************************** 1. row ***************************
Table: varchar2_example
Create Table: CREATE TABLE "varchar2_example" (
"example" varchar(32) DEFAULT NULL
)CREATE TABLE serial_example (
id SERIAL,
data VARCHAR(32)
);SHOW CREATE TABLE serial_example\GCREATE TABLE integer_example (
example INTEGER
);SHOW CREATE TABLE integer_example\G
*************************** 1. row ***************************
Table: integer_example
Create Table: CREATE TABLE `integer_example` (
`example` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE TABLE t1 (x INT3);
DESC t1;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| x | mediumint(9) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+CREATE TABLE int3_example (
example INT3
);SHOW CREATE TABLE int3_example\G
*************************** 1. row ***************************
Table: int3_example
Create Table: CREATE TABLE `int3_example` (
`example` mediumint(9) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE TABLE numeric_example (
example NUMERIC
);SHOW CREATE TABLE numeric_example\G*************************** 1. row ***************************
Table: numeric_example
Create Table: CREATE TABLE `numeric_example` (
`example` decimal(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE TABLE t1 (x INT1);
DESC t1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| x | tinyint(4) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+CREATE TABLE int1_example (
example INT1
);SHOW CREATE TABLE int1_example\G*************************** 1. row ***************************
Table: int1_example
Create Table: CREATE TABLE `int1_example` (
`example` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE TABLE t1 (x INT8);
DESC t1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| x | bigint(20) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+CREATE TABLE int8_example (
example INT8
);SHOW CREATE TABLE int8_example\G
*************************** 1. row ***************************
Table: int8_example
Create Table: CREATE TABLE `int8_example` (
`example` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1Synonym for INT. This type uses 4 bytes of storage.
INT4 is a synonym for INT.
CREATE TABLE t1 (x INT4);
DESC t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+CREATE TABLE int4_example (
example INT4
);SHOW CREATE TABLE int4_example\G
*************************** 1. row ***************************
Table: int4_example
Create Table: CREATE TABLE `int4_example` (
`example` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is licensed: CC BY-SA / Gnu FDL
LONG VARCHAR is a compatibility synonym for the MEDIUMTEXT string data type.
See MEDIUMTEXT.
CREATE TABLE long_varchar_example (
example LONG VARCHAR
);SHOW CREATE TABLE long_varchar_example\G*************************** 1. row ***************************
Table: long_varchar_example
Create Table: CREATE TABLE `long_varchar_example` (
`example` mediumtext DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is: Copyright © 2025 MariaDB. All rights reserved.
LONG and LONG VARCHAR are compatibility synonyms for the MEDIUMTEXT string data type.
LONG and LONG VARCHAR are synonyms for MEDIUMTEXT.
CREATE TABLE t1 (a LONG, b LONG VARCHAR);
DESC t1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a | mediumtext | YES | | NULL | |
| b | mediumtext | YES | | NULL | |
+-------+------------+------+-----+---------+-------+This page is licensed: CC BY-SA / Gnu FDL
Overview of large object types. This page compares BLOB (binary) and TEXT (character) types, explaining their storage and usage differences.
A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types are
,
, and
.
These differ only in the maximum length of the values they can hold.
The TEXT types are
,
,
, and
.
These correspond to the four BLOB types and have the same maximum lengths and .
BLOB and TEXT columns can have a DEFAULT value.
It is possible to set a unique index on columns that use the BLOB or TEXT data types.
This page is licensed: GPLv2, originally from
Synonym for YEAR. This keyword is an alias used for declaring a column to store year values.
See YEAR.
CREATE TABLE sql_tsi_year_example (
example SQL_TSI_YEAR
);SHOW CREATE TABLE sql_tsi_year_example\G*************************** 1. row ***************************
Table: sql_tsi_year_example
Create Table: CREATE TABLE `sql_tsi_year_example` (
`example` year(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is: Copyright © 2025 MariaDB. All rights reserved.
In Oracle mode, RAW is a variable-length binary data type synonymous with VARBINARY.
See VARBINARY.
SET sql_mode='oracle';CREATE TABLE raw_example (
example RAW(32)
);SHOW CREATE TABLE raw_example\GThis page is: Copyright © 2025 MariaDB. All rights reserved.
Declare variables based on existing types. This feature allows defining variables or parameters that inherit the data type of a table column or another variable.
This is special declaration only available inside a stored procedure.
CREATE TABLE typeof_table(
descr VARCHAR(20),
val INT
);INSERT INTO typeof_table VALUES ('Life', 42);DELIMITER $$
CREATE PROCEDURE typeof_proc()
BEGIN
DECLARE descr TYPE OF typeof_table.descr;
DECLARE val TYPE OF typeof_table.val;
SELECT * INTO descr, val FROM typeof_table;
SELECT descr, val;
END;
$$
DELIMITER ;This page is: Copyright © 2025 MariaDB. All rights reserved.
LONG VARCHARACTER is a compatibility synonym for the MEDIUMTEXT string data type.
See MEDIUMTEXT.
CREATE TABLE long_varcharacter_example (
example LONG VARCHARACTER
);SHOW CREATE TABLE long_varcharacter_example\G*************************** 1. row ***************************
Table: long_varcharacter_example
Create Table: CREATE TABLE `long_varcharacter_example` (
`example` mediumtext DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is: Copyright © 2025 MariaDB. All rights reserved.
In Oracle mode, CLOB is an alias for the LONGTEXT data type used to store large text objects.
This is a synonym for LONGTEXT.
SET sql_mode='oracle';
CREATE TABLE clob_example (
example CLOB
);SHOW CREATE TABLE clob_example\G*************************** 1. row ***************************
Table: clob_example
Create Table: CREATE TABLE "clob_example" (
"example" longtext DEFAULT NULL
)This page is: Copyright © 2025 MariaDB. All rights reserved.
LONG VARBINARY is a compatibility synonym for the MEDIUMBLOB binary data type.
See MEDIUMBLOB.
CREATE TABLE long_varbinary_example (
example LONG VARBINARY
);SHOW CREATE TABLE long_varbinary_example\G*************************** 1. row ***************************
Table: long_varbinary_example
Create Table: CREATE TABLE `long_varbinary_example` (
`example` mediumblob DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is: Copyright © 2025 MariaDB. All rights reserved.
NCHAR VARYING is a synonym for the VARCHAR string data type using the utf8 character set.
See NATIONAL VARCHAR.
CREATE TABLE nchar_varying_example (
example NCHAR VARYING(32)
);SHOW CREATE TABLE nchar_varying_example\G*************************** 1. row ***************************
Table: nchar_varying_example
Create Table: CREATE TABLE `nchar_varying_example` (
`example` varchar(32) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is: Copyright © 2025 MariaDB. All rights reserved.
NCHAR VARCHARACTER is a synonym for VARCHAR using the utf8 character set.
See NATIONAL VARCHAR.
CREATE TABLE nchar_varcharacter_example (
example NCHAR VARCHARACTER(32)
);SHOW CREATE TABLE nchar_varcharacter_example\G*************************** 1. row ***************************
Table: nchar_varcharacter_example
Create Table: CREATE TABLE `nchar_varcharacter_example` (
`example` varchar(32) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is: Copyright © 2025 MariaDB. All rights reserved.
NATIONAL CHAR is a synonym for the CHAR data type that uses the predefined utf8 character set.
Fixed-length string of specific character set with limit up to 255 bytes.
CREATE TABLE national_char_example (
example NATIONAL CHAR(32)
);SHOW CREATE TABLE national_char_example\G*************************** 1. row ***************************
Table: national_char_example
Create Table: CREATE TABLE `national_char_example` (
`example` char(32) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1Additional information is available .
This page is: Copyright © 2025 MariaDB. All rights reserved.
Coordinated Universal Time (UTC) is the primary time standard by which the world regulates clocks and time, and is the internal storage format for MariaDB timestamp values.
UTC stands for Coordinated Universal Time. It is the world standard for regulating time.
MariaDB stores values internally in UTC, converting them to the required time zone as required.
In general terms it is equivalent to Greenwich Mean Time (GMT), but UTC is used in technical contexts, as it is precisely defined at the subsecond level.
Time zones are offset relative to UTC. For example, time in Tonga is UTC + 13, so 03h00 UTC is 16h00 in Tonga.
This page is licensed: CC BY-SA / Gnu FDL
NATIONAL VARCHAR is a synonym for the VARCHAR data type using the predefined utf8 character set.
Variable-length string of specific character set with limit up to 65,535 bytes.
CREATE TABLE national_varchar_example (
example NATIONAL VARCHAR(32)
);SHOW CREATE TABLE national_varchar_example\G*************************** 1. row ***************************
Table: national_varchar_example
Create Table: CREATE TABLE `national_varchar_example` (
`example` varchar(32) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is: Copyright © 2025 MariaDB. All rights reserved.
Very large character string. A LONGTEXT column can store up to 4GB of text data, subject to protocol limits.
Explore MariaDB's support for Unicode, covering the differences between the utf8mb3 and utf8mb4 character sets for multi-byte storage.
Unicode is a standard for encoding text across multiple writing systems. MariaDB supports a number of for storing Unicode data:
*************************** 1. row ***************************
Table: serial_example
Create Table: CREATE TABLE `serial_example` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`data` varchar(32) DEFAULT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1*************************** 1. row ***************************
Table: raw_example
Create Table: CREATE TABLE "raw_example" (
"example" varbinary(32) DEFAULT NULL
)CALL typeof_proc();
+-------+------+
| descr | val |
+-------+------+
| Life | 42 |
+-------+------+1641MBit values can be inserted with b'value' notation, where value is the bit value in 0's and 1's.
Bit fields are automatically zero-padded from the left to the full length of the bit, so for example in a BIT(4) field, '10' is equivalent to '0010'.
Bits are returned as binary, so to display them, either add 0, or use a function such as HEX, OCT or BIN to convert them.
Example of BIT:
This page is licensed: GPLv2, originally from fill_help_tables.sql
Exception: If the REAL_AS_FLOAT SQL mode is enabled, REAL is a synonym for FLOAT rather than DOUBLE.
This page is licensed: GPLv2, originally from fill_help_tables.sql
DOUBLE PRECISION[(M,D)] [SIGNED | UNSIGNED | ZEROFILL]
REAL[(M,D)] [SIGNED | UNSIGNED | ZEROFILL]LONGBLOBIn , BLOB is a synonym for LONGBLOB.
Note that the maximum size of a LONGBLOB is so large that it cannot be sent to the server without breaking the value up into chunks (something that the command-line client cannot do). For values larger than 16M you can increase the max_allowed_packet size up to a maximum of 1024M to increase the allowed size of non-chunked values.
When SQL_MODE is strict (the default) a value is considered "too long" when its length exceeds the size of the data type, and an error is generated.
Example of data too long behavior for LONGBLOB:
This page is licensed: GPLv2, originally from fill_help_tables.sql
LONGTEXTLONGTEXTJSON is an alias for LONGTEXT. See JSON Data Type for details.
In , CLOB is a synonym for LONGTEXT.
Example of LONGTEXT:
The maximum size of a LONGTEXT is so large that it cannot be sent to the server without breaking the value up into chunks (something that the command-line client cannot do). For values larger than 16M, you can increase the max_allowed_packet size up to a maximum of 1024M to increase the allowed size of non-chunked values.
When SQL_MODE is strict (the default) a value is considered "too long" when its length exceeds the size of the data type, and an error is generated.
Example of data too long behavior for LONGTEXT:
This page is licensed: GPLv2, originally from fill_help_tables.sql
The following are synonyms for MEDIUMTEXT:
LONG
LONG CHAR VARYING
LONG CHARACTER VARYING
LONG VARCHAR
LONG VARCHARACTER
Example of MEDIUMTEXT:
When SQL_MODE is strict (the default) a value is considered "too long" when its length exceeds the size of the data type, and an error is generated.
Example of data too long behavior for MEDIUMTEXT:
This page is licensed: GPLv2, originally from fill_help_tables.sql
LONG VARBINARY is a synonym for MEDIUMBLOB .
Example of MEDIUMBLOB:
When SQL_MODE is strict (the default), a value is considered "too long" when its length exceeds the size of the data type, and an error is generated.
Example of data too long behavior for MEDIUMBLOB:
This page is licensed: GPLv2, originally from fill_help_tables.sql
Example of TINYTEXT:
When SQL_MODE is strict (the default) a value is considered "too long" when its length exceeds the size of the data type, and an error is generated.
Example of data too long behavior for TINYTEXT:
This page is licensed: GPLv2, originally from fill_help_tables.sql
-1.7976931348623157E+308 to -2.2250738585072014E-308
0
2.2250738585072014E-308 to 1.7976931348623157E+308
These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.
M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits allowed by the hardware. A double-precision floating-point number is accurate to approximately 15 decimal places.
UNSIGNED, if specified, disallows negative values.
ZEROFILL, if specified, pads the number with zeros, up to the total number of digits specified by M.
REAL and DOUBLE PRECISION are synonyms, unless the REAL_AS_FLOAT SQL mode is enabled, in which case REAL is a synonym for FLOAT rather than DOUBLE.
See Floating Point Accuracy for issues when using floating-point numbers.
For more details on the attributes, see Numeric Data Type Overview.
This page is licensed: GPLv2, originally from fill_help_tables.sql
utf16
UTF-16, same as ucs2, but stores supplementary characters in 32 bits. 16 or 32-bits.
utf32
UTF-32, fixed-length 32-bit encoding.
Support for the UCA-14.0.0 collations was added in (MDEV-27009).
Support for the MySQL 8.0 UCA-9-0-0 (utf8mb4_0900_...) collations will be added to MariaDB 11.4.5.
This page is licensed: CC BY-SA / Gnu FDL
ucs2
UCS-2, each character is represented by a 2-byte code with the most significant byte first. Fixed-length 16-bit encoding.
utf8
utf8 is an alias for utf8mb3, but this can changed to ut8mb4 by changing the default value of the old_mode system variable.
utf8mb3
UTF-8 encoding using one to three bytes per character. Basic Latin letters, numbers and punctuation use one byte. European and Middle East letters mostly fit into 2 bytes. Korean, Chinese, and Japanese ideographs use 3-bytes. No supplementary characters are stored. Until , this was an alias for utf8. From MariaDB 10.6, utf8 is by default an alias for utf8mb3, but this can changed to ut8mb4 by changing the default value of the old_mode system variable.
utf8mb4
UTF-8 encoding the same as utf8mb3 but which stores supplementary characters in four bytes.
Synonym for TINYINT(1). Like BOOL, this type is used for boolean logic, storing 0 for false and 1 (or other non-zero numbers) for true.
BOOL, BOOLEANThese types are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true.
However, the values TRUE and FALSE are merely aliases for 1 and 0. See , as well as the for testing values against a boolean.
TRUE and FALSE as aliases for 1 and 0:
The last two statements display the results shown because 2 is equal to neither 1 nor 0.
This page is licensed: GPLv2, originally from
Fixed-length binary string type. This type stores a fixed number of bytes, padding with zero bytes if the data is shorter.
This page describes the BINARY data type. For details about the operator, see Binary Operator.
The BINARY type is similar to the type, but stores binary byte strings rather than non-binary character strings. M represents the column length in bytes.
It contains no character set, and comparison and sorting are based on the numeric value of the bytes.
If the maximum length is exceeded, and is not enabled , the extra characters will be dropped with a warning. If strict mode is enabled, an error will occur.
BINARY values are right-padded with 0x00 (the zero byte) to the specified length when inserted. The padding is not removed on select, so this needs to be taken into account when sorting and comparing, where all bytes are significant. The zero byte, 0x00 is less than a space for comparison purposes.
Inserting too many characters, first with strict mode off, then with it on:
Sorting is performed with the byte value:
Using to sort as a instead:
The field is a BINARY(10), so padding of two '\0's are inserted, causing comparisons that don't take this into account to fail:
Example of BINARY:
When SQL_MODE is strict (the default), a value is considered "too long" when its length exceeds the size of the data type, and an error is generated.
Example of data too long behavior for BINARY:
This page is licensed: GPLv2, originally from
Very small binary object. A TINYBLOB column can store up to 255 bytes of binary data.
A BLOB column with a maximum length of 255 (2⁸ - 1) bytes. Each TINYBLOB value is stored using a one-byte length prefix that indicates the number of bytes in the value.
Example of TINYBLOB:
When SQL_MODE is strict (the default), a value is considered "too long" when its length exceeds the size of the data type, and an error is generated.
Example of data too long behavior for TINYBLOB:
This page is licensed: GPLv2, originally from
Learn the core concepts of character sets and collations in MariaDB, including how they define string storage and sorting rules.
A character set is a set of characters, while a collation comprises the rules for comparing and sorting a particular character set.
For example, a subset of a character set could consist of the letters A, B and C. A default collation could define these as appearing in an ascending order of A, B, C.
If we consider different case characters, more complexity is added. A binary collation would evaluate the characters A and a differently, ordering them in a particular way. A case-insensitive collation would evaluate A and a equivalently, while the German phone book collation evaluates the characters ue and ü equivalently.
A character set can have many collations associated with it, while each collation is only associated with one character set. In MariaDB, the character set name is always part of the collation name. For example, the latin1_german1_ci collation applies only to the latin1 character set. Each character set also has one default collation. The latin1 default collation is latin1_swedish_ci.
As an example, by default, the character y comes between x and z, while in Lithuanian, it's sorted between i and k. Similarly, the German phone book order is different to the German dictionary order, so while they share the same character set, the collation is different.
The default character set is utf8mb4 and the default collation is utf8mb4_uca1400_ai_ci.
This may differ in some distros, see for example .
The default is latin1 and the default collation is latin1_swedish_ci.
This may differ in some distros, see for example .
You can view a full list of character sets and collations supported by MariaDB at , or see what's supported on your server with the and commands.
By default, A comes before Z, so the following evaluates to true:
By default, comparisons are case-insensitive:
Character sets and collations can be set from the server level right down to the column level, as well as for client-server communication.
For example, ue and ü are by default evaluated differently.
By using the system variable to change the connection character set to latin1_german2_ci, or German phone book, the same two characters will evaluate as equivalent.
See for more.
This page is licensed: CC BY-SA / Gnu FDL
Variable-length binary large object. BLOB columns can store binary data up to 65,535 bytes, suitable for images or other non-text files.
A BLOB column with a maximum length of 65,535 (2¹⁶ - 1) bytes. Each BLOB value is stored using a two-byte length prefix that indicates the number of bytes in the value.
An optional length M can be given for this type. If this is done, MariaDB creates the column as the smallest BLOB type large enough to hold values M bytes long.
BLOB values can also be used to store .
BLOB and TEXT columns can both be assigned a value.
On a column that uses the BLOB data type, setting a unique index is now possible.
In previous releases, setting a unique index on a column that uses the BLOB data type was not possible. Index would only guarantee the uniqueness of a fixed number of characters.
In , BLOB is a synonym for LONGBLOB.
Example of BLOB:
When SQL_MODE is strict (the default) a value is considered "too long" when its length exceeds the size of the data type, and an error is generated.
Example of data too long behavior for BLOB:
This page is licensed: GPLv2, originally from
String object with zero or more values from a predefined list. A SET column can store multiple values selected from a list of permitted strings.
SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]A set. A string object that can have zero or more values, each of which must be chosen from the list of values 'value1', 'value2', ... A SET column can have a maximum of 64 members. SET values are
represented internally as integers.
SET values cannot contain commas.
If a SET contains duplicate values, an error will be returned if is enabled, or a warning if strict mode is not enabled.
This page is licensed: GPLv2, originally from
BIT[(M)]CREATE TABLE bit_example (
description VARCHAR(20),
b1 BIT,
b4 BIT(4),
b16 BIT(16)
);INSERT INTO bit_example VALUES
('Zero', 0, 0, 0),
('One', 1, 1, 1),
('Two', 0, 2, 2),
('Eight', 0, 8, b'1000'),
('All on', 1, 15, b'1111111111111111');
SELECT description, b1+0, LPAD(BIN(b4), 4, 0) AS b4, HEX(b16)
FROM bit_example;
+-------------+------+------+----------+
| description | b1+0 | b4 | HEX(b16) |
+-------------+------+------+----------+
| Zero | 0 | 0000 | 0 |
| One | 1 | 0001 | 1 |
| Two | 0 | 0010 | 2 |
| Eight | 0 | 1000 | 8 |
| All on | 1 | 1111 | FFFF |
+-------------+------+------+----------+CREATE TABLE b ( b1 BIT(8) );INSERT INTO b VALUES (b'11111111');
INSERT INTO b VALUES (b'01010101');
INSERT INTO b VALUES (b'1111111111111');
ERROR 1406 (22001): Data too long for column 'b1' at row 1
SELECT b1+0, HEX(b1), OCT(b1), BIN(b1) FROM b;
+------+---------+---------+----------+
| b1+0 | HEX(b1) | OCT(b1) | BIN(b1) |
+------+---------+---------+----------+
| 255 | FF | 377 | 11111111 |
| 85 | 55 | 125 | 1010101 |
+------+---------+---------+----------+CREATE TABLE double_precision_example (
example DOUBLE PRECISION
);SHOW CREATE TABLE double_precision_example\G
<</code>>
<<sql>>
*************************** 1. row ***************************
Table: double_precision_example
Create Table: CREATE TABLE `double_precision_example` (
`example` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1LONGBLOBCREATE TABLE longblob_example (
description VARCHAR(20),
example LONGBLOB
) DEFAULT CHARSET=latin1; -- One byte per char makes the examples clearerINSERT INTO longblob_example VALUES
('Normal foo', 'foo'),
('Trailing spaces foo', 'foo '),
('NULLed', NULL),
('Empty', ''),
('Maximum', RPAD('', 4294967295, CHAR(7)));ERROR 1301 (HY000): Result of rpad() was larger than max_allowed_packet (16777216) - truncatedTRUNCATE longblob_example;
INSERT INTO longblob_example VALUES
('Overflow', RPAD('', 4294967296, CHAR(7)));ERROR 1301 (HY000): Result of rpad() was larger than max_allowed_packet (16777216) - truncatedLONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]CREATE TABLE longtext_example (
description VARCHAR(20),
example LONGTEXT
) DEFAULT CHARSET=latin1; -- One byte per char makes the examples clearerINSERT INTO longtext_example VALUES
('Normal foo', 'foo'),
('Trailing spaces foo', 'foo '),
('NULLed', NULL),
('Empty', ''),
('Maximum', RPAD('', 4294967295, 'x'));ERROR 1301 (HY000): Result of rpad() was larger than max_allowed_packet (16777216) - truncatedTRUNCATE longtext_example;
INSERT INTO longtext_example VALUES
('Overflow', RPAD('', 4294967296, 'x'));ERROR 1301 (HY000): Result of rpad() was larger than max_allowed_packet (16777216) - truncatedMEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]CREATE TABLE mediumtext_example (
description VARCHAR(20),
example MEDIUMTEXT
) DEFAULT CHARSET=latin1; -- One byte per char makes the examples clearerINSERT INTO mediumtext_example VALUES
('Normal foo', 'foo'),
('Trailing spaces foo', 'foo '),
('NULLed', NULL),
('Empty', ''),
('Maximum', RPAD('', 16777215, 'x'));SELECT description, LENGTH(example) AS length
FROM mediumtext_example;+---------------------+----------+
| description | length |
+---------------------+----------+
| Normal foo | 3 |
| Trailing spaces foo | 9 |
| NULLed | NULL |
| Empty | 0 |
| Maximum | 16777215 |
+---------------------+----------+TRUNCATE mediumtext_example;
INSERT INTO mediumtext_example VALUES
('Overflow', RPAD('', 16777216, 'x'));ERROR 1406 (22001): Data too long for column 'example' at row 1MEDIUMBLOBCREATE TABLE mediumblob_example (
description VARCHAR(20),
example MEDIUMBLOB
) DEFAULT CHARSET=latin1; -- One byte per char makes the examples clearerINSERT INTO mediumblob_example VALUES
('Normal foo', 'foo'),
('Trailing spaces foo', 'foo '),
('NULLed', NULL),
('Empty', ''),
('Maximum', RPAD('', 16777215, CHAR(7)));SELECT description, LENGTH(example) AS length
FROM mediumblob_example;+---------------------+----------+
| description | length |
+---------------------+----------+
| Normal foo | 3 |
| Trailing spaces foo | 9 |
| NULLed | NULL |
| Empty | 0 |
| Maximum | 16777215 |
+---------------------+----------+TRUNCATE mediumblob_example;INSERT INTO mediumblob_example VALUES
('Overflow', RPAD('', 16777216, CHAR(7)));ERROR 1406 (22001): Data too long for column 'example' at row 1TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]CREATE TABLE tinytext_example (
description VARCHAR(20),
example TINYTEXT
) DEFAULT CHARSET=latin1; -- One byte per char makes the examples clearerINSERT INTO tinytext_example VALUES
('Normal foo', 'foo'),
('Trailing spaces foo', 'foo '),
('NULLed', NULL),
('Empty', ''),
('Maximum', RPAD('', 255, 'x'));SELECT description, LENGTH(example) AS length FROM tinytext_example;+---------------------+--------+
| description | length |
+---------------------+--------+
| Normal foo | 3 |
| Trailing spaces foo | 9 |
| NULLed | NULL |
| Empty | 0 |
| Maximum | 255 |
+---------------------+--------+TRUNCATE tinytext_example;
INSERT INTO tinytext_example VALUES
('Overflow', RPAD('', 256, 'x'));ERROR 1406 (22001): Data too long for column 'example' at row 1DOUBLE[(M,D)] [SIGNED | UNSIGNED | ZEROFILL]
DOUBLE PRECISION[(M,D)] [SIGNED | UNSIGNED | ZEROFILL]
REAL[(M,D)] [SIGNED | UNSIGNED | ZEROFILL]CREATE TABLE t1 (d DOUBLE(5,0) zerofill);
INSERT INTO t1 VALUES (1),(2),(3),(4);
SELECT * FROM t1;
+-------+
| d |
+-------+
| 00001 |
| 00002 |
| 00003 |
| 00004 |
+-------+BINARY(M)TINYBLOBBLOB[(M)]'838:59:59.999999'MariaDB displays TIME values in 'HH:MM:SS.ssssss' format, but allows assignment of times in looser formats, including 'D HH:MM:SS', 'HH:MM:SS', 'HH:MM', 'D HH:MM', 'D HH', 'SS', or 'HHMMSS', as well as permitting dropping of any leading zeros when a delimiter is provided, for example '3:9:10'. For details, see date and time literals.
The --mysql56-temporal-format option, on by default, allows MariaDB to store TIME values using the same low-level format MySQL 5.6 uses.
A new temporal format was introduced from MySQL 5.6 that alters how the TIME, DATETIME and TIMESTAMP columns operate at lower levels. These changes allow these temporal data types to have fractional parts and negative values. You can disable this feature using the mysql56_temporal_format system variable.
Tables that include TIMESTAMP values that were created on an older version of MariaDB or that were created while the mysql56_temporal_format system variable was disabled continue to store data using the older data type format.
In order to update table columns from the older format to the newer format, execute an ALTER TABLE... MODIFY COLUMN statement that changes the column to the same data type. This change may be needed if you want to export the table's tablespace and import it onto a server that has mysql56_temporal_format=ON set (see MDEV-15225).
For instance, if you have a TIME column in your table:
When MariaDB executes the ALTER TABLE statement, it converts the data from the older temporal format to the newer one.
In the event that you have several tables and columns using temporal data types that you want to switch over to the new format, make sure the system variable is enabled, then perform a dump and restore using mariadb-dump. The columns using relevant temporal data types are restored using the new temporal format.
Columns with old temporal formats are marked with a /* mariadb-5.3 */ comment in the output of SHOW CREATE TABLE, SHOW COLUMNS, DESCRIBE statements, as well as in the COLUMN_TYPE column of the INFORMATION_SCHEMA.COLUMNS Table.
Columns with the current format are not marked with a comment.
Time example:
The resulting data look like this:
This page is licensed: GPLv2, originally from fill_help_tables.sql
A fixed-length string that is always right-padded with spaces to the specified length when stored. M represents the column length in characters. The range of M is 0 to 255. If M is omitted, the length is 1.
CHAR(0) columns can contain 2 values: an empty string or NULL. Such columns cannot be part of an index. The CONNECT storage engine does not support CHAR(0).
Note: Trailing spaces are removed when CHAR values are retrieved unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.
If a unique index consists of a column where trailing pad characters are stripped or ignored, inserts into that column where values differ only by the number of trailing pad characters will result in a duplicate-key error.
Trailing spaces:
Example of CHAR:
When SQL_MODE is strict (the default), a value is considered "too long" when its length exceeds the size of the data type, and an error is generated.
Example of data too long behavior for CHAR:
NO PAD collations regard trailing spaces as normal characters. You can get a list of all NO PAD collations by querying the Information Schema Collations table, for example:
This page is licensed: GPLv2, originally from fill_help_tables.sql
CREATE TABLE boolean_example (
example BOOLEAN
);SHOW CREATE TABLE boolean_example\G*************************** 1. row ***************************
Table: boolean_example
Create Table: CREATE TABLE `boolean_example` (
`example` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE TABLE boo (i BOOLEAN);
DESC boo;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| i | tinyint(1) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+SELECT IF(0, 'true', 'false');
+------------------------+
| IF(0, 'true', 'false') |
+------------------------+
| false |
+------------------------+
SELECT IF(1, 'true', 'false');
+------------------------+
| IF(1, 'true', 'false') |
+------------------------+
| true |
+------------------------+
SELECT IF(2, 'true', 'false');
+------------------------+
| IF(2, 'true', 'false') |
+------------------------+
| true |
+------------------------+SELECT IF(0 = FALSE, 'true', 'false');
+--------------------------------+
| IF(0 = FALSE, 'true', 'false') |
+--------------------------------+
| true |
+--------------------------------+
SELECT IF(1 = TRUE, 'true', 'false');
+-------------------------------+
| IF(1 = TRUE, 'true', 'false') |
+-------------------------------+
| true |
+-------------------------------+
SELECT IF(2 = TRUE, 'true', 'false');
+-------------------------------+
| IF(2 = TRUE, 'true', 'false') |
+-------------------------------+
| false |
+-------------------------------+
SELECT IF(2 = FALSE, 'true', 'false');
+--------------------------------+
| IF(2 = FALSE, 'true', 'false') |
+--------------------------------+
| false |
+--------------------------------+CREATE TABLE bins (a BINARY(10));
INSERT INTO bins VALUES('12345678901');
Query OK, 1 row affected, 1 warning (0.04 sec)
SELECT * FROM bins;
+------------+
| a |
+------------+
| 1234567890 |
+------------+
SET sql_mode='STRICT_ALL_TABLES';
INSERT INTO bins VALUES('12345678901');
ERROR 1406 (22001): Data too long for column 'a' at row 1TRUNCATE bins;
INSERT INTO bins VALUES('A'),('B'),('a'),('b');
SELECT * FROM bins ORDER BY a;
+------+
| a |
+------+
| A |
| B |
| a |
| b |
+------+SELECT * FROM bins ORDER BY CAST(a AS CHAR);
+------+
| a |
+------+
| a |
| A |
| b |
| B |
+------+TRUNCATE bins;
INSERT INTO bins VALUES('12345678');
SELECT a = '12345678', a = '12345678\0\0' from bins;
+----------------+--------------------+
| a = '12345678' | a = '12345678\0\0' |
+----------------+--------------------+
| 0 | 1 |
+----------------+--------------------+CREATE TABLE binary_example (
description VARCHAR(20),
example BINARY(255)
) DEFAULT CHARSET=latin1; -- One byte per char makes the examples clearer
INSERT INTO binary_example VALUES
('Normal foo', 'foo'),
('Trailing spaces foo', 'foo '),
('NULLed', NULL),
('Empty', ''),
('Maximum', RPAD('', 255, CHAR(7)));
SELECT description, LENGTH(example) AS length
FROM binary_example;
+---------------------+--------+
| description | length |
+---------------------+--------+
| Normal foo | 255 |
| Trailing spaces foo | 255 |
| NULLed | NULL |
| Empty | 255 |
| Maximum | 255 |
+---------------------+--------+TRUNCATE binary_example;
INSERT INTO binary_example VALUES
('Overflow', RPAD('', 256, CHAR(7)));
ERROR 1406 (22001): Data too long for column 'example' at row 1CREATE TABLE tinyblob_example (
description VARCHAR(20),
example TINYBLOB
) DEFAULT CHARSET=latin1; -- One byte per char makes the examples clearerINSERT INTO tinyblob_example VALUES
('Normal foo', 'foo'),
('Trailing spaces foo', 'foo '),
('NULLed', NULL),
('Empty', ''),
('Maximum', RPAD('', 255, CHAR(7)));SELECT description, LENGTH(example) AS length
FROM tinyblob_example;+---------------------+--------+
| description | length |
+---------------------+--------+
| Normal foo | 3 |
| Trailing spaces foo | 9 |
| NULLed | NULL |
| Empty | 0 |
| Maximum | 255 |
+---------------------+--------+TRUNCATE tinyblob_example;
INSERT INTO tinyblob_example VALUES
('Overflow', RPAD('', 256, CHAR(7)));ERROR 1406 (22001): Data too long for column 'example' at row 1SELECT "A" < "Z";
+-----------+
| "A" < "Z" |
+-----------+
| 1 |
+-----------+SELECT "A" < "a", "A" = "a";
+-----------+-----------+
| "A" < "a" | "A" = "a" |
+-----------+-----------+
| 0 | 1 |
+-----------+-----------+SELECT 'Mueller' = 'Müller';
+----------------------+
| 'Müller' = 'Mueller' |
+----------------------+
| 0 |
+----------------------+SET collation_connection = latin1_german2_ci;
SELECT 'Mueller' = 'Müller';
+-----------------------+
| 'Mueller' = 'Müller' |
+-----------------------+
| 1 |
+-----------------------+CREATE TABLE set_example (
description VARCHAR(20),
example SET('Foo', 'Bar', 'Baz', 'Bob')
);INSERT INTO set_example VALUES
('1 val', 'Foo'),
('2 vals', 'Baz,Foo'),
('4 vals', 'Bob,Foo,Bar,Foo,Baz,Bob');SELECT * FROM set_example;
+-------------+-----------------+
| description | example |
+-------------+-----------------+
| 1 val | Foo |
| 2 vals | Foo,Baz |
| 4 vals | Foo,Bar,Baz,Bob |
+-------------+-----------------+TIME [(<microsecond precision>)]SHOW VARIABLES LIKE 'mysql56_temporal_format';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| mysql56_temporal_format | ON |
+-------------------------+-------+
ALTER TABLE example_table MODIFY ts_col TIME;SHOW CREATE TABLE mariadb5312_time\G
*************************** 1. row ***************************
Table: mariadb5312_time
Create Table: CREATE TABLE `mariadb5312_time` (
`t0` time /* mariadb-5.3 */ DEFAULT NULL,
`t6` time(6) /* mariadb-5.3 */ DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1INSERT INTO time VALUES ('90:00:00'), ('800:00:00'), (800), (22), (151413), ('9:6:3'), ('12 09');
SELECT * FROM time;
+-----------+
| t |
+-----------+
| 90:00:00 |
| 800:00:00 |
| 00:08:00 |
| 00:00:22 |
| 15:14:13 |
| 09:06:03 |
| 297:00:00 |
+-----------+CREATE TABLE time_example (
description VARCHAR(30),
example TIME(6)
);INSERT INTO time_example VALUES
('HH:MM:SS', '12:34:56'),
('HHMMSS', '123456'),
('SS.microsec', '42.123456');SELECT * FROM time_example;+-------------+-----------------+
| description | example |
+-------------+-----------------+
| HH:MM:SS | 12:34:56.000000 |
| HHMMSS | 12:34:56.000000 |
| SS.microsec | 00:00:42.123456 |
+-------------+-----------------+[NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]CREATE TABLE strtest (c CHAR(10));
INSERT INTO strtest VALUES('Maria ');
SELECT c='Maria',c='Maria ' FROM strtest;
+-----------+--------------+
| c='Maria' | c='Maria ' |
+-----------+--------------+
| 1 | 1 |
+-----------+--------------+
SELECT c LIKE 'Maria',c LIKE 'Maria ' FROM strtest;
+----------------+-------------------+
| c LIKE 'Maria' | c LIKE 'Maria ' |
+----------------+-------------------+
| 1 | 0 |
+----------------+-------------------+CREATE TABLE char_example (
description VARCHAR(20),
example CHAR(255)
) DEFAULT CHARSET=latin1; -- One byte per char makes the examples clearerINSERT INTO char_example VALUES
('Normal foo', 'foo'),
('Trailing spaces foo', 'foo '),
('NULLed', NULL),
('Empty', ''),
('Maximum', RPAD('', 255, 'x'));SELECT description, LENGTH(example) AS length FROM char_example;
+---------------------+--------+
| description | length |
+---------------------+--------+
| Normal foo | 3 |
| Trailing spaces foo | 3 |
| NULLed | NULL |
| Empty | 0 |
| Maximum | 255 |
+---------------------+--------+TRUNCATE char_example;
INSERT INTO char_example VALUES
('Overflow', RPAD('', 256, 'x'));
ERROR 1406 (22001): Data too long for column 'example' at row 1SELECT collation_name FROM information_schema.collations
WHERE collation_name LIKE "%nopad%";
+------------------------------+
| collation_name |
+------------------------------+
| big5_chinese_nopad_ci |
| big5_nopad_bin |
...LAST_INSERT_ID() is connection specific, so there is no problem from race conditions.
You don't. Insert, then find out what you did with LAST_INSERT_ID().
Run a query, ALTER TABLE yourTable AUTO_INCREMENT = x; . The next insert will contain x or MAX(autoField) + 1, whichever is higher.
As an alternative, run INSERT INTO yourTable (autoField) VALUES (x); . The next insert will contain x+1 or MAX(autoField) + 1, whichever is higher.
Issuing TRUNCATE TABLE will delete all the rows in the table, and will reset the auto_increment value to 0 in most cases.
Typically, you don't want to. Gaps are hardly ever a problem; if your application can't handle gaps in the sequence, you probably should rethink your application.
Yes, if you use the MyISAM engine.
This isn't possible. It's only available after insert.
You can't, not even with an AFTER INSERT trigger. Insert, then go back and update using LAST_INSERT_ID(). Those two statements could be wrapped into one stored procedure if you wish.
However, you can mimic this behavior with a BEFORE INSERT trigger and a second table to store the sequence position:
The same sequence table can maintain separate sequences for multiple tables (or separate sequences for different fields in the same table) by adding extra rows.
No, it only has to be indexed. It doesn't even have to be unique.
There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers wrap over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0.
- an alternative to AUTO_INCREMENT
The initial version of this article was copied, with permission, from Autoincrement_FAQ on 2012-10-05.
This page is licensed: CC BY-SA / Gnu FDL
M represents the maximum column length in bytes.It contains no character set, and comparison and sorting are based on the numeric value of the bytes.
If the maximum length is exceeded, and SQL strict mode is not enabled , the extra characters will be dropped with a warning. If strict mode is enabled, an error will occur.
Unlike BINARY values, VARBINARY values are not right-padded when inserting.
In , RAW is a synonym for VARBINARY.
Inserting too many characters, first with strict mode off, then with it on:
Sorting is performed with the byte value:
For our example of VARBINARY, we picked a maximum size that avoids overflowing the maximum row size (65535). Keep in mind that a multi-byte character set would need more space in the row than a single-byte character set. We also avoid the auto-conversion of a VARBINARY into a BLOB, MEDIUMBLOB, or LONGBLOB that can happen when STRICT_TRANS_TABLES is not set in the SQL_MODE.
The example:
When SQL_MODE is strict (the default), a value is considered "too long" when its length exceeds the size of the data type, and an error is generated.
Example of data too long behavior for VARBINARY:
This page is licensed: GPLv2, originally from fill_help_tables.sql
Small integer type. A SMALLINT column uses 2 bytes and stores values from -32768 to 32767 (signed) or 0 to 65535 (unsigned).
A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535.
If a column has been set to ZEROFILL, all values will be prepended by zeros so that the SMALLINT value contains a number of M digits.
If the ZEROFILL attribute has been specified, the column will automatically become UNSIGNED.
INT2 is a synonym for SMALLINT.
For more details on the attributes, see .
The SMALLINT data type may be SIGNED (allowing negative values) or UNSIGNED (not allowing negative values).
Example of SMALLINT SIGNED (the default):
Example of SMALLINT UNSIGNED:
A value is considered "out of range" when it is too small or too large to be stored in a data type. When sql_mode=STRICT_TRANS_TABLES (the default) is set, an out-of-range value generates an error. If strict mode is not in effect, the value is rounded to the nearest valid value and a warning is generated (which might be hidden, depending on your warning settings).
An example of non-strict out-of-range behavior:
A special type of SMALLINT UNSIGNED is SMALLINT ZEROFILL, which pads out the values with leading zeros in SELECT results. The number of leading zeros are just enough to pad the field out to the length of the type's maximum unsigned value, but the zeros are not included in an expression result or in a UNION SELECT column.
Using SMALLINT ZEROFILL works the same way as SMALLINT UNSIGNED for most operations except a simple SELECT. For example, with the following test table setup:
The resulting data would look like this:
This page is licensed: GPLv2, originally from
JSON document storage. While often implemented as an alias for LONGTEXT, this type includes automatic validation to ensure valid JSON format.
The JSON alias was added to make it possible to use JSON columns in statement based replication from MySQL to MariaDB and to make it possible for MariaDB to read mysqldumps from MySQL.
JSON is an alias for LONGTEXT COLLATE utf8mb4_bin introduced for compatibility reasons with MySQL's JSON data type. MariaDB implements this as a LONGTEXT rather, as the JSON data type contradicts the SQL:2016 standard, and MariaDB's benchmarks indicate that performance is at least equivalent.
In order to ensure that a valid json document is inserted, the JSON_VALID function can be used as a CHECK constraint. This constraint is automatically included for types using the JSON alias.
The assigned text value is retained verbatim. If a value fails JSON_VALID(), an error is raised. This CHECK constraint can also be manually added to any LONGTEXT field. When a JSON object contains duplicate keys, only the first key-value pair is accessible via functions like JSON_EXTRACT().
With validation:
JSON example:
The JSON type in MySQL stores the JSON object in a compact form, not as as in MariaDB.
This means that row-based replication will not work for JSON types from MySQL to MariaDB.
There are a few different ways to solve this:
Use statement-based replication.
Change the JSON column to type TEXT in MySQL.
If you must use row-based replication and cannot change the MySQL master from JSON to TEXT, you can try to introduce an intermediate MySQL replica, and change the column type from JSON
MariaDB can't directly access MySQL's JSON format.
There are a few different ways to move the table to MariaDB:
From , you can use the plugin. See (blog post).
Change the JSON column to type TEXT in MySQL. After this, MariaDB can directly use the table without any need for a dump and restore.
.
In MySQL, JSON is an object and is . In MariaDB JSON strings are normal strings and compared as strings. One exception is when using in which case strings are unescaped before comparison.
This page is licensed: CC BY-SA / Gnu FDL
String object with a value chosen from a predefined list. An ENUM column allows only one value selected from a list of permitted strings.
An enumeration. A string object that can have only one value, chosen from the list of values 'value1', 'value2', ..., NULL or the special '' error value. In theory, an ENUM column can have a maximum of 65,535 distinct values; in practice, the real maximum depends on many factors. ENUM values are represented internally as integers.
Trailing spaces are automatically stripped from ENUM values on table creation.
ENUM values require relatively little storage space compared to strings, either one or two bytes depending on the number of enumeration values.
An ENUM can also contain NULL and empty values. If the ENUM column is declared to permit NULL values, NULL becomes a valid value, as well as the default value (see below). If is not enabled, and an invalid value is inserted into an ENUM, a special empty string, with an index value of zero (see Numeric index, below), is inserted, with a warning. This may be confusing, because the empty string is also a possible value, and the only difference if that is this case its index is not 0. Inserting will fail with an error if strict mode is active.
If a DEFAULT clause is missing, the default value will be:
NULL if the column is nullable;
otherwise, the first value in the enumeration.
ENUM values are indexed numerically in the order they are defined, and sorting will be performed in this numeric order. We suggest not using ENUM to store numerals, as there is little to no storage space benefit, and it is easy to confuse the enum integer with the enum numeral value by leaving out the quotes.
An ENUM defined as ENUM('apple','orange','pear') would have the following index values:
Selecting by numeric index:
Sorting is according to the index value:
It's easy to get confused between returning the enum integer with the stored value, so we don't suggest using ENUM to store numerals. The first example returns the 1st indexed field ('2' has an index value of 1, as it's defined first), while the second example returns the string value '1'.
Example of ENUM:
This page is licensed: GPLv2, originally from
Store year values. This type stores a year in 2-digit or 4-digit format, supporting values from 1901 to 2155, and 0000.
A year in two-digit or four-digit format. The default is four-digit format.
In four-digit format, the allowable values are 1901 to 2155, and 0000. In two-digit format, the allowable values are 70 to 69, representing years from 1970 to 2069. MariaDB displays YEAR values in YYYY format, but allows you to assign values to YEAR columns using either strings or numbers.
Inserting numeric zero has a different result for YEAR(4) and YEAR(2). For YEAR(2), the value 00 reflects the year 2000. For YEAR(4), the value 0000 reflects the year zero. This only applies to numeric zero. String zero always reflects the year 2000.
Accepting a string or a number:
With set — values out of range:
With unset — values out of range:
Truncating:
Difference between YEAR(2) and YEAR(4), and string and numeric zero:
This page is licensed: GPLv2, originally from
Variable-length character string. A TEXT column can store up to 65,535 characters, often used for storing longer text content.
A TEXT column with a maximum length of 65,535 (2¹⁶ - 1) characters. The effective maximum length is less if the value contains multi-byte characters. Each TEXT value is stored using a two-byte length prefix that indicates the number of bytes in the value. If you need a bigger storage, consider using instead.
An optional length M can be given for this type. If this is done, MariaDB creates the column as the smallest TEXT type large enough to hold valuesM characters long.
BLOB and TEXT columns can be assigned a value.
When SQL_MODE is strict (the default) a value is considered "too long" when its length exceeds the size of the data type, and an error is generated.
Example of data too long behavior for TEXT:
A can be created on a TEXT column.
Internally, this uses hash indexing to quickly check the values and if a hash collision is found, the actual stored values are compared in order to retain the uniqueness.
columns can be fully indexed. TEXT columns can only be indexed over a specified length.
Using TEXT or in a query that uses temporary tables for storing intermediate results will force the temporary table to be disk based (using the instead of the , which is a bit slower. This is not that bad as the caches the rows in memory. To get the benefit of this, one should ensure that the variable is big enough to hold most of the row and index data for temporary tables.
Internally the full length of the column is allocated inside each TABLE objects record[] structure. As there are three such buffers, each open table will allocate 3 times max-length-to-store-varchar bytes of memory.
TEXT and BLOB columns are stored with a pointer (4 or 8 bytes) + a 1-4 bytes length. The TEXT data is only stored once. This means that internally TEXT uses less memory for each open table but instead has the additional overhead that each TEXT object needs to be allocated and freed for each row access (with some caching in between).
This page is licensed: GPLv2, originally from
The LOCALES plugin enables the INFORMATION_SCHEMA.LOCALES table and SHOW LOCALES statement, allowing users to view all locales compiled into the server.
The LOCALES plugin creates the LOCALES table in the INFORMATION_SCHEMA database. The plugin also adds the SHOW LOCALES statement.The table and statement can be queried to see all locales that are compiled into the server.
Although the plugin's shared library is distributed with MariaDB by default, the plugin is not actually installed by MariaDB by default. There are two methods that can be used to install the plugin with MariaDB.
The first method can be used to install the plugin without restarting the server. You can install the plugin dynamically by executing INSTALL SONAME or . For example:
The second method can be used to tell the server to load the plugin when it starts up. The plugin can be installed this way by providing the or the options. This can be specified as a command-line argument to or it can be specified in a relevant server in an . For example:
You can uninstall the plugin dynamically by executing or . For example:
If you installed the plugin by providing the or the options in a relevant server in an , then those options should be removed to prevent the plugin from being loaded the next time the server is restarted.
localesDescription: Controls how the server should treat the plugin when the server starts up.
Valid values are:
OFF - Disables the plugin without removing it from the table.
This page is licensed: CC BY-SA / Gnu FDL
Fixed-length binary string type. This type stores a fixed number of bytes, padding with zero bytes if the data is shorter.
The CHAR BYTE data type is an alias for the BINARY data type. This is a compatibility feature.
This page is licensed: GPLv2, originally from
General introduction to numeric data types. This page summarizes the available integer, fixed-point, and floating-point types and their storage characteristics.
There are a number of numeric data types:
- Synonym for TINYINT(1)
- Synonym for TINYINT
- Synonym for SMALLINT
SELECT LAST_INSERT_ID();CREATE TABLE sequence (table_name VARCHAR(255), position INT UNSIGNED);
INSERT INTO sequence VALUES ('testTable', 0);
CREATE TABLE testTable (firstAuto INT UNSIGNED, secondAuto INT UNSIGNED);
DELIMITER //
CREATE TRIGGER testTable_BI BEFORE INSERT ON testTable FOR EACH ROW BEGIN
UPDATE sequence SET position = LAST_INSERT_ID(position + 1) WHERE table_name = 'testTable';
SET NEW.firstAuto = LAST_INSERT_ID();
SET NEW.secondAuto = LAST_INSERT_ID();
END//
DELIMITER ;
INSERT INTO testTable VALUES (NULL, NULL), (NULL, NULL);
SELECT * FROM testTable;
+-----------+------------+
| firstAuto | secondAuto |
+-----------+------------+
| 1 | 1 |
| 2 | 2 |
+-----------+------------+CREATE TABLE autoinc_test (
h INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
m INT UNSIGNED
) AUTO_INCREMENT = 100;
INSERT INTO autoinc_test ( m ) VALUES ( 1 );
SELECT * FROM autoinc_test;
+-----+------+
| h | m |
+-----+------+
| 100 | 1 |
+-----+------+VARBINARY(M)CREATE TABLE varbins (a VARBINARY(10));
INSERT INTO varbins VALUES('12345678901');
Query OK, 1 row affected, 1 warning (0.04 sec)
SELECT * FROM varbins;
+------------+
| a |
+------------+
| 1234567890 |
+------------+
SET sql_mode='STRICT_ALL_TABLES';
INSERT INTO varbins VALUES('12345678901');
ERROR 1406 (22001): Data too long for column 'a' at row 1TRUNCATE varbins;
INSERT INTO varbins VALUES('A'),('B'),('a'),('b');
SELECT * FROM varbins ORDER BY a;
+------+
| a |
+------+
| A |
| B |
| a |
| b |
+------+SELECT * FROM varbins ORDER BY CAST(a AS CHAR);
+------+
| a |
+------+
| a |
| A |
| b |
| B |
+------+CREATE TABLE varbinary_example (
description VARCHAR(20),
example VARBINARY(65511)
) DEFAULT CHARSET=latin1; -- One byte per char makes the examples clearerINSERT INTO varbinary_example VALUES
('Normal foo', 'foo'),
('Trailing spaces foo', 'foo '),
('NULLed', NULL),
('Empty', ''),
('Maximum', RPAD('', 65511, CHAR(7)));SELECT description, LENGTH(example) AS length
FROM varbinary_example;+---------------------+--------+
| description | length |
+---------------------+--------+
| Normal foo | 3 |
| Trailing spaces foo | 9 |
| NULLed | NULL |
| Empty | 0 |
| Maximum | 65511 |
+---------------------+--------+TRUNCATE varbinary_example;
INSERT INTO varbinary_example VALUES
('Overflow', RPAD('', 65512, CHAR(7)));ERROR 1406 (22001): Data too long for column 'example' at row 1CREATE TABLE blob_example (
description VARCHAR(20),
example BLOB
) DEFAULT CHARSET=latin1; -- One byte per char makes the examples clearerINSERT INTO blob_example VALUES
('Normal foo', 'foo'),
('Trailing spaces foo', 'foo '),
('NULLed', NULL),
('Empty', ''),
('Maximum', RPAD('', 65535, CHAR(7)));SELECT description, LENGTH(example) AS length
FROM blob_example;
+---------------------+--------+
| description | length |
+---------------------+--------+
| Normal foo | 3 |
| Trailing spaces foo | 9 |
| NULLed | NULL |
| Empty | 0 |
| Maximum | 65535 |
+---------------------+--------+TRUNCATE blob_example;
INSERT INTO blob_example VALUES
('Overflow', RPAD('', 65536, CHAR(7)));
ERROR 1406 (22001): Data too long for column 'example' at row 1SMALLINT[(M)] [SIGNED | UNSIGNED | ZEROFILL]ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]YEAR[(4)]TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]NULL
NULL
0
''
1
'apple'
2
'orange'
3
'pear'
ON - Enables the plugin. If the plugin cannot be initialized, then the server still continues starting up, but the plugin will be disabled.FORCE - Enables the plugin. If the plugin cannot be initialized, then the server fails to start with an error.
FORCE_PLUS_PERMANENT - Enables the plugin. If the plugin cannot be initialized, then the server fails to start with an error. In addition, the plugin cannot be uninstalled with UNINSTALL SONAME or UNINSTALL PLUGIN while the server is running.
See Plugin Overview: Configuring Plugin Activation at Server Startup for more information.
Command line: --locales=value
Data Type: enumerated
Default Value: ON
Valid Values: OFF, ON, FORCE, FORCE_PLUS_PERMANENT
CREATE TABLE char_byte_example (
example CHAR BYTE
);SHOW CREATE TABLE char_byte_example\G9999-12-31DATEYYYY-MM-DDYY-MM-DDYYMMDDYYYY/MM/DD'0000-00-00' is a permitted special value (zero-date), unless the NO_ZERO_DATE SQL_MODE is used. Also, individual components of a date can be set to 0 (for example: '2015-00-12'), unless the NO_ZERO_IN_DATE SQL_MODE is used. In many cases, the result of en expression involving a zero-date, or a date with zero-parts, is NULL. If the ALLOW_INVALID_DATES SQL_MODE is enabled, if the day part is in the range between 1 and 31, the date does not produce any error, even for months that have less than 31 days.
In Oracle mode, DATE with a time portion is a synonym for DATETIME. See also mariadb_schema.
The resulting data look like this:
If SQL_MODE is strict (the default), the example above generates the following error and no values are inserted:
If SQL_MODE is not strict, the example above generates a warning and (possibly modified) values are inserted. The Below minimum range value is accepted because it contains a zero component. The Above maximum range value is truncated since it is an unacceptable date.
The resulting data would look like this:
When using a date value in an expression, such as DATE_ADD(), the following illustrates that a NULL is generated when a date value is not a real date and when a real date overflows:
The following example enhances the SQL_MODE to ensure that ALLOW_INVALID_DATES is set and illustrates the difference between a day that is outside the range of 1 to 31 and one that is just too large for its month:
The resulting data would look like this:
mariadb_schema data type qualifier
This page is licensed: GPLv2, originally from fill_help_tables.sql
INT3 - Synonym for MEDIUMINT
INT, INTEGER
INT4 - Synonym for INT
INT8 - Synonym for BIGINT
DECIMAL, DEC, NUMERIC, FIXED
DOUBLE, DOUBLE PRECISION, REAL
See the specific articles for detailed information on each.
Most numeric types can be defined as SIGNED, UNSIGNED or ZEROFILL, for example:
If SIGNED, or no attribute, is specified, a portion of the numeric type will be reserved for the sign (plus or minus). For example, a TINYINT SIGNED can range from -128 to 127.
If UNSIGNED is specified, no portion of the numeric type is reserved for the sign, so for integer types range can be larger. For example, a TINYINT UNSIGNED can range from 0 to 255. Floating point and fixed-point types also can be UNSIGNED, but this only prevents negative values from being stored and doesn't alter the range.
If ZEROFILL is specified, the column will be set to UNSIGNED and the spaces used by default to pad the field are replaced with zeros. ZEROFILL is ignored in expressions or as part of a UNION. ZEROFILL is a non-standard MySQL and MariaDB enhancement.
Note that although the preferred syntax indicates that the attributes are exclusive, more than one attribute can be specified.
Only the following combinations are supported:
SIGNED
UNSIGNED
ZEROFILL
UNSIGNED ZEROFILL
ZEROFILL UNSIGNED
The latter two should be replaced with simply ZEROFILL, but are still accepted by the parser.
When attempting to add a value that is out of the valid range for the numeric type, MariaDB will react depending on the strict SQL_MODE setting.
If strict_mode has been set (the default), MariaDB will return an error.
If strict_mode has not been set, MariaDB will adjust the number to fit in the field, returning a warning.
With strict_mode set:
With strict_mode unset:
The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows. For more details, see auto_increment.
This page is licensed: CC BY-SA / Gnu FDL
CREATE TABLE smallints (a SMALLINT,b SMALLINT UNSIGNED,c SMALLINT ZEROFILL);INSERT INTO smallints VALUES (-10,-10,-10);
ERROR 1264 (22003): Out of range value for column 'b' at row 1
INSERT INTO smallints VALUES (-10,10,-10);
ERROR 1264 (22003): Out of range value for column 'c' at row 1
INSERT INTO smallints VALUES (-10,10,10);
INSERT INTO smallints VALUES (32768,32768,32768);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
INSERT INTO smallints VALUES (32767,32768,32768);
SELECT * FROM smallints;
+-------+-------+-------+
| a | b | c |
+-------+-------+-------+
| -10 | 10 | 00010 |
| 32767 | 32768 | 32768 |
+-------+-------+-------+CREATE TABLE smallint_signed_example (
description VARCHAR(20),
example SMALLINT SIGNED
);INSERT INTO smallint_signed_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', -32768),
('Maximum', 32767);CREATE TABLE smallint_unsigned_example (
description VARCHAR(20),
example SMALLINT UNSIGNED
);INSERT INTO smallint_unsigned_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', 0),
('Maximum', 65535);TRUNCATE smallint_signed_example;
-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO smallint_signed_example VALUES
('Underflow', -32769),
('Overflow', 32768);Warning (Code 1264): Out of range value for column 'example' at row 1
Warning (Code 1264): Out of range value for column 'example' at row 2SELECT * FROM smallint_signed_example;
+-------------+---------+
| description | example |
+-------------+---------+
| Underflow | -32768 |
| Overflow | 32767 |
+-------------+---------+TRUNCATE smallint_unsigned_example;
-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO smallint_unsigned_example VALUES
('Underflow', -1),
('Overflow', 65536);Warning (Code 1264): Out of range value for column 'example' at row 1
Warning (Code 1264): Out of range value for column 'example' at row 2SELECT * FROM smallint_unsigned_example;+-------------+---------+
| description | example |
+-------------+---------+
| Underflow | 0 |
| Overflow | 65535 |
+-------------+---------+CREATE TABLE smallint_zerofill_example (
description VARCHAR(20),
example SMALLINT ZEROFILL
);INSERT INTO smallint_zerofill_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', 0),
('Maximum', 65535);
-- Turn off strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO smallint_zerofill_example VALUES
('Underflow', -1),
('Overflow', 65536);Warning (Code 1264): Out of range value for column 'example' at row 1
Warning (Code 1264): Out of range value for column 'example' at row 2SELECT *, example + 0 FROM smallint_zerofill_example;+-------------+---------+-------------+
| description | example | example + 0 |
+-------------+---------+-------------+
| Zero | 00000 | 0 |
| Forty-Two | 00042 | 42 |
| Minimum | 00000 | 0 |
| Maximum | 65535 | 65535 |
| Underflow | 00000 | 0 |
| Overflow | 65535 | 65535 |
+-------------+---------+-------------+CREATE TABLE fruits (
id INT NOT NULL auto_increment PRIMARY KEY,
fruit ENUM('apple','orange','pear'),
bushels INT);
DESCRIBE fruits;
+---------+-------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| fruit | enum('apple','orange','pear') | YES | | NULL | |
| bushels | int(11) | YES | | NULL | |
+---------+-------------------------------+------+-----+---------+----------------+
INSERT INTO fruits
(fruit,bushels) VALUES
('pear',20),
('apple',100),
('orange',25);
INSERT INTO fruits
(fruit,bushels) VALUES
('avocado',10);
ERROR 1265 (01000): Data truncated for column 'fruit' at row 1
SELECT * FROM fruits;
+----+--------+---------+
| id | fruit | bushels |
+----+--------+---------+
| 1 | pear | 20 |
| 2 | apple | 100 |
| 3 | orange | 25 |
+----+--------+---------+SELECT * FROM fruits WHERE fruit=2;
+----+--------+---------+
| id | fruit | bushels |
+----+--------+---------+
| 3 | orange | 25 |
+----+--------+---------+CREATE TABLE enums (a ENUM('2','1'));
INSERT INTO enums VALUES ('1'),('2');
SELECT * FROM enums ORDER BY a ASC;
+------+
| a |
+------+
| 2 |
| 1 |
+------+SELECT * FROM enums WHERE a=1;
+------+
| a |
+------+
| 2 |
+------+
SELECT * FROM enums WHERE a='1';
+------+
| a |
+------+
| 1 |
+------+CREATE TABLE enum_example (
description VARCHAR(20),
example ENUM('Alpha', 'Beta', 'Gamma', 'RC', 'Stable')
);INSERT INTO enum_example VALUES
('foo', 'Beta'),
('bar', 'RC'),
('baz', 'Alpha'),
('bob', 5);SELECT * FROM enum_example;
+-------------+---------+
| description | example |
+-------------+---------+
| foo | Beta |
| bar | RC |
| baz | Alpha |
| bob | Stable |
+-------------+---------+CREATE TABLE y(y YEAR);
INSERT INTO y VALUES (1990),('2012');
SELECT * FROM y;
+------+
| y |
+------+
| 1990 |
| 2012 |
+------+INSERT INTO y VALUES (1005),('3080');
ERROR 1264 (22003): Out of range value for column 'y' at row 1
INSERT INTO y VALUES ('2013-12-12');
ERROR 1265 (01000): Data truncated for column 'y' at row 1
SELECT * FROM y;
+------+
| y |
+------+
| 1990 |
| 2012 |
+------+INSERT INTO y VALUES (1005),('3080');
Query OK, 2 rows affected, 2 warnings (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 2
SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'y' at row 1 |
| Warning | 1264 | Out of range value for column 'y' at row 2 |
+---------+------+--------------------------------------------+
SELECT * FROM y;
+------+
| y |
+------+
| 1990 |
| 2012 |
| 0000 |
| 0000 |
+------+INSERT INTO y VALUES ('2013-12-12');
Query OK, 1 row affected, 1 warning (0.05 sec)
SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'y' at row 1 |
+---------+------+----------------------------------------+
SELECT * FROM y;
+------+
| y |
+------+
| 1990 |
| 2012 |
| 0000 |
| 0000 |
| 2013 |
+------+CREATE TABLE y2(y YEAR(4), y2 YEAR(2));
Query OK, 0 rows affected, 1 warning (0.40 sec)
Note (Code 1287): 'YEAR(2)' is deprecated and will be removed in a future release.
Please use YEAR(4) instead
INSERT INTO y2 VALUES(0,0),('0','0');
SELECT YEAR(y),YEAR(y2) FROM y2;
+---------+----------+
| YEAR(y) | YEAR(y2) |
+---------+----------+
| 0 | 2000 |
| 2000 | 2000 |
+---------+----------+CREATE TABLE strtest (d TEXT(10));
INSERT INTO strtest VALUES('Maria ');
SELECT d='Maria',d='Maria ' FROM strtest;
+-----------+--------------+
| d='Maria' | d='Maria ' |
+-----------+--------------+
| 1 | 1 |
+-----------+--------------+
SELECT d LIKE 'Maria',d LIKE 'Maria ' FROM strtest;
+----------------+-------------------+
| d LIKE 'Maria' | d LIKE 'Maria ' |
+----------------+-------------------+
| 0 | 1 |
+----------------+-------------------+CREATE TABLE text_example (
description VARCHAR(20),
example TEXT
) DEFAULT CHARSET=latin1; -- One byte per char makes the examples clearerINSERT INTO text_example VALUES
('Normal foo', 'foo'),
('Trailing spaces foo', 'foo '),
('NULLed', NULL),
('Empty', ''),
('Maximum', RPAD('', 65535, 'x'));SELECT description, LENGTH(example) AS length
FROM text_example;+---------------------+--------+
| description | length |
+---------------------+--------+
| Normal foo | 3 |
| Trailing spaces foo | 9 |
| NULLed | NULL |
| Empty | 0 |
| Maximum | 65535 |
+---------------------+--------+TRUNCATE text_example;
INSERT INTO text_example VALUES
('Overflow', RPAD('', 65536, 'x'));ERROR 1406 (22001): Data too long for column 'example' at row 1INSTALL SONAME 'locales';[mariadb]
...
plugin_load_add = localesUNINSTALL SONAME 'locales';SELECT * FROM INFORMATION_SCHEMA.LOCALES;
+-----+-------+-------------------------------------+-----------------------+---------------------+---------------+--------------+------------------------+
| ID | NAME | DESCRIPTION | MAX_MONTH_NAME_LENGTH | MAX_DAY_NAME_LENGTH | DECIMAL_POINT | THOUSAND_SEP | ERROR_MESSAGE_LANGUAGE |
+-----+-------+-------------------------------------+-----------------------+---------------------+---------------+--------------+------------------------+
| 0 | en_US | English - United States | 9 | 9 | . | , | english |
| 1 | en_GB | English - United Kingdom | 9 | 9 | . | , | english |
| 2 | ja_JP | Japanese - Japan | 3 | 3 | . | , | japanese |
| 3 | sv_SE | Swedish - Sweden | 9 | 7 | , | | swedish |
| 4 | de_DE | German - Germany | 9 | 10 | , | . | german |
| 5 | fr_FR | French - France | 9 | 8 | , | | french |
| 6 | ar_AE | Arabic - United Arab Emirates | 6 | 8 | . | , | english |
| 7 | ar_BH | Arabic - Bahrain | 6 | 8 | . | , | english |
| 8 | ar_JO | Arabic - Jordan | 12 | 8 | . | , | english |
...
| 106 | no_NO | Norwegian - Norway | 9 | 7 | , | . | norwegian |
| 107 | sv_FI | Swedish - Finland | 9 | 7 | , | | swedish |
| 108 | zh_HK | Chinese - Hong Kong SAR | 3 | 3 | . | , | english |
| 109 | el_GR | Greek - Greece | 11 | 9 | , | . | greek |
| 110 | rm_CH | Romansh - Switzerland | 9 | 9 | , | . | english |
+-----+-------+-------------------------------------+-----------------------+---------------------+---------------+--------------+------------------------+*************************** 1. row ***************************
Table: char_byte_example
Create Table: CREATE TABLE `char_byte_example` (
`example` binary(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1DATECREATE TABLE t1 (d DATE);
INSERT INTO t1 VALUES ("2010-01-12"), ("2011-2-28"), ('120314'),('13*04*21');
SELECT * FROM t1;
+------------+
| d |
+------------+
| 2010-01-12 |
| 2011-02-28 |
| 2012-03-14 |
| 2013-04-21 |
+------------+CREATE TABLE date_formats_example (
description VARCHAR(30),
example DATE
);INSERT INTO date_formats_example VALUES
('Full year', '2022-12-30'),
('Short year', '22-12-30'),
('Short year no delimiters', '221230'),
('No delimiters', '20221230'),
('Pipe delimiters', '22|2|3'),
('Forward slash delimiter', '22/12/30'),
('Backward slash delimiter', '22\12\30'),
('Asterisk delimiter', '22*12*30'),
('Comma delimiter', '22,2,3');SELECT * FROM date_formats_example;+--------------------------+------------+
| description | example |
+--------------------------+------------+
| Full year | 2022-12-30 |
| Short year | 2022-12-30 |
| Short year no delimiters | 2022-12-30 |
| No delimiters | 2022-12-30 |
| Pipe delimiters | 2022-02-03 |
| Forward slash delimiter | 2022-12-30 |
| Backward slash delimiter | 2022-12-30 |
| Asterisk delimiter | 2022-12-30 |
| Comma delimiter | 2022-02-03 |
+--------------------------+------------+CREATE TABLE date_range_example (
description VARCHAR(30),
example DATE
);INSERT INTO date_range_example VALUES
('Minimum date', '0001-01-01'),
('Maximum date', '9999-12-31'),
('Below minimum range', '0000*1*1'),
('Above maximum range', '10000,12,31');ERROR 1292 (22007): Incorrect date value: '10000,12,31' for column `test`.`date_range_example`.`example` at row 4Warning (Code 1265): Data truncated for column 'example' at row 4SELECT * FROM date_range_example;+---------------------+------------+
| description | example |
+---------------------+------------+
| Minimum date | 0001-01-01 |
| Maximum date | 9999-12-31 |
| Below minimum range | 0000-01-01 |
| Above maximum range | 0000-00-00 |
+---------------------+------------+SELECT example, DATE_ADD(example, INTERVAL 1 DAY)
FROM date_range_example;+------------+-----------------------------------+
| example | DATE_ADD(example, INTERVAL 1 DAY) |
+------------+-----------------------------------+
| 0001-01-01 | 0001-01-02 |
| 9999-12-31 | NULL |
| 0000-01-01 | NULL |
| 0000-00-00 | NULL |
+------------+-----------------------------------+Warning (Code 1441): Datetime function: datetime field overflow
Warning (Code 1441): Datetime function: datetime field overflow
Warning (Code 1292): Incorrect datetime value: '0000-00-00'-- Disable STRICT_TRANS_TABLES and enable ALLOW_INVALID_DATES
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
SET sql_mode=(SELECT CONCAT(@@sql_mode, ',ALLOW_INVALID_DATES'));
<</code>>
<<sql>>
INSERT INTO date_range_example VALUES
('day is invalid for all months', '2019-12-32'),
('day is just large for February', '2019-02-31');Warning (Code 1265): Data truncated for column 'example' at row 1SELECT * FROM date_range_example;+--------------------------------+------------+
| description | example |
+--------------------------------+------------+
| day is invalid for all months | 0000-00-00 |
| day is just large for February | 2019-02-31 |
+--------------------------------+------------+TINYINT[(M)] [SIGNED | UNSIGNED | ZEROFILL]CREATE TABLE zf (
i1 TINYINT SIGNED,
i2 TINYINT UNSIGNED,
i3 TINYINT ZEROFILL
);
INSERT INTO zf VALUES (2,2,2);
SELECT * FROM zf;
+------+------+------+
| i1 | i2 | i3 |
+------+------+------+
| 2 | 2 | 002 |
+------+------+------+SHOW VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+
CREATE TABLE ranges (i1 TINYINT, i2 SMALLINT, i3 TINYINT UNSIGNED);
INSERT INTO ranges VALUES (257,257,257);
ERROR 1264 (22003): Out of range value for column 'i1' at row 1
SELECT * FROM ranges;
Empty set (0.10 sec)SHOW VARIABLES LIKE 'sql_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
CREATE TABLE ranges (i1 TINYINT, i2 SMALLINT, i3 TINYINT UNSIGNED);
INSERT INTO ranges VALUES (257,257,257);
Query OK, 1 row affected, 2 warnings (0.00 sec)
SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'i1' at row 1 |
| Warning | 1264 | Out of range value for column 'i3' at row 1 |
+---------+------+---------------------------------------------+
2 rows in set (0.00 sec)
SELECT * FROM ranges;
+------+------+------+
| i1 | i2 | i3 |
+------+------+------+
| 127 | 257 | 255 |
+------+------+------+TEXTSIGNEDZEROFILLINTINTEGERINTINT4 is a synonym for INT.
For details on the attributes, see Numeric Data Type Overview.
The INT data type may be SIGNED (allowing negative values) or UNSIGNED (not allowing negative values).
Example of INT SIGNED (the default):
Example of INT UNSIGNED:
A value is considered "out-of-range" when it is too small or too large to be stored in a data type. When sql_mode=STRICT_TRANS_TABLES (the default) is set, an out-of-range value generates an error. If strict mode is not in effect, the value is rounded to the nearest valid value and a warning is generated (which might be hidden, depending on your warning settings).
An example of non-strict out-of-range behavior:
A special type of INT UNSIGNED is INT ZEROFILL, which pads out the values with leading zeros in SELECT results. The number of leading zeros are just enough to pad the field out to the length of the type's maximum unsigned value, but the zeros are not included in an expression result or in a UNION SELECT column.
Using INT ZEROFILL works the same way as INT UNSIGNED for most operations except a simple SELECT. For example, with the following test table setup:
The resulting data would look like this:
This page is licensed: CC BY-SA / Gnu FDL
The UUID data type is intended for the storage of 128-bit UUID (Universally Unique Identifier) data. See the UUID function page for more details on UUIDs themselves.
Data retrieved by this data type is in the string representation defined in RFC4122.
String literals of hexadecimal characters and CHAR/VARCHAR/TEXT can be cast to the UUID data type. Likewise hexadecimal literals, binary-literals, and BINARY/VARBINARY/BLOB types can also be cast to UUID.
The data type will not accept a short UUID generated with the UUID_SHORT function, but will accept a value without the - character generated by the SYS_GUID function (or inserted directly). Hyphens can be partially omitted as well, or included after any group of two digits.
The type does not accept UUID values in braces, permitted by some implementations.
UUID values are stored in an index-friendly manner; the order of a UUID of llllllll-mmmm-Vhhh-vsss-nnnnnnnnnnnn is stored as:
This provides a sorting order, assuming a UUIDv1 (node and timestamp) is used, of the node, followed by the timestamp. The key aspect is the timestamps are sequential.
MariaDB starting with
Taking into account that UUIDv7 and other versions are designed around time ordering, UUID values version >= 6 are stored without byte-swapping, and UUID values with version >=8 and variant=0 are now considered invalid (as the SQL standard suggests).
UUID values version >= 6 are not stored without byte-swapping, and UUID values with version >=8 and variant=0 are not considered invalid.
Directly inserting via string literals:
Directly Inserting via hexadecimal literals:
Generating and inserting via the UUID function.
Retrieval:
The UUID_SHORT function does not generate valid full-length UUID:
Accepting a value without the - character, either directly or generated by the SYS_GUID function:
Valid and invalid hyphen and brace usage:
10.7 preview feature: UUID Data Type (mariadb.org blog post)
UUID function for generating v1 UUIDs
UUID function for generating v4 UUIDs
UUID function for generating v7 UUIDs
- UUID without the - character for Oracle compatibility
This page is licensed: CC BY-SA / Gnu FDL
Reference for storage space usage. This page lists the disk space consumed by each data type, helping with capacity planning and schema optimization.
The following tables indicate the approximate data storage requirements for each data type.
Note that MEDIUMINT columns will require 4 bytes in memory (for example, in InnoDB buffer pool).
are stored using a binary format, with the integer and the fraction stored separately. Each nine-digit multiple requires 4 bytes, followed by a number of bytes for whatever remains, as follows:
In the descriptions below, M is the declared column length (in characters or in bytes), while len is the actual length in bytes of the value.
In some , not all characters use the same number of bytes. utf8 encodes characters with one to three bytes per character, while utf8mb4 requires one to four bytes per character.
When using field the COMPRESSED attribute, 1 byte is reserved for metadata. For example, VARCHAR(255) will use +2 bytes instead of +1.
Assuming a single-byte character-set:
MariaDB defaults to the MySQL format (by means of the variable). Microseconds have the following additional storage requirements:
For the InnoDB , and row formats, a number of bytes will be allocated in the record header for the nullable fields. If there are between 1 and 8 nullable fields, 1 such byte will be allocated. In the record payload area, no space will be reserved for values that are NULL.
For the , the overhead is 1 bit in the record header (as a part of the 1-byte or 2-byte "end of field" pointer). In that format, a NULL fixed-length field will consume the same amount of space as any NOT NULL value in the record payload area. The motivation is that it is possible to update in place between NOT NULL and NULL values.
In other formats, NULL values usually require 1 bit in the data file, 1 byte in the index file.
This page is licensed: CC BY-SA / Gnu FDL
Store date and time combinations. This type stores values in 'YYYY-MM-DD HH:MM:SS' format, with a range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
A date and time combination.
MariaDB displays DATETIME values in 'YYYY-MM-DD HH:MM:SS.ffffff' format, but allows assignment of values to DATETIME columns using either strings or numbers. For details, see .
DATETIME columns also accept as the default value.
The option, on by default, allows MariaDB to store DATETME values using the same low-level format MySQL 5.6 uses. For more information, see , below.
For storage requirements, see .
MariaDB stores values that use the DATETIME data type in a format that supports values between 1000-01-01 00:00:00.000000 and 9999-12-31 23:59:59.999999.
MariaDB can also store with a precision between 0 and 6. If no microsecond precision is specified, then 0 is used by default.
MariaDB also supports '0000-00-00' as a special zero-date value, unless is specified in the . Similarly, individual components of a date can be set to 0 (for example: '2015-00-12'), unless is specified in the . In many cases, the result of en expression involving a zero-date, or a date with zero-parts, is NULL. If the SQL_MODE is enabled, if the day part is in the range between 1 and 31, the date does not produce any error, even for months that have less than 31 days.
In , DATE with a time portion is a synonym for DATETIME. See also .
A new temporal format was introduced from MySQL 5.6 that alters how the TIME, DATETIME and TIMESTAMP columns operate at lower levels. These changes allow these temporal data types to have fractional parts and negative values. You can disable this feature using the system variable.
Tables that include TIMESTAMP values that were created on an older version of MariaDB or that were created while the system variable was disabled continue to store data using the older data type format.
In order to update table columns from the older format to the newer format, execute an statement that changes the column to the same data type. This change may be needed if you want to export the table's tablespace and import it onto a server that has mysql56_temporal_format=ON set (see ).
For instance, if you have a DATETIME column in your table:
When MariaDB executes the statement, it converts the data from the older temporal format to the newer one.
In the event that you have several tables and columns using temporal data types that you want to switch over to the new format, make sure the system variable is enabled, then perform a dump and restore using mysqldump. The columns using relevant temporal data types are restored using the new temporal format.
Columns with old temporal formats are marked with a /* mariadb-5.3 */ comment in the output of , , statements, as well as in the COLUMN_TYPE column of the .
Strings used in datetime context are automatically converted to datetime(6). If you want to have a datetime without seconds, you should use .
The resulting data would look like this:
The default microsecond precision when unspecified is 0, and you can use that in a cast in order to trim off stored microseconds:
data type qualifier
This page is licensed: GPLv2, originally from
Large integer type. A BIGINT uses 8 bytes and can store values from -9223372036854775808 to 9223372036854775807 (signed) or 0 to 18446744073709551615 (unsigned).
A large integer. The signed range is -9223372036854775808 to9223372036854775807. The unsigned range is 0 to18446744073709551615.
If a column has been set to ZEROFILL, all values will be prepended by zeros so that the BIGINT value contains a number of M digits.
Note: If the ZEROFILL attribute has been specified, the column will automatically become UNSIGNED.
For more details on the attributes, see .
SERIAL is an alias for:
INT8 is a synonym for BIGINT.
The BIGINT data type may be SIGNED (allowing negative values) or UNSIGNED (not allowing negative values).
Example of BIGINT SIGNED (the default):
Example of BIGINT UNSIGNED:
A value is considered "out of range" when it is too small or too large to be stored in a data type. When sql_mode=STRICT_TRANS_TABLES (the default) is set, an out-of-range value generates an error. If strict mode is not in effect, the value is rounded to the nearest valid value and a warning is generated (which might be hidden, depending on your warning settings).
An example of non-strict out-of-range behavior:
A special type of BIGINT UNSIGNED is BIGINT ZEROFILL, which pads out the values with leading zeros in SELECT results. The number of leading zeros are just enough to pad the field out to the length of the type's maximum unsigned value, but the zeros are not included in an expression result or in a UNION SELECT column.
Using BIGINT ZEROFILL works the same way as BIGINT UNSIGNED for most operations except a simple SELECT. For example, with the following test table setup:
The resulting data would look like this:
This page is licensed: GPLv2, originally from
Medium-sized integer. A MEDIUMINT column uses 3 bytes and stores values from -8388608 to 8388607 (signed) or 0 to 16777215 (unsigned).
A medium-sized integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.
ZEROFILL pads the integer with zeroes and assumes UNSIGNED (even if UNSIGNED is not specified).
INT3 is a synonym for MEDIUMINT.
For details on the attributes, see .
The MEDIUMINT data type may be SIGNED (allowing negative values) or UNSIGNED (not allowing negative values).
Example of MEDIUMINT SIGNED (the default):
Example of MEDIUMINT UNSIGNED:
A value is considered "out of range" when it is too small or too large to be stored in a data type. When sql_mode=STRICT_TRANS_TABLES (the default) is set, an out-of-range value generates an error. If strict mode is not in effect, the value is rounded to the nearest valid value and a warning is generated (which might be hidden, depending on your warning settings).
An example of non-strict out-of-range behavior:
A special type of MEDIUMINT UNSIGNED is MEDIUMINT ZEROFILL, which pads out the values with leading zeros in SELECT results. The number of leading zeros are just enough to pad the field out to the length of the type's maximum unsigned value, but the zeros are not included in an expression result or in a UNION SELECT column.
Using MEDIUMINT ZEROFILL works the same way as MEDIUMINT UNSIGNED for most operations except a simple SELECT. For example, with the following test table setup:
The resulting data would look like this:
This page is licensed: GPLv2, originally from
Learn how to configure the lc_messages and lc_messages_dir system variables to display server error messages in a supported local language.
MariaDB server error messages are by default in English. However, MariaDB server also supports error message localization in many different languages. Each supported language has its own version of the error message file called errmsg.sys in a dedicated directory for that language.
Error message localization is supported for the following languages:
Bulgarian
Chinese (from , , , , )
Czech
Danish
Dutch
English
Estonian
French
Georgian (from )
German
Greek
Hindi
Hungarian
Italian
Japanese
Korean
Norwegian
Norwegian-ny (Nynorsk)
Polish
Portuguese
Romanian
Russian
Serbian
Slovak
Spanish
Swahili (from )
Swedish
Ukrainian
lc_messages and lc_messages_dir System VariablesThe and system variables can be used to set the used for error messages.
The system variable can be specified as a name. The language of the associated will be used for error messages. See for a list of supported locales and their associated languages.
The system variable is set to en_US by default, which means that error messages are in English by default.
If the system variable is set to a valid name, but the server can't find an for the language associated with the , then the default language will be used instead.
This system variable can be specified as command-line arguments to or it can be specified in a relevant server in an . For example:
The system variable can also be changed dynamically with . For example:
If a server has the system variable set to the fr_CA locale like the above example, then error messages would be in French. For example:
The system variable can be specified either as the path to the directory storing the server's or as the path to the directory storing the specific language's .
The server initially tries to interpret the value of the system variable as a path to the directory storing the server's . Therefore, it constructs the path to the language's by concatenating the value of the system variable with the language name of the specified by the system variable .
If the server does not find the for the language, then it tries to interpret the value of the system variable as a direct path to the directory storing the specific language's .
This system variable can be specified as command-line arguments to or it can be specified in a relevant server in an .
For example, to specify the path to the directory storing the server's :
Or to specify the path to the directory storing the specific language's :
The system variable can not be changed dynamically.
The option can also be used to set the server's language for error messages, but it is deprecated. It is recommended to set the system variable instead.
The option can be specified either as a language name or as the path to the directory storing the language's . See for a list of supported locales and their associated languages.
This option can be specified as command-line arguments to or it can be specified in a relevant server in an .
For example, to specify a language name:
Or to specify the path to the directory storing the language's :
The character set that the error messages are returned in is determined by the variable, which defaults to UTF8.
This page is licensed: CC BY-SA / Gnu FDL
Variable-length character string type. VARCHAR columns store strings of variable length up to a specified maximum (up to 65,535).
A variable-length string. M represents the maximum column length in characters. The range of M is 0 to 65,532. The effective maximum length of a VARCHAR is subject to the maximum row size and the character set used. For example, utf-8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf-8 character set can be declared to be a maximum of 21,844 characters.
For the engine, M represents the maximum column length in bytes.
MariaDB stores VARCHAR values as a one-byte or two-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A VARCHAR column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.
MariaDB follows the standard SQL specification and does not remove trailing spaces from VARCHAR values.
VARCHAR(0) columns can contain 2 values: an empty string or NULL. Such columns cannot be part of an index. The storage engine does not support VARCHAR(0).
VARCHAR is shorthand for CHARACTER VARYING. NATIONAL VARCHAR is the standard SQL way to define that a VARCHAR column should use some predefined character set. MariaDB uses utf-8 as its
predefined character set, as does MySQL. NVARCHAR is shorthand for NATIONAL VARCHAR.
For MariaDB, a number of are available.
If a unique index consists of a column where trailing pad characters are stripped or ignored, inserts into that column where values differ only by the number of trailing pad characters will result in a duplicate-key error.
The following are synonyms for VARCHAR:
CHAR VARYING
CHARACTER VARYING
VARCHAR2
VARCHARACTER
The following are equivalent:
For our example of VARCHAR, we picked a maximum size that avoids overflowing the maximum row size (65535). Keep in mind that a multi-byte character set would need more space in the row than a single-byte character set. We also avoid the auto-conversion of a VARCHAR into a TEXT, MEDIUMTEXT, or LONGTEXT that can happen when STRICT_TRANS_TABLES is not set in the SQL_MODE.
The example:
When SQL_MODE is strict (the default), a value is considered "too long" when its length exceeds the size of the data type, and an error is generated.
Example of data too long behavior for VARCHAR:
Depending on whether or not is set, you will either get a warning or an error if you try to insert a string that is too long into a VARCHAR column. If the extra characters are spaces, the spaces that can't fit will be removed, and you will always get a warning, regardless of the setting.
VARCHAR columns can be fully indexed. columns can only be indexed over a specified length.
Using or in a query that uses temporary tables for storing intermediate results will force the temporary table to be disk-based (using the instead of the , which is a bit slower. This is not that bad, as the caches the rows in memory. To get the benefit of this, one should ensure that the variable is big enough to hold most of the row and index data for temporary tables.
In , VARCHAR2 is a synonym.
Internally, the full length of the VARCHAR column is allocated inside each TABLE objects record[] structure. As there are three such buffers, each open table will allocate 3 times the max-length-to-store-varchar bytes of memory.
and columns are stored with a pointer (4 or 8 bytes) + a 1-4 byte length. The data is only stored once. This means that internally TEXT uses less memory for each open table but instead has the additional overhead that each TEXT object needs to be allocated and freed for each row access (with some caching in between).
This page is licensed: GPLv2, originally from
IPv4 address data type. Stores IPv4 addresses as 4-byte binary strings for efficient storage and retrieval.
INET4 is a data type to store IPv4 addresses, as 4-byte binary strings.
Casting from data types to INET6 is permitted, allowing for example comparisons between the two data types, and for INET4 values to be inserted into INET6 columns.
Casting from data types to INET6 is not permitted.
Casting from INET4 to is permitted, allowing direct inserts.
Comparisons are also permitted:
This page is licensed: CC BY-SA / Gnu FDL
Manage time zone settings in MariaDB, including the global server time zone, session time zone, and system time zone configurations.
CREATE TABLE t (j JSON);
DESC t;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| j | longtext | YES | | NULL | |
+-------+----------+------+-----+---------+-------+CREATE TABLE t2 (
j JSON
CHECK (JSON_VALID(j))
);
INSERT INTO t2 VALUES ('invalid');
ERROR 4025 (23000): CONSTRAINT `j` failed for `test`.`t2`
INSERT INTO t2 VALUES ('{"id": 1, "name": "Monty"}');
Query OK, 1 row affected (0.13 sec)CREATE TABLE json_example (
description VARCHAR(20),
example JSON
);SHOW CREATE TABLE json_example\G*************************** 1. row ***************************
Table: json_example
Create Table: CREATE TABLE `json_example` (
`description` varchar(20) DEFAULT NULL,
`example` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`example`))
) ENGINE=InnoDB DEFAULT CHARSET=latin1INSERT INTO json_example VALUES
('Array', '[ 1, 2, 3 ]'),
('Dictionary', '{ "a": 1, "b": 2 }'),
('Duplicates', '{ "a":1,"b":2, "a":3,"b": 4,"b":5}');SELECT * FROM json_example;+-------------+------------------------------------+
| description | example |
+-------------+------------------------------------+
| Array | [ 1, 2, 3 ] |
| Dictionary | { "a": 1, "b": 2 } |
| Duplicates | { "a":1,"b":2, "a":3,"b": 4,"b":5} |
+-------------+------------------------------------+SELECT description, JSON_EXTRACT(example, '$.b')
FROM json_example;+-------------+------------------------------+
| description | JSON_EXTRACT(example, '$.b') |
+-------------+------------------------------+
| Array | NULL |
| Dictionary | 2 |
| Duplicates | 2 |
+-------------+------------------------------+INSERT INTO json_example VALUES
('Invalid', '{');ERROR 4025 (23000): CONSTRAINT `json_example.example` failed for `test`.`json_example`INT[(M)] [SIGNED | UNSIGNED | ZEROFILL]
INTEGER[(M)] [SIGNED | UNSIGNED | ZEROFILL]CREATE TABLE ints (a INT,b INT UNSIGNED,c INT ZEROFILL);INSERT INTO ints VALUES (-10,-10,-10);
ERROR 1264 (22003): Out of range value for column 'b' at row 1
INSERT INTO ints VALUES (-10,10,-10);
ERROR 1264 (22003): Out of range value for column 'c' at row 1
INSERT INTO ints VALUES (-10,10,10);
INSERT INTO ints VALUES (2147483648,2147483648,2147483648);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
INSERT INTO ints VALUES (2147483647,2147483648,2147483648);
SELECT * FROM ints;
+------------+------------+------------+
| a | b | c |
+------------+------------+------------+
| -10 | 10 | 0000000010 |
| 2147483647 | 2147483648 | 2147483648 |
+------------+------------+------------+CREATE TABLE int_signed_example (
description VARCHAR(20),
example INT SIGNED
);INSERT INTO int_signed_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', -2147483648),
('Maximum', 2147483647);CREATE TABLE int_unsigned_example (
description VARCHAR(20),
example INT UNSIGNED
);INSERT INTO int_unsigned_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', 0),
('Maximum', 4294967295);TRUNCATE int_signed_example;
-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO int_signed_example VALUES
('Underflow', -2147483649),
('Overflow', 2147483648);Warning (sql 1264): Out of range value for column 'example' at row 1
Warning (sql 1264): Out of range value for column 'example' at row 2SELECT * FROM int_signed_example;
+-------------+-------------+
| description | example |
+-------------+-------------+
| Underflow | -2147483648 |
| Overflow | 2147483647 |
+-------------+-------------+TRUNCATE int_unsigned_example;
-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO int_unsigned_example VALUES
('Underflow', -1),
('Overflow', 4294967296);Warning (sql 1264): Out of range value for column 'example' at row 1
Warning (sql 1264): Out of range value for column 'example' at row 2SELECT * FROM int_unsigned_example;
+-------------+------------+
| description | example |
+-------------+------------+
| Underflow | 0 |
| Overflow | 4294967295 |
+-------------+------------+CREATE TABLE int_zerofill_example (
description VARCHAR(20),
example INT ZEROFILL
);INSERT INTO int_zerofill_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', 0),
('Maximum', 4294967295);
-- Turn off strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO int_zerofill_example VALUES
('Underflow', -1),
('Overflow', 4294967296);Warning (sql 1264): Out of range value for column 'example' at row 1
Warning (sql 1264): Out of range value for column 'example' at row 2SELECT *, example + 0 FROM int_zerofill_example;
+-------------+------------+-------------+
| description | example | example + 0 |
+-------------+------------+-------------+
| Zero | 0000000000 | 0 |
| Forty-Two | 0000000042 | 42 |
| Minimum | 0000000000 | 0 |
| Maximum | 4294967295 | 4294967295 |
| Underflow | 0000000000 | 0 |
| Overflow | 4294967295 | 4294967295 |
+-------------+------------+-------------+UUIDnnnnnnnnnnnn-vsss-Vhhh-mmmm-llllllllCREATE TABLE t1 (id UUID);INSERT INTO t1 VALUES('123e4567-e89b-12d3-a456-426655440000');INSERT INTO t1 VALUES (x'fffffffffffffffffffffffffffffffe');INSERT INTO t1 VALUES (UUID());SELECT * FROM t1;
+--------------------------------------+
| id |
+--------------------------------------+
| 123e4567-e89b-12d3-a456-426655440000 |
| ffffffff-ffff-ffff-ffff-fffffffffffe |
| 93aac041-1a14-11ec-ab4e-f859713e4be4 |
+--------------------------------------+INSERT INTO t1 VALUES (UUID_SHORT());
ERROR 1292 (22007): Incorrect uuid value: '99440417627439104'
for column `test`.`t1`.`id` at row 1INSERT INTO t1 VALUES (SYS_GUID());
SELECT * FROM t1;
+--------------------------------------+
| id |
+--------------------------------------+
| 123e4567-e89b-12d3-a456-426655440000 |
| ffffffff-ffff-ffff-ffff-fffffffffffe |
| 93aac041-1a14-11ec-ab4e-f859713e4be4 |
| ea0368d3-1a14-11ec-ab4e-f859713e4be4 |
+--------------------------------------+
SELECT SYS_GUID();
+----------------------------------+
| SYS_GUID() |
+----------------------------------+
| ff5b6bcc1a1411ecab4ef859713e4be4 |
+----------------------------------+
INSERT INTO t1 VALUES ('ff5b6bcc1a1411ecab4ef859713e4be4');
SELECT * FROM t1;
+--------------------------------------+
| id |
+--------------------------------------+
| 123e4567-e89b-12d3-a456-426655440000 |
| ffffffff-ffff-ffff-ffff-fffffffffffe |
| 93aac041-1a14-11ec-ab4e-f859713e4be4 |
| ea0368d3-1a14-11ec-ab4e-f859713e4be4 |
| ff5b6bcc-1a14-11ec-ab4e-f859713e4be4 |
+--------------------------------------+TRUNCATE t1;
INSERT INTO t1 VALUES ('f8aa-ed66-1a1b-11ec-ab4e-f859-713e-4be4');
INSERT INTO t1 VALUES ('1b80667f1a1c-11ecab4ef859713e4be4');
INSERT INTO t1 VALUES ('2fd6c945-1a-1c-11ec-ab4e-f859713e4be4');
INSERT INTO t1 VALUES ('49-c9-f9-59-1a-1c-11ec-ab4e-f859713e4be4');
INSERT INTO t1 VALUES ('57-96-da-c1-1a-1c-11-ec-ab-4e-f8-59-71-3e-4b-e4');
INSERT INTO t1 VALUES ('6-eb74f8f-1a1c-11ec-ab4e-f859713e4be4');
INSERT INTO t1 VALUES ('{29bad136-1a1d-11ec-ab4e-f859713e4be4}');
ERROR 1292 (22007): Incorrect uuid value: '{29bad136-1a1d-11ec-ab4e-f859713e4be4}'
for column `test`.`t1`.`id` at row 1
SELECT * FROM t1;
+--------------------------------------+
| id |
+--------------------------------------+
| f8aaed66-1a1b-11ec-ab4e-f859713e4be4 |
| 1b80667f-1a1c-11ec-ab4e-f859713e4be4 |
| 2fd6c945-1a1c-11ec-ab4e-f859713e4be4 |
| 49c9f959-1a1c-11ec-ab4e-f859713e4be4 |
| 5796dac1-1a1c-11ec-ab4e-f859713e4be4 |
| 6eb74f8f-1a1c-11ec-ab4e-f859713e4be4 |
+--------------------------------------+DATETIME [(microsecond precision)]BIGINT[(M)] [SIGNED | UNSIGNED | ZEROFILL]MEDIUMINT[(M)] [SIGNED | UNSIGNED | ZEROFILL][NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]INET4-3.402823466E+38 to -1.175494351E-38
0
1.175494351E-38 to 3.402823466E+38.
These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.
M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits allowed by the hardware. A single-precision floating-point number is accurate to approximately 7 decimal places.
UNSIGNED, if specified, disallows negative values.
Using FLOAT might give you some unexpected problems because all calculations in MariaDB are done with double precision. See Floating Point Accuracy.
For more details on the attributes, see Numeric Data Type Overview.
The FLOAT data type may be SIGNED (allowing negative values) or UNSIGNED (not allowing negative values).
Example of FLOAT SIGNED (SIGNED is the default):
Example of FLOAT UNSIGNED:
A value is considered "out of range" when it is too small or too large to be stored in a data type. The size specified when creating the column is the limit for what values can be represented. The limits can also vary based on your hardware and operating system. When SQL_MODE is strict (the default) an out-of-range value generates an error and the operation fails. If strict mode is not in effect, the value is rounded to the nearest valid value and a warning is generated (which might be hidden, depending on your warning settings).
A value whose significant digits must be rounded to fit only generates a warning note about data truncation, since it is only an out-of-range value if the rounding causes the value to overflow. A somewhat strange exception happens when the decimal places are 16 digits or larger: at that point the value can round up to be one digit larger than you would expect to be accepted, but only for the next larger power of 10. For instance, a FLOAT(17,16) should max out at 9.9999999999999999, but that value rounds up to being equivalent to 10 (and 11 overflows).
A special type of FLOAT UNSIGNED is FLOAT ZEROFILL, which pads out the values with leading zeros in SELECT results. The number of leading zeros are just enough to pad the field out to the length of the type's field size (counting the decimal point), but the zeros are not included in an expression result or in a UNION SELECT column.
Using FLOAT ZEROFILL works the same way as FLOAT UNSIGNED for most operations except a simple SELECT. For example, with the following test table setup:
This page is licensed: GPLv2, originally from fill_help_tables.sql
BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUECREATE TABLE bigints (a BIGINT,b BIGINT UNSIGNED,c BIGINT ZEROFILL);
INSERT INTO bigints VALUES (-10,-10,-10);
ERROR 1264 (22003): Out of range value for column 'b' at row 1
INSERT INTO bigints VALUES (-10,10,-10);
ERROR 1264 (22003): Out of range value for column 'c' at row 1
INSERT INTO bigints VALUES (-10,10,10);
INSERT INTO bigints VALUES (9223372036854775808,9223372036854775808,9223372036854775808);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
INSERT INTO bigints VALUES (9223372036854775807,9223372036854775808,9223372036854775808);
SELECT * FROM bigints;
+---------------------+---------------------+----------------------+
| a | b | c |
+---------------------+---------------------+----------------------+
| -10 | 10 | 00000000000000000010 |
| 9223372036854775807 | 9223372036854775808 | 09223372036854775808 |
+---------------------+---------------------+----------------------+CREATE TABLE bigint_signed_example (
description VARCHAR(20),
example BIGINT SIGNED
);INSERT INTO bigint_signed_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', -9223372036854775808),
('Maximum', 9223372036854775807);CREATE TABLE bigint_unsigned_example (
description VARCHAR(20),
example BIGINT UNSIGNED
);INSERT INTO bigint_unsigned_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', 0),
('Maximum', 18446744073709551615);TRUNCATE bigint_signed_example;
-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO bigint_signed_example VALUES
('Underflow', -9223372036854775809),
('Overflow', 9223372036854775808);
Warning (Code 1264): Out of range value for column 'example' at row 1
Warning (Code 1264): Out of range value for column 'example' at row 2
SELECT * FROM bigint_signed_example;
+-------------+----------------------+
| description | example |
+-------------+----------------------+
| Underflow | -9223372036854775808 |
| Overflow | 9223372036854775807 |
+-------------+----------------------+
TRUNCATE bigint_unsigned_example;
-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO bigint_unsigned_example VALUES
('Underflow', -1),
('Overflow', 18446744073709551616);
Warning (Code 1264): Out of range value for column 'example' at row 1
Warning (Code 1264): Out of range value for column 'example' at row 2
SELECT * FROM bigint_unsigned_example;
+-------------+----------------------+
| description | example |
+-------------+----------------------+
| Underflow | 0 |
| Overflow | 18446744073709551615 |
+-------------+----------------------+CREATE TABLE bigint_zerofill_example (
description VARCHAR(20),
example BIGINT ZEROFILL
);
INSERT INTO bigint_zerofill_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', 0),
('Maximum', 18446744073709551615);
-- Turn off strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO bigint_zerofill_example VALUES
('Underflow', -1),
('Overflow', 18446744073709551616);
Warning (Code 1264): Out of range value for column 'example' at row 1
Warning (Code 1264): Out of range value for column 'example' at row 2SELECT *, example + 0 FROM bigint_zerofill_example;
+-------------+----------------------+----------------------+
| description | example | example + 0 |
+-------------+----------------------+----------------------+
| Zero | 00000000000000000000 | 0 |
| Forty-Two | 00000000000000000042 | 42 |
| Minimum | 00000000000000000000 | 0 |
| Maximum | 18446744073709551615 | 18446744073709551615 |
| Underflow | 00000000000000000000 | 0 |
| Overflow | 18446744073709551615 | 18446744073709551615 |
+-------------+----------------------+----------------------+CREATE TABLE mediumints (a MEDIUMINT,b MEDIUMINT UNSIGNED,c MEDIUMINT ZEROFILL);
DESCRIBE mediumints;
+-------+--------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------------+------+-----+---------+-------+
| a | mediumint(9) | YES | | NULL | |
| b | mediumint(8) unsigned | YES | | NULL | |
| c | mediumint(8) unsigned zerofill | YES | | NULL | |
+-------+--------------------------------+------+-----+---------+-------+INSERT INTO mediumints VALUES (-10,-10,-10);
ERROR 1264 (22003): Out of range value for column 'b' at row 1
INSERT INTO mediumints VALUES (-10,10,-10);
ERROR 1264 (22003): Out of range value for column 'c' at row 1
INSERT INTO mediumints VALUES (-10,10,10);
INSERT INTO mediumints VALUES (8388608,8388608,8388608);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
INSERT INTO mediumints VALUES (8388607,8388608,8388608);
SELECT * FROM mediumints;
+---------+---------+----------+
| a | b | c |
+---------+---------+----------+
| -10 | 10 | 00000010 |
| 8388607 | 8388608 | 08388608 |
+---------+---------+----------+CREATE TABLE mediumint_signed_example (
description VARCHAR(20),
example MEDIUMINT SIGNED
);INSERT INTO mediumint_signed_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', -8388608),
('Maximum', 8388607);CREATE TABLE mediumint_unsigned_example (
description VARCHAR(20),
example MEDIUMINT UNSIGNED
);INSERT INTO mediumint_unsigned_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', 0),
('Maximum', 16777215);TRUNCATE mediumint_signed_example;-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));INSERT INTO mediumint_signed_example VALUES
('Underflow', -8388609),
('Overflow', 8388608);Warning (sql 1264): Out of range value for column 'example' at row 1
Warning (sql 1264): Out of range value for column 'example' at row 2SELECT * FROM mediumint_signed_example;+-------------+----------+
| description | example |
+-------------+----------+
| Underflow | -8388608 |
| Overflow | 8388607 |
+-------------+----------+TRUNCATE mediumint_unsigned_example;-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));INSERT INTO mediumint_unsigned_example VALUES
('Underflow', -1),
('Overflow', 16777216);Warning (sql 1264): Out of range value for column 'example' at row 1
Warning (sql 1264): Out of range value for column 'example' at row 2SELECT * FROM mediumint_unsigned_example;+-------------+----------+
| description | example |
+-------------+----------+
| Underflow | 0 |
| Overflow | 16777215 |
+-------------+----------+CREATE TABLE mediumint_zerofill_example (
description VARCHAR(20),
example MEDIUMINT ZEROFILL
);INSERT INTO mediumint_zerofill_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', 0),
('Maximum', 16777215);-- Turn off strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));INSERT INTO mediumint_zerofill_example VALUES
('Underflow', -1),
('Overflow', 16777216);Warning (sql 1264): Out of range value for column 'example' at row 1
Warning (sql 1264): Out of range value for column 'example' at row 2SELECT *, example + 0 FROM mediumint_zerofill_example;+-------------+----------+-------------+
| description | example | example + 0 |
+-------------+----------+-------------+
| Zero | 00000000 | 0 |
| Forty-Two | 00000042 | 42 |
| Minimum | 00000000 | 0 |
| Maximum | 16777215 | 16777215 |
| Underflow | 00000000 | 0 |
| Overflow | 16777215 | 16777215 |
+-------------+----------+-------------+CREATE OR REPLACE TABLE t1 (a INET4);
INSERT INTO t1 VALUES('0.0.0.0'), ('255.10.0.0'), ('255.255.255.255');
INSERT INTO t1 VALUES (0xa0000001);
INSERT INTO t1 VALUES (0xf0000000);
INSERT INTO t1 VALUES (0xff000001);
SELECT HEX(a), a FROM t1 ORDER BY a;
+----------+-----------------+
| HEX(a) | a |
+----------+-----------------+
| 00000000 | 0.0.0.0 |
| A0000001 | 160.0.0.1 |
| F0000000 | 240.0.0.0 |
| FF000001 | 255.0.0.1 |
| FF0A0000 | 255.10.0.0 |
| FFFFFFFF | 255.255.255.255 |
+----------+-----------------+CREATE TABLE t1 (a INET6);
INSERT INTO t1 VALUES('0.0.0.0'), ('255.10.0.0'), ('255.255.255.255');
Query OK, 3 rows affected (0.027 sec)CREATE OR REPLACE TABLE t1 (i4 INET4, i6 INET6);
INSERT INTO t1 VALUES('10.10.10.10','::ffff:192.168.0.1');
SELECT LEAST(i4,i6) FROM t1;
+--------------------+
| LEAST(i4,i6) |
+--------------------+
| ::ffff:10.10.10.10 |
+--------------------+CREATE TABLE t1 (a INET6);
INSERT INTO t1 VALUES('0.0.0.0'), ('255.10.0.0'), ('255.255.255.255');
ERROR 1292 (22007): Incorrect inet6 value: '0.0.0.0' for column `test`.`t1`.`a` at row 1FLOAT[(M,D)] [SIGNED | UNSIGNED | ZEROFILL]CREATE TABLE float_signed_example (
description VARCHAR(20),
example FLOAT,
sz6_2 FLOAT(6,2),
sz20_19 FLOAT(20,19) SIGNED
);SET @pi = 3.1415926535897932384626433832795;
INSERT INTO float_signed_example VALUES
('Pi', @pi, @pi, @pi),
('Series', 1234.567890123, 1234.567890123, 1.234567890123),
('Negative', -1234.567890123, -1234.567890123, -1.234567890123),
('Various', 1234567890, 9999.99, 9.9999999999999999999);SELECT * FROM float_signed_example;
+-------------+------------+----------+------------------------+
| description | example | sz6_2 | sz20_19 |
+-------------+------------+----------+------------------------+
| Pi | 3.14159 | 3.14 | 3.1415927410125732000 |
| Series | 1234.57 | 1234.57 | 1.2345678806304932000 |
| Negative | -1234.57 | -1234.57 | -1.2345678806304932000 |
| Various | 1234570000 | 9999.99 | 10.0000000000000000000 |
+-------------+------------+----------+------------------------+CREATE TABLE float_unsigned_example (
description VARCHAR(20),
example FLOAT UNSIGNED,
sz6_2 FLOAT(6,2) UNSIGNED,
sz20_19 FLOAT(20,19) UNSIGNED
);SET @pi = 3.1415926535897932384626433832795;INSERT INTO float_unsigned_example VALUES
('Pi', @pi, @pi, @pi),
('Series', 1234.567890123, 1234.567890123, 1.234567890123),
('Various', 1234567890, 9999.99, 9.9999999999999999999);SELECT * FROM float_unsigned_example;
+-------------+------------+---------+------------------------+
| description | example | sz6_2 | sz20_19 |
+-------------+------------+---------+------------------------+
| Pi | 3.14159 | 3.14 | 3.1415927410125732000 |
| Series | 1234.57 | 1234.57 | 1.2345678806304932000 |
| Various | 1234570000 | 9999.99 | 10.0000000000000000000 |
+-------------+------------+---------+------------------------+TRUNCATE float_signed_example;
-- Disable strict mode
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO float_signed_example VALUES
('OK', 3.402823466e38, NULL, NULL),
('Overflow', 4.0e38, NULL, NULL),
('OK', -3.402823466e38, NULL, NULL),
('Underflow', -4.0e38, NULL, NULL),
('OK', NULL, 9999.99, NULL),
('Rounded overflow', NULL, 9999.995, NULL),
('Overflow', NULL, 10000, NULL),
('OK', NULL, -9999.99, NULL),
('Rounded underflow', NULL, -9999.995, NULL),
('Underflow', NULL, -10000, NULL),
('OK', NULL, NULL, 9.9999999999999999),
('Rounded OK', NULL, NULL, 9.99999999999999995),
('Overflow', NULL, NULL, 11),
('OK', NULL, NULL, -9.9999999999999999),
('Rounded OK', NULL, NULL, -9.99999999999999995),
('Underflow', NULL, NULL, -11);Warning (sql 1264): Out of range value for column 'example' at row 2
Warning (sql 1264): Out of range value for column 'example' at row 4
Warning (sql 1264): Out of range value for column 'sz6_2' at row 6
Warning (sql 1264): Out of range value for column 'sz6_2' at row 7
Warning (sql 1264): Out of range value for column 'sz6_2' at row 9
Warning (sql 1264): Out of range value for column 'sz6_2' at row 10
Warning (sql 1264): Out of range value for column 'sz20_19' at row 13
Warning (sql 1264): Out of range value for column 'sz20_19' at row 16SELECT * FROM float_signed_example;
+-------------------+-------------+----------+-------------------------+
| description | example | sz6_2 | sz20_19 |
+-------------------+-------------+----------+-------------------------+
| OK | 3.40282e38 | NULL | NULL |
| Overflow | 3.40282e38 | NULL | NULL |
| OK | -3.40282e38 | NULL | NULL |
| Underflow | -3.40282e38 | NULL | NULL |
| OK | NULL | 9999.99 | NULL |
| Rounded overflow | NULL | 9999.99 | NULL |
| Overflow | NULL | 9999.99 | NULL |
| OK | NULL | -9999.99 | NULL |
| Rounded underflow | NULL | -9999.99 | NULL |
| Underflow | NULL | -9999.99 | NULL |
| OK | NULL | NULL | 10.0000000000000000000 |
| Rounded OK | NULL | NULL | 10.0000000000000000000 |
| Overflow | NULL | NULL | 10.0000000000000000000 |
| OK | NULL | NULL | -10.0000000000000000000 |
| Rounded OK | NULL | NULL | -10.0000000000000000000 |
| Underflow | NULL | NULL | -10.0000000000000000000 |
+-------------------+-------------+----------+-------------------------+TRUNCATE float_unsigned_example;
-- Disable strict mode
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO float_unsigned_example VALUES
('OK', 3.402823466e38, NULL, NULL),
('Overflow', 4.0e38, NULL, NULL),
('Underflow', -1, NULL, NULL);Warning (sql 1264): Out of range value for column 'example' at row 2
Warning (sql 1264): Out of range value for column 'example' at row 3SELECT * FROM float_unsigned_example;
+-------------+------------+-------+---------+
| description | example | sz6_2 | sz20_19 |
+-------------+------------+-------+---------+
| OK | 3.40282e38 | NULL | NULL |
| Overflow | 3.40282e38 | NULL | NULL |
| Underflow | 0 | NULL | NULL |
+-------------+------------+-------+---------+CREATE TABLE float_zerofill_example (
description VARCHAR(20),
example FLOAT ZEROFILL,
sz6_2 FLOAT(6,2) ZEROFILL,
sz20_19 FLOAT(20,19) ZEROFILL
);SET @pi = 3.1415926535897932384626433832795;INSERT INTO float_zerofill_example VALUES
('Pi', @pi, @pi, @pi),
('Series', 1234.567890123, 1234.567890123, 1.234567890123),
('Various', 1234567890, 9999.99, 9.9999999999999999999);SELECT * FROM float_zerofill_example;
+-------------+--------------+---------+------------------------+
| description | example | sz6_2 | sz20_19 |
+-------------+--------------+---------+------------------------+
| Pi | 000003.14159 | 003.14 | 3.1415927410125732000 |
| Series | 000001234.57 | 1234.57 | 1.2345678806304932000 |
| Various | 001234570000 | 9999.99 | 10.0000000000000000000 |
+-------------+--------------+---------+------------------------+6
3 bytes
7
4 bytes
8
4 bytes
,
len + 3 bytes
,
len + 4 bytes
Given M members of the set, (M+7)/8 bytes, rounded up to 1, 2, 3, 4, or 8 bytes
16 bytes
16 bytes
'12'
2 bytes
'12'
3 bytes
1 byte
2 bytes
3 bytes
4 bytes
8 bytes
FLOAT(p)
4 bytes if p <= 24, otherwise 8 bytes
8 bytes
See table below
BIT(M)
(M+7)/8 bytes
0
0 bytes
1
1 byte
2
1 byte
3
2 bytes
4
2 bytes
5
3 bytes
1 byte for up to 255 enum values, 2 bytes for 256 to 65,535 enum values
M × w bytes, where w is the number of bytes required for the maximum-length character in the character set
M bytes
len + 1 bytes if column is 0 – 255 bytes, len + 2 bytes if column may require more than 255 bytes
len + 1 bytes
len + 2 bytes
''
' '
2 bytes
''
1 byte
'1'
'1 '
2 bytes
'1'
2 bytes
3 bytes
3 bytes
8 bytes
4 bytes
1 byte
0
0 bytes
1,2
1 byte
3,4
2 bytes
5,6
3 bytes
'12'
DThe decimal point and (for negative numbers) the "-" sign are not counted in M.
If D is 0, values have no decimal point or fractional part, and on INSERT, the value will be rounded to the nearest DECIMAL.
The maximum number of digits (M) for DECIMAL is 65.
The maximum number of supported decimals (D) is 30 before and 38 afterwards.
If D is omitted, the default is 0. If M is omitted, the default is 10.
UNSIGNED, if specified, disallows negative values.
ZEROFILL, if specified, pads the number with zeros, up to the total number of digits specified by M.
All basic calculations (+, -, *, /) with DECIMAL columns are done with a precision of 65 digits.
For more details on the attributes, see Numeric Data Type Overview.
DEC, NUMERIC, and FIXED are synonyms, as well as NUMBER in .
The DECIMAL data type may be SIGNED (allowing negative values) or UNSIGNED (not allowing negative values).
Example of DECIMAL SIGNED (SIGNED is the default):
Example of DECIMAL UNSIGNED:
A value is considered "out of range" when it is too small or too large to be stored in a data type. The size specified when creating the column is the strict limit for what values can be represented. When SQL_MODE is strict (the default), an out-of-range value generates an error, and the operation fails. If strict mode is not in effect, the value is rounded to the nearest valid value, and a warning is generated (which might be hidden, depending on your warning settings).
A value whose significant digits must be rounded to fit only generates a warning note about data truncation, since it is only an out-of-range value if the rounding causes the value to overflow.
A special type of DECIMAL UNSIGNED is DECIMAL ZEROFILL, which pads out the values with leading zeros in SELECT results. The number of leading zeros is just enough to pad the field out to the length of the type's field size (not counting the decimal place), but the zeros are not included in an expression result or in a UNION SELECT column.
Using DECIMAL ZEROFILL works the same way as DECIMAL UNSIGNED for most operations except a simple SELECT. For example, with the following test table setup:
This page is licensed: GPLv2, originally from fill_help_tables.sql
The TINYINT data type may be SIGNED (allowing negative values) or UNSIGNED (not allowing negative values).
Example of TINYINT SIGNED (the default):
Example of TINYINT UNSIGNED:
A value is considered "out of range" when it is too small or too large to be stored in a data type. When sql_mode=STRICT_TRANS_TABLES (the default) is set, an out-of-range value generates an error. If strict mode is not in effect, the value is rounded to the nearest valid value and a warning is generated (which might be hidden, depending on your warning settings).
An example of non-strict out-of-range behavior:
A special type of TINYINT UNSIGNED is TINYINT ZEROFILL, which pads out the values with leading zeros in SELECT results. The number of leading zeros are just enough to pad the field out to the length of the type's maximum unsigned value, but the zeros are not included in an expression result or in a UNION SELECT column.
Using TINYINT ZEROFILL works the same way as TINYINT UNSIGNED for most operations except a simple SELECT. For example, with the following test table setup:
The resulting data would look like this:
This page is licensed: GPLv2, originally from fill_help_tables.sql
SYSTEM with replication in either statement or mixed mode, you MUST use the same value for system_time_zone on all replicas (otherwise TIMESTAMP columns will not replicate correctly). See An offset from Coordinated Universal Time (UTC), such as +5:00 or -9:00, can also be used.
If the time zone tables in the mysql database were loaded, then a named time zone, such as America/New_York, Africa/Johannesburg, or Europe/Helsinki, is also permissible. See mysql Time Zone Tables below for more information.
There are two time zone settings that can be set within MariaDB--the global server time zone, and the time zone for your current session. There is also a third time zone setting which may be relevant--the system time zone.
The global server time zone can be changed at server startup by setting the --default-time-zone option either on the command-line or in a server option group in an option file. For example:
The global server time zone can also be changed dynamically by setting the time_zone system variable as a user account that has the SUPER privilege. For example:
The current global server time zone can be viewed by looking at the global value of the time_zone system variable. For example:
Each session that connects to the server will also have its own time zone. This time zone is initially inherited from the global value of the time_zone system variable, which sets the session value of the same variable.
A session's time zone can be changed dynamically by setting the time_zone system variable. For example:
The current session time zone can be viewed by looking at the session value of the time_zone system variable. For example:
The system time zone is determined when the server starts, and it sets the value of the system_time_zone system variable. The system time zone is usually read from the operating system's environment. You can change the system time zone in several different ways, such as:
If you are starting the server with mariadbd-safe, then you can set the system time zone with the --timezone option either on the command-line or in the [mariadbd-safe] option group in an option file. For example:
If you are using a Unix-like operating system, then you can set the system time zone by setting the TZ environment variable in your shell before starting the server. For example:
On some Linux operating systems, you can change the default time zone for the whole system by making the /etc/localtime symbolic link point to the desired time zone. For example:
On some Debian-based Linux operating systems, you can change the default time zone for the whole system by executing the following:
On Linux operating systems that use systemd, you can change the default time zone for the whole system by using the timedatectl utility. For example:
Some functions are affected by the time zone settings. These include:
Some functions are not affected. These include:
Some data types are affected by the time zone settings.
TIMESTAMP - See TIMESTAMP: Time Zones for information on how this data type is affected by time zones.
DATETIME - See DATETIME: Time Zones for information on how this data type is affected by time zones.
The mysql database contains a number of time zone tables:
By default, these time zone tables in the mysql database are created, but not populated.
If you are using a Unix-like operating system, then you can populate these tables using the mariadb-tzinfo-to-sql utility, which uses the zoneinfo data available on Linux, Mac OS X, FreeBSD and Solaris.
If you are using Windows, then you will need to import pre-populated time zone tables. These are available at MariaDB mirrors.
Time zone data needs to be updated on occasion. When that happens, the time zone tables may need to be reloaded.
This page is licensed: CC BY-SA / Gnu FDL
VARCHAR(30) CHARACTER SET utf8
NATIONAL VARCHAR(30)
NVARCHAR(30)
NCHAR VARCHAR(30)
NATIONAL CHARACTER VARYING(30)
NATIONAL CHAR VARYING(30)CREATE TABLE strtest (v VARCHAR(10));
INSERT INTO strtest VALUES('Maria ');
SELECT v='Maria',v='Maria ' FROM strtest;
+-----------+--------------+
| v='Maria' | v='Maria ' |
+-----------+--------------+
| 1 | 1 |
+-----------+--------------+
SELECT v LIKE 'Maria',v LIKE 'Maria ' FROM strtest;
+----------------+-------------------+
| v LIKE 'Maria' | v LIKE 'Maria ' |
+----------------+-------------------+
| 0 | 1 |
+----------------+-------------------+CREATE TABLE varchar_example (
description VARCHAR(20),
example VARCHAR(65511)
) DEFAULT CHARSET=latin1; -- One byte per char makes the examples clearerINSERT INTO varchar_example VALUES
('Normal foo', 'foo'),
('Trailing spaces foo', 'foo '),
('NULLed', NULL),
('Empty', ''),
('Maximum', RPAD('', 65511, 'x'));SELECT description, LENGTH(example) AS length
FROM varchar_example;+---------------------+--------+
| description | length |
+---------------------+--------+
| Normal foo | 3 |
| Trailing spaces foo | 9 |
| NULLed | NULL |
| Empty | 0 |
| Maximum | 65511 |
+---------------------+--------+TRUNCATE varchar_example;
INSERT INTO varchar_example VALUES
('Overflow', RPAD('', 65512, 'x'));ERROR 1406 (22001): Data too long for column 'example' at row 1DECIMAL[(M[,D])] [SIGNED | UNSIGNED | ZEROFILL]CREATE TABLE t1 (d DECIMAL UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES (1),(2),(3),(4.0),(5.2),(5.7);
Query OK, 6 rows affected, 2 warnings (0.16 sec)
Records: 6 Duplicates: 0 Warnings: 2
Note (sql 1265): Data truncated for column 'd' at row 5
Note (sql 1265): Data truncated for column 'd' at row 6
SELECT * FROM t1;
+------------+
| d |
+------------+
| 0000000001 |
| 0000000002 |
| 0000000003 |
| 0000000004 |
| 0000000005 |
| 0000000006 |
+------------+INSERT INTO t1 VALUES (-7);
ERROR 1264 (22003): Out of range value for column 'd' at row 1CREATE TABLE decimal_signed_example (
description VARCHAR(20),
sz10_0 DECIMAL,
sz6_2 DECIMAL(6,2),
sz20_19 DECIMAL(20,19) SIGNED
);SET @pi = 3.1415926535897932384626433832795;INSERT INTO decimal_signed_example VALUES
('Pi', @pi, @pi, @pi),
('Series', 1234.567890123, 1234.567890123, 1.234567890123),
('Negative', -1234.567890123, -1234.567890123, -1.234567890123),
('Various', 1234567890, 9999.99, 9.9999999999999999999);SELECT * FROM decimal_signed_example;
+-------------+------------+----------+------------------------+
| description | sz10_0 | sz6_2 | sz20_19 |
+-------------+------------+----------+------------------------+
| Pi | 3 | 3.14 | 3.1415926535897932385 |
| Series | 1235 | 1234.57 | 1.2345678901230000000 |
| Negative | -1235 | -1234.57 | -1.2345678901230000000 |
| Various | 1234567890 | 9999.99 | 9.9999999999999999999 |
+-------------+------------+----------+------------------------+CREATE TABLE decimal_unsigned_example (
description VARCHAR(20),
sz10_0 DECIMAL UNSIGNED,
sz6_2 DECIMAL(6,2) UNSIGNED,
sz20_19 DECIMAL(20,19) UNSIGNED
);SET @pi = 3.1415926535897932384626433832795;INSERT INTO decimal_unsigned_example VALUES
('Pi', @pi, @pi, @pi),
('Series', 1234.567890123, 1234.567890123, 1.234567890123),
('Various', 1234567890, 9999.99, 9.9999999999999999999);SELECT * FROM decimal_unsigned_example;
+-------------+------------+---------+-----------------------+
| description | sz10_0 | sz6_2 | sz20_19 |
+-------------+------------+---------+-----------------------+
| Pi | 3 | 3.14 | 3.1415926535897932385 |
| Series | 1235 | 1234.57 | 1.2345678901230000000 |
| Various | 1234567890 | 9999.99 | 9.9999999999999999999 |
+-------------+------------+---------+-----------------------+TRUNCATE decimal_signed_example;
-- Disable strict mode
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO decimal_signed_example VALUES
('OK', 9999999999.4, NULL, NULL),
('Rounded overflow', 9999999999.5, NULL, NULL),
('Overflow', 10000000000, NULL, NULL),
('OK', -9999999999.4, NULL, NULL),
('Rounded underflow', -9999999999.5, NULL, NULL),
('Underflow', -10000000000, NULL, NULL),
('OK', NULL, 9999.994, NULL),
('Rounded overflow', NULL, 9999.995, NULL),
('Overflow', NULL, 10000, NULL),
('OK', NULL, -9999.994, NULL),
('Rounded underflow', NULL, -9999.995, NULL),
('Underflow', NULL, -10000, NULL),
('OK', NULL, NULL, 9.99999999999999999994),
('Rounded overflow', NULL, NULL, 9.99999999999999999995),
('Overflow', NULL, NULL, 10),
('OK', NULL, NULL, -9.99999999999999999994),
('Rounded underflow', NULL, NULL, -9.99999999999999999995),
('Underflow', NULL, NULL, -10);Note (sql 1265): Data truncated for column 'sz10_0' at row 1
Warning (sql 1264): Out of range value for column 'sz10_0' at row 2
Warning (sql 1264): Out of range value for column 'sz10_0' at row 3
Note (sql 1265): Data truncated for column 'sz10_0' at row 4
Warning (sql 1264): Out of range value for column 'sz10_0' at row 5
Warning (sql 1264): Out of range value for column 'sz10_0' at row 6
Note (sql 1265): Data truncated for column 'sz6_2' at row 7
Warning (sql 1264): Out of range value for column 'sz6_2' at row 8
Warning (sql 1264): Out of range value for column 'sz6_2' at row 9
Note (sql 1265): Data truncated for column 'sz6_2' at row 10
Warning (sql 1264): Out of range value for column 'sz6_2' at row 11
Warning (sql 1264): Out of range value for column 'sz6_2' at row 12
Note (sql 1265): Data truncated for column 'sz20_19' at row 13
Warning (sql 1264): Out of range value for column 'sz20_19' at row 14
Warning (sql 1264): Out of range value for column 'sz20_19' at row 15
Note (sql 1265): Data truncated for column 'sz20_19' at row 16
Warning (sql 1264): Out of range value for column 'sz20_19' at row 17
Warning (sql 1264): Out of range value for column 'sz20_19' at row 18SELECT * FROM decimal_signed_example;+-------------------+-------------+----------+------------------------+
| description | sz10_0 | sz6_2 | sz20_19 |
+-------------------+-------------+----------+------------------------+
| OK | 9999999999 | NULL | NULL |
| Rounded overflow | 9999999999 | NULL | NULL |
| Overflow | 9999999999 | NULL | NULL |
| OK | -9999999999 | NULL | NULL |
| Rounded underflow | -9999999999 | NULL | NULL |
| Underflow | -9999999999 | NULL | NULL |
| OK | NULL | 9999.99 | NULL |
| Rounded overflow | NULL | 9999.99 | NULL |
| Overflow | NULL | 9999.99 | NULL |
| OK | NULL | -9999.99 | NULL |
| Rounded underflow | NULL | -9999.99 | NULL |
| Underflow | NULL | -9999.99 | NULL |
| OK | NULL | NULL | 9.9999999999999999999 |
| Rounded overflow | NULL | NULL | 9.9999999999999999999 |
| Overflow | NULL | NULL | 9.9999999999999999999 |
| OK | NULL | NULL | -9.9999999999999999999 |
| Rounded underflow | NULL | NULL | -9.9999999999999999999 |
| Underflow | NULL | NULL | -9.9999999999999999999 |
+-------------------+-------------+----------+------------------------+CREATE TABLE decimal_zerofill_example (
description VARCHAR(20),
sz10_0 DECIMAL ZEROFILL,
sz6_2 DECIMAL(6,2) ZEROFILL,
sz20_19 DECIMAL(20,19) ZEROFILL
);SET @pi = 3.1415926535897932384626433832795;INSERT INTO decimal_zerofill_example VALUES
('Pi', @pi, @pi, @pi),
('Series', 1234.567890123, 1234.567890123, 1.234567890123),
('Various', 1234567890, 9999.99, 9.9999999999999999999);SELECT * FROM decimal_zerofill_example;
+-------------+------------+---------+-----------------------+
| description | sz10_0 | sz6_2 | sz20_19 |
+-------------+------------+---------+-----------------------+
| Pi | 0000000003 | 0003.14 | 3.1415926535897932385 |
| Series | 0000001235 | 1234.57 | 1.2345678901230000000 |
| Various | 1234567890 | 9999.99 | 9.9999999999999999999 |
+-------------+------------+---------+-----------------------+TINYINT[(M)] [SIGNED | UNSIGNED | ZEROFILL]CREATE TABLE tinyints (a TINYINT,b TINYINT UNSIGNED,c TINYINT ZEROFILL);INSERT INTO tinyints VALUES (-10,-10,-10);
ERROR 1264 (22003): Out of range value for column 'b' at row 1
INSERT INTO tinyints VALUES (-10,10,-10);
ERROR 1264 (22003): Out of range value for column 'c' at row 1
INSERT INTO tinyints VALUES (-10,10,10);
SELECT * FROM tinyints;
+------+------+------+
| a | b | c |
+------+------+------+
| -10 | 10 | 010 |
+------+------+------+
INSERT INTO tinyints VALUES (128,128,128);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
INSERT INTO tinyints VALUES (127,128,128);
SELECT * FROM tinyints;
+------+------+------+
| a | b | c |
+------+------+------+
| -10 | 10 | 010 |
| 127 | 128 | 128 |
+------+------+------+CREATE TABLE tinyint_signed_example (
description VARCHAR(20),
example TINYINT SIGNED
);INSERT INTO tinyint_signed_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', -128),
('Maximum', 127);CREATE TABLE tinyint_unsigned_example (
description VARCHAR(20),
example TINYINT UNSIGNED
);INSERT INTO tinyint_unsigned_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', 0),
('Maximum', 255);TRUNCATE tinyint_signed_example;
-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO tinyint_signed_example VALUES
('Underflow', -129),
('Overflow', 128);Warning (sql 1264): Out of range value for column 'example' at row 1
Warning (sql 1264): Out of range value for column 'example' at row 2SELECT * FROM tinyint_signed_example;+-------------+---------+
| description | example |
+-------------+---------+
| Underflow | -128 |
| Overflow | 127 |
+-------------+---------+TRUNCATE tinyint_unsigned_example;
-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO tinyint_unsigned_example VALUES
('Underflow', -1),
('Overflow', 256);Warning (sql 1264): Out of range value for column 'example' at row 1
Warning (sql 1264): Out of range value for column 'example' at row 2SELECT * FROM tinyint_unsigned_example;+-------------+---------+
| description | example |
+-------------+---------+
| Underflow | 0 |
| Overflow | 255 |
+-------------+---------+CREATE TABLE tinyint_zerofill_example (
description VARCHAR(20),
example TINYINT ZEROFILL
);
INSERT INTO tinyint_zerofill_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', 0),
('Maximum', 255);-- Turn off strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));INSERT INTO tinyint_zerofill_example VALUES
('Underflow', -1),
('Overflow', 256);Warning (sql 1264): Out of range value for column 'example' at row 1
Warning (sql 1264): Out of range value for column 'example' at row 2SELECT *, example + 0 FROM tinyint_zerofill_example;+-------------+---------+-------------+
| description | example | example + 0 |
+-------------+---------+-------------+
| Zero | 000 | 0 |
| Forty-Two | 042 | 42 |
| Minimum | 000 | 0 |
| Maximum | 255 | 255 |
| Underflow | 000 | 0 |
| Overflow | 255 | 255 |
+-------------+---------+-------------+[mariadb]
...
default_time_zone = 'America/New_York'SET GLOBAL time_zone = 'America/New_York';SHOW GLOBAL VARIABLES LIKE 'time_zone';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| time_zone | SYSTEM |
+---------------+--------+SET time_zone = 'America/New_York';SHOW SESSION VARIABLES LIKE 'time_zone';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| time_zone | SYSTEM |
+---------------+--------+[mariadbd-safe]
timezone='America/New_York'$ export TZ='America/New_York'
$ service mariadb start$ sudo rm /etc/localtime
$ sudo ln -s /usr/share/zoneinfo/America/New_York /etc/localtimesudo dpkg-reconfigure tzdatasudo timedatectl set-timezone America/New_YorkSHOW VARIABLES LIKE 'mysql56_temporal_format';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| mysql56_temporal_format | ON |
+-------------------------+-------+
ALTER TABLE example_table MODIFY ts_col DATETIME;SHOW CREATE TABLE mariadb5312_datetime\G
*************************** 1. row ***************************
Table: mariadb5312_datetime
Create Table: CREATE TABLE `mariadb5312_datetime` (
`dt0` datetime /* mariadb-5.3 */ DEFAULT NULL,
`dt6` datetime(6) /* mariadb-5.3 */ DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES ("2011-03-11"), ("2012-04-19 13:08:22"),
("2013-07-18 13:44:22.123456");
SELECT * FROM t1;
+---------------------+
| d |
+---------------------+
| 2011-03-11 00:00:00 |
| 2012-04-19 13:08:22 |
| 2013-07-18 13:44:22 |
+---------------------+CREATE TABLE t2 (d DATETIME(6));
INSERT INTO t2 VALUES ("2011-03-11"), ("2012-04-19 13:08:22"),
("2013-07-18 13:44:22.123456");
SELECT * FROM t2;
+----------------------------+
| d |
+----------------------------+
| 2011-03-11 00:00:00.000000 |
| 2012-04-19 13:08:22.000000 |
| 2013-07-18 13:44:22.123456 |
+----------------------------++SELECT CONVERT('2007-11-30 10:30:19',datetime);
+-----------------------------------------+
| CONVERT('2007-11-30 10:30:19',datetime) |
+-----------------------------------------+
| 2007-11-30 10:30:19 |
+-----------------------------------------+
SELECT CONVERT('2007-11-30 10:30:19',datetime(6));
+--------------------------------------------+
| CONVERT('2007-11-30 10:30:19',datetime(6)) |
+--------------------------------------------+
| 2007-11-30 10:30:19.000000 |
+--------------------------------------------+CREATE TABLE datetime_formats_example (
description VARCHAR(30),
example DATETIME(6)
);-- The time zone has no effect on the values
SET @@time_zone = '+00:00';
INSERT INTO datetime_formats_example VALUES
('Date without time', '2019-12-30'),
('Full year', '2019-12-30 00:00:00'),
('Short year', '19-12-30 00:00:00.000'),
('Pipe delimiters', '19|2|3 19|00|00.123456'),
('Forward slash delimiter', '19/12/30 00/00/00.0');
SET @@time_zone = '-07:00';
INSERT INTO datetime_formats_example VALUES
('Asterisk delimiter', '19*12*30 8*35*00'),
('Comma delimiter', '19,2,3 12,34,56.123');SELECT * FROM datetime_formats_example;+-------------------------+----------------------------+
| description | example |
+-------------------------+----------------------------+
| Date without time | 2019-12-30 00:00:00.000000 |
| Full year | 2019-12-30 00:00:00.000000 |
| Short year | 2019-12-30 00:00:00.000000 |
| Pipe delimiters | 2019-02-03 19:00:00.123456 |
| Forward slash delimiter | 2019-12-30 00:00:00.000000 |
| Asterisk delimiter | 2019-12-30 08:35:00.000000 |
| Comma delimiter | 2019-02-03 12:34:56.123000 |
+-------------------------+----------------------------+SELECT description, CONVERT(example, DATETIME) AS example
FROM datetime_formats_example;
+-------------------------+---------------------+
| description | example |
+-------------------------+---------------------+
| Date without time | 2019-12-30 00:00:00 |
| Full year | 2019-12-30 00:00:00 |
| Short year | 2019-12-30 00:00:00 |
| Pipe delimiters | 2019-02-03 19:00:00 |
| Forward slash delimiter | 2019-12-30 00:00:00 |
| Asterisk delimiter | 2019-12-30 08:35:00 |
| Comma delimiter | 2019-02-03 12:34:56 |
+-------------------------+---------------------+[mariadb]
...
lc_messages=fr_CASET GLOBAL lc_messages='fr_CA';SELECT blah;
ERROR 1054 (42S22): Champ 'blah' inconnu dans field list[mariadb]
...
lc_messages_dir=/usr/share/mysql/[mariadb]
...
lc_messages_dir=/usr/share/mysql/french/[mariadb]
...
language=french[mariadb]
...
language=/usr/share/mysql/french/Understand the concept of NULL. This page explains how NULL represents missing or unknown data and how it interacts with comparison operators and functions.
NULL represents an unknown value. It is not an empty string (by default), or a zero value. These are all valid values, and are not NULL values.
When a table is created or the format altered, columns can be specified as accepting NULL values, or not accepting them, with the NULL and NOT NULL clauses respectively.
For example, a customer table could contain dates of birth. For some customers, this information is unknown, so the value could be NULL.
The same system could allocate a customer ID for each customer record, and in this case a NULL value would not be permitted.
are NULL until a value is explicitly assigned.
parameters and can always be set to NULL. If no DEFAULT value is specified for a local variable, its initial value will be NULL. If no value is assigned to an OUT parameter in a stored procedure, NULL is assigned at the end of the procedure.
The case of NULL is not relevant. (uppercase) is an alias for NULL.
The operator accepts UNKNOWN as an alias for NULL, which is meant for .
NULL values cannot be used with most . For example, , , , , , or cannot be used, as any comparison with a NULL always returns a NULL value, never true (1) or false (0).
To overcome this, certain operators are specifically designed for use with NULL values. To cater for testing equality between two values that may contain NULL values, there's , NULL-safe equal.
Other operators for working with NULL values include and , (for testing an expression) and (for returning the first non-NULL parameter).
When you order by a field that may contain NULL values, any NULL values are considered to have the lowest value. So ordering in DESC order will see the NULL values appearing last. To force NULL values to be regarded as highest values, one can add another column which has a higher value when the main field is NULL. Example:
Descending order, with NULL values first:
All NULL values are also regarded as equivalent for the purposes of the DISTINCT and GROUP BY clauses.
In most cases, functions will return NULL if any of the parameters are NULL. There are also functions specifically for handling NULL values. These include , and .
Aggregate functions, such as and ignore NULL values.
The one exception is , which counts rows, and doesn't look at whether a value is NULL or not. Compare for example, COUNT(x), which ignores the NULL, and COUNT(*), which counts it:
MariaDB handles NULL values in a special way if the field is an , a or a . Inserting a NULL value into a numeric AUTO_INCREMENT column will result in the next number in the auto increment sequence being inserted instead. This technique is frequently used with AUTO_INCREMENT fields, which are left to take care of themselves.
Similarly, if a NULL value is assigned to a TIMESTAMP field, the current date and time is assigned instead.
After a pause:
If a NULL is assigned to a VIRTUAL or PERSISTENT column, the default value is assigned instead.
In all these special cases, NULL is equivalent to the DEFAULT keyword.
If a NULL value is single-row inserted into a column declared as NOT NULL, an error will be returned. However, if the is not (strict is the default), if a NULL value is multi-row inserted into a column declared as NOT NULL, the implicit default for the column type will be inserted (and NOT the default value in the table definition). The implicit defaults are an empty string for string types, and the zero value for numeric, date and time types.
By default, both cases will result in an error.
Single-row insert:
Multi-row insert with not :
The specified defaults have not been used; rather, the implicit column type defaults have been inserted.
UNIQUE indexes can contain multiple NULL values. Primary keys are never nullable.
In , NULL can be used as a statement:
In , and the ignore .
When setting , empty strings and NULL values are the same thing. For example:
This page is licensed: CC BY-SA / Gnu FDL
Store timestamp values. This type stores the number of seconds since the Unix Epoch, with a range from '1970-01-01' to '2038-01-19' in UTC.
This page is about the TIMESTAMP data type. For the timestamp function, see TIMESTAMP FUNCTION.
A timestamp in the format YYYY-MM-DD HH:MM:SS.ffffff.
The timestamp field is generally used to define at which moment in time a row was added or updated and by default will automatically be assigned the current datetime when a record is inserted or updated. The automatic properties only apply to the first TIMESTAMP in the record; subsequent TIMESTAMP columns will not be changed.
MariaDB includes the option, on by default, which allows MariaDB to store TIMESTAMP values using the same low-level format MySQL 5.6 uses.
For more information, see .
MariaDB stores values that use the TIMESTAMP data type as the number of seconds since '1970-01-01 00:00:00' (). This means that the TIMESTAMP data type can hold values between '1970-01-01 00:00:01' () and '2106-02-07 06:28:15 UTC'.
MariaDB stores values that use the TIMESTAMP data type as the number of seconds since '1970-01-01 00:00:00' (). This means that the TIMESTAMP data type can hold values between '1970-01-01 00:00:01' () and '2038-01-19 03:14:07' ().
MariaDB can also store with a precision between 0 and 6. If no microsecond precision is specified, then 0 is used by default.
MariaDB has special behavior for the first column that uses the TIMESTAMP data type in a specific table when the system variable is not set (which was the default until ). For the first column that uses the TIMESTAMP data type in a specific table, MariaDB automatically assigns the following properties to the column:
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP
This means that if the column is not explicitly assigned a value in an INSERT or UPDATE query, then MariaDB will automatically initialize the column's value with the current date and time.
This automatic initialization for INSERT and UPDATE queries can also be explicitly enabled for a column that uses the TIMESTAMP data type by specifying the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses for the column. In these clauses, any synonym of is accepted, including CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, and LOCALTIMESTAMP().
This automatic initialization for INSERT queries can also be explicitly disabled for a column that uses the TIMESTAMP data type by specifying a constant DEFAULT value. For example, DEFAULT 0.
This automatic initialization for UPDATE queries can also be explicitly disabled for a column that uses the TIMESTAMP data type by specifying a DEFAULT clause for the column, but no ON UPDATE clause. If a DEFAULT clause is explicitly specified for a column that uses the TIMESTAMP data type, but an ON UPDATE clause is not specified for the column, then the timestamp value will not automatically change when an UPDATE statement is executed.
MariaDB also has special behavior if NULL is assigned to column that uses the TIMESTAMP data type. If the column is assigned the NULL value in an INSERT or UPDATE query, then MariaDB will automatically initialize the column's value with the current date and time. For details, see .
This automatic initialization for NULL values can also be explicitly disabled for a column that uses the TIMESTAMP data type by specifying the NULL attribute for the column. In this case, if the column's value is set to NULL, then the column's value will actually be set to NULL.
If a column uses the TIMESTAMP data type, then any inserted values are converted from the session's time zone to when stored, and converted back to the session's time zone when retrieved.
MariaDB validates TIMESTAMP literals against the session's time zone. For example, if a specific time range never occurred in a specific time zone due to daylight savings time, then TIMESTAMP values within that range would be invalid for that time zone.
MariaDB does not store any time zone identifier with the value of the TIMESTAMP data type. See for more information.
MariaDB does not support time zone literals that contain time zone identifiers. See for more information.
Because the TIMESTAMP value is stored as Epoch Seconds, the timestamp value '1970-01-01 00:00:00' (UTC) is reserved since the second #0 is used to represent '0000-00-00 00:00:00'.
If the is set to MAXDB, TIMESTAMP fields will be silently converted to .
A temporal format was introduced from MySQL 5.6 that alters how the TIME, DATETIME and TIMESTAMP columns operate at lower levels. These changes allow these temporal data types to have fractional parts and negative values. You can disable this feature using the system variable.
Tables that include TIMESTAMP values that were created on an older version of MariaDB or that were created while the system variable was disabled continue to store data using the older data type format.
In order to update table columns from the older format to the newer format, execute an statement that changes the column to the same data type. This change may be needed if you want to export the table's tablespace and import it onto a server that has mysql56_temporal_format=ON set (see ).
For instance, if you have a TIMESTAMP column in your table:
When MariaDB executes the statement, it converts the data from the older temporal format to the newer one.
In the event that you have several tables and columns using temporal data types that you want to switch over to the new format, make sure the system variable is enabled, then perform a dump and restore using . The columns using relevant temporal data types are restored using the new temporal format.
Columns with old temporal formats are marked with a /* mariadb-5.3 */ comment in the output of , , statements, as well as in the COLUMN_TYPE column of the .
Columns with old temporal formats are not marked with a /* mariadb-5.3 */ comment in the output of , , statements, as well as in the COLUMN_TYPE column of the .
Converting to Unix epoch:
Update also changes the timestamp:
Default NULL:
Only the first timestamp is automatically inserted and updated:
Explicitly setting a timestamp with the function:
Specifying the timestamp as NOT NULL:
This page is licensed: CC BY-SA / Gnu FDL
ROW is a data type used in stored programs to store a complete row of data from a cursor or table.
ROW is a data type for stored procedure variables.
ROW fields (members) act as normal variables, and are able to appear in all query parts where a stored procedure variable is allowed:
Assignment is using the := operator and the command:
Passing to functions and operators:
Clauses (select list, WHERE, HAVING, LIMIT, etc...,):
INSERT values:
SELECT .. INTO targets:
Dynamic SQL out parameters ( and )
ROW type variables are allowed as targets:
where cur is a CURSOR and rec is a ROW type stored procedure variable.
Note, currently an attempt to use FETCH for a ROW type variable returns this error:
FETCH from a cursor cur into a ROW variable rec works as follows:
The number of fields in cur must match the number of fields in rec. Otherwise, an error is reported.
Assignment is done from left to right. The first cursor field is assigned to the first variable field, the second cursor field is assigned to the second variable field, etc.
Field names in rec are not important and can differ from field names in cur.
See (below) for examples of using this withsql_mode=ORACLE and sql_mode=DEFAULT.
SELECT...INTO targetsROW type variables are allowed as SELECT..INTO targets with some differences depending on which sql_mode is in use.
When using sql_mode=ORACLE, table%ROWTYPE and cursor%ROWTYPE variables can be used as SELECT...INTO targets.
Using multiple ROW variables in the SELECT..INTO list will report an error.
See (below) for examples of using this with sql_mode=ORACLE and sql_mode=DEFAULT.
The following features are planned, but not implemented yet:
Returning a ROW type expression from a stored function (see ). This will need some grammar change to support field names after parentheses:
Returning a ROW type expression from a built-in hybrid type function, such as CASE, IF, etc.
ROW of ROW values
A complete FETCH example for sql_mode=ORACLE:
A complete FETCH example for sql_mode=DEFAULT:
A SELECT...INTO example for sql_mode=DEFAULT:
The above example returns:
A SELECT...INTO example for sql_mode=ORACLE:
The above example returns:
An example for sql_mode=ORACLE using table%ROWTYPE variables as SELECT..INTO targets:
The above example returns:
An example for sql_mode=ORACLE using cursor%ROWTYPE variables as SELECT..INTO targets:
The above example returns:
Row Table example:
This page is licensed: CC BY-SA / Gnu FDL
TIMESTAMP [(<microsecond precision)]ROW (<field name> <data type> [{, <field name> <data type>}... ])ROW variables with a different column count than in the SELECT..INTO list will report an error.a.x:= 10;
a.x:= b.x;
SET a.x= 10, a.y=20, a.z= b.z;SELECT f1(rec.a), rec.a<10;SELECT var.a, t1.b FROM t1 WHERE t1.b=var.b LIMIT var.c;INSERT INTO t1 VALUES (rec.a, rec.b, rec.c);SELECT a,b INTO rec.a, rec.b FROM t1 WHERE t1.id=10;EXECUTE IMMEDIATE 'CALL proc_with_out_param(?)' USING rec.a;FETCH cur INTO rec;ERROR 1328 (HY000): Incorrect number of FETCH variablesSELECT f1().x FROM DUAL;DELIMITER $$
CREATE PROCEDURE p1()
BEGIN
DECLARE r ROW (c1 INT, c2 VARCHAR(10));
SET r.c1= 10;
SET r.c2= 'test';
INSERT INTO t1 VALUES (r.c1, r.c2);
END;
$$
DELIMITER ;
CALL p1();DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
INSERT INTO t1 VALUES (20,'b20');
INSERT INTO t1 VALUES (30,'b30');
SET sql_mode=oracle;
DROP PROCEDURE IF EXISTS p1;
DELIMITER $$
CREATE PROCEDURE p1 AS
rec ROW(a INT, b VARCHAR(32));
CURSOR c IS SELECT a,b FROM t1;
BEGIN
OPEN c;
LOOP
FETCH c INTO rec;
EXIT WHEN c%NOTFOUND;
SELECT ('rec=(' || rec.a ||','|| rec.b||')');
END LOOP;
CLOSE c;
END;
$$
DELIMITER ;
CALL p1();DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
INSERT INTO t1 VALUES (20,'b20');
INSERT INTO t1 VALUES (30,'b30');
SET sql_mode=DEFAULT;
DROP PROCEDURE IF EXISTS p1;
DELIMITER $$
CREATE PROCEDURE p1()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE rec ROW(a INT, b VARCHAR(32));
DECLARE c CURSOR FOR SELECT a,b FROM t1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN c;
read_loop:
LOOP
FETCH c INTO rec;
IF done THEN
LEAVE read_loop;
END IF;
SELECT CONCAT('rec=(',rec.a,',',rec.b,')');
END LOOP;
CLOSE c;
END;
$$
DELIMITER ;
CALL p1();SET sql_mode=DEFAULT;
DROP TABLE IF EXISTS t1;
DROP PROCEDURE IF EXISTS p1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
DELIMITER $$
CREATE PROCEDURE p1()
BEGIN
DECLARE rec1 ROW(a INT, b VARCHAR(32));
SELECT * FROM t1 INTO rec1;
SELECT rec1.a, rec1.b;
END;
$$
DELIMITER ;
CALL p1();+--------+--------+
| rec1.a | rec1.b |
+--------+--------+
| 10 | b10 |
+--------+--------+SET sql_mode=ORACLE;
DROP TABLE IF EXISTS t1;
DROP PROCEDURE IF EXISTS p1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
DELIMITER $$
CREATE PROCEDURE p1 AS
rec1 ROW(a INT, b VARCHAR(32));
BEGIN
SELECT * FROM t1 INTO rec1;
SELECT rec1.a, rec1.b;
END;
$$
DELIMITER ;
CALL p1();+--------+--------+
| rec1.a | rec1.b |
+--------+--------+
| 10 | b10 |
+--------+--------+SET sql_mode=ORACLE;
DROP TABLE IF EXISTS t1;
DROP PROCEDURE IF EXISTS p1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
DELIMITER $$
CREATE PROCEDURE p1 AS
rec1 t1%ROWTYPE;
BEGIN
SELECT * FROM t1 INTO rec1;
SELECT rec1.a, rec1.b;
END;
$$
DELIMITER ;
CALL p1();+--------+--------+
| rec1.a | rec1.b |
+--------+--------+
| 10 | b10 |
+--------+--------+SET sql_mode=ORACLE;
DROP TABLE IF EXISTS t1;
DROP PROCEDURE IF EXISTS p1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
DELIMITER $$
CREATE PROCEDURE p1 AS
CURSOR cur1 IS SELECT * FROM t1;
rec1 cur1%ROWTYPE;
BEGIN
SELECT * FROM t1 INTO rec1;
SELECT rec1.a, rec1.b;
END;
$$
DELIMITER ;
CALL p1();+--------+--------+
| rec1.a | rec1.b |
+--------+--------+
| 10 | b10 |
+--------+--------+CREATE TABLE row_table(
descr VARCHAR(20),
val INT
);INSERT INTO row_table VALUES ('Life', 42);DELIMITER $$
CREATE PROCEDURE row_proc()
BEGIN
DECLARE rec1 ROW(descr VARCHAR(20), val INT);
SELECT * INTO rec1 FROM row_table;
SELECT rec1.descr, rec1.val;
END;
$$
DELIMITER ;CALL row_proc();
+------------+----------+
| rec1.descr | rec1.val |
+------------+----------+
| Life | 42 |
+------------+----------+Automatically generate unique identities for new rows. This attribute is commonly used for primary keys to ensure sequential numbering.
The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows. When you insert a new record into the table (or upon adding an attribute with the statement), and the AUTO_INCREMENT field is or DEFAULT (in the case of an INSERT), automatically be incremented. This also applies to 0, unless the is enabled.
AUTO_INCREMENT
CREATE TABLE customer (
id INT NOT NULL,
date_of_birth DATE NULL
...
)SELECT NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
| NULL |
+-------------+
SELECT 99 = NULL;
+-----------+
| 99 = NULL |
+-----------+
| NULL |
+-----------+SELECT 99 <=> NULL, NULL <=> NULL;
+-------------+---------------+
| 99 <=> NULL | NULL <=> NULL |
+-------------+---------------+
| 0 | 1 |
+-------------+---------------+SELECT col1 FROM tab ORDER BY ISNULL(col1), col1;SELECT col1 FROM tab ORDER BY IF(col1 IS NULL, 0, 1), col1 DESC;SELECT IFNULL(1,0);
+-------------+
| IFNULL(1,0) |
+-------------+
| 1 |
+-------------+
SELECT IFNULL(NULL,10);
+-----------------+
| IFNULL(NULL,10) |
+-----------------+
| 10 |
+-----------------+
SELECT COALESCE(NULL,NULL,1);
+-----------------------+
| COALESCE(NULL,NULL,1) |
+-----------------------+
| 1 |
+-----------------------+CREATE TABLE t(x INT);
INSERT INTO t VALUES (1),(9),(NULL);
SELECT SUM(x) FROM t;
+--------+
| SUM(x) |
+--------+
| 10 |
+--------+
SELECT AVG(x) FROM t;
+--------+
| AVG(x) |
+--------+
| 5.0000 |
+--------+SELECT COUNT(x) FROM t;
+----------+
| COUNT(x) |
+----------+
| 2 |
+----------+
SELECT COUNT(*) FROM t;
+----------+
| COUNT(*) |
+----------+
| 3 |
+----------+CREATE TABLE t2(id INT PRIMARY KEY AUTO_INCREMENT, letter CHAR(1));
INSERT INTO t2(letter) VALUES ('a'),('b');
SELECT * FROM t2;
+----+--------+
| id | letter |
+----+--------+
| 1 | a |
| 2 | b |
+----+--------+CREATE TABLE t3 (x INT, ts TIMESTAMP);
INSERT INTO t3(x) VALUES (1),(2);INSERT INTO t3(x) VALUES (3);
SELECT* FROM t3;
+------+---------------------+
| x | ts |
+------+---------------------+
| 1 | 2013-09-05 10:14:18 |
| 2 | 2013-09-05 10:14:18 |
| 3 | 2013-09-05 10:14:29 |
+------+---------------------+CREATE TABLE virt (c INT, v INT AS (c+10) PERSISTENT) ENGINE=InnoDB;
INSERT INTO virt VALUES (1, NULL);
SELECT c, v FROM virt;
+------+------+
| c | v |
+------+------+
| 1 | 11 |
+------+------+CREATE TABLE nulltest (
a INT(11),
x VARCHAR(10) NOT NULL DEFAULT 'a',
y INT(11) NOT NULL DEFAULT 23
);INSERT INTO nulltest (a,x,y) VALUES (1,NULL,NULL);
ERROR 1048 (23000): Column 'x' cannot be nullshow variables like 'sql_mode%';
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| sql_mode | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
INSERT INTO nulltest (a,x,y) VALUES (1,NULL,NULL),(2,NULL,NULL);
Query OK, 2 rows affected, 4 warnings (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 4SELECT * FROM nulltest;
+------+---+---+
| a | x | y |
+------+---+---+
| 1 | | 0 |
| 2 | | 0 |
+------+---+---+IF a=10 THEN NULL; ELSE NULL; END IFSET sql_mode=EMPTY_STRING_IS_NULL;
SELECT '' IS NULL; -- returns TRUE
INSERT INTO t1 VALUES (''); -- inserts NULLSHOW VARIABLES LIKE 'mysql56_temporal_format';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| mysql56_temporal_format | ON |
+-------------------------+-------+
ALTER TABLE example_table MODIFY ts_col TIMESTAMP;SHOW CREATE TABLE mariadb5312_timestamp\G
*************************** 1. row ***************************
Table: mariadb5312_timestamp
Create Table: CREATE TABLE `mariadb5312_timestamp` (
`ts0` timestamp /* mariadb-5.3 */ NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`ts6` timestamp(6) /* mariadb-5.3 */ NOT NULL DEFAULT '0000-00-00 00:00:00.000000'
) ENGINE=MyISAM DEFAULT CHARSET=latin1CREATE TABLE t (id INT, ts TIMESTAMP);
DESC t;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id | int(11) | YES | | NULL | |
| ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
INSERT INTO t(id) VALUES (1),(2);
SELECT * FROM t;
+------+---------------------+
| id | ts |
+------+---------------------+
| 1 | 2013-07-22 12:50:05 |
| 2 | 2013-07-22 12:50:05 |
+------+---------------------+
INSERT INTO t VALUES (3,NULL),(4,'2001-07-22 12:12:12');
SELECT * FROM t;
+------+---------------------+
| id | ts |
+------+---------------------+
| 1 | 2013-07-22 12:50:05 |
| 2 | 2013-07-22 12:50:05 |
| 3 | 2013-07-22 12:51:56 |
| 4 | 2001-07-22 12:12:12 |
+------+---------------------+SELECT ts, UNIX_TIMESTAMP(ts) FROM t;
+---------------------+--------------------+
| ts | UNIX_TIMESTAMP(ts) |
+---------------------+--------------------+
| 2013-07-22 12:50:05 | 1374490205 |
| 2013-07-22 12:50:05 | 1374490205 |
| 2013-07-22 12:51:56 | 1374490316 |
| 2001-07-22 12:12:12 | 995796732 |
+---------------------+--------------------+UPDATE t set id=5 WHERE id=1;
SELECT * FROM t;
+------+---------------------+
| id | ts |
+------+---------------------+
| 5 | 2013-07-22 14:52:33 |
| 2 | 2013-07-22 12:50:05 |
| 3 | 2013-07-22 12:51:56 |
| 4 | 2001-07-22 12:12:12 |
+------+---------------------+CREATE TABLE t2 (id INT, ts TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP);
INSERT INTO t(id) VALUES (1),(2);
SELECT * FROM t2;
INSERT INTO t2(id) VALUES (1),(2);
SELECT * FROM t2;
+------+------+
| id | ts |
+------+------+
| 1 | NULL |
| 2 | NULL |
+------+------+
UPDATE t2 SET id=3 WHERE id=1;
SELECT * FROM t2;
+------+---------------------+
| id | ts |
+------+---------------------+
| 3 | 2013-07-22 15:32:22 |
| 2 | NULL |
+------+---------------------+CREATE TABLE t3 (id INT, ts1 TIMESTAMP, ts2 TIMESTAMP);
INSERT INTO t3(id) VALUES (1),(2);
SELECT * FROM t3;
+------+---------------------+---------------------+
| id | ts1 | ts2 |
+------+---------------------+---------------------+
| 1 | 2013-07-22 15:35:07 | 0000-00-00 00:00:00 |
| 2 | 2013-07-22 15:35:07 | 0000-00-00 00:00:00 |
+------+---------------------+---------------------+
DESC t3;
+-------+-----------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------------------+-----------------------------+
| id | int(11) | YES | | NULL | |
| ts1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| ts2 | timestamp | NO | | 0000-00-00 00:00:00 | |
+-------+-----------+------+-----+---------------------+-----------------------------+INSERT INTO t3(id,ts2) VALUES (3,CURRENT_TIMESTAMP());
SELECT * FROM t3;
+------+---------------------+---------------------+
| id | ts1 | ts2 |
+------+---------------------+---------------------+
| 1 | 2013-07-22 15:35:07 | 0000-00-00 00:00:00 |
| 2 | 2013-07-22 15:35:07 | 0000-00-00 00:00:00 |
| 3 | 2013-07-22 15:38:52 | 2013-07-22 15:38:52 |
+------+---------------------+---------------------+CREATE TABLE t4 (id INT, ts TIMESTAMP NOT NULL);
INSERT INTO t4(id) VALUES (1);
SELECT SLEEP(1);
INSERT INTO t4(id,ts) VALUES (2,NULL);
SELECT * FROM t4;0Each table can have only one AUTO_INCREMENT column. It must defined as a key (not necessarily the PRIMARY KEY or UNIQUE key). In some storage engines (including the default InnoDB), if the key consists of multiple columns, the AUTO_INCREMENT column must be the first column. Storage engines that permit the column to be placed elsewhere are Aria, MyISAM, MERGE, Spider, TokuDB, BLACKHOLE, FederatedX and Federated.
SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
You can use an ALTER TABLE statement to assign a new value to the auto_increment table option, or set the insert_id server system variable to change the next AUTO_INCREMENT value inserted by the current session.
LAST_INSERT_ID() can be used to see the last AUTO_INCREMENT value inserted by the current session.
AUTO_INCREMENT is persistent in InnoDB.
See also AUTO_INCREMENT Handling in InnoDB.
It is possible to specify a value for an AUTO_INCREMENT column. If the key is primary or unique, the value must not already exist in the key.
If the new value is higher than the current maximum value, the AUTO_INCREMENT value is updated, so the next value will be higher. If the new value is lower than the current maximum value, the AUTO_INCREMENT value remains unchanged.
The following example demonstrates these behaviors:
The ARCHIVE storage engine does not allow to insert a value that is lower than the current maximum.
An AUTO_INCREMENT column normally has missing values. This happens because if a row is deleted, or an AUTO_INCREMENT value is explicitly updated, old values are never re-used. The REPLACE statement also deletes a row, and its value is wasted. With InnoDB, values can be reserved by a transaction; but if the transaction fails (for example, because of a ROLLBACK) the reserved value will be lost.
Thus AUTO_INCREMENT values can be used to sort results in a chronological order, but not to create a numeric sequence.
To make master-master or Galera safe to use AUTO_INCREMENT , you should use the system variables auto_increment_increment and auto_increment_offset to generate unique values for each server.
If auto_increment_offset is larger than auto_increment_increment, the value of auto_increment_offset is ignored, and the offset reverts to the default of 1 instead:
auto_increment_increment and auto_incremenet_offset when adding a new master to a multi-master setupThe purpose of auto_increment_increment and auto_increment_offset is to ensure that in a multi-master or multi-source setup, all masters generate unique values for auto_increment fields or for sequences with INCREMENT=0.
If auto_increment_increment is larger than the current number of masters, then one can configure the new master with the not used auto_increment_offset. The easiest way to add a new master is to stop all MariaDB servers, update auto_increment_increment and auto_increment_offset in the configuration files, and restart.\
This has to be done if auto_increment_increment is 1. If one has more than one master (auto_increment_increment > 1), there is a way to add more masters with only having to restart one of the masters. The 'trick' is to configure one of the masters to not use all the values in its current sequence.
The following example illustrates how to do it. Assume you have two masters, A and B.
In this case you will have auto_increment_increment=2 for both masters and A would have auto_increment_offset=1 and B would have auto_increment_offset=2. For A, all auto_increment and generated sequence values will be odd.
For B, all values will be even:
See the Replication section above.
If we change auto_increment_increment from 2 to 4 in A, it will now generate values from this sequence:
As you can see, values 3, 7, 11 are not going to be used.We can get C to use values from this sequence by configuring auto_increment_increment=4 and auto_increment_offset=3.
If we would like to add a 4'th master (D), we can do that by changing 'B' to use auto_increment_increment=4 and then configure D to have auto_increment_increment=4 and auto_increment_offset=4.
AUTO_INCREMENT columns are not permitted in CHECK constraints, DEFAULT value expressions and virtual columns.
If the NO_AUTO_VALUE_ON_ZERO SQL_MODE is set, zero values will not be automatically incremented:
Sequences - an alternative to auto_increment available from
- Generate unique ids
This page is licensed: GPLv2, originally from fill_help_tables.sql
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('fox'),('whale'),('ostrich');SELECT * FROM animals;
+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | fox |
| 5 | whale |
| 6 | ostrich |
+----+---------+CREATE TABLE t (id SERIAL, c CHAR(1)) ENGINE=InnoDB;
SHOW CREATE TABLE t \G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`c` char(1) DEFAULT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1ALTER TABLE animals AUTO_INCREMENT=8;
INSERT INTO animals (name) VALUES ('aardvark');
SELECT * FROM animals;
+----+-----------+
| id | name |
+----+-----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | fox |
| 5 | whale |
| 6 | ostrich |
| 8 | aardvark |
+----+-----------+
SET insert_id=12;
INSERT INTO animals (name) VALUES ('gorilla');
SELECT * FROM animals;
+----+-----------+
| id | name |
+----+-----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | fox |
| 5 | whale |
| 6 | ostrich |
| 8 | aardvark |
| 12 | gorilla |
+----+-----------+CREATE TABLE t (id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY) ENGINE = InnoDB;
INSERT INTO t VALUES (NULL);
SELECT id FROM t;
+----+
| id |
+----+
| 1 |
+----+
INSERT INTO t VALUES (10); -- higher value
SELECT id FROM t;
+----+
| id |
+----+
| 1 |
| 10 |
+----+
INSERT INTO t VALUES (2); -- lower value
INSERT INTO t VALUES (NULL); -- auto value
SELECT id FROM t;
+----+
| id |
+----+
| 1 |
| 2 |
| 10 |
| 11 |
+----+SET @@auto_increment_increment=3;
SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 3 |
| auto_increment_offset | 1 |
+--------------------------+-------+
CREATE TABLE t (c INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
INSERT INTO t VALUES (NULL), (NULL), (NULL);
SELECT * FROM t;
+---+
| c |
+---+
| 1 |
| 4 |
| 7 |
+---+
CREATE TABLE t2 (c INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
SET @@auto_increment_offset=2;
SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 3 |
| auto_increment_offset | 2 |
+--------------------------+-------+
INSERT INTO t2 VALUES (NULL), (NULL), (NULL);
SELECT * FROM t2;
+---+
| c |
+---+
| 2 |
| 5 |
| 8 |
+---+SET @@auto_increment_offset=5;
SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 3 |
| auto_increment_offset | 5 |
+--------------------------+-------+
CREATE TABLE t3 (c INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
INSERT INTO t3 VALUES (NULL), (NULL), (NULL);
SELECT * FROM t3;
+---+
| c |
+---+
| 1 |
| 4 |
| 5 |
+---+
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 3 |
| auto_increment_offset | 3 |
+--------------------------+-------+
INSERT INTO t4 VALUES (NULL), (NULL), (NULL);
SELECT * FROM t4;
+---+
| c |
+---+
| 3 |
| 6 |
| 9 |
+---+1
3
5
72
4
6
81
5
9
133
7
11CREATE OR REPLACE TABLE t1 (a INT);
INSERT t1 VALUES (0),(0),(0);
ALTER TABLE t1 MODIFY a INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
SELECT * FROM t1;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
+---+CREATE OR REPLACE TABLE t1 (a INT);
INSERT t1 VALUES (5),(0),(8),(0);
ALTER TABLE t1 MODIFY a INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
SELECT * FROM t1;
+---+
| a |
+---+
| 5 |
| 6 |
| 8 |
| 9 |
+---+SET SQL_MODE='no_auto_value_on_zero';
CREATE OR REPLACE TABLE t1 (a INT);
INSERT t1 VALUES (3), (0);
ALTER TABLE t1 MODIFY a INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
SELECT * FROM t1;
+---+
| a |
+---+
| 0 |
| 3 |
+---+IPv6 address data type. Stores IPv6 addresses as 16-byte binary strings, also supporting IPv4 addresses via mapping.
The INET6 data type is intended for storage of IPv6 addresses, as well as IPv4 addresses assuming conventional mapping of IPv4 addresses into IPv6 addresses.
Both short and long IPv6 notation are permitted, according to RFC-5952.
Values are stored as a 16-byte fixed length binary string, with most significant byte first.
Storage engines see INET6 as BINARY(16).
Clients see INET6 as CHAR(39) and get text representation on retrieval.
The IPv4-compatible notation is considered as deprecated. It is supported for compatibility with the function, which also understands this format. It's recommended to use the mapped format to store IPv4 addresses in INET6.
When an IPv4 mapped (or compatible) value is stored in INET6, it still occupies 16 bytes:
On retrieval, in the client-server text protocol, INET6 values are converted to the short text representation, according to RFC-5952, that is with all leading zeroes in each group removed and with consequent zero groups compressed.
Besides creating one's own , there is no a way to retrieve an INET6 value using long text representation.
from a character string to INET6 understands addresses in short or long text notation (including IPv4 mapped and compatible addresses). NULL is returned if the format is not understood.
CAST from a binary string to INET6 requires a 16-byte string as an argument. NULL is returned if the argument length is not equal to 16.
An INET6 expression can be compared to:
another INET6 expression
a character string expression with a text (short or long) address representation:
a 16-byte binary string expression.
Attempting to compare INET6 to an expression of any other data type returns an error.
An INET6 expression can be mixed for result (i.e. , , etc) with:
another INET6 expression. The resulting data type is INET6.
a character string in text (short or long) address representation. The result data type is INET6. The character string counterpart is automatically converted to INET6. If the string format is not understood, it's converted with a warning to either NULL or to '::', depending on the NULL-ability of the result.
Attempts to mix INET6 for result with other data types will return an error.
Mixing INET6 with other data types for and , when mixing for comparison and mixing for result are involved at the same time, uses the same rules with mixing for result, described in the previous paragraphs.
with an INET6 argument returns a hexadecimal representation of the underlying 16-byte binary string
Arithmetic operators (+,-,*,/,MOD,DIV) are not supported for INET6. This may change in the future.
The function now understands INET6 values as an argument
The prototypes of the and I functions have changed from a BINARY(16)
When the argument for the aforementioned two functions is not INET6, automatic implicit CAST to INET6 is applied. As a consequence, both functions understand arguments in both text representation and binary(16) representation.
When the argument for the aforementioned two functions is not INET6, automatic implicit CAST to INET6 is not applied.
INET6 understands both and address representation in parameters (.. and statements).
You may have used as a storage for IPv6 internet addresses, in combination with and to respectively insert and retrieve data.
However, you can BINARY(16) columns storing IPv6 addresses to INET6. After such an alter, there is no a need to use INET6_ATON() and INET6_NTOA(). Addresses can be inserted and retrieved directly.
You may use as a storage for IPv6 internet addresses, in combination with and to respectively insert and retrieve data.
It is also possible to convert INET6 columns to BINARY(16) and continue using the data in combination with INET6_NTOA() and INET6_ATON().
Inserting using short text address notation:
Long text address notation:
16-byte binary string notation:
IPv4 addresses, using IPv4-mapped and IPv4-compatible notations:
IPv4 mapped (or compatible) values still occupy 16 bytes:
Casting from INET6 to anything other than returns an error:
Comparison with another INET6 expression:
With a character string expression with a text (short or long) address representation:
With a 16-byte binary string expression:
With an expression of another data type:
Mixed with another INET6 expression, returning an INET6 data type:
Mixed with a character string in text (short or long) address representation:
Mixed with a 16-byte binary string:
Mixing with other data types:
with an INET6 argument returning a hexadecimal representation:
now understands INET6 values as an argument:
and prototype now a BINARY(16)):
Automatic implicit CAST to INET6:
Migrating to INET6:
There's no conversion you can use:
Migration from INET6 to BINARY(16):
Casting from data types to INET6 is permitted, allowing INET4 values to be inserted into INET6 columns.
Casting from data types to INET6 is not permitted. You get an error if you try:
(video)
This page is licensed: CC BY-SA / Gnu FDL
Learn how to configure character sets and collations at the server, table, and column levels to ensure correct data handling.
The default character set is utf8mb4 and the default collation is utf8mb4_uca1400_ai_ci.
This may differ in some distros, see for example .
The default is latin1 and the default collation is latin1_swedish_ci.
This may differ in some distros, see for example .
The character sets and the collations can be specified from the server right down to the column level, as well as for client-server connections. When changing a character set and not specifying a collation, the default collation for the new character set is always used.
INET6CAST from other data types to INET6 first converts data to a character string, then CAST from character string to INET6 is applied.CAST from INET6 to CHAR returns short text address notation.
CAST from INET6 to BINARY returns its 16-byte binary string representation.
CAST from INET6 to data types other than CHAR (e.g. SIGNED, UNSIGNED, TIME, etc) returns an error.
a 16-byte binary string. The resulting data type is INET6. The binary string counterpart is automatically converted to INET6. If the length of the binary string is not equal to 16, it's converted with a warning to NULL or to '::' depending on the NULL-ability of the result.
a INET6CREATE TABLE t1 (a INET6);INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');INSERT INTO t1 VALUES ('2001:0db8:0000:0000:0000:ff00:0042:8329');INSERT INTO t1 VALUES (0x20010DB8000000000000FF0000428329);
INSERT INTO t1 VALUES (UNHEX('20010DB8000000000000FF0000428329'));INSERT INTO t1 VALUES ('::ffff:192.0.2.128'); -- mapped
INSERT INTO t1 VALUES ('::192.0.2.128'); -- compatibleSELECT * FROM t1;
+------------------------+
| a |
+------------------------+
| 2001:db8::ff00:42:8329 |
| 2001:db8::ff00:42:8329 |
| 2001:db8::ff00:42:8329 |
| 2001:db8::ff00:42:8329 |
| ::ffff:192.0.2.128 |
| ::192.0.2.128 |
+------------------------+CREATE OR REPLACE TABLE t1 (a INET6);
INSERT INTO t1 VALUES ('::ffff:192.0.2.128');
SELECT * FROM t1;
+--------------------+
| a |
+--------------------+
| ::ffff:192.0.2.128 |
+--------------------+
SELECT HEX(a) FROM t1;
+----------------------------------+
| HEX(a) |
+----------------------------------+
| 00000000000000000000FFFFC0000280 |
+----------------------------------+SELECT CAST(a AS DECIMAL) FROM t1;
ERROR 4079 (HY000): Illegal parameter data type inet6 for operation 'decimal_typecast'CREATE OR REPLACE TABLE t1 (a INET6);
CREATE OR REPLACE TABLE t2 (a INET6);
INSERT INTO t1 VALUES ('2001:db8::ff00:42:8328'),('2001:db8::ff00:42:8329');
INSERT INTO t2 VALUES ('2001:db8::ff00:42:832a'),('2001:db8::ff00:42:8329');
SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
+------------------------+
| a |
+------------------------+
| 2001:db8::ff00:42:8329 |
+------------------------+CREATE OR REPLACE TABLE t1 (a INET6);
INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');
SELECT * FROM t1 WHERE a='2001:db8::ff00:42:8329';
+------------------------+
| a |
+------------------------+
| 2001:db8::ff00:42:8329 |
+------------------------+CREATE OR REPLACE TABLE t1 (a INET6);
INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');
SELECT * FROM t1 WHERE a=X'20010DB8000000000000FF0000428329';
+------------------------+
| a |
+------------------------+
| 2001:db8::ff00:42:8329 |
+------------------------+SELECT * FROM t1 WHERE a=1;
ERROR 4078 (HY000): Illegal parameter data types inet6 and int for operation '='CREATE OR REPLACE TABLE t1 (a INET6, b INET6);
INSERT INTO t1 VALUES (NULL,'2001:db8::ff00:42:8329');
SELECT a FROM t1 UNION SELECT b FROM t1;
+------------------------+
| a |
+------------------------+
| NULL |
| 2001:db8::ff00:42:8329 |
+------------------------+
SELECT COALESCE(a, b) FROM t1;
+------------------------+
| COALESCE(a, b) |
+------------------------+
| 2001:db8::ff00:42:8329 |
+------------------------+CREATE OR REPLACE TABLE t1 (a INET6, b VARCHAR(64));
INSERT INTO t1 VALUES (NULL,'2001:db8::ff00:42:8328');
INSERT INTO t1 VALUES (NULL,'2001:db8::ff00:42:832a garbage');
SELECT COALESCE(a,b) FROM t1;
+------------------------+
| COALESCE(a,b) |
+------------------------+
| 2001:db8::ff00:42:8328 |
| NULL |
+------------------------+
2 rows in set, 1 warning (0.001 sec)
SHOW WARNINGS;
+---------+------+---------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------+
| Warning | 1292 | Incorrect inet6 value: '2001:db8::ff00:42:832a garbage' |
+---------+------+---------------------------------------------------------+CREATE OR REPLACE TABLE t1 (a INET6, b VARBINARY(16));
INSERT INTO t1 VALUES (NULL,CONCAT(0xFFFF,REPEAT(0x0000,6),0xFFFF));
INSERT INTO t1 VALUES (NULL,0x00/*garbage*/);
SELECT COALESCE(a,b) FROM t1;
+---------------+
| COALESCE(a,b) |
+---------------+
| ffff::ffff |
| NULL |
+---------------+
2 rows in set, 1 warning (0.001 sec)
SHOW WARNINGS;
+---------+------+-------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------+
| Warning | 1292 | Incorrect inet6 value: '\x00' |
+---------+------+-------------------------------+SELECT CAST('ffff::ffff' AS INET6) UNION SELECT 1;
ERROR 4078 (HY000): Illegal parameter data types inet6 and int for operation 'UNION'SELECT HEX(CAST('2001:db8::ff00:42:8329' AS INET6));
+----------------------------------------------+
| HEX(CAST('2001:db8::ff00:42:8329' AS INET6)) |
+----------------------------------------------+
| 20010DB8000000000000FF0000428329 |
+----------------------------------------------+CREATE OR REPLACE TABLE t1 (a INET6);
INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');
SELECT a, HEX(INET6_ATON(a)) FROM t1;
+------------------------+----------------------------------+
| a | HEX(INET6_ATON(a)) |
+------------------------+----------------------------------+
| 2001:db8::ff00:42:8329 | 20010DB8000000000000FF0000428329 |
+------------------------+----------------------------------+CREATE OR REPLACE TABLE t1 (a INET6);
INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');
INSERT INTO t1 VALUES ('::ffff:192.168.0.1');
INSERT INTO t1 VALUES ('::192.168.0.1');
SELECT a, IS_IPV4_MAPPED(a), IS_IPV4_COMPAT(a) FROM t1;
+------------------------+-------------------+-------------------+
| a | IS_IPV4_MAPPED(a) | IS_IPV4_COMPAT(a) |
+------------------------+-------------------+-------------------+
| 2001:db8::ff00:42:8329 | 0 | 0 |
| ::ffff:192.168.0.1 | 1 | 0 |
| ::192.168.0.1 | 0 | 1 |
+------------------------+-------------------+-------------------+CREATE OR REPLACE TABLE t1 (
a INET6,
b VARCHAR(39) DEFAULT a
);
INSERT INTO t1 (a) VALUES ('ffff::ffff'),('::ffff:192.168.0.1');
SELECT a, IS_IPV4_MAPPED(a), b, IS_IPV4_MAPPED(b) FROM t1;
+--------------------+-------------------+--------------------+-------------------+
| a | IS_IPV4_MAPPED(a) | b | IS_IPV4_MAPPED(b) |
+--------------------+-------------------+--------------------+-------------------+
| ffff::ffff | 0 | ffff::ffff | 0 |
| ::ffff:192.168.0.1 | 1 | ::ffff:192.168.0.1 | 1 |
+--------------------+-------------------+--------------------+-------------------+
CREATE OR REPLACE TABLE t1 (
a INET6,
b BINARY(16) DEFAULT UNHEX(HEX(a))
);
INSERT INTO t1 (a) VALUES ('ffff::ffff'),('::ffff:192.168.0.1');
SELECT a, IS_IPV4_MAPPED(a), HEX(b), IS_IPV4_MAPPED(b) FROM t1;
+--------------------+-------------------+----------------------------------+-------------------+
| a | IS_IPV4_MAPPED(a) | HEX(b) | IS_IPV4_MAPPED(b) |
+--------------------+-------------------+----------------------------------+-------------------+
| ffff::ffff | 0 | FFFF000000000000000000000000FFFF | 0 |
| ::ffff:192.168.0.1 | 1 | 00000000000000000000FFFFC0A80001 | 1 |
+--------------------+-------------------+----------------------------------+-------------------+CREATE OR REPLACE TABLE t1 (a INET6);
EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING 'ffff::fffe';
EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING X'FFFF000000000000000000000000FFFF';
SELECT * FROM t1;
+------------+
| a |
+------------+
| ffff::fffe |
| ffff::ffff |
+------------+
EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING 'ffff::fffe';
+------------+
| a |
+------------+
| ffff::fffe |
+------------+
EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING X'FFFF000000000000000000000000FFFF';
+------------+
| a |
+------------+
| ffff::ffff |
+------------+CREATE OR REPLACE TABLE t1 (a BINARY(16));
INSERT INTO t1 VALUES (INET6_ATON('ffff::ffff'));
SELECT INET6_NTOA(a) FROM t1;
+---------------+
| INET6_NTOA(a) |
+---------------+
| ffff::ffff |
+---------------+ALTER TABLE t1 MODIFY a INET6;
INSERT INTO t1 VALUES ('ffff::fffe');
SELECT * FROM t1;
+------------+
| a |
+------------+
| ffff::ffff |
| ffff::fffe |
+------------+CREATE OR REPLACE TABLE t1 (a BINARY(16));
INSERT INTO t1 VALUES (INET6_ATON('ffff::ffff'));
SELECT INET6_NTOA(a) FROM t1;
+---------------+
| INET6_NTOA(a) |
+---------------+
| ffff::ffff |
+---------------+CREATE OR REPLACE TABLE t1 (a INET6);
INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');
INSERT INTO t1 VALUES ('::ffff:192.168.0.1');
INSERT INTO t1 VALUES ('::192.168.0.1');
ALTER TABLE t1 MODIFY a BINARY(16);
SELECT INET6_NTOA(a) FROM t1;
+------------------------+
| INET6_NTOA(a) |
+------------------------+
| 2001:db8::ff00:42:8329 |
| ::ffff:192.168.0.1 |
| ::192.168.0.1 |
+------------------------+CREATE TABLE t1 (a INET6);
INSERT INTO t1 VALUES('0.0.0.0'), ('255.10.0.0'), ('255.255.255.255');
Query OK, 3 rows affected (0.027 sec)CREATE TABLE t1 (a INET6);
INSERT INTO t1 VALUES('0.0.0.0'), ('255.10.0.0'), ('255.255.255.255');
ERROR 1292 (22007): Incorrect inet6 value: '0.0.0.0' for column `test`.`t1`.`a` at row 1Default collations for each character set can be viewed with the SHOW COLLATION statement, for example, to find the default collation for the latin2 character set:
The character_set_server system variable can be used to change the default server character set. It can be set both on startup or dynamically, with the SET command:
Similarly, the collation_server variable is used for setting the default server collation.
The CREATE DATABASE and ALTER DATABASE statements have optional character set and collation clauses. If these are left out, the server defaults are used.
To determine the default character set used by a database, use:
Alternatively, for the character set and collation:
It is also possible to specify only the collation, and, since each collation only applies to one character set, the associated character set will automatically be specified.
Although there are character_set_database and collation_database system variables which can be set dynamically, these are used for determining the character set and collation for the default database, and should only be set by the server.
The CREATE TABLE and ALTER TABLE statements support optional character set and collation clauses, a MariaDB and MySQL extension to standard SQL.
If neither character set nor collation is provided, the database default will be used. If only the character set is provided, the default collation for that character set will be used . If only the collation is provided, the associated character set will be used. See Supported Character Sets and Collations.
If no collation is provided, the collation will be set to the default collation for that character set. See Supported Character Sets and Collations.
For VARCHAR or TEXT columns, CONVERT TO CHARACTER SET changes the data type if needed to ensure the new column is long enough to store as many characters as the original column.
For example, an ascii TEXT column requires a single byte per character, so the column can hold up to 65,535 characters. If the column is converted to utf8mb4, 4 bytes can be required for each character, so the column will be converted to MEDIUMTEXT to be able to hold the same number of characters.
CONVERT TO CHARACTER SET binary will convert CHAR, VARCHAR and TEXT columns to BINARY, VARBINARY and BLOB respectively, and from that point will no longer have a character set, or be affected by future CONVERT TO CHARACTER SET statements.
To avoid data type changes resulting from CONVERT TO CHARACTER SET, use MODIFY on the individual columns instead. For example:
Character sets and collations can also be specified for columns that are character types CHAR, TEXT or VARCHAR. The CREATE TABLE and ALTER TABLE statements support optional character set and collation clauses for this purpose - unlike those at the table level, the column level definitions are standard SQL.
If neither collation nor character set is provided, the table default is used. If only the character set is specified, that character set's default collation is used, while if only the collation is specified, the associated character set is used.
When using ALTER TABLE to change a column's character set, you need to ensure the character sets are compatible with your data. MariaDB will map the data as best it can, but it's possible to lose data if care is not taken.
The SHOW CREATE TABLE statement or INFORMATION SCHEMA database can be used to determine column character sets and collations.
The character_set_filesystem system variable has controlled interpretation of file names that are given as literal strings. This affects the following statements and functions:
By default, the character set and collation used for literals is determined by the character_set_connection and collation_connection system variables. However, they can also be specified explicitly:
The character set of string literals that do not have a character set introducer is determined by the character_set_connection system variable.
This query always returns the same character set name in both columns.:
character_set_client and character_set_connection are normally (e.g. during handshake, or after a SET NAMES query) are set to equal values. However, it's possible to set to different values.
Examples when setting @@character_set_client and @@character_set_connection to different values can be useful:
Example 1:
Suppose, we have a utf8 database with this table:
Now we connect to it using a client which uses the DOS character set (cp850 on a West European machine), and want to fetch all records that are equal to 'ö' according to the German phonebook rules.
It's possible with the following:
This will return:
It works as follows:
The client sends the query using cp850.
The server, when parsing the query, creates a utf8 string literal by converting 'ö' from @@character_set_client (cp850) to @@character_set_connection (utf8).
The server applies the collation utf8_german2_ci to this string literal.
The server uses utf8_german2_ci for comparison.
Note, if we rewrite the script like this:
We get an error:
Reason:
In step #2, the literal is not converted to utf8 any more and is created using cp850.
In step #3, the server fails to apply utf8_german2_ci to an cp850 string literal.
Example 2:
Suppose we have a utf8 database and use "mysql.exe" from a West European machine again.
We can do this:
It creates a table with a column of the type VARCHAR(1) CHARACTER SET utf8.
Note, if we rewrite the query like this:
It creates a table with a column of the type VARCHAR(1) CHARACTER SET cp850, which is probably not a good idea.
Also, N or n can be used as prefix to convert a literal into the National Character set (which in MariaDB is always utf8).
For example:
The literals which occur in stored programs and views, by default, use the character set and collation which was specified by the character_set_connection and collation_connection system variables when the stored program was created. These values can be seen using the SHOW CREATE statements. To change the character sets used for literals in an existing stored program, it is necessary to drop and recreate the stored program.
For stored routines parameters and return values, a character set and a collation can be specified via the CHARACTER SET and COLLATE clauses.
The following example shows that the character set and collation are determined at the time of creation:
The following example shows how to specify a function parameters character set and collation:
It is possible to change the default collation associated with a particular character set. The character_set_collations system variable accepts a comma-delimited list of character sets and new default collations, for example:
It is not possible to change the default collation associated with a particular character set.
The new variable will take effect in all cases where a character set is explicitly or implicitly specified without an explicit COLLATE clause, including but not limited to:
Column collation
Table collation
Database collation
CHAR(expr USING csname)
CONVERT(expr USING csname)
CAST(expr AS CHAR CHARACTER SET csname)
'' - character string literal
_utf8mb3'text' - a character string literal with an introducer
_utf8mb3 X'61' - a character string literal with an introducer with hex notation
_utf8mb3 0x61 - a character string literal with an introducer with hex hybrid notation
@@collation_connection after a SET NAMES statement without COLLATE
To change the default character set from latin1 to UTF-8, the following settings should be specified in the my.cnf configuration file.
Note that the default-character-set option is a client option, not a server option.
This page is licensed: CC BY-SA / Gnu FDL
SELECT CHARSET(N'a string');
+----------------------+
| CHARSET(N'a string') |
+----------------------+
| utf8mb3 |
+----------------------+SELECT 'Mueller' = 'Müller' COLLATE 'latin1_german2_ci';
+---------------------------------------------------+
| 'Mueller' = 'Müller' COLLATE 'latin1_german2_ci' |
+---------------------------------------------------+
| 1 |
+---------------------------------------------------+SELECT CHARSET(N'a string');
+----------------------+
| CHARSET(N'a string') |
+----------------------+
| utf8 |
+----------------------+SET @@character_set_collations = 'utf8mb4=uca1400_ai_ci, latin2=latin2_hungarian_ci';SHOW COLLATION LIKE 'latin2%';
+---------------------+---------+----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+---------------------+---------+----+---------+----------+---------+
| latin2_czech_cs | latin2 | 2 | | Yes | 4 |
| latin2_general_ci | latin2 | 9 | Yes | Yes | 1 |
| latin2_hungarian_ci | latin2 | 21 | | Yes | 1 |
| latin2_croatian_ci | latin2 | 27 | | Yes | 1 |
| latin2_bin | latin2 | 77 | | Yes | 1 |
+---------------------+---------+----+---------+----------+---------+SET character_set_server = 'latin2';SET collation_server = 'latin2_czech_cs';CREATE DATABASE czech_slovak_names
CHARACTER SET = 'keybcs2'
COLLATE = 'keybcs2_bin';ALTER DATABASE czech_slovak_names COLLATE = 'keybcs2_general_ci';SHOW CREATE DATABASE czech_slovak_names;
+--------------------+--------------------------------------------------------------------------------+
| Database | Create Database |
+--------------------+--------------------------------------------------------------------------------+
| czech_slovak_names | CREATE DATABASE `czech_slovak_names` /*!40100 DEFAULT CHARACTER SET keybcs2 */ |
+--------------------+--------------------------------------------------------------------------------+SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def | czech_slovak_names | keybcs2 | keybcs2_general_ci | NULL |
| def | information_schema | utf8 | utf8_general_ci | NULL |
| def | mysql | latin1 | latin1_swedish_ci | NULL |
| def | performance_schema | utf8 | utf8_general_ci | NULL |
| def | test | latin1 | latin1_swedish_ci | NULL |
+--------------+--------------------+----------------------------+------------------------+----------+CREATE DATABASE danish_names COLLATE 'utf8_danish_ci';
SHOW CREATE DATABASE danish_names;
+--------------+----------------------------------------------------------------------------------------------+
| Database | Create Database |
+--------------+----------------------------------------------------------------------------------------------+
| danish_names | CREATE DATABASE `danish_names` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_danish_ci */ |
+--------------+----------------------------------------------------------------------------------------------+CREATE TABLE english_names (id INT, name VARCHAR(40))
CHARACTER SET 'utf8'
COLLATE 'utf8_icelandic_ci';ALTER TABLE table_name
CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];ALTER TABLE table_name MODIFY ascii_text_column TEXT CHARACTER SET utf8;
ALTER TABLE table_name MODIFY ascii_varchar_column VARCHAR(M) CHARACTER SET utf8;CREATE TABLE european_names (
croatian_names VARCHAR(40) COLLATE 'cp1250_croatian_ci',
greek_names VARCHAR(40) CHARACTER SET 'greek');SHOW CREATE TABLE european_names\G
*************************** 1. row ***************************
Table: european_names
Create Table: CREATE TABLE `european_names` (
`croatian_names` varchar(40) CHARACTER SET cp1250 COLLATE cp1250_croatian_ci DEFAULT NULL,
`greek_names` varchar(40) CHARACTER SET greek DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ciSELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE 'european%'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: danish_names
TABLE_NAME: european_names
COLUMN_NAME: croatian_names
ORDINAL_POSITION: 1
COLUMN_DEFAULT: NULL
IS_NULLABLE: YES
DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 40
CHARACTER_OCTET_LENGTH: 40
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: cp1250
COLLATION_NAME: cp1250_croatian_ci
COLUMN_TYPE: varchar(40)
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
*************************** 2. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: danish_names
TABLE_NAME: european_names
COLUMN_NAME: greek_names
ORDINAL_POSITION: 2
COLUMN_DEFAULT: NULL
IS_NULLABLE: YES
DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 40
CHARACTER_OCTET_LENGTH: 40
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: greek
COLLATION_NAME: greek_general_ci
COLUMN_TYPE: varchar(40)
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:[_charset_name]'string' [COLLATE collation_name]SELECT CHARSET('a'), @@character_set_connection;CREATE TABLE t1 (a VARCHAR(10)) CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO t1 VALUES ('oe'),('ö');SET @@character_set_client=cp850, @@character_set_connection=utf8;
SELECT a FROM t1 WHERE a='ö' COLLATE utf8_german2_ci;+------+
| a |
+------+
| oe |
| ö |
+------+SET NAMES cp850;
SELECT a FROM t1 WHERE a='ö' COLLATE utf8_german2_ci;ERROR 1253 (42000): COLLATION 'utf8_german2_ci' is not valid for CHARACTER SET 'cp850'SET @@character_set_client=cp850, @@character_set_connection=utf8;
CREATE TABLE t2 AS SELECT 'ö';SET NAMES cp850;
CREATE TABLE t2 AS SELECT 'ö';SELECT _latin2 'Müller';
+-----------+
| MĂźller |
+-----------+
| MĂźller |
+-----------+SET @@local.character_set_connection='latin1';
DELIMITER ||
CREATE PROCEDURE `test`.`x`()
BEGIN
SELECT CHARSET('x');
END;
||
Query OK, 0 rows affected (0.00 sec)
DELIMITER ;
SET @@local.character_set_connection='utf8';
CALL `test`.`x`();
+--------------+
| CHARSET('x') |
+--------------+
| latin1 |
+--------------+CREATE FUNCTION `test`.`y`(`str` TEXT CHARACTER SET utf8 COLLATE utf8_bin)
RETURNS TEXT CHARACTER SET latin1 COLLATE latin1_bin
BEGIN
SET @param_coll = COLLATION(`str`);
RETURN `str`;
END;
-- return value's collation:
SELECT COLLATION(`test`.`y`('Hello, planet!'));
+-----------------------------------------+
| COLLATION(`test`.`y`('Hello, planet!')) |
+-----------------------------------------+
| latin1_bin |
+-----------------------------------------+
-- parameter's collation:
SELECT @param_coll;
+-------------+
| @param_coll |
+-------------+
| utf8_bin |
+-------------+[mysql]
...
default-character-set=utf8mb4
...
[mysqld]
...
collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4
...Server locale settings control the language for date and time functions via lc_time_names and the language for error messages via lc_messages.
The lc_time_names server system variable sets the language used by the date and time functions DAYNAME(), MONTHNAME(), and DATE_FORMAT(), and the lc_messages sets the language for error messages.
The list of the locales supported by the current MariaDB installation can be obtained via the LOCALES plugin.
MariaDB supports the following locale values:
ar_AE
Arabic
United Arab Emirates
Setting the and variables to localize the units of date and time, and the server error messages.
This page is licensed: CC BY-SA / Gnu FDL
ar_BH
Arabic
Bahrain
ar_DZ
Arabic
Algeria
ar_EG
Arabic
Egypt
ar_IN
Arabic
Iran
ar_IQ
Arabic
Iraq
ar_JO
Arabic
Jordan
ar_KW
Arabic
Kuwait
ar_LB
Arabic
Lebanon
ar_LY
Arabic
Libya
ar_MA
Arabic
Morocco
ar_OM
Arabic
Oman
ar_QA
Arabic
Qatar
ar_SA
Arabic
Saudi Arabia
ar_SD
Arabic
Sudan
ar_SY
Arabic
Syria
ar_TN
Arabic
Tunisia
ar_YE
Arabic
Yemen
be_BY
Belarusian
Belarus
bg_BG
Bulgarian
Bulgaria
ca_ES
Catalan
Catalan
cs_CZ
Czech
Czech Republic
da_DK
Danish
Denmark
de_AT
German
Austria
de_BE
German
Belgium
de_CH
German
Switzerland
de_DE
German
Germany
de_LU
German
Luxembourg
el_GR
Greek
Greece
en_AU
English
Australia
en_CA
English
Canada
en_GB
English
United Kingdom
en_IN
English
India
en_NZ
English
New Zealand
en_PH
English
Philippines
en_US
English
United States
en_ZA
English
South Africa
en_ZW
English
Zimbabwe
es_AR
Spanish
Argentina
es_BO
Spanish
Bolivia
es_CL
Spanish
Chile
es_CO
Spanish
Columbia
es_CR
Spanish
Costa Rica
es_DO
Spanish
Dominican Republic
es_EC
Spanish
Ecuador
es_ES
Spanish
Spain
es_GT
Spanish
Guatemala
es_HN
Spanish
Honduras
es_MX
Spanish
Mexico
es_NI
Spanish
Nicaragua
es_PA
Spanish
Panama
es_PE
Spanish
Peru
es_PR
Spanish
Puerto Rico
es_PY
Spanish
Paraguay
es_SV
Spanish
El Salvador
es_US
Spanish
United States
es_UY
Spanish
Uruguay
es_VE
Spanish
Venezuela
et_EE
Estonian
Estonia
eu_ES
Basque
Basque
fi_FI
Finnish
Finland
fo_FO
Faroese
Faroe Islands
fr_BE
French
Belgium
fr_CA
French
Canada
fr_CH
French
Switzerland
fr_FR
French
France
fr_LU
French
Luxembourg
gl_ES
Galician
Galician
gu_IN
Gujarati
India
he_IL
Hebrew
Israel
hi_IN
Hindi
India
hr_HR
Croatian
Croatia
hu_HU
Hungarian
Hungary
id_ID
Indonesian
Indonesia
is_IS
Icelandic
Iceland
it_CH
Italian
Switzerland
it_IT
Italian
Italy
ja_JP
Japanese
Japan
ka_GE
Georgian
Georgia (From )
ko_KR
Korean
Republic of Korea
lt_LT
Lithuanian
Lithuania
lv_LV
Latvian
Latvia
mk_MK
Macedonian
FYROM
mn_MN
Mongolia
Mongolian
ms_MY
Malay
Malaysia
nb_NO
Norwegian(Bokmål)
Norway
nl_BE
Dutch
Belgium
nl_NL
Dutch
The Netherlands
no_NO
Norwegian
Norway
pl_PL
Polish
Poland
pt_BR
Portugese
Brazil
pt_PT
Portugese
Portugal
rm_CH
Romansh
Switzerland
ro_RO
Romanian
Romania
ru_RU
Russian
Russia
ru_UA
Russian
Ukraine
sk_SK
Slovak
Slovakia
sl_SI
Slovenian
Slovenia
sq_AL
Albanian
Albania
sr_YU
Serbian
Serbia (Deprecated in and removed in )
sv_FI
Swedish
Finland
sv_SE
Swedish
Sweden
sw_KE
Swahili
Kenya (from )
ta_IN
Tamil
India
te_IN
Telugu
India
th_TH
Thai
Thailand
tr_TR
Turkish
Turkey
uk_UA
Ukrainian
Ukraine
ur_PK
Urdu
Pakistan
vi_VN
Vietnamese
Viet Nam
zh_CN
Chinese
China
zh_HK
Chinese
Hong Kong
zh_TW
Chinese
Taiwan Province of China
SELECT DAYNAME('2013-04-01'), MONTHNAME('2013-04-01');
+-----------------------+-------------------------+
| DAYNAME('2013-04-01') | MONTHNAME('2013-04-01') |
+-----------------------+-------------------------+
| Monday | April |
+-----------------------+-------------------------+
SET lc_time_names = 'fr_CA';
SELECT DAYNAME('2013-04-01'), MONTHNAME('2013-04-01');
+-----------------------+-------------------------+
| DAYNAME('2013-04-01') | MONTHNAME('2013-04-01') |
+-----------------------+-------------------------+
| lundi | avril |
+-----------------------+-------------------------+
SELECT blah;
ERROR 1054 (42S22): Unknown column 'blah' in 'field' list'
SET lc_messages = 'nl_NL';
SELECT blah;
ERROR 1054 (42S22): Onbekende kolom 'blah' in field listView a comprehensive list of the character sets and collations available in MariaDB database, including their default properties and maximum lengths.
You can see which character sets are available in a particular version by running the SHOW CHARACTER SET statement or by querying the Information Schema CHARACTER_SETS Table.
It is possible to change the default collation associated with a character set. See Changing Default Collation.
It is not possible to change the default collation associated with a character set. See Changing Default Collation
MariaDB supports the following character sets:
Note that the only supports a limited number of character sets. See .
MariaDB supports the following collations (from ):
ℹ️ The UCA-14.0.0 collations were added in .
ℹ️ Before , the utf8mb3* collations listed above were named utf8*.
ℹ️ added UCA-9.0.0 collations, as well as a Comment column to information_schema.collations, to make it clear that the UCA-9.0.0 collations are mapped to the UCA-14.0.0 collations. The UCA-9.0.0 collations have mainly been added to make it easy to replicate from MySQL 8.0 to MariaDB 11.4.5 and newer.
You can find all combinations of the supported character set and collation in the table.
A 'ci' at the end of a collation name indicates the collation is case-insensitive. A 'cs' at the end of a
collation name indicates the collation is case sensitive.
NO PAD collations regard trailing spaces as normal characters. You can get a list of all of these by querying the as follows:
An accent insensitive collation is one where the accented and unaccented versions of a letter are considered to be identical for sorting purposes.
ℹ️ added the accent insensitivity flag, and new collations are marked with '_ai' or '_as' in the name to indicate this, for example:
added 184 UCA-14.0.0 collations. Unicode-14.0.0 was released in September 2021. They contain 939 . Old collations based on UCA-4.0.0 and UCA-5.2.0 did not support built-in contractions. This is a step towards better Unicode Collation Algorithm compliance. With built-in contractions, some languages (e.g., Thai) won't need specific collations and will just work with the default "root" collation.
changed the utf8 character set by default to be an alias for utf8mb3 rather than the other way around. It can be set to imply utf8mb4 by changing the value of the system variable.
added 88 NO PAD
This page is licensed: CC BY-SA / Gnu FDL
binary
Binary pseudo charset
binary
1
cp1250
Windows Central European
cp1250_general_ci
1
cp1251
Windows Cyrillic
cp1251_general_ci
1
cp1256
Windows Arabic
cp1256_general_ci
1
cp1257
Windows Baltic
cp1257_general_ci
1
cp850
DOS West European
cp850_general_ci
1
cp852
DOS Central European
cp852_general_ci
1
cp866
DOS Russian
cp866_general_ci
1
cp932
SJIS for Windows Japanese
cp932_japanese_ci
2
dec8
DEC West European
dec8_swedish_ci
1
eucjpms
UJIS for Windows Japanese
eucjpms_japanese_ci
3
euckr
EUC-KR Korean
euckr_korean_ci
2
gb2312
GB2312 Simplified Chinese
gb2312_chinese_ci
2
gbk
GBK Simplified Chinese
gbk_chinese_ci
2
geostd8
GEOSTD8 Georgian
geostd8_general_ci
1
greek
ISO 8859-7 Greek
greek_general_ci
1
hebrew
ISO 8859-8 Hebrew
hebrew_general_ci
1
hp8
HP West European
hp8_english_ci
1
keybcs2
DOS Kamenicky Czech-Slovak
keybcs2_general_ci
1
koi8r
KOI8-R Relcom Russian
koi8r_general_ci
1
koi8u
KOI8-U Ukrainian
koi8u_general_ci
1
latin1
cp1252 West European
latin1_swedish_ci
1
latin2
ISO 8859-2 Central European
latin2_general_ci
1
latin5
ISO 8859-9 Turkish
latin5_turkish_ci
1
latin7
ISO 8859-13 Baltic
latin7_general_ci
1
macce
Mac Central European
macce_general_ci
1
macroman
Mac West European
macroman_general_ci
1
sjis
Shift-JIS Japanese
sjis_japanese_ci
2
swe7
7bit Swedish
swe7_swedish_ci
1
tis620
TIS620 Thai
tis620_thai_ci
1
ucs2
UCS-2 Unicode
ucs2_general_ci (<= MariaDB 11.4) ucs2_uca1400_ai_ci (>=)
2
ujis
EUC-JP Japanese
ujis_japanese_ci
3
utf8
UTF-8 Unicode
utf8_general_ci
3/4 (see )
utf16
UTF-16 Unicode
utf16_general_ci (<= ) utf16_uca1400_ai_ci (>=)
4
utf16le
UTF-16LE Unicode
utf16le_general_ci
4
utf32
UTF-32 Unicode
utf32_general_ci (<= ) utf32_uca1400_ai_ci (>=)
4
utf8mb3
UTF-8 Unicode
utf8mb3_general_ci (<= ) utf8mb3_uca1400_ai_ci (>=)
3
utf8mb4
UTF-8 Unicode
utf8mb4_general_ci (<= ) utf8mb4_uca1400_ai_ci (>=)
4
added the utf8_thai_520_w2, utf8mb4_thai_520_w2, ucs2_thai_520_w2, utf16_thai_520_w2 and utf32_thai_520_w2 collations.
added the utf8_myanmar_ci, ucs2_myanmar_ci, utf8mb4_myanmar_ci, utf16_myanmar_ci and utf32_myanmar_ci collations.
added the utf8_german2_ci, utf8mb4_german2_ci, ucs2_german2_ci, utf16_german2_ci and utf32_german2_ci collations.
added a Croatian collation patch from Alexander Barkov to fix some problems with the Croatian character set and LIKE queries. This patch added utf8_croatian_ci and ucs2_croatian_ci collations to MariaDB.
armscii8
ARMSCII-8 Armenian
armscii8_general_ci
1
ascii
US ASCII
ascii_general_ci
1
big5
Big5 Traditional Chinese
big5_chinese_ci
2
SELECT collation_name, character_set_name as cs_name, id, is_default AS def, is_compiled AS com,
sortlen, comment FROM information_schema.collations ORDER BY collation_name;
+--------------------------------+----------+------+------+-----+---------+--------------------------------------------------+
| collation_name | cs_name | id | def | com | sortlen | comment |
+--------------------------------+----------+------+------+-----+---------+--------------------------------------------------+
| armscii8_bin | armscii8 | 64 | | Yes | 1 | ARMSCII-8 Armenian |
| armscii8_general_ci | armscii8 | 32 | Yes | Yes | 1 | ARMSCII-8 Armenian |
| armscii8_general_nopad_ci | armscii8 | 1056 | | Yes | 1 | ARMSCII-8 Armenian |
| armscii8_nopad_bin | armscii8 | 1088 | | Yes | 1 | ARMSCII-8 Armenian |
| ascii_bin | ascii | 65 | | Yes | 1 | US ASCII |
| ascii_general_ci | ascii | 11 | Yes | Yes | 1 | US ASCII |
| ascii_general_nopad_ci | ascii | 1035 | | Yes | 1 | US ASCII |
| ascii_nopad_bin | ascii | 1089 | | Yes | 1 | US ASCII |
| big5_bin | big5 | 84 | | Yes | 1 | Big5 Traditional Chinese |
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 | Big5 Traditional Chinese |
| big5_chinese_nopad_ci | big5 | 1025 | | Yes | 1 | |
| big5_nopad_bin | big5 | 1108 | | Yes | 1 | |
| binary | binary | 63 | Yes | Yes | 1 | Binary pseudo charset |
| cp1250_bin | cp1250 | 66 | | Yes | 1 | Windows Central European |
| cp1250_croatian_ci | cp1250 | 44 | | Yes | 1 | Windows Central European |
| cp1250_czech_cs | cp1250 | 34 | | Yes | 2 | Windows Central European |
| cp1250_general_ci | cp1250 | 26 | Yes | Yes | 1 | Windows Central European |
| cp1250_general_nopad_ci | cp1250 | 1050 | | Yes | 1 | Windows Central European |
| cp1250_nopad_bin | cp1250 | 1090 | | Yes | 1 | Windows Central European |
| cp1250_polish_ci | cp1250 | 99 | | Yes | 1 | Windows Central European |
| cp1251_bin | cp1251 | 50 | | Yes | 1 | Windows Cyrillic |
| cp1251_bulgarian_ci | cp1251 | 14 | | Yes | 1 | Windows Cyrillic |
| cp1251_general_ci | cp1251 | 51 | Yes | Yes | 1 | Windows Cyrillic |
| cp1251_general_cs | cp1251 | 52 | | Yes | 1 | Windows Cyrillic |
| cp1251_general_nopad_ci | cp1251 | 1075 | | Yes | 1 | Windows Cyrillic |
| cp1251_nopad_bin | cp1251 | 1074 | | Yes | 1 | Windows Cyrillic |
| cp1251_ukrainian_ci | cp1251 | 23 | | Yes | 1 | Windows Cyrillic |
| cp1256_bin | cp1256 | 67 | | Yes | 1 | Windows Arabic |
| cp1256_general_ci | cp1256 | 57 | Yes | Yes | 1 | Windows Arabic |
| cp1256_general_nopad_ci | cp1256 | 1081 | | Yes | 1 | Windows Arabic |
| cp1256_nopad_bin | cp1256 | 1091 | | Yes | 1 | Windows Arabic |
| cp1257_bin | cp1257 | 58 | | Yes | 1 | Windows Baltic |
| cp1257_general_ci | cp1257 | 59 | Yes | Yes | 1 | Windows Baltic |
| cp1257_general_nopad_ci | cp1257 | 1083 | | Yes | 1 | Windows Baltic |
| cp1257_lithuanian_ci | cp1257 | 29 | | Yes | 1 | Windows Baltic |
| cp1257_nopad_bin | cp1257 | 1082 | | Yes | 1 | Windows Baltic |
| cp850_bin | cp850 | 80 | | Yes | 1 | DOS West European |
| cp850_general_ci | cp850 | 4 | Yes | Yes | 1 | DOS West European |
| cp850_general_nopad_ci | cp850 | 1028 | | Yes | 1 | DOS West European |
| cp850_nopad_bin | cp850 | 1104 | | Yes | 1 | DOS West European |
| cp852_bin | cp852 | 81 | | Yes | 1 | DOS Central European |
| cp852_general_ci | cp852 | 40 | Yes | Yes | 1 | DOS Central European |
| cp852_general_nopad_ci | cp852 | 1064 | | Yes | 1 | DOS Central European |
| cp852_nopad_bin | cp852 | 1105 | | Yes | 1 | DOS Central European |
| cp866_bin | cp866 | 68 | | Yes | 1 | DOS Russian |
| cp866_general_ci | cp866 | 36 | Yes | Yes | 1 | DOS Russian |
| cp866_general_nopad_ci | cp866 | 1060 | | Yes | 1 | DOS Russian |
| cp866_nopad_bin | cp866 | 1092 | | Yes | 1 | DOS Russian |
| cp932_bin | cp932 | 96 | | Yes | 1 | SJIS for Windows Japanese |
| cp932_japanese_ci | cp932 | 95 | Yes | Yes | 1 | SJIS for Windows Japanese |
| cp932_japanese_nopad_ci | cp932 | 1119 | | Yes | 1 | |
| cp932_nopad_bin | cp932 | 1120 | | Yes | 1 | |
| dec8_bin | dec8 | 69 | | Yes | 1 | DEC West European |
| dec8_nopad_bin | dec8 | 1093 | | Yes | 1 | DEC West European |
| dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 | DEC West European |
| dec8_swedish_nopad_ci | dec8 | 1027 | | Yes | 1 | DEC West European |
| eucjpms_bin | eucjpms | 98 | | Yes | 1 | UJIS for Windows Japanese |
| eucjpms_japanese_ci | eucjpms | 97 | Yes | Yes | 1 | UJIS for Windows Japanese |
| eucjpms_japanese_nopad_ci | eucjpms | 1121 | | Yes | 1 | |
| eucjpms_nopad_bin | eucjpms | 1122 | | Yes | 1 | |
| euckr_bin | euckr | 85 | | Yes | 1 | EUC-KR Korean |
| euckr_korean_ci | euckr | 19 | Yes | Yes | 1 | EUC-KR Korean |
| euckr_korean_nopad_ci | euckr | 1043 | | Yes | 1 | |
| euckr_nopad_bin | euckr | 1109 | | Yes | 1 | |
| gb2312_bin | gb2312 | 86 | | Yes | 1 | GB2312 Simplified Chinese |
| gb2312_chinese_ci | gb2312 | 24 | Yes | Yes | 1 | GB2312 Simplified Chinese |
| gb2312_chinese_nopad_ci | gb2312 | 1048 | | Yes | 1 | |
| gb2312_nopad_bin | gb2312 | 1110 | | Yes | 1 | |
| gbk_bin | gbk | 87 | | Yes | 1 | GBK Simplified Chinese |
| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 | GBK Simplified Chinese |
| gbk_chinese_nopad_ci | gbk | 1052 | | Yes | 1 | |
| gbk_nopad_bin | gbk | 1111 | | Yes | 1 | |
| geostd8_bin | geostd8 | 93 | | Yes | 1 | GEOSTD8 Georgian |
| geostd8_general_ci | geostd8 | 92 | Yes | Yes | 1 | GEOSTD8 Georgian |
| geostd8_general_nopad_ci | geostd8 | 1116 | | Yes | 1 | GEOSTD8 Georgian |
| geostd8_nopad_bin | geostd8 | 1117 | | Yes | 1 | GEOSTD8 Georgian |
| greek_bin | greek | 70 | | Yes | 1 | ISO 8859-7 Greek |
| greek_general_ci | greek | 25 | Yes | Yes | 1 | ISO 8859-7 Greek |
| greek_general_nopad_ci | greek | 1049 | | Yes | 1 | ISO 8859-7 Greek |
| greek_nopad_bin | greek | 1094 | | Yes | 1 | ISO 8859-7 Greek |
| hebrew_bin | hebrew | 71 | | Yes | 1 | ISO 8859-8 Hebrew |
| hebrew_general_ci | hebrew | 16 | Yes | Yes | 1 | ISO 8859-8 Hebrew |
| hebrew_general_nopad_ci | hebrew | 1040 | | Yes | 1 | ISO 8859-8 Hebrew |
| hebrew_nopad_bin | hebrew | 1095 | | Yes | 1 | ISO 8859-8 Hebrew |
| hp8_bin | hp8 | 72 | | Yes | 1 | HP West European |
| hp8_english_ci | hp8 | 6 | Yes | Yes | 1 | HP West European |
| hp8_english_nopad_ci | hp8 | 1030 | | Yes | 1 | HP West European |
| hp8_nopad_bin | hp8 | 1096 | | Yes | 1 | HP West European |
| keybcs2_bin | keybcs2 | 73 | | Yes | 1 | DOS Kamenicky Czech-Slovak |
| keybcs2_general_ci | keybcs2 | 37 | Yes | Yes | 1 | DOS Kamenicky Czech-Slovak |
| keybcs2_general_nopad_ci | keybcs2 | 1061 | | Yes | 1 | DOS Kamenicky Czech-Slovak |
| keybcs2_nopad_bin | keybcs2 | 1097 | | Yes | 1 | DOS Kamenicky Czech-Slovak |
| koi8r_bin | koi8r | 74 | | Yes | 1 | KOI8-R Relcom Russian |
| koi8r_general_ci | koi8r | 7 | Yes | Yes | 1 | KOI8-R Relcom Russian |
| koi8r_general_nopad_ci | koi8r | 1031 | | Yes | 1 | KOI8-R Relcom Russian |
| koi8r_nopad_bin | koi8r | 1098 | | Yes | 1 | KOI8-R Relcom Russian |
| koi8u_bin | koi8u | 75 | | Yes | 1 | KOI8-U Ukrainian |
| koi8u_general_ci | koi8u | 22 | Yes | Yes | 1 | KOI8-U Ukrainian |
| koi8u_general_nopad_ci | koi8u | 1046 | | Yes | 1 | KOI8-U Ukrainian |
| koi8u_nopad_bin | koi8u | 1099 | | Yes | 1 | KOI8-U Ukrainian |
| latin1_bin | latin1 | 47 | | Yes | 1 | cp1252 West European |
| latin1_danish_ci | latin1 | 15 | | Yes | 1 | cp1252 West European |
| latin1_general_ci | latin1 | 48 | | Yes | 1 | cp1252 West European |
| latin1_general_cs | latin1 | 49 | | Yes | 1 | cp1252 West European |
| latin1_german1_ci | latin1 | 5 | | Yes | 1 | cp1252 West European |
| latin1_german2_ci | latin1 | 31 | | Yes | 2 | cp1252 West European |
| latin1_nopad_bin | latin1 | 1071 | | Yes | 1 | |
| latin1_spanish_ci | latin1 | 94 | | Yes | 1 | cp1252 West European |
| latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 | cp1252 West European |
| latin1_swedish_nopad_ci | latin1 | 1032 | | Yes | 1 | |
| latin2_bin | latin2 | 77 | | Yes | 1 | ISO 8859-2 Central European |
| latin2_croatian_ci | latin2 | 27 | | Yes | 1 | ISO 8859-2 Central European |
| latin2_czech_cs | latin2 | 2 | | Yes | 4 | ISO 8859-2 Central European |
| latin2_general_ci | latin2 | 9 | Yes | Yes | 1 | ISO 8859-2 Central European |
| latin2_general_nopad_ci | latin2 | 1033 | | Yes | 1 | ISO 8859-2 Central European |
| latin2_hungarian_ci | latin2 | 21 | | Yes | 1 | ISO 8859-2 Central European |
| latin2_nopad_bin | latin2 | 1101 | | Yes | 1 | ISO 8859-2 Central European |
| latin5_bin | latin5 | 78 | | Yes | 1 | ISO 8859-9 Turkish |
| latin5_nopad_bin | latin5 | 1102 | | Yes | 1 | ISO 8859-9 Turkish |
| latin5_turkish_ci | latin5 | 30 | Yes | Yes | 1 | ISO 8859-9 Turkish |
| latin5_turkish_nopad_ci | latin5 | 1054 | | Yes | 1 | ISO 8859-9 Turkish |
| latin7_bin | latin7 | 79 | | Yes | 1 | ISO 8859-13 Baltic |
| latin7_estonian_cs | latin7 | 20 | | Yes | 1 | ISO 8859-13 Baltic |
| latin7_general_ci | latin7 | 41 | Yes | Yes | 1 | ISO 8859-13 Baltic |
| latin7_general_cs | latin7 | 42 | | Yes | 1 | ISO 8859-13 Baltic |
| latin7_general_nopad_ci | latin7 | 1065 | | Yes | 1 | ISO 8859-13 Baltic |
| latin7_nopad_bin | latin7 | 1103 | | Yes | 1 | ISO 8859-13 Baltic |
| macce_bin | macce | 43 | | Yes | 1 | Mac Central European |
| macce_general_ci | macce | 38 | Yes | Yes | 1 | Mac Central European |
| macce_general_nopad_ci | macce | 1062 | | Yes | 1 | Mac Central European |
| macce_nopad_bin | macce | 1067 | | Yes | 1 | Mac Central European |
| macroman_bin | macroman | 53 | | Yes | 1 | Mac West European |
| macroman_general_ci | macroman | 39 | Yes | Yes | 1 | Mac West European |
| macroman_general_nopad_ci | macroman | 1063 | | Yes | 1 | Mac West European |
| macroman_nopad_bin | macroman | 1077 | | Yes | 1 | Mac West European |
| sjis_bin | sjis | 88 | | Yes | 1 | Shift-JIS Japanese |
| sjis_japanese_ci | sjis | 13 | Yes | Yes | 1 | Shift-JIS Japanese |
| sjis_japanese_nopad_ci | sjis | 1037 | | Yes | 1 | |
| sjis_nopad_bin | sjis | 1112 | | Yes | 1 | |
| swe7_bin | swe7 | 82 | | Yes | 1 | 7bit Swedish |
| swe7_nopad_bin | swe7 | 1106 | | Yes | 1 | 7bit Swedish |
| swe7_swedish_ci | swe7 | 10 | Yes | Yes | 1 | 7bit Swedish |
| swe7_swedish_nopad_ci | swe7 | 1034 | | Yes | 1 | 7bit Swedish |
| tis620_bin | tis620 | 89 | | Yes | 1 | TIS620 Thai |
| tis620_nopad_bin | tis620 | 1113 | | Yes | 1 | |
| tis620_thai_ci | tis620 | 18 | Yes | Yes | 4 | TIS620 Thai |
| tis620_thai_nopad_ci | tis620 | 1042 | | Yes | 4 | |
| uca1400_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_croatian_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_croatian_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_croatian_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_croatian_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_croatian_nopad_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_croatian_nopad_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_croatian_nopad_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_croatian_nopad_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_czech_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_czech_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_czech_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_czech_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_czech_nopad_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_czech_nopad_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_czech_nopad_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_czech_nopad_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_danish_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_danish_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_danish_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_danish_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_danish_nopad_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_danish_nopad_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_danish_nopad_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_danish_nopad_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_esperanto_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_esperanto_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_esperanto_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_esperanto_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_esperanto_nopad_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_esperanto_nopad_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_esperanto_nopad_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_esperanto_nopad_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_estonian_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_estonian_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_estonian_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_estonian_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_estonian_nopad_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_estonian_nopad_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_estonian_nopad_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_estonian_nopad_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_german2_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_german2_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_german2_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_german2_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_german2_nopad_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_german2_nopad_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_german2_nopad_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_german2_nopad_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_hungarian_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_hungarian_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_hungarian_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_hungarian_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_hungarian_nopad_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_hungarian_nopad_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_hungarian_nopad_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_hungarian_nopad_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_icelandic_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_icelandic_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_icelandic_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_icelandic_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_icelandic_nopad_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_icelandic_nopad_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_icelandic_nopad_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_icelandic_nopad_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_latvian_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_latvian_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_latvian_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_latvian_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_latvian_nopad_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_latvian_nopad_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_latvian_nopad_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_latvian_nopad_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_lithuanian_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_lithuanian_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_lithuanian_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_lithuanian_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_lithuanian_nopad_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_lithuanian_nopad_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_lithuanian_nopad_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_lithuanian_nopad_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_nopad_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_nopad_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_nopad_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_nopad_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_persian_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_persian_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_persian_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_persian_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_persian_nopad_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_persian_nopad_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_persian_nopad_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_persian_nopad_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_polish_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_polish_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_polish_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_polish_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_polish_nopad_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_polish_nopad_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_polish_nopad_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_polish_nopad_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_romanian_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_romanian_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_romanian_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_romanian_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_romanian_nopad_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_romanian_nopad_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_romanian_nopad_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_romanian_nopad_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_roman_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_roman_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_roman_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_roman_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_roman_nopad_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_roman_nopad_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_roman_nopad_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_roman_nopad_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_sinhala_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_sinhala_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_sinhala_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_sinhala_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_sinhala_nopad_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_sinhala_nopad_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_sinhala_nopad_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_sinhala_nopad_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_slovak_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_slovak_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_slovak_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_slovak_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_slovak_nopad_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_slovak_nopad_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_slovak_nopad_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_slovak_nopad_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_slovenian_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_slovenian_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_slovenian_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_slovenian_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_slovenian_nopad_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_slovenian_nopad_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_slovenian_nopad_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_slovenian_nopad_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_spanish2_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_spanish2_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_spanish2_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_spanish2_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_spanish2_nopad_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_spanish2_nopad_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_spanish2_nopad_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_spanish2_nopad_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_spanish_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_spanish_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_spanish_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_spanish_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_spanish_nopad_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_spanish_nopad_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_spanish_nopad_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_spanish_nopad_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_swedish_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_swedish_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_swedish_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_swedish_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_swedish_nopad_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_swedish_nopad_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_swedish_nopad_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_swedish_nopad_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_turkish_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_turkish_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_turkish_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_turkish_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_turkish_nopad_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_turkish_nopad_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_turkish_nopad_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_turkish_nopad_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_vietnamese_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_vietnamese_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_vietnamese_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_vietnamese_as_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_vietnamese_nopad_ai_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_vietnamese_nopad_ai_cs | NULL | NULL | NULL | Yes | 8 | |
| uca1400_vietnamese_nopad_as_ci | NULL | NULL | NULL | Yes | 8 | |
| uca1400_vietnamese_nopad_as_cs | NULL | NULL | NULL | Yes | 8 | |
| ucs2_bin | ucs2 | 90 | | Yes | 1 | UCS-2 Unicode |
| ucs2_croatian_ci | ucs2 | 640 | | Yes | 8 | |
| ucs2_croatian_mysql561_ci | ucs2 | 149 | | Yes | 8 | |
| ucs2_czech_ci | ucs2 | 138 | | Yes | 8 | |
| ucs2_danish_ci | ucs2 | 139 | | Yes | 8 | |
| ucs2_esperanto_ci | ucs2 | 145 | | Yes | 8 | |
| ucs2_estonian_ci | ucs2 | 134 | | Yes | 8 | |
| ucs2_general_ci | ucs2 | 35 | Yes | Yes | 1 | UCS-2 Unicode |
| ucs2_general_mysql500_ci | ucs2 | 159 | | Yes | 1 | |
| ucs2_general_nopad_ci | ucs2 | 1059 | | Yes | 1 | |
| ucs2_german2_ci | ucs2 | 148 | | Yes | 8 | |
| ucs2_hungarian_ci | ucs2 | 146 | | Yes | 8 | |
| ucs2_icelandic_ci | ucs2 | 129 | | Yes | 8 | |
| ucs2_latvian_ci | ucs2 | 130 | | Yes | 8 | |
| ucs2_lithuanian_ci | ucs2 | 140 | | Yes | 8 | |
| ucs2_myanmar_ci | ucs2 | 641 | | Yes | 8 | |
| ucs2_nopad_bin | ucs2 | 1114 | | Yes | 1 | |
| ucs2_persian_ci | ucs2 | 144 | | Yes | 8 | |
| ucs2_polish_ci | ucs2 | 133 | | Yes | 8 | |
| ucs2_romanian_ci | ucs2 | 131 | | Yes | 8 | |
| ucs2_roman_ci | ucs2 | 143 | | Yes | 8 | |
| ucs2_sinhala_ci | ucs2 | 147 | | Yes | 8 | |
| ucs2_slovak_ci | ucs2 | 141 | | Yes | 8 | |
| ucs2_slovenian_ci | ucs2 | 132 | | Yes | 8 | |
| ucs2_spanish2_ci | ucs2 | 142 | | Yes | 8 | |
| ucs2_spanish_ci | ucs2 | 135 | | Yes | 8 | |
| ucs2_swedish_ci | ucs2 | 136 | | Yes | 8 | |
| ucs2_thai_520_w2 | ucs2 | 642 | | Yes | 4 | |
| ucs2_turkish_ci | ucs2 | 137 | | Yes | 8 | |
| ucs2_unicode_520_ci | ucs2 | 150 | | Yes | 8 | |
| ucs2_unicode_520_nopad_ci | ucs2 | 1174 | | Yes | 8 | |
| ucs2_unicode_ci | ucs2 | 128 | | Yes | 8 | |
| ucs2_unicode_nopad_ci | ucs2 | 1152 | | Yes | 8 | |
| ucs2_vietnamese_ci | ucs2 | 151 | | Yes | 8 | |
| ujis_bin | ujis | 91 | | Yes | 1 | EUC-JP Japanese |
| ujis_japanese_ci | ujis | 12 | Yes | Yes | 1 | EUC-JP Japanese |
| ujis_japanese_nopad_ci | ujis | 1036 | | Yes | 1 | |
| ujis_nopad_bin | ujis | 1115 | | Yes | 1 | |
| utf16le_bin | utf16le | 62 | | Yes | 1 | UTF-16LE Unicode |
| utf16le_general_ci | utf16le | 56 | Yes | Yes | 1 | UTF-16LE Unicode |
| utf16le_general_nopad_ci | utf16le | 1080 | | Yes | 1 | UTF-16LE Unicode |
| utf16le_nopad_bin | utf16le | 1086 | | Yes | 1 | UTF-16LE Unicode |
| utf16_bin | utf16 | 55 | | Yes | 1 | UTF-16 Unicode |
| utf16_croatian_ci | utf16 | 672 | | Yes | 8 | |
| utf16_croatian_mysql561_ci | utf16 | 122 | | Yes | 8 | |
| utf16_czech_ci | utf16 | 111 | | Yes | 8 | |
| utf16_danish_ci | utf16 | 112 | | Yes | 8 | |
| utf16_esperanto_ci | utf16 | 118 | | Yes | 8 | |
| utf16_estonian_ci | utf16 | 107 | | Yes | 8 | |
| utf16_general_ci | utf16 | 54 | Yes | Yes | 1 | UTF-16 Unicode |
| utf16_general_nopad_ci | utf16 | 1078 | | Yes | 1 | UTF-16 Unicode |
| utf16_german2_ci | utf16 | 121 | | Yes | 8 | |
| utf16_hungarian_ci | utf16 | 119 | | Yes | 8 | |
| utf16_icelandic_ci | utf16 | 102 | | Yes | 8 | |
| utf16_latvian_ci | utf16 | 103 | | Yes | 8 | |
| utf16_lithuanian_ci | utf16 | 113 | | Yes | 8 | |
| utf16_myanmar_ci | utf16 | 673 | | Yes | 8 | |
| utf16_nopad_bin | utf16 | 1079 | | Yes | 1 | UTF-16 Unicode |
| utf16_persian_ci | utf16 | 117 | | Yes | 8 | |
| utf16_polish_ci | utf16 | 106 | | Yes | 8 | |
| utf16_romanian_ci | utf16 | 104 | | Yes | 8 | |
| utf16_roman_ci | utf16 | 116 | | Yes | 8 | |
| utf16_sinhala_ci | utf16 | 120 | | Yes | 8 | |
| utf16_slovak_ci | utf16 | 114 | | Yes | 8 | |
| utf16_slovenian_ci | utf16 | 105 | | Yes | 8 | |
| utf16_spanish2_ci | utf16 | 115 | | Yes | 8 | |
| utf16_spanish_ci | utf16 | 108 | | Yes | 8 | |
| utf16_swedish_ci | utf16 | 109 | | Yes | 8 | |
| utf16_thai_520_w2 | utf16 | 674 | | Yes | 4 | |
| utf16_turkish_ci | utf16 | 110 | | Yes | 8 | |
| utf16_unicode_520_ci | utf16 | 123 | | Yes | 8 | |
| utf16_unicode_520_nopad_ci | utf16 | 1147 | | Yes | 8 | |
| utf16_unicode_ci | utf16 | 101 | | Yes | 8 | |
| utf16_unicode_nopad_ci | utf16 | 1125 | | Yes | 8 | |
| utf16_vietnamese_ci | utf16 | 124 | | Yes | 8 | |
| utf32_bin | utf32 | 61 | | Yes | 1 | UTF-32 Unicode |
| utf32_croatian_ci | utf32 | 736 | | Yes | 8 | |
| utf32_croatian_mysql561_ci | utf32 | 181 | | Yes | 8 | |
| utf32_czech_ci | utf32 | 170 | | Yes | 8 | |
| utf32_danish_ci | utf32 | 171 | | Yes | 8 | |
| utf32_esperanto_ci | utf32 | 177 | | Yes | 8 | |
| utf32_estonian_ci | utf32 | 166 | | Yes | 8 | |
| utf32_general_ci | utf32 | 60 | Yes | Yes | 1 | UTF-32 Unicode |
| utf32_general_nopad_ci | utf32 | 1084 | | Yes | 1 | UTF-32 Unicode |
| utf32_german2_ci | utf32 | 180 | | Yes | 8 | |
| utf32_hungarian_ci | utf32 | 178 | | Yes | 8 | |
| utf32_icelandic_ci | utf32 | 161 | | Yes | 8 | |
| utf32_latvian_ci | utf32 | 162 | | Yes | 8 | |
| utf32_lithuanian_ci | utf32 | 172 | | Yes | 8 | |
| utf32_myanmar_ci | utf32 | 737 | | Yes | 8 | |
| utf32_nopad_bin | utf32 | 1085 | | Yes | 1 | UTF-32 Unicode |
| utf32_persian_ci | utf32 | 176 | | Yes | 8 | |
| utf32_polish_ci | utf32 | 165 | | Yes | 8 | |
| utf32_romanian_ci | utf32 | 163 | | Yes | 8 | |
| utf32_roman_ci | utf32 | 175 | | Yes | 8 | |
| utf32_sinhala_ci | utf32 | 179 | | Yes | 8 | |
| utf32_slovak_ci | utf32 | 173 | | Yes | 8 | |
| utf32_slovenian_ci | utf32 | 164 | | Yes | 8 | |
| utf32_spanish2_ci | utf32 | 174 | | Yes | 8 | |
| utf32_spanish_ci | utf32 | 167 | | Yes | 8 | |
| utf32_swedish_ci | utf32 | 168 | | Yes | 8 | |
| utf32_thai_520_w2 | utf32 | 738 | | Yes | 4 | |
| utf32_turkish_ci | utf32 | 169 | | Yes | 8 | |
| utf32_unicode_520_ci | utf32 | 182 | | Yes | 8 | |
| utf32_unicode_520_nopad_ci | utf32 | 1206 | | Yes | 8 | |
| utf32_unicode_ci | utf32 | 160 | | Yes | 8 | |
| utf32_unicode_nopad_ci | utf32 | 1184 | | Yes | 8 | |
| utf32_vietnamese_ci | utf32 | 183 | | Yes | 8 | |
| utf8mb3_bin | utf8mb3 | 83 | | Yes | 1 | UTF-8 Unicode |
| utf8mb3_croatian_ci | utf8mb3 | 576 | | Yes | 8 | |
| utf8mb3_croatian_mysql561_ci | utf8mb3 | 213 | | Yes | 8 | |
| utf8mb3_czech_ci | utf8mb3 | 202 | | Yes | 8 | |
| utf8mb3_danish_ci | utf8mb3 | 203 | | Yes | 8 | |
| utf8mb3_esperanto_ci | utf8mb3 | 209 | | Yes | 8 | |
| utf8mb3_estonian_ci | utf8mb3 | 198 | | Yes | 8 | |
| utf8mb3_general_ci | utf8mb3 | 33 | Yes | Yes | 1 | UTF-8 Unicode |
| utf8mb3_general_mysql500_ci | utf8mb3 | 223 | | Yes | 1 | |
| utf8mb3_general_nopad_ci | utf8mb3 | 1057 | | Yes | 1 | |
| utf8mb3_german2_ci | utf8mb3 | 212 | | Yes | 8 | |
| utf8mb3_hungarian_ci | utf8mb3 | 210 | | Yes | 8 | |
| utf8mb3_icelandic_ci | utf8mb3 | 193 | | Yes | 8 | |
| utf8mb3_latvian_ci | utf8mb3 | 194 | | Yes | 8 | |
| utf8mb3_lithuanian_ci | utf8mb3 | 204 | | Yes | 8 | |
| utf8mb3_myanmar_ci | utf8mb3 | 577 | | Yes | 8 | |
| utf8mb3_nopad_bin | utf8mb3 | 1107 | | Yes | 1 | |
| utf8mb3_persian_ci | utf8mb3 | 208 | | Yes | 8 | |
| utf8mb3_polish_ci | utf8mb3 | 197 | | Yes | 8 | |
| utf8mb3_romanian_ci | utf8mb3 | 195 | | Yes | 8 | |
| utf8mb3_roman_ci | utf8mb3 | 207 | | Yes | 8 | |
| utf8mb3_sinhala_ci | utf8mb3 | 211 | | Yes | 8 | |
| utf8mb3_slovak_ci | utf8mb3 | 205 | | Yes | 8 | |
| utf8mb3_slovenian_ci | utf8mb3 | 196 | | Yes | 8 | |
| utf8mb3_spanish2_ci | utf8mb3 | 206 | | Yes | 8 | |
| utf8mb3_spanish_ci | utf8mb3 | 199 | | Yes | 8 | |
| utf8mb3_swedish_ci | utf8mb3 | 200 | | Yes | 8 | |
| utf8mb3_thai_520_w2 | utf8mb3 | 578 | | Yes | 4 | |
| utf8mb3_turkish_ci | utf8mb3 | 201 | | Yes | 8 | |
| utf8mb3_unicode_520_ci | utf8mb3 | 214 | | Yes | 8 | |
| utf8mb3_unicode_520_nopad_ci | utf8mb3 | 1238 | | Yes | 8 | |
| utf8mb3_unicode_ci | utf8mb3 | 192 | | Yes | 8 | |
| utf8mb3_unicode_nopad_ci | utf8mb3 | 1216 | | Yes | 8 | |
| utf8mb3_vietnamese_ci | utf8mb3 | 215 | | Yes | 8 | |
| utf8mb4_0900_ai_ci | utf8mb4 | 255 | | Yes | 8 | Alias for utf8mb4_uca1400_nopad_ai_ci |
| utf8mb4_0900_as_ci | utf8mb4 | 305 | | Yes | 8 | Alias for utf8mb4_uca1400_nopad_as_ci |
| utf8mb4_0900_as_cs | utf8mb4 | 278 | | Yes | 8 | Alias for utf8mb4_uca1400_nopad_as_cs |
| utf8mb4_0900_bin | utf8mb4 | 309 | | Yes | 1 | Alias for utf8mb4_bin |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | UTF-8 Unicode |
| utf8mb4_croatian_ci | utf8mb4 | 608 | | Yes | 8 | |
| utf8mb4_croatian_mysql561_ci | utf8mb4 | 245 | | Yes | 8 | |
| utf8mb4_cs_0900_ai_ci | utf8mb4 | 266 | | Yes | 8 | Alias for utf8mb4_uca1400_czech_nopad_ai_ci |
| utf8mb4_cs_0900_as_cs | utf8mb4 | 289 | | Yes | 8 | Alias for utf8mb4_uca1400_czech_nopad_as_cs |
| utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 | |
| utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 | |
| utf8mb4_da_0900_ai_ci | utf8mb4 | 267 | | Yes | 8 | Alias for utf8mb4_uca1400_danish_nopad_ai_ci |
| utf8mb4_da_0900_as_cs | utf8mb4 | 290 | | Yes | 8 | Alias for utf8mb4_uca1400_danish_nopad_as_cs |
| utf8mb4_de_pb_0900_ai_ci | utf8mb4 | 256 | | Yes | 8 | Alias for utf8mb4_uca1400_german2_nopad_ai_ci |
| utf8mb4_de_pb_0900_as_cs | utf8mb4 | 279 | | Yes | 8 | Alias for utf8mb4_uca1400_german2_nopad_as_cs |
| utf8mb4_eo_0900_ai_ci | utf8mb4 | 273 | | Yes | 8 | Alias for utf8mb4_uca1400_esperanto_nopad_ai_ci |
| utf8mb4_eo_0900_as_cs | utf8mb4 | 296 | | Yes | 8 | Alias for utf8mb4_uca1400_esperanto_nopad_as_cs |
| utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 | |
| utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 | |
| utf8mb4_es_0900_ai_ci | utf8mb4 | 263 | | Yes | 8 | Alias for utf8mb4_uca1400_spanish_nopad_ai_ci |
| utf8mb4_es_0900_as_cs | utf8mb4 | 286 | | Yes | 8 | Alias for utf8mb4_uca1400_spanish_nopad_as_cs |
| utf8mb4_es_trad_0900_ai_ci | utf8mb4 | 270 | | Yes | 8 | Alias for utf8mb4_uca1400_spanish2_nopad_ai_ci |
| utf8mb4_es_trad_0900_as_cs | utf8mb4 | 293 | | Yes | 8 | Alias for utf8mb4_uca1400_spanish2_nopad_as_cs |
| utf8mb4_et_0900_ai_ci | utf8mb4 | 262 | | Yes | 8 | Alias for utf8mb4_uca1400_estonian_nopad_ai_ci |
| utf8mb4_et_0900_as_cs | utf8mb4 | 285 | | Yes | 8 | Alias for utf8mb4_uca1400_estonian_nopad_as_cs |
| utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 | UTF-8 Unicode |
| utf8mb4_general_nopad_ci | utf8mb4 | 1069 | | Yes | 1 | UTF-8 Unicode |
| utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 | |
| utf8mb4_hr_0900_ai_ci | utf8mb4 | 275 | | Yes | 8 | Alias for utf8mb4_uca1400_croatian_nopad_ai_ci |
| utf8mb4_hr_0900_as_cs | utf8mb4 | 298 | | Yes | 8 | Alias for utf8mb4_uca1400_croatian_nopad_as_cs |
| utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 | |
| utf8mb4_hu_0900_ai_ci | utf8mb4 | 274 | | Yes | 8 | Alias for utf8mb4_uca1400_hungarian_nopad_ai_ci |
| utf8mb4_hu_0900_as_cs | utf8mb4 | 297 | | Yes | 8 | Alias for utf8mb4_uca1400_hungarian_nopad_as_cs |
| utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 | |
| utf8mb4_is_0900_ai_ci | utf8mb4 | 257 | | Yes | 8 | Alias for utf8mb4_uca1400_icelandic_nopad_ai_ci |
| utf8mb4_is_0900_as_cs | utf8mb4 | 280 | | Yes | 8 | Alias for utf8mb4_uca1400_icelandic_nopad_as_cs |
| utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 | |
| utf8mb4_la_0900_ai_ci | utf8mb4 | 271 | | Yes | 8 | Alias for utf8mb4_uca1400_roman_nopad_ai_ci |
| utf8mb4_la_0900_as_cs | utf8mb4 | 294 | | Yes | 8 | Alias for utf8mb4_uca1400_roman_nopad_as_cs |
| utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 | |
| utf8mb4_lt_0900_ai_ci | utf8mb4 | 268 | | Yes | 8 | Alias for utf8mb4_uca1400_lithuanian_nopad_ai_ci |
| utf8mb4_lt_0900_as_cs | utf8mb4 | 291 | | Yes | 8 | Alias for utf8mb4_uca1400_lithuanian_nopad_as_cs |
| utf8mb4_lv_0900_ai_ci | utf8mb4 | 258 | | Yes | 8 | Alias for utf8mb4_uca1400_latvian_nopad_ai_ci |
| utf8mb4_lv_0900_as_cs | utf8mb4 | 281 | | Yes | 8 | Alias for utf8mb4_uca1400_latvian_nopad_as_cs |
| utf8mb4_myanmar_ci | utf8mb4 | 609 | | Yes | 8 | |
| utf8mb4_nopad_bin | utf8mb4 | 1070 | | Yes | 1 | UTF-8 Unicode |
| utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 | |
| utf8mb4_pl_0900_ai_ci | utf8mb4 | 261 | | Yes | 8 | Alias for utf8mb4_uca1400_polish_nopad_ai_ci |
| utf8mb4_pl_0900_as_cs | utf8mb4 | 284 | | Yes | 8 | Alias for utf8mb4_uca1400_polish_nopad_as_cs |
| utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 | |
| utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 | |
| utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 | |
| utf8mb4_ro_0900_ai_ci | utf8mb4 | 259 | | Yes | 8 | Alias for utf8mb4_uca1400_romanian_nopad_ai_ci |
| utf8mb4_ro_0900_as_cs | utf8mb4 | 282 | | Yes | 8 | Alias for utf8mb4_uca1400_romanian_nopad_as_cs |
| utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 | |
| utf8mb4_sk_0900_ai_ci | utf8mb4 | 269 | | Yes | 8 | Alias for utf8mb4_uca1400_slovak_nopad_ai_ci |
| utf8mb4_sk_0900_as_cs | utf8mb4 | 292 | | Yes | 8 | Alias for utf8mb4_uca1400_slovak_nopad_as_cs |
| utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 | |
| utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 | |
| utf8mb4_sl_0900_ai_ci | utf8mb4 | 260 | | Yes | 8 | Alias for utf8mb4_uca1400_slovenian_nopad_ai_ci |
| utf8mb4_sl_0900_as_cs | utf8mb4 | 283 | | Yes | 8 | Alias for utf8mb4_uca1400_slovenian_nopad_as_cs |
| utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 | |
| utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 | |
| utf8mb4_sv_0900_ai_ci | utf8mb4 | 264 | | Yes | 8 | Alias for utf8mb4_uca1400_swedish_nopad_ai_ci |
| utf8mb4_sv_0900_as_cs | utf8mb4 | 287 | | Yes | 8 | Alias for utf8mb4_uca1400_swedish_nopad_as_cs |
| utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 | |
| utf8mb4_thai_520_w2 | utf8mb4 | 610 | | Yes | 4 | |
| utf8mb4_tr_0900_ai_ci | utf8mb4 | 265 | | Yes | 8 | Alias for utf8mb4_uca1400_turkish_nopad_ai_ci |
| utf8mb4_tr_0900_as_cs | utf8mb4 | 288 | | Yes | 8 | Alias for utf8mb4_uca1400_turkish_nopad_as_cs |
| utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 | |
| utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 8 | |
| utf8mb4_unicode_520_nopad_ci | utf8mb4 | 1270 | | Yes | 8 | |
| utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 | |
| utf8mb4_unicode_nopad_ci | utf8mb4 | 1248 | | Yes | 8 | |
| utf8mb4_vietnamese_ci | utf8mb4 | 247 | | Yes | 8 | |
| utf8mb4_vi_0900_ai_ci | utf8mb4 | 277 | | Yes | 8 | Alias for utf8mb4_uca1400_vietnamese_nopad_ai_ci |
| utf8mb4_vi_0900_as_cs | utf8mb4 | 300 | | Yes | 8 | Alias for utf8mb4_uca1400_vietnamese_nopad_as_cs |
+--------------------------------+----------+------+------+-----+---------+--------------------------------------------------+
550 rows in set (0.005 sec)SELECT collation_name FROM information_schema.COLLATIONS
WHERE collation_name LIKE "%nopad%";
+------------------------------+
| collation_name |
+------------------------------+
| big5_chinese_nopad_ci |
| big5_nopad_bin |
......
| uca1400_spanish2_ai_ci |
| uca1400_spanish2_ai_cs |
| uca1400_spanish2_as_ci |
| uca1400_spanish2_as_cs |
...