14.2.2 Syntaxe de ALTER TABLE

ALTER [IGNORE] TABLE tbl_name     alter_specification [, alter_specification] ... alter_specification:     ADD [COLUMN] column_definition [FIRST | AFTER col_name ]   | ADD [COLUMN] (column_definition,...)   | ADD INDEX [index_name] [index_type] (index_col_name,...)   | ADD [CONSTRAINT [symbol]]         PRIMARY KEY [index_type] (index_col_name,...)   | ADD [CONSTRAINT [symbol]]         UNIQUE [index_name] [index_type] (index_col_name,...)   | ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...)   | ADD [CONSTRAINT [symbol]]         FOREIGN KEY [index_name] (index_col_name,...)         [reference_definition]   | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}   | CHANGE [COLUMN] old_col_name column_definition         [FIRST|AFTER col_name]   | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]   | DROP [COLUMN] col_name   | DROP PRIMARY KEY   | DROP INDEX index_name   | DROP FOREIGN KEY fk_symbol   | DISABLE KEYS   | ENABLE KEYS   | RENAME [TO] new_tbl_name   | ORDER BY col_name   | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]   | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]   | DISCARD TABLESPACE   | IMPORT TABLESPACE   | table_options

ALTER TABLE vous permet de changer la structure d'une table existante. Par exemple, vous pouvez ajouter ou supprimer des colonnes, des index, changer le type des colonnes existantes, renommer ces colonnes, ou la table elle-même. Vous pouvez de même changer le commentaire sur la table, ou le type de celle-ci.La syntaxe de nombreuses altérations est similaires aux clauses de la commande CREATE TABLE . CREATE TABLE Syntax .

Si vous utilisez ALTER TABLE pour modifier les spécifications d'une colonne mais que DESCRIBE nom_de_table vous indique que cette colonne n'a pas été modifiée, il est possible que MySQL ait ignoré vos modifications pour une des raisons décrite dans Changement de type de colonne automatique . Par exemple, si vous essayez de changer une colonne de type VARCHAR en CHAR , MySQL continuera d'utiliser VARCHAR si la table contient d'autres colonnes de taille variable.

