Implementation of Automatic increment of MySQL, MSSQL, and Oracle

Oracle does not set an auto-incrementing primary key. If you want to implement the auto_increment function of databases such as MySQL, you need to create a sequence.

 

1. Define the primary key as the automatic growth identifier type

In MySQL, if the table's primary key is set to auto_increment type, the database automatically assigns a value to the primary key. For example:

Create Table MERs (ID int auto_increment primary key not null, name varchar (15 ));

Insert into MERs (name) values ("name1"), ("name2 ");

Select ID from MERs MERS;

The preceding SQL statement first creates the MERs table, then inserts two records, and only sets the value of the Name field during the insertion. Finally, query the ID field in the table. The query result is:

ID

1

2

It can be seen that once the ID is set to auto_increment type, MySQL database automatically assigns a value to the primary key in ascending mode.

In Ms sqlserver, if the table's primary key is set to the identity type, the database automatically assigns a value to the primary key. For example:

Create Table MERs (ID int identity (1, 1) primary key not null, name varchar (15 ));

Insert into MERs (name) values ("name1"), ("name2 ");

Select ID from MERs MERS;

The query result is the same as that of MySQL. It can be seen that once the ID is set to the identity type, the MS sqlserver database automatically assigns a value to the primary key in ascending mode. Identity contains two parameters. The first parameter indicates the start value and the second parameter indicates the increment.

2. Get the auto-increment Identifier from the sequence

In Oracle, you can create a separate sequence for the primary key of each table, obtain the automatically added Identifier from the sequence, and assign it to the primary key. For example, the following statement creates a sequence named mermer_id_seq. The starting value of this sequence is 1 and the increment is 2.

Create sequence customer_id_seq increment by 2 start with 1

Once the customer_id_seq sequence is defined, the curval and nextval attributes of the sequence can be accessed.

Curval: returns the current value of the sequence.

Nextval: first increase the sequence value and then return the sequence value.

The following SQL statement first creates the MERs table, then inserts two records, and sets the values of the ID and name fields during insertion. The value of the ID field comes from the customer_id_seq sequence. Finally, query the ID field in the MERs table.

Create Table MERs (ID int primary key not null, name varchar (15 ));

Insert into MERs values (customer_id_seq.curval, "name1"), (customer_id_seq.nextval, "name2 ");

Select ID from MERs MERS;

If the preceding statement is executed in Oracle, the query result is:

ID

1