I was surprised to see how few people know that in MySQL you can do :
INSERT INTO tbl_01(id, name) VALUES (1, ‘One’)
INSERT INTO tbl_01(id, name) VALUES(2, ‘Two’)
But also
INSERT INT tbl_01(id, name) VALUES
(1, ‘One’), (2, ‘Two’)
In practice it is very useful.
In Oracle, sequences (often called autonumber) are used to maintain a unique series of numbers for an id field. Sequences are independent of any table hence they can be used to keep a value unique across a number of tables. Sequences actually do not have to be used in relationship with any table.
The syntax for creating a sequence is :
CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;
If you omit the MAXVALUE, it will default to
MAXVALUE 999999999999999999999999999
To retrieve the next value from the sequence, nextval is to be used :
sequence_name.nextval
The nextval statement needs to be used in an SQL statement, for instance :
INSERT INTO tbl_name(tbl_id, tbl_col_name)
VALUES(sequence_name.nextval, ‘Name’);
The CACHE option specifies how many values would be stored in memory for faster access. It sounds good, but if for some reason a system failure occurs, all cached sequence values would be “lost” – when the system is be back up, the sequence will use the next value starting from MINVALUE + CACHE. NOCACHE sacrifices some performance, but you won’t get a “gap” in the values.
Oracle DOES NOT implement any column with autoincrement property. In turn, MySQL does not support sequences. MySQL uses autoincrement,applied to the primary key during table creation :
create table tbl_01 (id int primary key auto_increment, name varchar(100));
The value for the primary key will be assigned automatically, if during an insert there is no value manually assigned to the id column.
Note that the “sequence” will start from the highest value. If we manually insert a record with a number for the id higher than the maximum id in that table, the next insert with no value for the id will automatically assign that max value incremented.
For example suppose you have the following data in the table:
id name
1 One
2 Two
3 Three
You do an insert like this :
INSERT INTO tbl_01(id, name) VALUES (10, ‘Ten’);
You will end up with the following table :
id name
1 One
2 Two
3 Three
10 Ten
When you execute the following SQL:
INSERT INTO TABLE tbl_01(name) VALUES (’Next’);
You will end up with :
id name
1 One
2 Two
3 Three
10 Ten
11 Next
You can insert record with a value for the id lower than the MAX(id) and that will not affect the next autoincrement value. It just has to follow the normal rules for the primary key (no duplicates).
Also if all the records are deleted from the table, the autoincrement is not affected (it will continue to assign the incremented MAX(id) based on the values that were previously in the table). In order to “reset the sequence” a the table must be truncated :
truncate table tbl_01
Now the first automatically generated value for the id will be 1. You can achieve the same by :
ALTER TABLE tbl_01 auto_increment=value
Compared to Oracle, MySQL is pretty straightforward, but does have its [big] limitations: it cannot be used across multiple tables and you cannot set different increment values.
Consider a table tbl_01 containing a column Name and consider the following query :
SELECT * FROM tbl_01 WHERE name = ‘paul’
In Oracle this query would return only the rows containing ‘paul’ in the column name, because Oracle is case sensitive.
MySQL is not, so the same query would return rows containing ‘Paul’, ‘paul’, ‘pAul’, etc. In order to force MySQL to check for the exact case, the keyword binary has to be used :
SELECT * FROM tbl_01 WHERE binary name = “paul”
There is also an alternative to this – making a column case sensitive on table creation:
CREATE TABLE tbl_01 (name varchar(100) binary);
Now the select on name cares for the way the name is written.
