Detailed Explanation and Example Code of MySQL Sequence AUTO_INCREMENT

Detailed Explanation and Example Code of MySQL Sequence AUTO_INCREMENT

MySQL sequence is a set of integers: 1, 2, 3,... Since a data table can only have one field self-increasing primary key, if you want to realize automatic increase of other fields, you can use MySQL sequence to realize it.

In this chapter, we will explain how to use the sequence of MySQL.

Use AUTO_INCREMENT

The easiest way to use sequences in MySQL is to define columns using MySQL AUTO_INCREMENT.

Instances

In the following example, the data table insect is created, and id in insect can grow automatically without specifying a value.

mysql> CREATE TABLE insect
  -> (
  -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  -> PRIMARY KEY (id),
  -> name VARCHAR(30) NOT NULL, # type of insect
  -> date DATE NOT NULL, # date collected
  -> origin VARCHAR(30) NOT NULL # where collected
);
Query OK, 0 rows affected (0.02 sec)
mysql> 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');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM insect ORDER BY id;
+----+-------------+------------+------------+
| id | name    | date    | origin   |
+----+-------------+------------+------------+
| 1 | housefly  | 2001-09-10 | kitchen  |
| 2 | millipede  | 2001-09-10 | driveway  |
| 3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)

Get the AUTO_INCREMENT value

In the MySQL client, you can use the LAST_INSERT_ID () function in SQL to get the value of the last self-incremental column inserted into the table.

Functions are also provided in the PHP or PERL scripts to get the value of the last self-incremental column inserted into the table.

PERL instance

Use the mysql_insertid property to get the value of AUTO_INCREMENT. Examples are as follows:

$dbh->do ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
my $seq = $dbh->{mysql_insertid};

PHP instance

PHP uses the mysql_insert_id () function to get the value of the AUTO_INCREMENT column inserted into the executed SQL statement.

mysql_query ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysql_insert_id ($conn_id);

Reset sequence

If you delete multiple records in the data table and want to rearrange the AUTO_INCREMENT columns of the remaining data, you can do so by deleting the self-adding columns and then adding them again. However, this operation should be very careful. If new records are added at the same time as deletion, data confusion may occur. The action is as follows:

mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect
  -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
  -> ADD PRIMARY KEY (id);

Set the starting value of the sequence

In general, the sequence starts at 1, but if you need to specify a starting value of 100, we can do this by the following statement:

mysql> CREATE TABLE insect
  -> (
  -> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
  -> PRIMARY KEY (id),
  -> name VARCHAR(30) NOT NULL, # type of insect
  -> date DATE NOT NULL, # date collected
  -> origin VARCHAR(30) NOT NULL # where collected
);

Alternatively, after the table is created successfully, you can do this by the following statement:

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

Thank you for reading, hope to help everyone, thank you for your support to this site!