How to create sequence in mysql without auto increment

How is Sequences handled in MySQL?

Sequence in MySql is handled using primary keys. Since the primary key will always be unique it can be generated in a sequence. The column, whose value is expected to be auto incremented, can be set as AUTO_INCREMENT.

Explain the purpose of Auto_increment in MySQL. Explain with an example.

Auto_increment is used to generate unique number for every row. A column can be used with Auto_increment.

Create table employee (
Id integer AUTO_INCREMENT,
Name varchar(200),
};

INSERT INTO employee (Name) VALUES (‘tom’,’jack’);

Output:
Id         Name
1          tom
2          Jack

Explain to generate sequences without using Auto_increment.

- To generate sequences without using auto_increment, LAST_INSERT_ID() is used
- If any insert or update is performed using LAST_UPDATE)ID(expr) the next call to LAST_INSERT_ID() with no argument returns the value of expr
- MySQL treats the argument passed (expr) to this function as though it was generated as a AUTO_INCREMENT value.

Example:

UPDATE table_name SET column_name LAST_INSERT_ID(column_name+1);
SELECT LAST_INSERT_ID();

Explain the issues working with Auto_increment in MySQL.

If the maximum value of the table on which auto increment is used is exceeded, auto increment stops incrementing. It does NOT result in loosing existing data but throws an error if further inserts are done.

Explain how to generate sequence in MySql.

Syntax for creating sequence:

CREATE SEQUENCE seqname [ INCREMENT increment ]
[ MINVALUE minvalue ] [ MAXVALUE maxvalue ]
[ START start ] [ CACHE cache ] [ CYCLE ]

Example:

CREATE SEQUENCE employee_id
START 200 INCREMENT 1;

MySQL Indexes

MySQL Indexes - How MySQL Uses Indexes?, State how to create and drop indexes in MySQL.....

MySQL Subquery

MySQL Subquery - A subquery is a query within a query. These sub queries are created with SQL statements.

MySQL Joins

MySQL Joins - A SQL server Join is helps to query data from two or more tables between columns of these tables.

I had to make 10 sequences in our Koha installation which uses MySQL. To my horror MySQL doesn't have CREATE SEQUENCE (insert MySQL vs PostgreSQL rant here). Even worse, MySQL manual suggest usage of LAST_INSERT_ID which would mean that I had to create 10 tables just to track my sequences.

There is better solution (based on StackOverflow question: Emulating a transaction-safe SEQUENCE in MySQL) outlined below:

create table ffzg_zs_seq (
        name varchar(2) unique not null,
        current integer unsigned not null
);

insert into ffzg_zs_seq values ('PA',100000);
insert into ffzg_zs_seq values ('PB',100000);
insert into ffzg_zs_seq values ('PC',100000);
insert into ffzg_zs_seq values ('PD',100000);
insert into ffzg_zs_seq values ('PE',100000);
insert into ffzg_zs_seq values ('DD',100000);
insert into ffzg_zs_seq values ('MR',100000);
insert into ffzg_zs_seq values ('DR',100000);
insert into ffzg_zs_seq values ('FO',100000);
insert into ffzg_zs_seq values ('SE',100000);

delimiter |
         
create function ffzg_zs_nextval( seq_name varchar(2) )
returns integer unsigned
begin        
        update ffzg_zs_seq set current = ( @next_val := current + 1 ) where name = seq_name ;
        return @next_val;
end|
    
delimiter ;

This will create single table to hold all sequences and set all values to 100000 (have in mind that first usable number is 100001). Since I already have data in database I needed following snippet of SQL to set sequences to existing values:

update ffzg_zs_seq
set current=(
        select  
                max(substring_index(itemcallnumber,' ',-1))
        from items
        where substring_index(itemcallnumber,' ',1) = ffzg_zs_seq.name
);

As you might guesses by now, I'm packing sequence name and number into single field

, so I had to use

to extract existing values.

Usage is simple, allmost as good as built-in sequences:

mysql> select ffzg_zs_nextval('DD');
+-----------------------+
| ffzg_zs_nextval('DD') |
+-----------------------+
|                100178 | 
+-----------------------+
1 row in set (0.00 sec)

AUTO_INCREMENT option allows you to automatically generate unique integer numbers (IDs, identity, sequence) for a column.

Quick Example:

   -- Define a table with an auto-increment column (id starts at 100)
   CREATE TABLE airlines
   (
      id INT AUTO_INCREMENT PRIMARY KEY, 
      name VARCHAR(90)
   )
   AUTO_INCREMENT = 100; 
 
   -- Insert a row, ID will be automatically generated
   INSERT INTO airlines (name) VALUES ('United Airlines');
 
   -- Get generated ID
   SELECT LAST_INSERT_ID();
   -- Returns: 100

Overview:

