MySQL
ALTER TABLE

Syntaxe

• ALTER [IGNORE] TABLE nom_de_table [ alter_specification [, alter_specification] ...] [partition_options]

Remarques

    alter_specification: table_options
      | ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]
      | ADD [COLUMN] (col_name column_definition,...)
      | ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ...
      | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ...
      | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ...
      | ADD FULLTEXT [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ...
      | ADD SPATIAL [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ...
      | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition
      | ALGORITHM [=] {DEFAULT|INPLACE|COPY}
      | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
      | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
      | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
      | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
      | DROP [COLUMN] col_name
      | DROP PRIMARY KEY
      | DROP {INDEX|KEY} index_name
      | DROP FOREIGN KEY fk_symbol
      | DISABLE KEYS
      | ENABLE KEYS
      | RENAME [TO|AS] new_tbl_name
      | RENAME {INDEX|KEY} old_index_name TO new_index_name
      | ORDER BY col_name [, col_name] ...
      | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
      | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
      | DISCARD TABLESPACE
      | IMPORT TABLESPACE
      | FORCE
      | {WITHOUT|WITH} VALIDATION
      | ADD PARTITION (partition_definition)
      | DROP PARTITION partition_names
      | DISCARD PARTITION {partition_names | ALL} TABLESPACE
      | IMPORT PARTITION {partition_names | ALL} TABLESPACE
      | TRUNCATE PARTITION {partition_names | ALL}
      | COALESCE PARTITION number
      | REORGANIZE PARTITION partition_names INTO (partition_definitions)
      | EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH|WITHOUT} VALIDATION]
      | ANALYZE PARTITION {partition_names | ALL}
      | CHECK PARTITION {partition_names | ALL}
      | OPTIMIZE PARTITION {partition_names | ALL}
      | REBUILD PARTITION {partition_names | ALL}
      | REPAIR PARTITION {partition_names | ALL}
      | REMOVE PARTITIONING
      | UPGRADE PARTITIONING
   index_col_name: col_name [(length)] [ASC | DESC]
   index_type: USING {BTREE | HASH}
   index_option: KEY_BLOCK_SIZE [=] value
      | index_type
      | WITH PARSER parser_name
      | COMMENT 'string'

Changement de moteur de stockage; reconstruire la table; changez file_per_table

Par exemple, si t1 n'est actuellement pas une table InnoDB, cette instruction modifie son moteur de stockage en InnoDB:

ALTER TABLE t1 ENGINE = InnoDB;

Si la table est déjà InnoDB, cela reconstruira la table et ses index et aura un effet similaire à OPTIMIZE TABLE . Vous pouvez obtenir une amélioration de l'espace disque.

Si la valeur de innodb_file_per_table est actuellement différente de celle utilisée lors de la construction de t1 , cela convertira en (ou depuis) ​​file_per_table.

ALTER COLONNE DE TABLE

CREATE DATABASE stackoverflow;

USE stackoverflow;

Create table stack(
    id_user int NOT NULL,
    username varchar(30) NOT NULL,
    password varchar(30) NOT NULL
);

ALTER TABLE stack ADD COLUMN submit date NOT NULL; -- add new column
ALTER TABLE stack DROP COLUMN submit; -- drop column
ALTER TABLE stack MODIFY submit DATETIME NOT NULL; -- modify type column
ALTER TABLE stack CHANGE submit submit_date DATETIME NOT NULL; -- change type and name of column
ALTER TABLE stack ADD COLUMN mod_id INT NOT NULL AFTER id_user; -- add new column after existing column

ALTER table ajouter INDEX

Pour améliorer les performances, vous pouvez ajouter des index aux colonnes.

ALTER TABLE TABLE_NAME ADD INDEX `index_name` (`column_name`)

modification pour ajouter des index composites (plusieurs colonnes)

ALTER TABLE TABLE_NAME ADD INDEX `index_name` (`col1`,`col2`)

Modifier la valeur d'incrémentation automatique

La modification d'une valeur d'incrémentation automatique est utile lorsque vous ne souhaitez pas créer d'espace dans une colonne AUTO_INCREMENT après une suppression massive.

Par exemple, vous avez beaucoup de lignes indésirables (publicité) affichées dans votre table, vous les avez supprimées et vous souhaitez corriger l'écart en valeurs d'incrémentation automatique. Supposons que la valeur MAX de la colonne AUTO_INCREMENT est de 100 maintenant. Vous pouvez utiliser les éléments suivants pour corriger la valeur d'incrémentation automatique.

ALTER TABLE your_table_name AUTO_INCREMENT = 101;

Modification du type d'une colonne de clé primaire

ALTER TABLE fish_data.fish DROP PRIMARY KEY;
ALTER TABLE fish_data.fish MODIFY COLUMN fish_id DECIMAL(20,0) NOT NULL PRIMARY KEY;

Une tentative de modification du type de cette colonne sans supprimer d'abord la clé primaire entraînerait une erreur.

Changer la définition de la colonne

La modification de la définition d'une colonne de base de données, la requête ci-dessous peut être utilisée par exemple, si nous avons ce schéma de base de données

users (
    firstname varchar(20),
    lastname varchar(20),
    age char(2)
)

Pour changer le type de colonne age de char en int , nous utilisons la requête ci-dessous:

