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
items.itemcallnumber, so I had to use
substring_indexto 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: 100Overview:
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 assignedYou 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 assignedTable content:
id | name |
100 | United Airlines |
101 | Delta |
102 | Southwest |
103 | American Airlines |
150 | Air France |
200 | Lufthansa |
201 | British Airways |
202 | KLM |
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: 203Restart 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 assignedAfter 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 assignedYou 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 assignedMySQL 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