MySQL
ZMIEŃ TABELĘ

Składnia

• ALTER [IGNORE] TABLE nazwa_bloku [ specyfikacja_zmiany [, specyfikacja_zmiany] ...] [opcje_partycji]

Uwagi

    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'

Zmiana silnika pamięci masowej; odbudować stół; zmień plik_tabela

Na przykład, jeśli t1 jest obecnie tabelą InnoDB, ta instrukcja zmienia silnik pamięci na InnoDB:

ALTER TABLE t1 ENGINE = InnoDB;

Jeśli tabela ma już wartość InnoDB, spowoduje to przebudowanie tabeli i jej indeksów oraz efekt podobny do OPTIMIZE TABLE . Możesz uzyskać poprawę przestrzeni dyskowej.

Jeśli wartość innodb_file_per_table jest obecnie różna od wartości obowiązującej podczas budowania t1 , zostanie ona przekonwertowana na (lub z) file_per_table.

ZMIEŃ KOLUMNĘ TABELI

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

Tabela ALTER dodaj INDEKS

Aby poprawić wydajność, można dodać indeksy do kolumn

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

zmiana w celu dodania indeksów złożonych (wielu kolumn)

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

Zmień wartość automatycznego przyrostu

Zmiana wartości automatycznego przyrostu jest przydatna, gdy nie chcesz przerwy w kolumnie AUTO_INCREMENT po masowym usunięciu.

Na przykład, masz wiele niechcianych wierszy (reklam) opublikowanych w tabeli, usunąłeś je i chcesz naprawić lukę w wartościach automatycznego przyrostu. Załóżmy, że wartość MAX kolumny AUTO_INCREMENT wynosi teraz 100. Aby naprawić wartość automatycznego przyrostu, możesz użyć następujących elementów.

ALTER TABLE your_table_name AUTO_INCREMENT = 101;

Zmiana typu kolumny klucza podstawowego

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

Próba zmodyfikowania typu tej kolumny bez uprzedniego upuszczenia klucza podstawowego spowoduje błąd.

Zmień definicję kolumny

Zmień definicję kolumny db, poniższe zapytanie może być użyte na przykład, jeśli mamy ten schemat db

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

Aby zmienić typ kolumny age z char na int , używamy poniższego zapytania:

ALTER TABLE users CHANGE age age tinyint UNSIGNED NOT NULL;

Ogólny format to:

ALTER TABLE table_name CHANGE column_name new_column_definition

Zmiana nazwy bazy danych MySQL

Nie ma jednego polecenia, aby zmienić nazwę bazy danych MySQL, ale można to zrobić za pomocą prostego obejścia, wykonując kopię zapasową i przywracając:

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>

Kroki:

1. Skopiuj powyższe wiersze do edytora tekstu.
2. Zastąp wszystkie odniesienia do <old name> , <new name> i <password> (+ opcjonalnie root aby użyć innego użytkownika) odpowiednimi wartościami.
3. Wykonaj jeden po drugim w linii poleceń (zakładając, że folder „bin” MySQL znajduje się na ścieżce i wpisując „y” po wyświetleniu monitu).

Alternatywne kroki:

Zmień nazwę (przenieś) każdą tabelę z jednej bazy danych na drugą. Zrób to dla każdego stołu:

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

Możesz tworzyć te instrukcje, robiąc coś podobnego

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

Ostrzeżenie. Nie próbuj tworzyć żadnych tabel ani baz danych, po prostu przenosząc pliki w systemie plików. To działało dobrze w dawnych czasach tylko MyISAM, ale w nowych czasach InnoDB i obszarów tabel nie będzie działać. Zwłaszcza, gdy „Słownik danych” zostanie przeniesiony z systemu plików do systemowych tabel InnoDB, prawdopodobnie w następnej głównej wersji. Przenoszenie (w przeciwieństwie do tylko DROPping ) do PARTITION z tabeli InnoDB wymaga stosowania tabel „przenośne”. W najbliższej przyszłości nie będzie nawet pliku, do którego można by sięgnąć.

Zamiana nazw dwóch baz danych MySQL

Do zamiany nazw dwóch baz danych MySQL ( <db1> i <db2> ) można użyć następujących poleceń:

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

Kroki:

1. Skopiuj powyższe wiersze do edytora tekstu.
2. Zastąp wszystkie odniesienia do <db1> , <db2> i <password> (+ opcjonalnie root aby użyć innego użytkownika) odpowiednimi wartościami.
3. Wykonaj jeden po drugim w linii poleceń (zakładając, że folder „bin” MySQL znajduje się na ścieżce i wpisując „y” po wyświetleniu monitu).

Zmiana nazwy tabeli MySQL

Zmiana nazwy tabeli może odbywać się za pomocą jednego polecenia:

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

Poniższa składnia robi dokładnie to samo:

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

W przypadku zmiany nazwy tabeli tymczasowej należy użyć wersji składni ALTER TABLE .

Kroki:

1. Zamień <old name> i <new name> w powyższym wierszu na odpowiednie wartości. Uwaga: Jeśli tabela jest przenoszona do innej bazy danych, nazwa dbname . składni tablename można użyć dla <old name> i / lub <new name> .
2. Uruchom go w odpowiedniej bazie danych w wierszu polecenia MySQL lub na kliencie, takim jak MySQL Workbench. Uwaga: Użytkownik musi mieć uprawnienia ALTER i DROP na starej tabeli oraz CREATE i INSERT na nowej.

Zmiana nazwy kolumny w tabeli MySQL

Zmiana nazwy kolumny może odbywać się w pojedynczej instrukcji, ale oprócz nowej nazwy należy również określić „definicję kolumny” (tj. Jej typ danych i inne opcjonalne właściwości, takie jak dopuszczalność zerowania, automatyczne zwiększanie itd.).

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

Kroki:

1. Otwórz wiersz poleceń MySQL lub klienta, takiego jak MySQL Workbench.
2. Uruchom następującą instrukcję: SHOW CREATE TABLE <table name>; (zastępując <table name> odpowiednią wartością).
3. Zanotuj całą definicję kolumny, której nazwę należy zmienić (tj. Wszystko, co pojawia się po nazwie kolumny, ale przed przecinkiem oddzielającym ją od nazwy następnej kolumny) .
4. Zastąp <old name> , <new name> i <column definition> w powyższym wierszu odpowiednimi wartościami, a następnie wykonaj je.