It’s common in database work to need unique integers. They get used in id columns in tables, and for other purposes. This note compares and contrasts the Oracle and MySQL approaches to doing this.
MySQL handles this need with autoincrement columns, in tables and Oracle handles it with database objects called sequences.
In MySQL, when you have an autoincrement column and you INSERT a new row in a table, you simply don’t mention the autoincrement column and MySQL puts it there. You can then insert the same number into another table by referencing
INSERT INTO person (name, date) /*MySQL*/ VALUES ('joe', '2015-01-01'); INSERT INTO contact (person_id, phone) VALUES (LAST_INSERT_ID(), '555-1212');
In Oracle, you can populate an id column by mentioning a sequence object’s
.nextval property. You can get the just-used value of that sequence by referencing its
INSERT INTO person (id, name, date) -- Oracle VALUES (person_seq.nextval, 'joe', '2015-01-01'); INSERT INTO contact (id, person_id, phone) VALUES (contact_seq.nextval, person_seq.currval, '555-1212');
Each time you mention the sequence’s
.nextval property, it’s guaranteed to give a new number.
Sequence objects are pretty cool when you need unique numbers that aren’t directly related to the primary key of some table or other. You can do this in MySQL, but it’s a kludge: If you create the following table:
CREATE TABLE sequence ( /*MySQL*/ sequence_id BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY (`sequence_id`) )
Then issue these three queries one after the other:
INSERT INTO sequence () VALUES (); /*MySQL*/ DELETE FROM sequence WHERE sequence_id < LAST_INSERT_ID(); SELECT LAST_INSERT_ID() AS sequence;
The third query is guaranteed to return a unique sequence number. This guarantee holds even if you have dozens of different client programs connected to your database. (The
DELETE query merely keeps this otherwise pointless table from taking up too much space.)
With Oracle, you create the sequence
create sequence seq --Oracle
and then just do
SELECT seq.nextval FROM DUAL --Oracle
to get a new sequence number and that’s it. It too guarantees uniqueness even with dozens of connected client programs.
Similarly, if you need the value of a sequence you just generated with
.nextval, you can issue this command and get it.
SELECT seq.currval FROM DUAL --Oracle
As with MySQL’s
LAST_INSERT_ID(), this is handled session by session so another client using the sequence won’t make you get their number instead of yours.
Bottom line: both DBMSs can generate unique integers. The integrity of both schemes is designed to be preserved across server stops and restarts. The Oracle sequence is more flexible.