MySQL
ALTER TABELLE

Syntax

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

Bemerkungen

    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'

Storage Engine wechseln; Tisch neu aufbauen; Ändern Sie file_per_table

Wenn beispielsweise t1 derzeit keine InnoDB-Tabelle ist, ändert diese Anweisung ihre Speicher-Engine in InnoDB:

ALTER TABLE t1 ENGINE = InnoDB;

Wenn die Tabelle bereits InnoDB ist, werden die Tabelle und ihre Indizes neu erstellt und haben einen ähnlichen Effekt wie OPTIMIZE TABLE . Möglicherweise verbessern Sie den Speicherplatz.

Wenn sich der Wert von innodb_file_per_table aktuell von dem Wert unterscheidet, der wirksam war, als t1 wurde, wird dies in (oder von) file_per_table konvertiert.

ALTER SPALTE DER TABELLE

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-Tabelle INDEX hinzufügen

Um die Leistung zu verbessern, möchten Sie möglicherweise Spalten zu Spalten hinzufügen

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

Ändern, um zusammengesetzte (mehrere Spalten) Indizes hinzuzufügen

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

Auto-Inkrement-Wert ändern

Das Ändern eines Auto-Inkrement-Werts ist hilfreich, wenn Sie nach einer massiven Löschung keine Lücke in einer AUTO_INCREMENT-Spalte wünschen.

Beispielsweise haben Sie viele unerwünschte (Ankündigungs-) Zeilen in Ihrer Tabelle gepostet, gelöscht, und Sie möchten die Lücke in den Auto-Increment-Werten beheben. Angenommen, der MAX-Wert der Spalte AUTO_INCREMENT ist jetzt 100. Sie können Folgendes verwenden, um den Wert für die automatische Erhöhung festzulegen.

ALTER TABLE your_table_name AUTO_INCREMENT = 101;

Ändern des Typs einer Primärschlüsselspalte

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

Wenn Sie versuchen, den Typ dieser Spalte zu ändern, ohne zuvor den Primärschlüssel zu löschen, führt dies zu einem Fehler.

Spaltendefinition ändern

Um die Definition einer Datenbankspalte zu ändern, kann die folgende Abfrage beispielsweise verwendet werden, wenn wir dieses Datenbankschema haben

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

Um den Typ der age von char in int zu ändern, verwenden wir die folgende Abfrage:

ALTER TABLE users CHANGE age age tinyint UNSIGNED NOT NULL;

Allgemeines Format ist:

ALTER TABLE table_name CHANGE column_name new_column_definition

Umbenennen einer MySQL-Datenbank

Es gibt keinen einzigen Befehl zum Umbenennen einer MySQL-Datenbank. Sie können dies jedoch durch eine einfache Problemumgehung erreichen, indem Sie ein Backup erstellen und wiederherstellen:

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>

Schritte:

1. Kopieren Sie die obigen Zeilen in einen Texteditor.
2. Ersetzen Sie alle Verweise auf <old name> , <new name> und <password> (+ optional root , um einen anderen Benutzer zu verwenden) durch die entsprechenden Werte.
3. Führen Sie nacheinander die Befehlszeile aus (vorausgesetzt, der MySQL-Ordner "bin" befindet sich im Pfad und gibt "y" ein, wenn Sie dazu aufgefordert werden).

Alternative Schritte:

Umbenennen (Verschieben) jeder Tabelle von einer Datenbank in die andere. Tun Sie dies für jede Tabelle:

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

Sie können diese Anweisungen erstellen, indem Sie Folgendes ausführen

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

Warnung. Versuchen Sie nicht, irgendeine Art von Tabelle oder Datenbank auszuführen, indem Sie einfach Dateien im Dateisystem verschieben. Das hat in den alten Tagen von MyISAM gut funktioniert, aber in den neuen Tagen von InnoDB und den Tablespaces wird es nicht funktionieren. Vor allem, wenn das "Data Dictionary" aus dem Dateisystem in System-InnoDB-Tabellen verschoben wird, wahrscheinlich in der nächsten Hauptversion. Um eine PARTITION einer InnoDB-Tabelle zu verschieben (im Gegensatz zum DROPping ), müssen "transportable Tablespaces" verwendet werden. In naher Zukunft wird es nicht einmal eine Akte geben, nach der man greifen kann.

Vertauschen der Namen zweier MySQL-Datenbanken

Die folgenden Befehle können verwendet werden, um die Namen zweier MySQL-Datenbanken ( <db1> und <db2> ) <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

Schritte:

1. Kopieren Sie die obigen Zeilen in einen Texteditor.
2. Ersetzen Sie alle Verweise auf <db1> , <db2> und <password> (+ optional root , um einen anderen Benutzer zu verwenden) durch die entsprechenden Werte.
3. Führen Sie nacheinander die Befehlszeile aus (vorausgesetzt, der MySQL-Ordner "bin" befindet sich im Pfad und gibt "y" ein, wenn Sie dazu aufgefordert werden).

Umbenennen einer MySQL-Tabelle

Das Umbenennen einer Tabelle kann in einem einzigen Befehl erfolgen:

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

Die folgende Syntax macht genau das gleiche:

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

Wenn Sie eine temporäre Tabelle umbenennen, muss die ALTER TABLE Version der Syntax verwendet werden.

Schritte:

1. Ersetzen Sie in der obigen Zeile <old name> und <new name> durch die entsprechenden Werte. Anmerkung: Wenn die Tabelle in eine andere Datenbank dbname , der dbname . tablename Syntax kann für <old name> und / oder <new name> .
2. Führen Sie es in der entsprechenden Datenbank in der MySQL-Befehlszeile oder auf einem Client wie MySQL Workbench aus. Hinweis: Der Benutzer muss über ALTER- und DROP-Berechtigungen für die alte Tabelle und CREATE und INSERT für die neue Tabelle verfügen.

Umbenennen einer Spalte in einer MySQL-Tabelle

Das Umbenennen einer Spalte kann in einer einzigen Anweisung erfolgen, aber neben dem neuen Namen muss auch die "Spaltendefinition" (dh der Datentyp und andere optionale Eigenschaften wie Nullwertigkeit, automatische Inkrementierung usw.) angegeben werden.

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

Schritte:

1. Öffnen Sie die MySQL-Befehlszeile oder einen Client wie MySQL Workbench.
2. Führen Sie die folgende Anweisung aus: SHOW CREATE TABLE <table name>; (Ersetzen von <table name> durch den entsprechenden Wert).
3. Notieren Sie sich die gesamte Spaltendefinition für die umzubenennende Spalte (dh alles, was nach dem Namen der Spalte, aber vor dem Komma, das sie vom nächsten Spaltennamen trennt) erscheint .
4. Ersetzen Sie in der obigen Zeile <old name> , <new name> und <column definition> durch die entsprechenden Werte und führen Sie sie aus.