Interesting. I am using an old version, 5.5.34 The database engine may also make a difference. I was using MyISAM. INNODB may be different.
A quick scan of the MySQL documentation suggests INNODB is different. For example, it doesn't store the next value in the database. If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk. To initialize an auto-increment counter after a server restart, InnoDB executes the equivalent of the following statement on the first insert into a table containing an AUTO_INCREMENT column. SELECT MAX(ai_col) FROM table_name FOR UPDATE; Simon's requested table is likely to get quite complicated :^) I suspect all you can really say about auto increment is that it will create a unique number. Andy -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Tony Papadimitriou Sent: Tue 01 November 2016 13:09 To: SQLite mailing list Subject: Re: [sqlite] Autoincrement sequence not updated by UPDATE Well, I got different results (so maybe it's version related). I tried it on MySQL v5.7.16-log: +----+------+ | id | v | +----+------+ | 2 | two | | 10 | one | +----+------+ +----+------+ | id | v | +----+------+ | 10 | one | | 40 | two | +----+------+ +----+-------+ | id | v | +----+-------+ | 3 | three | | 10 | one | | 30 | two | +----+-------+ -----Original Message----- From: Simon Slavin Sent: Tuesday, November 01, 2016 1:50 PM To: SQLite mailing list Subject: Re: [sqlite] Autoincrement sequence not updated by UPDATE On 1 Nov 2016, at 11:44am, Andy Ling <andy.l...@s-a-m.com> wrote: > It remembers...... Ah, neat. Thanks for the testing. And the "show create table" command you used makes it clear that the engine keeps a record for the table. Apparently a single value for the table's primary key rather than a value for each "INTEGER PRIMARY KEY AUTOINCREMENT" column. I'd be interested in the equivalents for progresql and Oracle, if anyone is set up to find out. Maybe we could work up a table like the one in <https://www.sqlite.org/nulls.html> Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users --------------------------------------------------------------------------------------- This email has been scanned for email related threats and delivered safely by Mimecast. For more information please visit http://www.mimecast.com --------------------------------------------------------------------------------------- _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
