Mysql 4 vs Mysql 5 auto-increment field when pasting

Question

I found out about this along the way, but I can’t understand where I read or heard it, because I did not find anything on the Internet, but I remember that when upgrading from mysql4.x to mysql5.x, one of the necessary changes was that the auto-increment field for inserts should have changed from '' to NULL if it was enabled.

I know that it is not necessary to have it in the box, but only for the point of interest ...

Mysql 4.x will allow: INSERT INTO TABLE (table_id, name, location) VALUES ('', 'john', 'NY'); A.

But mysql 5.x should have: INSERT INTO TABLE (table_id, name, location) VALUES ( NULL , 'john', 'NY'); A.

I cannot find any information on mysql website to support this, but I know that it causes an error in mysql 5.x and knows that it worked with '' in 4.x, but where is this documented?

Answer 1

Both 4.1 and 5.0 docs state that 0 or NULL is required:

No value is specified for the AUTO_INCREMENT column, so MySQL assigned serial numbers automatically. You can also explicitly assign NULL or 0 to the column to generate serial numbers.

Answer 2

It doesn't matter, mysql internally still converts to an integer

mysql> CREATE TABLE some_test (id int (10) unsigned NOT NULL auto_increment, primary key (id));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into some_test values ('');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'id' at row 1 |
+---------+------+------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from some_test;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

0,