La clause, AUTO_INCREMENT, permet \u00e0 MySQL de g\u00e9n\u00e9rer un entier unique pour tout nouvel enregistrement d’une table. Cette clause ne peut se mettre que sur les champs de type entier, index\u00e9 et non nul. Elle est donc souvent utilis\u00e9e comme cl\u00e9 primaire.<\/p>\n
Cependant, sont comportement n’est pas tout \u00e0 fait identique sur une table MyISAM et sur une table InnoDB.<\/p>\n
\n
\n
mysql><\/span> CREATE TABLE table_myisam (id INT AUTO_INCREMENT PRIMARY KEY) engine=MyISAM;<\/p>\n mysql><\/span> SHOW CREATE TABLE table_myisam;<\/p>\n \n CREATE TABLE `table_myisam` (<\/em><\/p>\n `id` int(11) NOT NULL AUTO_INCREMENT,<\/em><\/p>\n PRIMARY KEY (`id`)<\/em><\/p>\n ) ENGINE=MyISAM <\/strong>DEFAULT CHARSET=latin1<\/em><\/p>\n \n \n mysql><\/span> INSERT INTO table_myisam (id) VALUES (NULL),(NULL),(NULL),(100);<\/p>\n Query OK, 4 rows affected (0.02 sec)<\/em><\/p>\n Records: 4 Duplicates: 0 Warnings: 0<\/em><\/p>\n \n mysql><\/span> SELECT id FROM table_myisam;<\/p>\n +—–+<\/em><\/p>\n | id |<\/em><\/p>\n +—–+<\/em><\/p>\n | 1 |<\/em><\/p>\n | 2 |<\/em><\/p>\n | 3 |<\/em><\/p>\n | 100 |<\/em><\/p>\n +—–+<\/em><\/p>\n \n mysql><\/span> SHOW CREATE TABLE table_myisam;<\/p>\n \n CREATE TABLE `table_myisam` (<\/em><\/p>\n `id` int(11) NOT NULL AUTO_INCREMENT,<\/em><\/p>\n PRIMARY KEY (`id`)<\/em><\/p>\n ) ENGINE=MyISAM AUTO_INCREMENT=101<\/strong> DEFAULT CHARSET=latin1<\/em><\/p>\n \n \n mysql><\/span> DELETE FROM table_myisam WHERE id=100;<\/p>\n mysql><\/span> SELECT id FROM table_myisam;<\/p>\n +—-+<\/em><\/p>\n | id |<\/em><\/p>\n +—-+<\/em><\/p>\n | 1 |<\/em><\/p>\n | 2 |<\/em><\/p>\n | 3 |<\/em><\/p>\n +—-+<\/em><\/p>\n \n mysql><\/span> SHOW CREATE TABLE table_myisam;<\/p>\n \n CREATE TABLE `table_myisam` (<\/em><\/p>\n `id` int(11) NOT NULL AUTO_INCREMENT,<\/em><\/p>\n PRIMARY KEY (`id`)<\/em><\/p>\n ) ENGINE=MyISAM AUTO_INCREMENT=101<\/strong> DEFAULT CHARSET=latin1<\/em><\/p>\n \n \n mysql><\/span> CREATE TABLE table_innodb (id INT AUTO_INCREMENT PRIMARY KEY) engine=InnoDB;<\/p>\n mysql><\/span> SHOW CREATE TABLE table_innodb;<\/p>\n CREATE TABLE `table_innodb` (<\/em><\/p>\n `id` int(11) NOT NULL AUTO_INCREMENT,<\/em><\/p>\n PRIMARY KEY (`id`)<\/em><\/p>\n ) ENGINE=InnoDB <\/strong>DEFAULT CHARSET=latin1<\/em><\/p>\n \n \n mysql><\/span> INSERT INTO table_innodb (id) VALUES (NULL),(NULL),(NULL),(100);<\/p>\n Query OK, 4 rows affected (0.22 sec)<\/em><\/p>\n Records: 4 Duplicates: 0 Warnings: 0<\/em><\/p>\n \n mysql><\/span> SELECT id FROM table_innodb;<\/p>\n +—–+<\/em><\/p>\n | id |<\/em><\/p>\n +—–+<\/em><\/p>\n | 1 |<\/em><\/p>\n | 2 |<\/em><\/p>\n | 3 |<\/em><\/p>\n | 100 |<\/em><\/p>\n +—–+<\/em><\/p>\n \n mysql><\/span> SHOW CREATE TABLE table_innodb;<\/p>\n CREATE TABLE `table_innodb` (<\/em><\/p>\n `id` int(11) NOT NULL AUTO_INCREMENT,<\/em><\/p>\n PRIMARY KEY (`id`)<\/em><\/p>\n ) ENGINE=InnoDB AUTO_INCREMENT=101<\/strong> DEFAULT CHARSET=latin1<\/em><\/p>\n \n \n mysql><\/span> DELETE FROM table_innodb WHERE id=100;<\/p>\n mysql><\/span> SELECT id FROM table_innodb;<\/p>\n +—-+<\/em><\/p>\n | id |<\/em><\/p>\n +—-+<\/em><\/p>\n | 1 |<\/em><\/p>\n | 2 |<\/em><\/p>\n | 3 |<\/em><\/p>\n +—-+<\/em><\/p>\n \n mysql><\/span> SHOW CREATE TABLE table_innodb;<\/p>\n CREATE TABLE `table_innodb` (<\/em><\/p>\n `id` int(11) NOT NULL AUTO_INCREMENT,<\/em><\/p>\n PRIMARY KEY (`id`)<\/em><\/p>\n ) ENGINE=InnoDB AUTO_INCREMENT=101<\/strong> DEFAULT CHARSET=latin1<\/em><\/p>\n \n \n \/*************************************\/<\/span><\/p>\n \/**** reboot du serveur MySQL<\/strong> ****\/<\/span><\/p>\n \/*************************************\/<\/span><\/p>\n \n mysql><\/span> SHOW CREATE TABLE table_myisam;<\/p>\n CREATE TABLE `table_myisam` (<\/em><\/p>\n `id` int(11) NOT NULL AUTO_INCREMENT,<\/em><\/p>\n PRIMARY KEY (`id`)<\/em><\/p>\n ) ENGINE=MyISAM AUTO_INCREMENT=101<\/strong> DEFAULT CHARSET=latin1<\/em><\/p>\n \n mysql><\/span> INSERT INTO table_myisam (id) VALUES (NULL);<\/p>\n mysql><\/span> SELECT id FROM table_myisam;<\/p>\n +—–+<\/em><\/p>\n | id |<\/em><\/p>\n +—–+<\/em><\/p>\n | 1 |<\/em><\/p>\n | 2 |<\/em><\/p>\n | 3 |<\/em><\/p>\n | 101<\/span><\/strong> |<\/em><\/p>\n +—–+<\/em><\/p>\n \n \n mysql><\/span> SHOW CREATE TABLE table_innodb;<\/p>\n CREATE TABLE `table_innodb` (<\/em><\/p>\n `id` int(11) NOT NULL AUTO_INCREMENT,<\/em><\/p>\n PRIMARY KEY (`id`)<\/em><\/p>\n ENGINE=InnoDB AUTO_INCREMENT=4<\/strong> DEFAULT CHARSET=latin1<\/em><\/p>\n \n mysql><\/span> INSERT INTO table_innodb (id) VALUES (NULL);<\/p>\n mysql><\/span> SELECT id FROM table_innodb;<\/p>\n +—-+<\/em><\/p>\n | id |<\/em><\/p>\n +—-+<\/em><\/p>\n | 1 |<\/em><\/p>\n | 2 |<\/em><\/p>\n | 3 |<\/em><\/p>\n | 4<\/strong> <\/span>|<\/em><\/p>\n +—-+<\/em><\/p>\n \n \n","protected":false},"excerpt":{"rendered":" La clause, AUTO_INCREMENT, permet \u00e0 MySQL de g\u00e9n\u00e9rer un entier unique pour tout nouvel enregistrement d’une table. Cette clause ne peut se mettre que sur les champs de type entier, index\u00e9 et non nul. Elle est donc souvent utilis\u00e9e comme cl\u00e9 primaire.<\/p>\n Cependant, sont comportement n’est pas tout \u00e0 fait identique sur une table MyISAM et sur une table InnoDB.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"categories":[8],"tags":[22,24,23,241],"class_list":["post-56","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-auto_increment","tag-innodb","tag-myisam","tag-mysql"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-U","jetpack-related-posts":[{"id":237,"url":"https:\/\/dasini.net\/blog\/2008\/11\/28\/les-nouveautes-de-mysql-51-part-15\/","url_meta":{"origin":56,"position":0},"title":"Les nouveaut\u00e9s de MySQL 5.1 — (part 1\/5)","author":"Olivier DASINI","date":"28 novembre 2008","format":false,"excerpt":"Que de chemin parcouru depuis ce 5 juillet 1999, date de lancement de MySQL 3.23.0. En plus des objectifs de simplicit\u00e9 d'administration, de hautes performances et de fiabilit\u00e9, se sont greff\u00e9es, au fur et \u00e0 mesure, les fonctionnalit\u00e9s les plus demand\u00e9es par les utilisateurs. La nouvelle version du SGBDR open-source\u2026","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":453,"url":"https:\/\/dasini.net\/blog\/2009\/02\/18\/optimisation-de-requetes-comprendre-loptimiseur-de-mysql\/","url_meta":{"origin":56,"position":1},"title":"Optimisation de requ\u00eates: comprendre l’optimiseur de MySQL","author":"Olivier DASINI","date":"18 f\u00e9vrier 2009","format":false,"excerpt":"Le but de cet article est d'optimiser une simple requ\u00eate (SELECT avg(Population) FROM city GROUP BY CountryCode) et surtout de comprendre comment l'optimiseur proc\u00e8de, en \u00e9tudiant les r\u00e9sultats donn\u00e9s par les variables qui permettent de surveiller le serveur MySQL.","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1533,"url":"https:\/\/dasini.net\/blog\/2015\/11\/17\/30-mins-avec-json-en-mysql\/","url_meta":{"origin":56,"position":2},"title":"30 mins avec JSON en MySQL","author":"Olivier DASINI","date":"17 novembre 2015","format":false,"excerpt":"Comme vous le savez MySQL 5.7 est GA. Cette nouvelle mouture de la base de donn\u00e9es open source la plus populaire au monde a plus de 150 nouvelles fonctionnalit\u00e9s. L'une d'entre elle est un type de donn\u00e9es JSON natif ainsi que les fonctions JSON associ\u00e9es. Prenons 30 minutes pour voir\u2026","rel":"","context":"Dans "json"","block_context":{"text":"json","link":"https:\/\/dasini.net\/blog\/category\/json-fr\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":430,"url":"https:\/\/dasini.net\/blog\/2009\/01\/29\/influencer-loptimiseur-de-mysql\/","url_meta":{"origin":56,"position":3},"title":"Influencer l’optimiseur de MySQL","author":"Olivier DASINI","date":"29 janvier 2009","format":false,"excerpt":"Il est possible d'influencer l'optimiseur pour qu'il choisisse d'utiliser ou de ne pas utiliser un index particulier. Les clauses \u00e0 placer dans votre requ\u00eate SELECT sont les suivantes: USE INDEX : utilise l'index pass\u00e9 en argument (MySQL ne l'utilisera pas si l'index est plus couteux qu'un full table scan) FORCE\u2026","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1557,"url":"https:\/\/dasini.net\/blog\/2015\/11\/30\/json-et-colonnes-generees-avec-mysql\/","url_meta":{"origin":56,"position":4},"title":"JSON et colonnes g\u00e9n\u00e9r\u00e9es avec MySQL","author":"Olivier DASINI","date":"30 novembre 2015","format":false,"excerpt":"Le 24 novembre dernier, lors du Forum PHP, Tomas Ulin (Oracle's MySQL VP of Engineering) a parl\u00e9 de l'utilisation de JSON dans MySQL \"MySQL 5.7 & JSON: New opportunities for developers\". Voici les r\u00e9ponses \u00e0 quelques questions qui m'ont \u00e9t\u00e9 pos\u00e9es: Comment se comporte mysqldump avec les colonnes g\u00e9n\u00e9r\u00e9es ?\u2026","rel":"","context":"Dans "json"","block_context":{"text":"json","link":"https:\/\/dasini.net\/blog\/category\/json-fr\/"},"img":{"alt_text":"JSON","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/json_icon.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":137,"url":"https:\/\/dasini.net\/blog\/2008\/11\/16\/enigme-order-by\/","url_meta":{"origin":56,"position":5},"title":"\u00c9nigme… order by","author":"Olivier DASINI","date":"16 novembre 2008","format":false,"excerpt":"Cela part d'un probl\u00e8me tout simple, trier les donn\u00e9es d'une table: mysql> SELECT id, technologie FROM enigme;","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/56","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/comments?post=56"}],"version-history":[{"count":6,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/56\/revisions"}],"predecessor-version":[{"id":62,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/56\/revisions\/62"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=56"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=56"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=56"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n<\/em><\/p>\n
\n<\/em><\/p>\n
\n<\/em><\/p>\n
\n<\/em><\/p>\n
\n<\/em><\/p>\n
\n<\/em><\/p>\n
\n<\/em><\/p>\n