What does the MySQL mode NO_AUTO_VALUE_ON_ZERO ?

 SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO'

What settings will change in the database after installing this mode?

PS read the documentation, but in English, and understood very vaguely. It would be great if there are links to Russian documentation ...

    1 answer 1

     create table test_ai(id int auto_increment primary key, val int) engine=innodb; insert into test_ai (id, val) values (0, 1);

    This is what the result will be in the id field and depends on this sql_mode :

    Without NO_AUTO_VALUE_ON_ZERO next auto_increment value will be generated. With the NO_AUTO_VALUE_ON_ZERO flag NO_AUTO_VALUE_ON_ZERO , exactly 0 will be written.

    mysql> SET SQL_MODE = '';
    Query OK, 0 rows affected (0,00 sec)

    mysql> insert into test_ai (id, val) values ​​(0, 1);
    Query OK, 1 row affected (0,01 sec)

    mysql> SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';
    Query OK, 0 rows affected (0,00 sec)

    mysql> insert into test_ai (id, val) values ​​(0, 2);
    Query OK, 1 row affected (0,01 sec)

    mysql> select * from test_ai; + ---- + ------ +
    | id | val |
    + ---- + ------ +
    | 0 | 2 |
    | 1 | 1 |
    + ---- + ------ +
    2 rows in set (0,00 sec)

    mysql> insert into test_ai (id, val) values ​​(0, 3);
    ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'

    This type of query is not affected by the settings:

     insert into test_ai (id, val) values (NULL, 1); insert into test_ai (val) values (1);

    The general meaning is: you don't need to try to assign explicit values ​​to the auto_increment field.