Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is it possible to get InnoDB internal row ID?

I have a table without a primary key. I need to implement some kind of online schema change and I don't want to change insertion order. In MySQL documentation, I found this:

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

Is it possible to get this value from the table which seems to help to solve my problem?

like image 419
chingeez Avatar asked Feb 04 '14 15:02

chingeez


1 Answers

No, there's no way to get this, because there also is no reason to. In a database rows are not sorted as long as you don't specify an ORDER BY clause in your SELECT (or UPDATE or DELETE). You should not rely on "insertion order".

Just add a column serving as primary key...

like image 122
fancyPants Avatar answered Oct 28 '22 22:10

fancyPants