Sequence number differences between Oracle and MySQL

Nigel McFarlane points out some essential differences between Oracle and MySQL sequence numbers.

If you're working with relational data, the day will soon come when you need to generate unique key values for your data, otherwise known as sequence numbers. In this tip I'll contrast the Oracle way with the MySQL way.

Since sequence numbers aren't part of the entry-level conformance of the SQL92 standard, their use creates portability problems for your SQL scripts straight away.

Way back when, there were no sequence number features in any relational database. In those days, if you wanted to generate unique numbers (like invoice numbers), you had to store them separately in a whole table, and increment them yourself:

CREATE TABLE my_sequence (id INT NOT NULL);

INSERT INTO my_sequence VALUES (0);

UPDATE my_sequence SET id = id + 1;

INSERT

INTO my_table

SELECT id, "some data"

FROM my_sequence;

That approach was rather clunky to say the least, but at least it is standard SQL.

In modern Oracle databases, all you need to do is create a sequence object, and then select new numbers from it:

CREATE SEQUENCE my_sequence;

INSERT

INTO my_table

SELECT my_sequence.nextval, "some data"

FROM dual;

The special table DUAL contains exactly one row and one column and is used mostly to report back a single piece of data - in this case derived from a sequence object. In all my life I've never been able to understand why a dummy table with one row and one column is called DUAL. Doesn't DUAL mean two-part or two-use? What's dual about a single row? Oh well.

By comparison, MySQL has no DUAL table. Instead, it allows you to put an extra detail on any numeric column of any table, provided it's an indexed key column. Not only can the column be NULLable, it can also be AUTO_INCREMENTable. Here's an example:

CREATE TABLE my_table (

id INT NOT NULL AUTO_INCREMENT,

data CHAR(30),

PRIMARY_KEY(id)

);

INSERT INTO my_table (null, "some data");

The INSERT statement looks highly suspect. After all, doesn't a primary key normally require a concrete value? True, and in this case MySQL calculates that for you. By passing in NULL to the column with the AUTO_INCREMENT property, you're telling MySQL to use the next free number not yet used for that column. If this INSERT statement were to be applied three times, each time the ID value would be one larger.

For the first three rows inserted, that number would merely be 1, then 2, then 3.

The fact that the sequence number is tied to a specific table in MySQL is a little restrictive, but there are various techniques that can be used to work around it. For example, MySQL provides a LAST_INSERT_ID() that can be used to dig the generated number out for other purposes.


This was first published in January 2005

Dig deeper on Unix-to-Linux migration

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataCenter

SearchServerVirtualization

SearchCloudComputing

SearchEnterpriseDesktop

Close