MySQL – Sequences
”;
A sequence is a series of integers, starting from 1 and incrementing by 1 with each successive value. These sequences are usually used in databases, as many applications require each row in a table to have a unique identifier, and sequences provide an easy way to generate such values.
Sequences in MySQL
MySQL does not have a built-in sequence feature but provides an alternative in the form of the AUTO_INCREMENT column, which serves a similar purpose.
In MySQL, the AUTO_INCREMENT attribute is used to automatically generate unique integer values (sequences) for a column. By default, this sequence begins with an initial value of 1 and increments by 1 for each new row that is added.
Syntax
Following is the syntax of AUTO_INCREMENT attribute in MySQL −
CREATE TABLE table_name ( column1 datatype AUTO_INCREMENT, column2 datatype, column3 datatype, ... columnN datatype );
Example
In the following example, we are creating a table named “CUSTOMERS” and, in addition, defining the AUTO_INCREMENT attribute for the “ID” column of the table −
CREATE TABLE CUSTOMERS ( ID INT AUTO_INCREMENT, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
Now, let us insert some records into the above-created table −
INSERT INTO CUSTOMERS VALUES (NULL, ''Ramesh'', 32, ''Ahmedabad'', 2000.00), (NULL, ''Khilan'', 25, ''Delhi'', 1500.00), (NULL, ''Kaushik'', 23, ''Kota'', 2000.00), (NULL, ''Chaitali'', 25, ''Mumbai'', 6500.00), (NULL, ''Hardik'', 27, ''Bhopal'', 8500.00), (NULL, ''Komal'', 22, ''Hyderabad'', 4500.00), (NULL, ''Muffy'', 24, ''Indore'', 10000.00);
Output
We can see in the table displayed below that the values in the “ID” column are automatically incremented −
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | Kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | Hyderabad | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
Retrieving AUTO_INCREMENT Values
To obtain AUTO_INCREMENT values in MySQL, you can use the LAST_INSERT_ID() SQL function. This function can be used in any client that can issue SQL statements. Alternatively, in PERL and PHP scripts, specific functions are available to retrieve the auto-incremented value of the last record.
PERL Example
You can access the AUTO_INCREMENT value generated by a query using the mysql_insertid attribute. This attribute can be accessed either through a database handle or a statement handle, depending on how you execute the query.
The following example references it through the database handle −
$dbh->do ("INSERT INTO insect (name,date,origin)
VALUES(''moth'',''2001-09-14'',''windowsill'')");
my $seq = $dbh->{mysql_insertid};
PHP Example
After executing a query that generates an AUTO_INCREMENT value, you can retrieve the value using the mysql_insert_id( ) command −
mysql_query ("INSERT INTO insect (name,date,origin)
VALUES(''moth'',''2001-09-14'',''windowsill'')", $conn_id);
$seq = mysql_insert_id ($conn_id);
Renumbering an Existing Sequence
In some cases, you may need to re-sequence records in a table, especially if you have deleted many records. Be careful when resequencing if your table is related to other tables through joins.
If you determine that the resequencing of an AUTO_INCREMENT column is unavoidable, the way to do it is to drop the AUTO_INCREMENT column from the table, then add it again.
Example
The following example shows how to renumber the id values in the table using this technique.
ALTER TABLE CUSTOMERS DROP id; ALTER TABLE CUSTOMERS ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id);
Output
Following is the output obtained −
Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0
Starting a Sequence at a Specific Value
By default, MySQL starts sequences from 1, but you can specify a different initial value when creating the table.
Example
The following example demonstrates how to start the sequence from 100 during table creation −
CREATE TABLE CUSTOMERS ( ID INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (ID), NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2) )AUTO_INCREMENT = 100;
Output
Output of the above code is as shown below −
Query OK, 0 rows affected (0.04 sec)
Alternatively, you can create the table first and then set the initial sequence value using the ALTER TABLE command as shown below −
ALTER TABLE CUSTOMERS AUTO_INCREMENT = 100;
Sequence Using a Client Program
We can also create a sequence using the client program.
Syntax
To create a sequence on a column of a table through a PHP program, we need to specify auto_increment for a specific column while creating the table using the mysqli function query() as follows −
$sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)"; $mysqli->query($sql);
To create a sequence on a column of a table through a JavaScript program, we need to specify auto_increment for a specific column while creating the table using the query() function of mysql2 library as follows −
sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)"; con.query(sql);
To create a sequence on a column of a table through a Java program, we need to specify auto_increment for a specific column while creating the table using the JDBC function execute() as follows −
String sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)"; statement.execute(sql);
To create a sequence on a column of a table through a Python program, we need to specify auto_increment for a specific column while creating the table using the execute() function of the MySQL Connector/Python as follows −
create_table_query = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), name VARCHAR(30) NOT NULL, date DATE NOT NULL, origin VARCHAR(30) NOT NULL)" cursorObj.execute(create_table_query)
Example
Following are the programs −
$dbhost = ''localhost'';
$dbuser = ''root'';
$dbpass = ''password'';
$db = ''TUTORIALS'';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
printf("Connect failed: %s
", $mysqli->connect_error);
exit();
}
//printf(''Connected successfully.
'');
//create table with autoincrement sequene
$sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
if($mysqli->query($sql)){
printf("Table created successfully....!n");
}
//let''s insert some record
$sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''housefly'',''2001-09-10'',''kitchen'')";
if($mysqli->query($sql)){
printf("First record inserted successfully...!n");
}
$sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''millipede'',''2001-09-10'',''driveway'')";
if($mysqli->query($sql)){
printf("Second record inserted successfully...!n");
}
$sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''grasshopper'',''2001-09-10'',''front yard'')";
if($mysqli->query($sql)){
printf("Third record inserted successfully...!n");
}
printf("Table records: n");
$sql = "SELECT * FROM insect";
if($result = $mysqli->query($sql)){
printf("Table record: n");
while($row = mysqli_fetch_array($result)){
printf("Id: %d, Name: %s, Date: %s, Origin %s",
$row[''id''],
$row[''name''],
$row[''date''],
$row[''origin'']);
printf("n");
}
}
if($mysqli->error){
printf("Error message: ", $mysqli->error);
}
$mysqli->close();
Output
The output obtained is as shown below −
Table created successfully....! First record inserted successfully...! Second record inserted successfully...! Third record inserted successfully...! Table records: Table record: Id: 1, Name: housefly, Date: 2001-09-10, Origin kitchen Id: 2, Name: millipede, Date: 2001-09-10, Origin driveway Id: 3, Name: grasshopper, Date: 2001-09-10, Origin front yard
var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
//Connecting to MySQL
con.connect(function(err) {
if (err) throw err;
// console.log("Connected successfully...!");
// console.log("--------------------------");
sql = "USE TUTORIALS";
con.query(sql);
//create table
sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
con.query(sql, function(err, result){
if (err) throw err;
console.log("Table created successfully....!");
});
//now let''s insert some record
sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''housefly'',''2001-09-10'',''kitchen'')";
con.query(sql, function(err, result){
if (err) throw err;
console.log("First record inserted successfully...!");
});
sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''millipede'',''2001-09-10'',''driveway'')";
con.query(sql, function(err, result){
if (err) throw err;
console.log("Second record inserted successfully...!");
});
sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''grasshopper'',''2001-09-10'',''front yard'')";
con.query(sql, function(err, result){
if (err) throw err;
console.log("Third record inserted successfully...!");
});
sql = "SELECT * FROM INSECT";
con.query(sql, function(err, result){
console.log("Table records(with ID auto_increment sequence).");
if (err) throw err;
console.log(result);
});
});
Output
The output obtained is as shown below −
Table created successfully....!
First record inserted successfully...!
Second record inserted successfully...!
Third record inserted successfully...!
Table records(with ID auto_increment sequence).
[
{
id: 1,
name: ''housefly'',
date: 2001-09-09T18:30:00.000Z,
origin: ''kitchen''
},
{
id: 2,
name: ''millipede'',
date: 2001-09-09T18:30:00.000Z,
origin: ''driveway''
},
{
id: 3,
name: ''grasshopper'',
date: 2001-09-09T18:30:00.000Z,
origin: ''front yard''
}
]
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Sequence {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/TUTORIALS";
String user = "root";
String password = "password";
ResultSet rs;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con = DriverManager.getConnection(url, user, password);
Statement st = con.createStatement();
//System.out.println("Database connected successfully...!");
//create table
String sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
st.execute(sql);
System.out.println("Table ''insect'' created successfully...!");
//lets insert some records into the table
String sql1 = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''housefly'',''2001-09-10'',''kitchen''), (NULL,''millipede'',''2001-09-10'',''driveway''), (NULL,''grasshopper'',''2001-09-10'',''front yard'')";
st.execute(sql1);
System.out.println("Records inserted successfully...!");
//lets print table records
String sql2 = "SELECT * FROM INSECT";
rs = st.executeQuery(sql2);
System.out.println("Table records: ");
while(rs.next()) {
String id = rs.getString("id");
String name = rs.getString("name");
String date = rs.getString("date");
String origin = rs.getString("origin");
System.out.println("Id: " + id + ", Name: " + name + ", Date: " + date + ", Origin: " + origin);
}
}catch(Exception e) {
e.printStackTrace();
}
}
}
Output
The output obtained is as shown below −
Table ''insect'' created successfully...! Records inserted successfully...! Table records: Id: 1, Name: housefly, Date: 2001-09-10, Origin: kitchen Id: 2, Name: millipede, Date: 2001-09-10, Origin: driveway Id: 3, Name: grasshopper, Date: 2001-09-10, Origin: front yard
import mysql.connector
# Connecting to MySQL
con = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="tut"
)
# Creating a cursor object
cursorObj = con.cursor()
# Creating the table
create_table_query = """
CREATE TABLE insect (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
name VARCHAR(30) NOT NULL,
date DATE NOT NULL,
origin VARCHAR(30) NOT NULL
)
"""
cursorObj.execute(create_table_query)
print("Table ''insect'' is created successfully!")
# Inserting records
sql = "INSERT INTO insect (name, date, origin) VALUES (%s, %s, %s)"
values = [
(''housefly'', ''2001-09-10'', ''kitchen''),
(''millipede'', ''2001-09-10'', ''driveway''),
(''grasshopper'', ''2001-09-10'', ''front yard'')
]
cursorObj.executemany(sql, values)
print("Data inserted into the ''insect'' table.")
con.commit()
# Retrieving records
cursorObj.execute("SELECT * FROM insect")
records = cursorObj.fetchall()
# Printing the records
print("Table records(with ID auto_increment sequence).")
for record in records:
print(record)
# Closing the connection
cursorObj.close()
con.close()
Output
The output obtained is as shown below −
Table ''insect'' is created successfully! Data inserted into the ''insect'' table. Table records(with ID auto_increment sequence). (1, ''housefly'', datetime.date(2001, 9, 10), ''kitchen'') (2, ''millipede'', datetime.date(2001, 9, 10), ''driveway'') (3, ''grasshopper'', datetime.date(2001, 9, 10), ''front yard'')
”;