mysql

Khóa học miễn phí MySQL – Using Sequences nhận dự án làm có lương

Posted on by user

MySQL – Sequences

Table of content

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'')

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

user