MySQL
ALTER TABLE

Sintassi

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

Osservazioni

    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'

Cambiare il motore di archiviazione; ricostruire la tabella; cambia file_per_table

Ad esempio, se t1 è attualmente una tabella InnoDB, questa istruzione cambia il suo motore di archiviazione in InnoDB:

ALTER TABLE t1 ENGINE = InnoDB;

Se la tabella è già InnoDB, questo ricostruirà la tabella e i suoi indici e avrà un effetto simile a OPTIMIZE TABLE . Potresti ottenere un miglioramento dello spazio sul disco.

Se il valore di innodb_file_per_table è attualmente diverso dal valore in vigore quando t1 stato creato, questo verrà convertito in (o da) file_per_table.

ALTER COLONNA DELLA TABELLA

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

La tabella ALTER aggiunge INDICE

Per migliorare le prestazioni si potrebbe voler aggiungere indici alle colonne

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

alterazione per aggiungere indici compositi (a colonne multiple)

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

Modifica il valore di incremento automatico

La modifica di un valore di incremento automatico è utile quando non si desidera uno spazio vuoto in una colonna AUTO_INCREMENT dopo una cancellazione massiccia.

Ad esempio, hai ottenuto un sacco di righe indesiderate (pubblicità) pubblicate nella tua tabella, le hai cancellate e vuoi correggere il divario nei valori di incremento automatico. Supponiamo che il valore MAX della colonna AUTO_INCREMENT sia 100 ora. È possibile utilizzare quanto segue per correggere il valore di incremento automatico.

ALTER TABLE your_table_name AUTO_INCREMENT = 101;

Modifica del tipo di una colonna chiave primaria

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

Un tentativo di modificare il tipo di questa colonna senza prima rilasciare la chiave primaria comporterebbe un errore.

Cambia la definizione della colonna

Per cambiare la definizione di una colonna db, la query seguente può essere utilizzata, ad esempio, se abbiamo questo schema db

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

Per cambiare il tipo di colonna age da char a int , utilizziamo la query seguente:

ALTER TABLE users CHANGE age age tinyint UNSIGNED NOT NULL;

Il formato generale è:

ALTER TABLE table_name CHANGE column_name new_column_definition

Rinominare un database MySQL

Non esiste un singolo comando per rinominare un database MySQL, ma una soluzione semplice può essere utilizzata per ottenere ciò mediante il backup e il ripristino:

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>

passi:

1. Copia le righe sopra in un editor di testo.
2. Sostituisci tutti i riferimenti a <old name> , <new name> e <password> (+ facoltativamente root per utilizzare un utente diverso) con i valori pertinenti.
3. Esegui uno alla volta sulla riga di comando (supponendo che la cartella "bin" di MySQL si trovi nel percorso e inserisca "y" quando richiesto).

Passaggi alternativi:

Rinominare (spostare) ogni tabella da un db all'altro. Fatelo per ogni tabella:

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

Puoi creare quelle affermazioni facendo qualcosa di simile

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

Avvertimento. Non tentare di creare alcun tipo di tabella o database semplicemente spostando i file sul file system. Questo ha funzionato bene ai vecchi tempi di MyISAM, ma nei nuovi giorni di InnoDB e tablespace, non funzionerà. Soprattutto quando il "Data Dictionary" viene spostato dal filesystem alle tabelle InnoDB del sistema, probabilmente nella prossima major release. Spostando (al contrario di solo DROPping ) una PARTITION di una tabella InnoDB richiede l'uso di "tablespace trasportabili". Nel prossimo futuro, non ci sarà nemmeno un file da raggiungere.

Scambiare i nomi di due database MySQL

I seguenti comandi possono essere utilizzati per scambiare i nomi di due database MySQL ( <db1> e <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

passi:

1. Copia le righe sopra in un editor di testo.
2. Sostituisci tutti i riferimenti a <db1> , <db2> e <password> (+ facoltativamente root per utilizzare un utente diverso) con i valori pertinenti.
3. Esegui uno alla volta sulla riga di comando (supponendo che la cartella "bin" di MySQL si trovi nel percorso e inserisca "y" quando richiesto).

Rinominare una tabella MySQL

È possibile rinominare una tabella in un singolo comando:

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

La seguente sintassi fa esattamente la stessa cosa:

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

Se si rinomina una tabella temporanea, è necessario utilizzare la versione ALTER TABLE della sintassi.

passi:

1. Sostituisci <old name> e <new name> nella riga sopra con i valori pertinenti. Nota: se la tabella viene spostata in un altro database, il dbname db. tablename sintassi tablename può essere utilizzata per <old name> e / o <new name> .
2. Eseguilo sul database pertinente nella riga di comando MySQL o su un client come MySQL Workbench. Nota: l'utente deve disporre dei privilegi ALTER e DROP sulla vecchia tabella e CREARE e INSERIRE su quello nuovo.

Rinominare una colonna in una tabella MySQL

La ridenominazione di una colonna può essere eseguita in una singola istruzione ma, oltre al nuovo nome, è necessario specificare anche la "definizione della colonna" (ovvero il suo tipo di dati e altre proprietà facoltative come il nullability, l'incremento automatico ecc.).

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

passi:

1. Apri la riga di comando MySQL o un client come MySQL Workbench.
2. Esegui la seguente dichiarazione: SHOW CREATE TABLE <table name>; (sostituendo <table name> con il valore pertinente).
3. Annotare l'intera definizione di colonna per la colonna da rinominare (ovvero tutto ciò che appare dopo il nome della colonna ma prima della virgola che lo separa dal nome della colonna successiva) .
4. Sostituisci <old name> , <new name> e <column definition> nella riga sopra con i valori pertinenti e quindi eseguilo.