A simple tutorial on using a sequence in Mysql _mysql

The sequence is a set of integer 1,2,3 ... The order in which the builds are generated. Sequences are frequently used in databases, because many applications require that each row in a table contains a unique value and sequence that provides an easy way to generate. This chapter describes how to use a sequence in MySQL.
To Use automatic incrementing columns:

The easiest way to use a sequence in MySQL is to define a auto_increment column and leave the rest to MySQL.
Instance:

Try the following example. This will create the table, and it will insert a few rows in this table that it is not required for the record ID because it is automatically incremented by MySQL.

mysql> CREATE TABLE Insect-> (-> ID INT UNSIGNED not NULL auto_increment,-> PRIMARY KEY (ID), ; Name VARCHAR is not NULL, # Type of insect-> date is not NULL, # Date collected-> origin (a) not VARCHAR
LL # where collected); Query OK, 0 rows affected (0.02 sec) mysql> INSERT into insect (id,name,date,origin) VALUES-> (NULL, ' housefly ', ' 2 001-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:

LAST_INSERT_ID () is a SQL function that can be used on any client to understand how to emit SQL statements. Otherwise, Perl and PHH scripts provide unique functionality to retrieve the last record of the automatic delivery increment.
PERL Example:

Use the Mysql_insertid property to get the Auto_increment value produced by the query. This property is accessed through a database handle or statement handle, depending on how the query is emitted. 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:

A query that produces a auto_increment value after it is emitted, and the retrieved value calls MYSQL_INSERT_ID ():

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

To be an existing sequence:

It is possible that when a record deletes a portion of all the records from the table that you want to reorder. This can be done by using a simple trick, but should be very careful if the table is to participate with other tables.

If you determine the unavoidable way to auto_increment the column, do so by deleting the column from the table and then adding it again. The following example shows how to use this technique to renumber ID values for bugs:

mysql> ALTER TABLE insect DROP ID;
mysql> ALTER TABLE Insect
  -> Add ID INT UNSIGNED not NULL auto_increment A,
  -> add PRIMARY KEY (ID);

Start sequence in a special value:

By default, MySQL will start in order 1, but you can specify any other number when you create the table. In the following example, MySQL will start from 100 order.

mysql> CREATE TABLE Insect
  -> (
  -> ID INT UNSIGNED not NULL auto_increment = m,
  -> PRIMARY KEY ( ID),
  -> name VARCHAR NOT NULL, # type of insect
  -> Date date is not NULL, # Date collected
  -> ori Gin VARCHAR () not NULL # where collected
);

Alternatively, you can create a table and then set the initial sequence value ALTER TABLE.

mysql> ALTER TABLE t auto_increment = 100;