MySQL sequence AUTO_INCREMENT detailed explanation and example code

MySQL sequence AUTO_INCREMENT detailed explanation and example code

A MySQL sequence is a set of integers: 1, 2, 3, .... Since a data table can only have one auto-incrementing primary key field, if you want to achieve auto-increment for other fields, you can use a MySQL sequence to achieve this.

In this chapter we will introduce how to use MySQL sequences.

Using AUTO_INCREMENT

The simplest way to use sequences in MySQL is to define the column using MySQL AUTO_INCREMENT.

Examples

The following example creates a data table named insect. The id in insect can be automatically increased 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 AUTO_INCREMENT Value

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

Corresponding functions are also provided in PHP or PERL scripts to obtain the value of the auto-increment column in the last inserted table.

PERL Examples

Use the mysql_insertid attribute to obtain the value of AUTO_INCREMENT. The following are examples:

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

PHP Example

PHP uses the mysql_insert_id() function to get the value of the AUTO_INCREMENT column in the executed insert 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 a table and want to rearrange the AUTO_INCREMENT columns of the remaining data, you can do this by deleting the auto-increment column and then adding it again. However, this operation must be performed with great caution. If new records are added while they are being deleted, data confusion may occur. The operation 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 start value of the sequence

Normally, the starting value of the sequence is 1, but if you need to specify a starting value of 100, we can do so with 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
);

Or you can also use the following statement to achieve it after the table is created successfully:

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

Thank you for reading, I hope it can help you, thank you for your support of this site!