7.7 ALTER TABLE

ALTER [IGNORE] TABLE Nom_table alter_spec [, alter_spec ...]
alter_specification:
        ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
  ou    ADD INDEX [Nom_index] (index_Nom_col,...)
  ou    ADD PRIMARY KEY (index_Nom_col,...)
  ou    ADD UNIQUE [Nom_index] (index_Nom_col,...)
  ou    ALTER [COLUMN] Nom_col {SET DEFAULT literal | DROP DEFAULT}
  ou    CHANGE [COLUMN] old_Nom_col create_definition
  ou    MODIFY [COLUMN] create_definition
  ou    DROP [COLUMN] Nom_col
  ou    DROP PRIMARY KEY
  ou    DROP INDEX key_name
  ou    RENAME [AS] new_Nom_table
  ou    table_option

ALTER TABLE modifie la structure d'une table existante. Par exemple, il est possible d'ajouter ou d'effacer des colonnes, de créer et détruire des index, de changer le type d'une colonne ou de la renommer, voire même de renommer la table elle-même. Il est aussi possible de changer le commentaire de la table et son type. Voir CREATE TABLE.

Si, lors d'une modification de type de colonne, la commande DESCRIBE Nom_table indique que la colonne n'a pas changé de type, il est possible que MySQL ait ignoré la modification pour une des raisons décrites dans 7.6.1 Modifications automatiques de type de colonne. Par exemple, si il existe plusieurs colonne de type de longueur variable, rien de sert de tenter de forcer une colonne du type VARCHAR à CHAR.

ALTER TABLE fonctionne en effectuant une copie temporaire de la table originale. La modification est effectuée sur la copie, puis l'originale est remplacée par la copie modifiée. Toutes les mises à jours sont automatiquement appelé, et sont faites dans un mode sans erreur. Pendant la modification, la table originale est toujours accessible en lecture par les autres clients. Les mises à jour et les écritures sont reportées jusqu'à la fin de la modification.

• Pour pouvoir utiliser la commande ALTER TABLE, il faut avoir les droits pour select, insert, delete, update, create et drop sur la table.
• IGNORE est une extension MySQL de la norme ANSI SQL92. Il permet de contrôler la façon avec laquelle réagit si il trouve des doublons dans une colonne de clés uniques. Si n'est pas précisé, la copie est annulée à la première erreur, et la modification est annulée. Si est précisé, alors la première occurrence d'une clés en double sera utilisée, les autres occurrences étant ignorées et effacées.
• Il est possible d'utiliser plusieurs clauses ADD, ALTER, DROP et CHANGE dans une commande ALTER TABLE. C'est une extension de MySQL à la norme ANSI SQL92 : cette dernière ne permet qu'une seule de ces clauses à chaque commande.
• CHANGE Nom_col, DROP Nom_col et DROP INDEX sont des extensions de MySQL à la norme ANSI SQL92
• CHANGE Nom_col, DROP Nom_col et DROP INDEX sont des extensions de MySQL à la norme ANSI SQL92 .
• MODIFY est une extension Oracle à ALTER TABLE.
• L'option COLUMN est simplement de la décoration et peut être ignoré.
• La commande '' ALTER TABLE Nom_table RENAME AS new_name '' sans aucune autre option, permet de renommer la table. MySQL va simplement renommer les fichiers correspondant à la table Nom_table. Il n'y a pas de création de table temporaire.
• La clause create_definition utilise la même syntaxe que les clauses ADD et CHANGE, ainsi que CREATE TABLE. Cette inclus le nom de la colonne, et pas seulement le type de colonne. Voir CREATE TABLE.
• La commande CHANGE old_Nom_col create_definition permet de changer le nom d'une colonne. Pour cela, il faut spécifier l'ancien et le nouveau nom de la colonne, ainsi que le type courant de la colonne. Par exemple, pour renommer une colonne de type INTEGER de 'a' en 'b', la commande suivante est valable :

mysql> ALTER TABLE t1 CHANGE a b INTEGER;

Pour changer le type de la colonne mais pas son nom, la syntaxe de CHANGE requiert deux noms de colonnes, même si ils sont identiques. Par exemple :

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

Cependant, à partir de MySQL 3.22.16a, il est possible d'utiliser la clause MODIFY pour changer le type de la colonne sans le renommer:

mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

• Lors de l'utilisation de CHANGE ou MODIFY pour réduire la taille d'une colonne qui possède un index (par exemple, un index sur les 10 premiers caractères d'une colonne de type VARCHAR ), il est impossible de rendre la colonne plus petite que ne le requiert l'index.
• En changeant le type d'une colonne avec CHANGE ou MODIFY, MySQL essaie de convertir au mieux les informations d'un type à l'autre.
• A partir de MySQL 3.22, il est possible d'utiliser FIRST ou ADD ... AFTER Nom_col pour ajouter une colonne à une position donnée, dans une table. Par défaut, l'ajout de fait après la dernière colonne.
• ALTER COLUMN spécifie une nouvelle valeur par défaut, ou bien efface l'ancienne valeur. Si la valeur par défaut de la colonne est effacée, et que la colonne peut être NULL, alors la nouvelle valeur par défaut est NULL.Si la colonne ne peut pas être NULL, alors MySQL assigne une valeur par défaut arbitraire, comme décrit dans le paragraphe CREATE TABLE.
• DROP INDEX efface un index. C'est une extension de MySQL à la norme ANSI SQL92 .
• Lorsqu'une colonne est effacée d'une table, la colonne est aussi effacée de toutes les index qui l'utilise. Si toutes les colonnes qui composent un index sont effacées, l'index disparaît aussi.
• DROP PRIMARY KEY efface la colonne qui porte l'attribut PRIMARY KEY . Si une telle colonne n'existe pas, la première colonne de type UNIQUE est effacée à la place. (MySQL utilise la première colonne UNIQUE comme PRIMARY KEY si aucune PRIMARY KEY n'est spécifiée.)
• Avec l'API C mysql_info(), il est possible de savoir combien de ligne ont été copiée, et (quand l'option IGNORE était mise), le nombre de lignes qui furent effacées, à cause de la duplication de clés.
• Les attributs FOREIGN KEY, CHECK et REFERENCES ne font en réalité rien de spécial. Ils sont uniquement fournis pour assurer la compatibilité et la portabilité de programme SQL et de les faire tourner avec MySQL.

Voici quelques exemples d'utilisation de la commande ALTER TABLE. On supposera que la table t1, créée ci-dessous, existe :

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

Renomme la table de t1 et t2:

mysql> ALTER TABLE t1 RENAME t2;

Change le type de la colonne a de INTEGER en TINYINT NOT NULL (mais ne change pas le nom), et change le type de la colonne b de CHAR(10) en CHAR(20) et renomme cette colonne en c.

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

Ajoute une nouvelle colonne d, de type TIMESTAMP.

mysql> ALTER TABLE t2 ADD d TIMESTAMP;

Ajoute un index sur la colonne d, et fait de la colonne a une PRIMARY KEY

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

Efface la colonne c:

mysql> ALTER TABLE t2 DROP COLUMN c;

Ajoute une nouvelle colonne de type AUTO_INCREMENT integer , nommée c:

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

Il faut remarquer que la colonne c a été indexée, car les colonnes de type AUTO_INCREMENT doivent être indexée, ce qui implique c doit avoir l'attribut NOT NULL, puisque les colonnes indexées ne peuvent pas avoir de valeur à NULL.