Notez que si vous utilisez une autre option que RENAME avec ALTER TABLE , MySQL créera toujours une table temporaire, même si les données n'ont pas besoin d'être copiées (comme quand vous changez le nom d'une colonne). Nous avons prévu de corriger cela dans les versions suivantes, mais comme la commande ALTER TABLE n'est pas utilisée très souvent, cette correction ne fait pas partie de nos priorités. Pour les tables MyISAM , vous pouvez accélérer la réindexation (qui est la partie la plus lente de la modification d'une table) en donnant à la variable système myisam_sort_buffer_size une valeur plus grande.

• Pour utiliser ALTER TABLE , vous devez avoir les droits ALTER , INSERT , et CREATE sur la table.
• IGNORE est une extension MySQL pour ANSI SQL92. Cette option contrôle la façon dont ALTER TABLE fonctionne s'il y a des duplications sur une clef unique de la nouvelle table. Si IGNORE n'est pas spécifiée, la copie est annulée et la table originale est restaurée. Si IGNORE est spécifiée, les lignes contenant les éléments doublons de la table seront effacées, hormis la première, qui sera conservée.
• Vous pouvez effectuer plusieurs opérations de ADD , ALTER , DROP , et CHANGE dans une même commande ALTER TABLE . C'est une extension de MySQL à la norme ANSI SQL92, qui n'autorise qu'une seule modification par commande ALTER TABLE .
• CHANGE nom_colonne , DROP nom_colonne , et DROP INDEX sont des extensions de MySQL à la norme ANSI SQL92.
• MODIFY est une extension Oracle à ALTER TABLE .
• Le mot optionnel COLUMN est purement de la fioriture et peut être ignoré.
• Si vous utilisez ALTER TABLE nom_de_table RENAME TO nouveau_nom sans autre option, MySQL va simplement renommer les fichiers qui correspondent à la table nom_de_table . Il n'y a pas de création de fichier temporaire. RENAME TABLE Syntax .
• La définition create_definition utilise la même syntaxe pour les clauses ADD et CHANGE que dans CREATE TABLE . Notez que cette syntaxe inclut le nom de la colonne, et pas seulement son type CREATE TABLE Syntax .
• Vous pouvez renommer une colonne avec la syntaxe CHANGE ancien_nom_de_colonne create_definition . Pour cela, indiquez l'ancien nom de la colonne, puis le nouveau nom et son type courant. Par exemple, pour renommer une colonne de type INTEGER , de a en b , vous pouvez faire ceci :

  mysql> ALTER TABLE t1 CHANGE a b INTEGER;

  Si vous ne voulez changer que le type de la colonne, avec la clause CHANGE vous devrez redonner le nom de la colonne. Par exemple :

  mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;

  Cependant, à partir de la version 3.22.16a de MySQL, vous pouvez aussi utiliser la clause MODIFY pour changer le type d'une colonne sans la renommer :

  mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

• Si vous utilisez les clauses CHANGE ou MODIFY pour réduire la taille d'une colonne qui comportait un index sur une partie de la colonne (par exemple, si vous aviez un index sur 10 caractères d'une colonne de type VARCHAR ), vous ne pouvez pas rendre la colonne plus petite que le nombre de caractères indexés.
• Quand vous changez le type d'une colonne avec CHANGE ou MODIFY , MySQL essaye de convertir les données au niveau type dans la mesure du possible.
• A partir de la version 3.22 de MySQL, vous pouvez utiliser FIRST ou ADD ... AFTER nom_colonne pour ajouter la colonne à un endroit spécifique dans la table. Par défaut, la colonne est ajoutée à la fin. A partir de la version 4.0.1, vous pouvez aussi utiliser les mots clés FIRST et AFTER avec CHANGE ou MODIFY .
• ALTER COLUMN spécifie une nouvelle valeur par défaut pour une colonne ou enlève l'ancienne. si l'ancienne valeur est effacée et que la colonne peut être NULL , la nouvelle valeur par défaut sera NULL . Si la colonne ne peut être NULL , MySQL assigne une valeur par défaut, comme défini dans CREATE TABLE Syntax .
• DROP INDEX supprime un index. C'est une extension MySQL à la norme ANSI SQL92. DROP INDEX Syntax .
• Si des colonnes sont effacées d'une table, ces colonnes sont aussi supprimés des index dont elles font partie. Si toutes les colonnes qui forment un index sont effacées, l'index lui même est supprimé.
• Si une table ne comporte qu'une seule colonne, La colonne ne peut être supprimée. Si vous voulez effacer la table, utilisez la commande DROP TABLE .
• DROP PRIMARY KEY supprime la clef primaire. Si cette clef n'existe pas, cette commande effacera le premier index UNIQUE de la table. (MySQL marque la première clef UNIQUE en tant que PRIMARY KEY si aucune PRIMARY KEY n'a été spécifiée explicitement.)

  Si vous ajoutez un UNIQUE INDEX ou PRIMARY KEY à une table, c'est enregistré avant les index non- UNIQUE pour que MySQL puisse détecter les valeurs dupliquées aussi vite que possible.

• ORDER BY vous permet de créer une nouvelle table tout en ordonnant les lignes par défaut. Notez que cet ordre ne sera pas conservé après les prochaines insertions et modifications. Dans certains cas, cela aide MySQL si les colonnes sont dans l'ordre dans lequel vous allez trier les valeurs. Cette option n'est vraiment utile que si vous savez à l'avance dans quel ordre vous effectuerez les tris : vous y gagnerez alors en performances.
• Si vous utilisez ALTER TABLE sur une table MyISAM , tous les index non-uniques sont créés par des opérations séparées. (comme dans REPAIR ). Cela devrait rendre ALTER TABLE plus rapide quand vous avez beaucoup d'index.Depuis la version 4.0, la fonctionnalité ci-dessus peut être activée explicitement. ALTER TABLE ... DISABLE KEYS force MySQL à ne plus mettre à jour les index non-uniques pour les tables au format MyISAM . ALTER TABLE ... ENABLE KEYS doit alors être utilisé pour recréer les index manquants. Comme MySQL le fait avec un algorithme spécial qui est plus rapide que le fait d'insérer les clefs une par une, désactiver les clefs peut vous faire gagner en performances.
• Les clauses FOREIGN KEY et REFERENCES sont supportées par le moteur de tables InnoDB , qui implémente les clauses ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES ... (...) . Contraintes FOREIGN KEY . Pour les autres moteurs de stockages, ces clauses sont lues mais ignorées. La clause CHECK est analysée mais ignorée par tous les moteurs de stockage. CREATE TABLE Syntax . La raison pour accepter mais ignorer ces clauses est que cela renforce la compatibilité avec le code des autres serveurs SQL, et qu'il est possible de créer des tables avec des références. Différences entre MySQL et ANSI SQL92 .
• Depuis MySQL 4.0.13, InnoDB supporte l'utilisation de ALTER TABLE pour effacer des clés étrangères :

  ALTER TABLE yourtablename     DROP FOREIGN KEY fk_symbol

  Pour plus d'informations, voyez Contraintes FOREIGN KEY .
