La clause, AUTO_INCREMENT, permet à MySQL de générer un entier unique pour tout nouvel enregistrement d’une table. Cette clause ne peut se mettre que sur les champs de type entier, indexé et non nul. Elle est donc souvent utilisée comme clé primaire.

Cependant, sont comportement n’est pas tout à fait identique sur une table MyISAM et sur une table InnoDB.

mysql> CREATE TABLE table_myisam (id INT AUTO_INCREMENT PRIMARY KEY) engine=MyISAM;

mysql> SHOW CREATE TABLE table_myisam;

CREATE TABLE `table_myisam` (

`id` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

mysql> INSERT INTO table_myisam (id) VALUES (NULL),(NULL),(NULL),(100);

Query OK, 4 rows affected (0.02 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> SELECT id FROM table_myisam;

+—–+

| id |

+—–+

| 1 |

| 2 |

| 3 |

| 100 |

+—–+

mysql> SHOW CREATE TABLE table_myisam;

CREATE TABLE `table_myisam` (

`id` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1

mysql> DELETE FROM table_myisam WHERE id=100;

mysql> SELECT id FROM table_myisam;

+—-+

| id |

+—-+

| 1 |

| 2 |

| 3 |

+—-+

mysql> SHOW CREATE TABLE table_myisam;

CREATE TABLE `table_myisam` (

`id` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1

mysql> CREATE TABLE table_innodb (id INT AUTO_INCREMENT PRIMARY KEY) engine=InnoDB;

mysql> SHOW CREATE TABLE table_innodb;

CREATE TABLE `table_innodb` (

`id` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> INSERT INTO table_innodb (id) VALUES (NULL),(NULL),(NULL),(100);

Query OK, 4 rows affected (0.22 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> SELECT id FROM table_innodb;

+—–+

| id |

+—–+

| 1 |

| 2 |

| 3 |

| 100 |

+—–+

mysql> SHOW CREATE TABLE table_innodb;

CREATE TABLE `table_innodb` (

`id` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1

mysql> DELETE FROM table_innodb WHERE id=100;

mysql> SELECT id FROM table_innodb;

+—-+

| id |

+—-+

| 1 |

| 2 |

| 3 |

+—-+

mysql> SHOW CREATE TABLE table_innodb;

CREATE TABLE `table_innodb` (

`id` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1

/*************************************/

/**** reboot du serveur MySQL ****/

/*************************************/

mysql> SHOW CREATE TABLE table_myisam;

CREATE TABLE `table_myisam` (

`id` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1

mysql> INSERT INTO table_myisam (id) VALUES (NULL);

mysql> SELECT id FROM table_myisam;

+—–+

| id |

+—–+

| 1 |

| 2 |

| 3 |

| 101 |

+—–+

mysql> SHOW CREATE TABLE table_innodb;

CREATE TABLE `table_innodb` (

`id` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

mysql> INSERT INTO table_innodb (id) VALUES (NULL);

mysql> SELECT id FROM table_innodb;

+—-+

| id |

+—-+

| 1 |

| 2 |

| 3 |

| 4 |

+—-+

Tags: auto_increment, InnoDB, MyISAM, MySQL