ALTER TABLE users CHANGE age age tinyint UNSIGNED NOT NULL;

Le format général est:

ALTER TABLE table_name CHANGE column_name new_column_definition

Renommer une base de données MySQL

Il n'y a pas de commande unique pour renommer une base de données MySQL, mais une solution simple peut être utilisée pour y parvenir en sauvegardant et en restaurant:

mysqladmin -uroot -p<password> create <new name>
mysqldump -uroot -p<password> --routines <old name> | mysql -uroot -pmypassword <new name>
mysqladmin -uroot -p<password> drop <old name>

Pas:

1. Copiez les lignes ci-dessus dans un éditeur de texte.
2. Remplacez toutes les références à <old name> , <new name> et <password> (+ éventuellement root pour utiliser un autre utilisateur) avec les valeurs appropriées.
3. Exécutez un par un sur la ligne de commande (en supposant que le dossier "bin" de MySQL se trouve dans le chemin et que vous entrez "y" lorsque vous y êtes invité).

Étapes alternatives:

Renommez (déplacez) chaque table d'un db à l'autre. Faites ceci pour chaque table:

RENAME TABLE `<old db>`.`<name>` TO  `<new db>`.`<name>`;

Vous pouvez créer ces déclarations en faisant quelque chose comme

SELECT CONCAT('RENAME TABLE old_db.', table_name, ' TO ',
                           'new_db.', table_name)
    FROM information_schema.TABLES
    WHERE table_schema = 'old_db';

Attention. N'essayez pas de créer une table ou une base de données en déplaçant simplement des fichiers sur le système de fichiers. Cela a bien fonctionné dans le bon vieux temps de MyISAM, mais dans les nouveaux jours d'InnoDB et des tablespaces, cela ne fonctionnera pas. Surtout lorsque le "Dictionnaire de données" est déplacé du système de fichiers dans les tables InnoDB du système, probablement dans la prochaine version majeure. Déplacer (par opposition à simplement DROPping ) une PARTITION d'une table InnoDB nécessite l'utilisation de "tablespaces transportables". Dans un avenir proche, il n'y aura même pas de fichier à atteindre.

Echanger les noms de deux bases de données MySQL

Les commandes suivantes peuvent être utilisées pour permuter les noms de deux bases de données MySQL ( <db1> et <db2> ):

mysqladmin -uroot -p<password> create swaptemp
mysqldump -uroot -p<password> --routines <db1> | mysql -uroot -p<password> swaptemp
mysqladmin -uroot -p<password> drop <db1>
mysqladmin -uroot -p<password> create <db1>
mysqldump -uroot -p<password> --routines <db2> | mysql -uroot -p<password> <db1>
mysqladmin -uroot -p<password> drop <db2>
mysqladmin -uroot -p<password> create <db2>
mysqldump -uroot -p<password> --routines swaptemp | mysql -uroot -p<password> <db2>
mysqladmin -uroot -p<password> drop swaptemp

Pas:

1. Copiez les lignes ci-dessus dans un éditeur de texte.
2. Remplacez toutes les références à <db1> , <db2> et <password> (+ éventuellement root pour utiliser un autre utilisateur) avec les valeurs appropriées.
3. Exécutez un par un sur la ligne de commande (en supposant que le dossier "bin" de MySQL se trouve dans le chemin et que vous entrez "y" lorsque vous y êtes invité).

Renommer une table MySQL

Renommer une table peut être fait en une seule commande:

RENAME TABLE `<old name>` TO `<new name>`;

La syntaxe suivante fait exactement la même chose:

ALTER TABLE `<old name>` RENAME TO `<new name>`;

Si vous renommez une table temporaire, vous devez utiliser la version ALTER TABLE de la syntaxe.

Pas:

1. Remplacez <old name> et <new name> dans la ligne ci-dessus par les valeurs correspondantes. Remarque: Si la table est déplacée vers une autre base de données, le dbname . tablename syntaxe de tablename peut être utilisée pour <old name> et / ou <new name> .
2. Exécutez-le sur la base de données appropriée sur la ligne de commande MySQL ou sur un client tel que MySQL Workbench. Remarque: L'utilisateur doit disposer des privilèges ALTER et DROP sur l'ancienne table et CREATE et INSERT sur la nouvelle.

Renommer une colonne dans une table MySQL

Renommer une colonne peut être fait dans une seule instruction, mais en plus du nouveau nom, la "définition de colonne" (c'est-à-dire son type de données et d'autres propriétés facultatives telles que nullité, incrémentation automatique, etc.) doit également être spécifiée.

ALTER TABLE `<table name>` CHANGE `<old name>` `<new name>` <column definition>;

Pas:

1. Ouvrez la ligne de commande MySQL ou un client tel que MySQL Workbench.
2. Exécutez l'instruction suivante: SHOW CREATE TABLE <table name>; (en remplaçant <table name> par la valeur correspondante).
3. Notez la définition complète de la colonne à renommer (c'est-à-dire tout ce qui apparaît après le nom de la colonne mais avant la virgule le séparant du nom de colonne suivant) .
4. Remplacez <old name> , <new name> et <column definition> dans la ligne ci-dessus par les valeurs appropriées, puis exécutez-la.