NO_AUTO_VALUE_ON_ZERO
This page is part of MariaDB's Documentation.
The parent of this page is: SQL Modes for MariaDB Xpand
Topics on this page:
Overview
This mode allows the use of 0 as a valid ID value in an AUTO_INCREMENT column.
When this mode is not set, inserting a value of 0 into an AUTO_INCREMENT column causes the next auto-increment value to be inserted, similar to inserting a NULL value. When this mode is set, only NULL has this special behavior because 0 is considered to be a valid value that is inserted unchanged.
DETAILS
The NO_AUTO_VALUE_ON_ZERO mode disables the default behavior that occurs when the value 0 is inserted into an AUTO_INCREMENT column.
By default, the value 0 is handled similar to NULL, so a new auto-increment value is generated and inserted into the column, and the value 0 is discarded. When this mode is enabled, no auto-increment value is generated, and the value 0 is inserted into the column.
AUTO_UNIQUE is a better performing alternative to AUTO_INCREMENT in environments with high levels of concurrency and parallelism.
For additional information, see "AUTO_INCREMENT Constraints with MariaDB Xpand".
EXAMPLES
Setting the SQL_MODE
Note that @@sql_mode is the same as @@session.sql_mode:
SELECT @@sql_mode, @@session.sql_mode;
+---------------------+---------------------+
| @@sql_mode | @@session.sql_mode |
+---------------------+---------------------+
| STRICT_TRANS_TABLES | STRICT_TRANS_TABLES |
+---------------------+---------------------+
The following adds the NO_AUTO_VALUE_ON_ZERO mode to the existing SQL_MODE session value:
SET sql_mode = CONCAT(@@sql_mode, ',NO_AUTO_VALUE_ON_ZERO');
SELECT @@sql_mode;
+-------------------------------------------+
| @@sql_mode |
+-------------------------------------------+
| NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES |
+-------------------------------------------+
The mode can also be set on its own:
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
Assigning an empty string turns off all SQL_MODE flags:
SET sql_mode = '';