Re: IDENTITY_INSERT in MySql

[Dmitry Tolpeko]

Hi Rod,

MySQL allows you inserting identity values explicitly, so you actually do not need IDENTITY_INSERT option, or in other words it is always set in MySQL.

So if INSERT statements contain values for identity (autoincrement), they all will be retained

  CREATE TABLE t_autoinc1
   (
     c1 INT AUTO_INCREMENT PRIMARY KEY
   );

   -- 0 or NULL means that next ID will be generated by MySQL automatically
   
   -- inserts 1
   INSERT INTO t_autoinc1 VALUES (0)
   -- inserts 2
   INSERT INTO t_autoinc1 VALUES (NULL)

   -- inserts 100, not 3
   INSERT INTO t_autoinc1 VALUES (100)

   -- inserts 101
   INSERT INTO t_autoinc1 VALUES (NULL)

More on conversion: http://www.sqlines.com/reference/mysql/statements/tables/auto_increment

Thanks,

Dmitry