• ALTER TABLE ignore les options de tables DATA DIRECTORY et INDEX DIRECTORY .
• Depuis MySQL 4.1.2, si vous voulez changer dans toutes les colonnes de texte ( CHAR , VARCHAR , TEXT ) le jeu de caractères, vous pouvez utiliser la commande suivante :

  ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

  C'est pratique lorsque vous passez de MySQL 4.0.x en 4.1.x. Mise à jour depuis MySQL 4.0 .

  Attention : l'opération précédente va convertir les valeurs des colonnes entre les deux jeux de caractères. Ce n'est pas ce que vous souhaitez faire si une colonne est de type latin1 mais que les valeurs sont en fait dans un autre jeu de caractères (comme utf8 ). Dans ce cas, vous devez faire ceci avec une telle colonne :

  ALTER TABLE t1 CHANGE c1 c1 BLOB; ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

  La raison est que dans ce cas, il n'y aura pas de conversion lorsque vous passer en type BLOB .

  Pour ne changer que le type de caractères par défaut , utilisez cette commande :

  ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;

  Le mot DEFAULT est optionnel. Le jeu de caractères par défaut est utilisé si vous ne spécifiez pas le jeu de caractères de la colonne explicitement, lorsque vous ajoutez une nouvelle colonne : par exemple, avec ALTER TABLE ... ADD column .

  Attention : depuis MySQL 4.1.2 et plus récent, ALTER TABLE ... DEFAULT CHARACTER SET et ALTER TABLE ... CHARACTER SET sont équivalent et ne changent que le jeu de caractères par défaut. Dans les versions antérieures à MySQL 4.1.2, ALTER TABLE ... DEFAULT CHARACTER SET changeait le jeu de caractères par défaut, mais ALTER TABLE ... CHARACTER SET (sans DEFAULT ) changeait le jeu de caractères par défaut, et convertissaient les colonnes dans le nouveau jeu.

• Pour une table InnoDB qui a été créée avec son propre espace de tables dans un fichier .ibd , ce fichier peut être supprimé et importé. Pour supprimer le fichier .ibd , utilisez la commande suivante :

  ALTER TABLE tbl_name DISCARD TABLESPACE;

  Elle efface le fichier .ibd courant, alors assurez vous que vous avez une copie de sauvegarde. Si vous tentez d'accéder à un espace de table sans ce fichier, vous obtiendrez une erreur.Pour importer un fichier de sauvegarde .ibd dans la table, copiez le nouveau fichier dans le dossier de la base, et utilisez cette commande :

  ALTER TABLE tbl_name IMPORT TABLESPACE;

  Espaces de tables multiples : mettre un fichier par table .
• Avec la fonction mysql_info() de l'API C, vous pouvez savoir combien d'enregistrements ont été copiés, et (quand IGNORE est spécifié) combien d'enregistrements ont été effacés à cause de la clef unique. mysql_info() .

mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));

mysql> ALTER TABLE t1 RENAME t2;

mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

mysql> ALTER TABLE t2 ADD d TIMESTAMP;

mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

mysql> ALTER TABLE t2 DROP COLUMN c;

mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
           ADD INDEX (c);

Avec les tables de type MyISAM , si vous ne changez pas la colonne AUTO_INCREMENT , l'indice d'auto-incrémentation ne sera pas affecté. Si vous effacez une colonne AUTO_INCREMENT puis en ajoutez une autre, l'indexation recommencera à partir de 1.