Start Value		Default is 1
Increment		Always 1
How to Generate IDs	Omit the AUTO_INCREMENT column in INSERT, or specify NULL or 0
Explicit ID Insert
Restrictions	Only one AUTO_INCREMENT column per table
Primary key or unique must be specified
DEFAULT is not allowed
Last ID	LAST_INSERT_ID returns the last value inserted in the current session
LAST_INSERT_ID returns ID for the first successfully inserted row in multi-row INSERT
Gaps	If a value larger than the current max ID value is explicitly inserted, then new IDs with start from this value + 1
Restart (Reset)	ALTER TABLE table_name AUTO_INCREMENT = new_start_value;

Version: MySQL 5.6

MySQL AUTO_INCREMENT Details

To generate a ID value, you can omit the auto-increment column in INSERT statement, or specify NULL or 0 value explicitly:

   -- Omit auto-increment column
   INSERT INTO airlines (name) VALUES ('Delta');
 
   -- Specify NULL or 0
   INSERT INTO airlines VALUES (NULL, 'Southwest');
   INSERT INTO airlines VALUES (0, 'American Airlines');

Make a Gap

You can insert an ID value explicitly, then MySQL will generate new IDs starting from it adding 1:

   INSERT INTO airlines VALUES (200, 'Lufthansa');
 
   INSERT INTO airlines (name) VALUES ('British Airways');   -- id 201 is assigned

You can still insert inside the gap using ID less than the current maximum ID, but this does not affect ID that will be used for other rows:

   INSERT INTO airlines VALUES (150, 'Air France');   -- id 150 inserted
 
   INSERT INTO airlines (name) VALUES ('KLM');   -- id 202 is assigned

Table content:

Getting Generated ID

LAST_INSERT_ID function returns ID of the first successfully inserted row. For example, in a multi-row INSERT:

   INSERT IGNORE INTO airlines VALUES 
      (150, 'North Air'),    -- this row will be skipped as ID 150 already exists, and IGNORE option used
      (0, 'Emirates'),        -- id 203 is assigned
      (0, 'Qantas');          -- id 204
 
   SELECT LAST_INSERT_ID();
   -- Returns: 203

Restart ID

You cannot reset the auto-increment counter to the start value less or equal than the current maximum ID:

   ALTER TABLE airlines AUTO_INCREMENT = 1;
 
   INSERT INTO airlines (name) VALUES ('US Airways');   -- id 205 is assigned

After you have deleted all rows, the counter is not automatically reset to the start value:

   DELETE FROM airlines;
 
   INSERT INTO airlines (name) VALUES ('United');   -- id 206 is assigned

You can restart the auto-increment to 1 if there are no rows in a table:

   DELETE FROM airlines;
 
   ALTER TABLE airlines AUTO_INCREMENT = 1;
 
   INSERT INTO airlines (name) VALUES ('United');   -- id 1 is assigned

MySQL AUTO_INCREMENT in Other Databases

Auto-increment columns in other databases:

Oracle:

Auto-increment or Identity

Can be emulated using sequence and trigger

SQL Server:

IDENTITY(start, increment)

Increment can be specified

PostgreSQL:

SERIAL Data Type
Start Value		Always 1	ALTER SEQUENCE to change
Increment		Always 1
Generate ID	NULL and 0 do not force ID generation
Last ID	LASTVAL(), CURRVAL('seq_name') and INSERT RETURNING

MySQL AUTO_INCREMENT Conversion to Other Databases

Converting MySQL AUTO_INCREMENT:

Oracle:

Oracle does not support AUTO_INCREMENT (IDENTITY) property on a column, but this functionality can be implemented using a sequence and a trigger:

   CREATE TABLE airlines
   ( 
     id NUMBER(10,0) PRIMARY KEY, 
     name VARCHAR2(90)
   ); 
 
   CREATE SEQUENCE airlines_seq START WITH 100 INCREMENT BY 1; 
 
   CREATE OR REPLACE TRIGGER airlines_seq_tr 
    BEFORE INSERT ON airlines FOR EACH ROW 
    WHEN (NEW.id IS NULL OR NEW.id = 0) 
   BEGIN 
    SELECT airlines_seq.NEXTVAL INTO :NEW.id FROM dual; 
   END; 
   /

Note that a trigger is required as Oracle does not allow using NEXTVAL in DEFAULT clause for a column.

SQL Server:

SQL Server supports IDENTITY property and allows you to specify the increment:

   CREATE TABLE airlines
   ( 
     id INT IDENTITY(100, 1) PRIMARY KEY, 
     name VARCHAR(90)
   );

PostgreSQL:

PostgreSQL supports SERIAL data type that allows you to automatically generate IDs. Although SERIAL does not provide options to set the initial and increment values, you can modify the underlying sequence object:

   CREATE TABLE airlines
   ( 
     id SERIAL PRIMARY KEY, 
     name VARCHAR(90)
   );
 
   ALTER SEQUENCE airlines_id_seq RESTART WITH 100;

For more information, see Generating IDs in PostgreSQL.

Resources

MySQL 5.6 Reference Manual

Can we create sequence in MySQL?

Is it mandatory to make primary column of a table as auto increment?

How can remove auto increment and primary key in MySQL?

Is primary key always auto increment?