AUTO_INCREMENT: Différences MyISAM – InnoDB
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 |
+—-+
Architecte Solution Cloud chez Oracle
MySQL Geek, Architecte, DBA, Consultant, Formateur, Auteur, Blogueur et Conférencier.
—–
Blog: www.dasini.net/blog/en/
Twitter: https://twitter.com/freshdaz
SlideShare: www.slideshare.net/freshdaz
Youtube: https://www.youtube.com/channel/UC12TulyJsJZHoCmby3Nm3WQ
—–
Très intéressant. Il faut vraiment en être conscient pour savoir quel moteur de stockage utiliser, car ça fait vraiment la différence.
Merci pour ces cas de figure.
J’ai constaté une autre différence sur la gestion des AUTO_INCREMENT lors de l’utilisation de l’instruction INSERT IGNORE. Lorsque la ligne est en doublon sur l’index UNIQUE (et non PRIMARY), elle n’est pas insérée. MyISAM réutilise la valeur de l’AUTO_INCREMENT qui n’a pas pu être inséré alors que InnoDB passe à la valeur suivante. Ce qui provoque des table à trou.
Dans mon cas, un très grand nombre de doublon sont envoyés, ce qui provoque un ID à trou. Je n’ai pas trouvé de solution pour obtenir le comportement de MyISAM avec InnoDB, si vous avez la solution je suis preneur.
Merci