Geek Answers Handbook

What is the difference between Oracle "sequence" and MySql Auto_increment?

I do not quite understand the difference between the Oracle sequence and MySql auto_increment.

I am a mysql guy but don't know anything about oracle. I hope someone can quickly talk about this for me.

Thanks in advance Dan Bogman

+7
oracle mysql
source share
3 answers

A sequence is a separate database object in Oracle.

In MySQL, when you have an auto-increment column and INSERT is a new row in the table, you just don't mention the auto-increment column, and MySQL puts it there. You can then insert the same number into another table by specifying LAST_INSERT_ID() .

  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 the id column by specifying a property of the .nextval object. You can get the value you just used for this sequence by specifying its .currval property.

  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 specify a .nextval property of a sequence, it is guaranteed to give a new number.

Serial objects are pretty cool when you need unique numbers that are not directly related to the primary key of any table. You can do this in MySQL, but this is kludge: if you create the following table:

  CREATE TABLE sequence ( /*MySQL*/ sequence_id BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY (`sequence_id`) )

Then complete these three queries one by one:

 INSERT INTO sequence () VALUES (); /*MySQL*/ DELETE FROM sequence WHERE sequence_id < LAST_INSERT_ID(); SELECT LAST_INSERT_ID() AS sequence;

The third request is guaranteed to return a unique serial number. This guarantee is maintained even if you have dozens of different client programs connected to your database. (The DELETE query simply keeps this pointless table from taking up too much space.)

In Oracle, you create a sequence

 create sequence seq --Oracle

and then just

 SELECT seq.nextval FROM DUAL --Oracle

to get a new serial number and it. This also guarantees uniqueness even with dozens of connected client programs.

Similarly, if you need the value of the sequence that you just generated using .nextval , you can send this command and get it.

 SELECT seq.currval FROM DUAL --Oracle

As with MySQL LAST_INSERT_ID() , this session is processed by the session, so another client using the sequence will not force you to get your number instead of yours.

Bottom line: both DBMSs can generate unique integers. The integrity of both circuits is designed to be stored on servers that stops and restarts. Oracle sequence is more flexible.

+9
source share

A sequence in Oracle DB is a separate object that you can access in your queries to get / increase its value, and you can use the same sequence with more than one table / field.

Auto-increment in MySQL is bound to one field in a table and is used to determine the value of this field when it is not specified in the insert request.

+3
source share

There are no auto-increment columns in Oracle. You need to create a sequence before the insert trigger, which reads NEXTVAL from the sequence and sets the value for the column

0
source share

More articles:


All Articles