W tym przewodnik do nauki nauczysz się używać Instrukcja MySQL ALTER TABLE aby dodać kolumnę, zmień kolumnę, usuń kolumnę, zmień nazwę kolumny lub zmień nazwę tabeli (ze składnią i przykładami).

Opis

Instrukcja MySQL ALTER TABLE służy do dodawania, zmiany lub usuwania kolumn w tabeli. Instrukcja MySQL ALTER TABLE służy również do zmiany nazwy tabeli.

Dodaj kolumnę do tabeli

Składnia

Składnia dodawania kolumny do tabeli MySQL (za pomocą instrukcji ALTER TABLE) to:

ALTER TABLE nazwa_tabeli

PIERWSZY | AFTER nazwa_kolumny jest opcjonalna. Mówi MySQL, gdzie w tabeli została utworzona kolumna. Jeśli ten parametr nie zostanie określony, nowa kolumna zostanie dodana na końcu tabeli.

Przykład

Spójrzmy na przykład, który pokazuje, jak dodać kolumnę do tabeli MySQL za pomocą instrukcji ALTER TABLE.
Na przykład:

MySQL

ALTER TABLE kontakty ADD nazwisko varchar(40) NIE NULL PO contact_id;

ALTER TABLE kontakty PO contact_id;

Ten przykład MySQL ALTER TABLE doda kolumnę o nazwie last_name do tabeli kontaktów. Zostanie utworzona jako kolumna NOT NULL i pojawi się w tabeli po polu contact_id.

Dodaj wiele kolumn do tabeli

Składnia

Składnia dodawania wielu kolumn do tabeli MySQL (za pomocą instrukcji ALTER TABLE) to:

ALTER TABLE nazwa_tabeli
DODAJ nową_nazwa_kolumny definicja_kolumny

DODAJ nową_nazwa_kolumny definicja_kolumny
[ PIERWSZY | PO nazwa_kolumny ],
…
;

table_name to nazwa tabeli do zmiany.
nowa_nazwa_kolumny to nazwa nowej kolumny do dodania do tabeli.
column_definition - typ danych i definicja kolumny (NULL lub NOT NULL itp.).
PIERWSZY | AFTER nazwa_kolumny jest opcjonalna. Mówi MySQL, gdzie w tabeli została utworzona kolumna. Jeśli ten parametr nie zostanie określony, nowa kolumna zostanie dodana na końcu tabeli.

Przykład

Spójrzmy na przykład, który pokazuje, jak dodać wiele kolumn do tabeli MySQL za pomocą instrukcji ALTER TABLE.
Na przykład:

MySQL

ALTER TABLE kontakty ADD nazwisko varchar(40) NOT NULL AFTER contact_id, ADD imię varchar(35) NULL AFTER nazwisko;

ALTER TABLE kontakty DODAJ last_namevarchar(40)NOT NULL PO contact_id, DODAJ first_namevarchar(35)NULL PO nazwisko;

Ten przykład ALTER TABLE doda dwie kolumny do tabeli kontaktów, nazwisko i imię .

Pole last_name zostanie utworzone jako kolumna varchar (40) NOT NULL i pojawi się w tabeli kontaktów po kolumnie contact_id. Kolumna first_name zostanie utworzona jako kolumna NULL varchar(35) i pojawi się w tabeli po kolumnie last_name.

Zmień kolumnę w tabeli

Składnia

Składnia zmiany kolumny w tabeli MySQL (za pomocą instrukcji ALTER TABLE) to:

ALTER TABLE nazwa_tabeli

[ PIERWSZY | PO nazwa_kolumny ];

table_name to nazwa tabeli do zmiany.

Przykład

Spójrzmy na przykład, który pokazuje, jak zmienić kolumnę w tabeli MySQL za pomocą instrukcji ALTER TABLE.
Na przykład:

MySQL

ALTER TABLE kontakty MODIFY nazwisko varchar(50) NULL;

ALTER TABLE kontakty MODYFIKUJ last_namevarchar(50)NULL ;

Ten przykład ALTER TABLE zmieni kolumnę o nazwie last_name na typ danych varchar(50) i ustawi ją na wartości NULL.

Zmień wiele kolumn w tabeli

Składnia

Składnia do zmiany wielu kolumn w tabeli MySQL (przy użyciu instrukcji ALTER TABLE) to:

ALTER TABLE nazwa_tabeli
MODIFY nazwa_kolumny definicja_kolumny
[ PIERWSZY | PO nazwa_kolumny ],
MODIFY nazwa_kolumny definicja_kolumny
[ PIERWSZY | PO nazwa_kolumny ],
…
;

table_name to nazwa tabeli do zmiany.
nazwa_kolumny to nazwa kolumny do zmiany w tabeli.
column_definition - zmieniony typ danych i definicja kolumny (NULL lub NOT NULL itp.).
PIERWSZY | AFTER nazwa_kolumny jest opcjonalna. Mówi MySQL, gdzie w tabeli znajduje się kolumna, jeśli chcesz zmienić jej pozycję.

Przykład

Spójrzmy na przykład, który pokazuje, jak zmienić wiele kolumn w tabeli MySQL za pomocą instrukcji ALTER TABLE.

MySQL

ALTER TABLE kontakty MODIFY nazwisko varchar(55) NULL AFTER typ_kontaktu, MODIFY imię varchar(30) NOT NULL;

ALTER TABLE kontakty ZMODYFIKUJ last_namevarchar(55)NULL PO contact_type, MODIFY first_namevarchar(30) NOT NULL ;

Ten przykład ALTER TABLE zmieni dwie kolumny w tabeli kontaktów, nazwisko i imię .
Pole last_name zostanie zmienione na kolumnę NULL varchar(55) i pojawi się w tabeli po kolumnie contact_type. Kolumna first_name zostanie zmieniona na kolumnę varchar (30) NOT NULL (i nie zmieni pozycji w definicji tabeli kontaktów, ponieważ nie określono FIRST | AFTER).

Usuwanie kolumny z tabeli

Składnia

Składnia usuwania kolumny z tabeli w MySQL (przy użyciu instrukcji ALTER TABLE) to:
Na przykład:

ALTER TABLE nazwa_tabeli
DROP COLUMN nazwa_kolumny;

table_name to nazwa tabeli do zmiany.
nazwa_kolumny to nazwa kolumny do usunięcia z tabeli.

Przykład

Spójrzmy na przykład, który pokazuje, jak usunąć kolumnę z tabeli w MySQL za pomocą instrukcji ALTER TABLE.
Na przykład:

MySQL

ALTER TABLE kontakty DROP COLUMN contact_type;

Można wyróżnić następujące poziomy ograniczeń kontroli:

• poziom atrybutu (kolumny),
• poziom krotki (ciąg),
• poziom relacji (tabele).

W ograniczeniu na poziomie kolumny sprawdzana jest wartość tylko jednej pojedynczej kolumny, innymi słowy, w ograniczeniu tego typu istnieje odniesienie tylko do jednej kolumny tabeli, której definicja zawiera to ograniczenie. Aby podać przykład takiego ograniczenia, wróćmy do schematu „Firma komputerowa”. W tabeli Produkt kolumna typu może zawierać jedną z trzech wartości. Możemy uniemożliwić wprowadzanie jakichkolwiek innych informacji w tej kolumnie z następującym ograniczeniem:

CHECK (wpisz IN ("drukarka" , "pc" , "laptop") )

Cofnijmy się o krok, aby zapoznać się z instrukcją ALTER TABLE, która pozwoli nam zmieniać strukturę tabeli bez konieczności jej każdorazowego ponownego tworzenia. Jest to tym ważniejsze, że zmiana struktury może być wymagana, gdy tabela zawiera już dane.

Za pomocą instrukcji ALTER TABLE można dodawać lub usuwać kolumny, wartości domyślne i ograniczenia.

W obecnie jesteśmy zainteresowani dodaniem ograniczenia na kolumnę typu, więc najpierw podajemy składnię operatora do dodania ograniczenia:

ZMIEŃ TABELĘ

DODAJ WIĄZANIE ;

Dodajmy teraz nasze ograniczenie i zobaczmy, jak to działa.

ALTER TABELA Produkt

DODAJ OGRANICZENIE chk_type CHECK (wpisz IN ("pc" , "laptop" , "printer") ) ;

Aby upewnić się, że wiązanie działa zgodnie z oczekiwaniami, spróbujmy dodać nowy typ modelu:

WSTAWIĆ W WARTOŚCI produktu ("A" , 1122 , "notebook" );

Zgodnie z oczekiwaniami w odpowiedzi otrzymamy komunikat o błędzie:

Instrukcja INSERT kolidowała z ograniczeniem CHECK „chk_type”. Konflikt wystąpił w bazie danych "learn", tabeli "dbo.product", kolumnie "type". Oświadczenie zostało zakończone.

(Instrukcja INSERT była w konflikcie z ograniczeniem CHECK „chk_type”. Konflikt wystąpił w bazie danych „learn”, tabela „dbo.product”, kolumna „type”. Ta instrukcja została przerwana.)

Jak można się domyślić, ograniczenie na poziomie wiersza zawiera odniesienia do wielu kolumn. W takim przypadku ograniczenie jest sprawdzane dla każdej zmienionej linii osobno. Wiersz można dodać (lub zmienić), jeśli ograniczenie nie zostanie naruszone.

Jako przykład zabrońmy producentowi Z produkowania czegokolwiek innego niż drukarki.

ALTER TABELA Produkt

DODAJ ograniczenie chk_maker_Z CHECK ((maker="Z" AND type= "printer" ) OR maker "Z" ) ;

Zatem ograniczenie sprawdza, czy model w tabeli Product musi być drukarką producenta Z (maker="Z" i type="printer") lub dowolnego innego producenta (ale nie Z).

Jeśli spróbujemy dodać model Z PC producenta,

ALTER TABLE nazwa_tabeli alter_spec [, alter_spec ...] alter_specification: ADD create_definition lub ADD (create_definition, create_definition,...) lub ADD INDEX (index_col_name,...) lub ADD PRIMARY KEY (index_col_name,...) lub ADD UNIQUE (index_col_name,...) lub ADD FULLTEXT (index_col_name,...) lub ADD FOREIGN KEY index_name (index_col_name,...) lub ALTER col_name (SET DEFAULT literał | DROP DEFAULT) lub CHANGE stara_nazwa_kolumny create_definition lub MODIFY create_col_name lub DROP lub DROP PRIMARY KEY lub DROP INDEX index_name lub DISABLE KEYS lub ENABLE KEYS lub ZMIEŃ NAZWĘ nowa_nazwa_tabeli lub ORDER BY col lub table_options

Instrukcja ALTER TABLE zapewnia możliwość zmiany struktury istniejącej tabeli. Na przykład można dodawać lub usuwać kolumny, tworzyć lub usuwać indeksy, zmieniać nazwy kolumn lub samą tabelę. Możesz także zmienić komentarz do tabeli i jej typ. Patrz sekcja.

Jeśli instrukcja ALTER TABLE jest używana do zmiany definicji typu kolumny, ale DESCRIBE nazwa_tabeli wskazuje, że kolumna nie uległa zmianie, to MySQL może ignorować modyfikację z jednego z powodów opisanych w sekcji 6.5.3.1 Ciche zmiany w definicjach kolumn. Na przykład, jeśli spróbujesz zmienić kolumnę VARCHAR na CHAR, MySQL będzie nadal używać VARCHAR, jeśli tabela zawiera inne kolumny o zmiennej długości.

Instrukcja ALTER TABLE tworzy tymczasową kopię oryginalnej tabeli w czasie wykonywania. Wymagana zmiana jest dokonywana na kopii, a następnie usuwana jest oryginalna tabela, a nazwa nowej jest zmieniana. Dzieje się tak, aby wszystkie aktualizacje z wyjątkiem nieudanych automatycznie trafiały do ​​nowej tabeli. Podczas wykonywania ALTER TABLE oryginalna tabela jest odczytywana przez innych klientów. Operacje aktualizacji i zapisu na tej tabeli są zawieszane do czasu, gdy nowa tabela będzie gotowa.

Należy zauważyć, że przy użyciu jakiejkolwiek innej opcji ALTER TABLE innej niż RENAME , MySQL zawsze utworzy tabelę tymczasową, nawet jeśli dane nie muszą być ściśle kopiowane (na przykład przy zmianie nazwy kolumny). Planujemy to naprawić w przyszłości, jednak ponieważ ALTER TABLE nie jest robione tak często, my (twórcy MySQL) nie uważamy tego za wysoki priorytet. W przypadku tabel MyISAM można zwiększyć szybkość odtwarzania części indeksowej (która jest najwolniejszą częścią procesu odzyskiwania tabeli), ustawiając zmienną myisam_sort_buffer_size na wystarczająco dużą wartość.

• Aby użyć instrukcji ALTER TABLE, potrzebne są uprawnienia ALTER , INSERT i CREATE do tabeli.
• Opcja IGNORE jest rozszerzeniem MySQL do ANSI SQL92. Kontroluje działanie ALTER TABLE, gdy w nowej tabeli znajdują się zduplikowane unikalne klucze. Jeśli opcja IGNORE nie jest określona, ​​proces jest przerywany dla tej kopii i wycofywany. Jeśli określono IGNORE, to dla wierszy ze zduplikowanymi unikalnymi kluczami używany jest tylko pierwszy wiersz, a reszta jest odrzucana.
• W jednej instrukcji ALTER TABLE można uruchomić wiele instrukcji ADD , ALTER , DROP i CHANGE . Jest to rozszerzenie MySQL do ANSI SQL92, w którym dozwolone jest tylko jedno z wyrażeń wymienionych w pojedynczej instrukcji ALTER TABLE.
• Opcje CHANGE nazwa_kolumny , DROP nazwa_kolumny i DROP INDEX są również rozszerzeniami MySQL dla ANSI SQL92.
• Opcja MODIFY jest rozszerzeniem Oracle do polecenia ALTER TABLE.
• Opcjonalne słowo COLUMN reprezentuje „biały szum” i może zostać pominięte.
• Używając ALTER TABLE nazwa_tabeli RENAME TO nowa_nazwa bez żadnych innych opcji, MySQL po prostu zmienia nazwy plików odpowiadających podanej tabeli. W takim przypadku nie ma potrzeby tworzenia tabeli tymczasowej. Patrz rozdział 6.5.5 Składnia instrukcji RENAME TABL E.
• Instrukcja create_definition dla ADD i CHANGE używa tej samej składni, co dla CREATE TABLE . Zauważ, że ta składnia zawiera nazwę kolumny, a nie tylko jej typ. Patrz rozdział 6.5.3 Składnia instrukcji CREATE TABLE.
• Nazwę kolumny można zmienić za pomocą instrukcji CHANGE nazwa_kolumny create_definition . Aby to zrobić, musisz określić starą i nową nazwę kolumny oraz jej bieżący typ. Na przykład, aby zmienić nazwę kolumny INTEGER z a na b , możesz wykonać następujące czynności: mysql> ALTER TABLE t1 CHANGE a b INTEGER; Jeśli zmienisz typ kolumny, ale nie jej nazwę, składnia wyrażenia CHANGE nadal wymaga określenia obu nazw kolumn, nawet jeśli są takie same. Na przykład: mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL; Jednak od wersji MySQL 3.22.16a można również użyć instrukcji MODIFY do zmiany typu kolumny bez zmiany jej nazwy: mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
• Używając CHANGE lub MODIFY do zmniejszenia długości kolumny, na której budowany jest indeks (na przykład indeks na pierwszych 10 znakach kolumny VARCHAR), nie można skrócić kolumny niż liczba indeksowanych znaków.
• Zmieniając typ kolumny za pomocą CHANGE lub MODIFY, MySQL próbuje jak najbardziej poprawnie przekonwertować dane na nowy typ.
• W MySQL w wersji 3.22 i nowszych możesz użyć FIRST lub ADD ... AFTER nazwa_kolumny, aby dodać kolumnę na danej pozycji w wierszu tabeli. Domyślnie kolumna jest dodawana na końcu. Począwszy od MySQL 4.0.1, możesz również użyć słów kluczowych FIRST i AFTER w opcjach CHANGE lub MODIFY.
• Opcja ALTER COLUMN ustawia nową wartość domyślną dla kolumny lub usuwa starą. Jeśli stara wartość domyślna zostanie usunięta i podana kolumna może być NULL , nową wartością domyślną będzie NULL . Jeśli kolumna nie może mieć wartości NULL , MySQL przypisuje wartość domyślną, jak opisano w rozdziale 6.5.3 Składnia instrukcji CREATE TABLE.
• Opcja DROP INDEX usuwa indeks. Jest to rozszerzenie MySQL do ANSI SQL92. Zobacz rozdział 6.5.8 Składnia instrukcji DROP INDEX.
• Jeśli kolumny zostaną usunięte z tabeli, zostaną one również usunięte z dowolnego indeksu, którego są częścią. Jeśli wszystkie kolumny tworzące indeks zostaną usunięte, to ten indeks również zostanie usunięty.
• Jeśli tabela zawiera tylko jedną kolumnę, nie można jej usunąć. Zamiast tego możesz usunąć podaną tabelę za pomocą polecenia DROP TABLE.
• Opcja DROP PRIMARY KEY usuwa indeks podstawowy. Jeśli taki indeks nie istnieje w danej tabeli, to pierwszy indeks UNIQUE w tej tabeli jest usuwany. (MySQL oznacza pierwszy unikalny klucz UNIKATOWY jako KLUCZ PODSTAWOWY, jeśli żaden inny KLUCZ PODSTAWOWY nie został wyraźnie określony.) Gdy do tabeli dodawany jest UNIQUE INDEX lub PRIMARY KEY, jest on przechowywany przed pozostałymi nieunikalnymi kluczami, aby zduplikowane klucze można było zidentyfikować tak szybko, jak to możliwe.
• Opcja ORDER BY umożliwia utworzenie nowej tabeli z wierszami w określonej kolejności. Należy zauważyć, że utworzona tabela nie zachowa tej kolejności wierszy po wstawieniu i usunięciu. W niektórych przypadkach ta funkcja może ułatwić sortowanie w MySQL, jeśli tabela ma układ kolumn, który chciałbyś mieć w przyszłości. Ta opcja jest przydatna głównie wtedy, gdy znasz z góry określoną kolejność, w jakiej wiersze będą najczęściej żądane. Użycie tej opcji po znaczących przekształceniach tabeli zapewnia lepszą wydajność.
• Podczas używania polecenia ALTER TABLE w tabelach MyISAM wszystkie nieunikalne indeksy są tworzone w osobnym pakiecie (podobnie jak REPAIR). Dzięki temu ALTER TABLE będzie szybsze, gdy istnieje wiele indeksów.
• Począwszy od MySQL 4.0, powyższą funkcję można włączyć jawnie. Polecenie ALTER TABLE… DISABLE KEYS blokuje MySQL przed aktualizacją nieunikatowych indeksów w tabelach MyISAM. Następnie można użyć polecenia ALTER TABLE... ENABLE KEYS, aby odtworzyć brakujące indeksy. Ponieważ MySQL robi to za pomocą specjalnego algorytmu, który jest znacznie szybszy niż wstawianie kluczy jeden po drugim, blokowanie kluczy może znacznie przyspieszyć w przypadku dużych tablic wstawiania.
• Używając funkcji mysql_info() C API, możesz określić, ile rekordów zostało skopiowanych i (używając IGNORE) ile rekordów zostało usuniętych z powodu zduplikowanych unikalnych wartości klucza.
• Wyrażenia FOREIGN KEY , CHECK i REFERENCES w rzeczywistości nic nie robią. Są one dołączone wyłącznie ze względu na kompatybilność, aby ułatwić przenoszenie kodu z innych Serwery SQL i uruchamianie aplikacji, które tworzą tabele z linkami. Zobacz rozdział 1.9.4 Różnice między MySQL i ANSI SQL92 .

Poniższe przykłady pokazują niektóre zastosowania polecenia ALTER TABLE. Przykład zaczyna się od tabeli t1 , która jest tworzona w następujący sposób:

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

Aby zmienić nazwę tabeli z t1 na t2:

mysql> ALTER TABELA t1 ZMIANA NAZWY t2;

Aby zmienić typ kolumny z INTEGER na TINYINT NOT NULL (zachowując tę ​​samą nazwę) i zmienić typ kolumny b z CHAR(10) na CHAR(20) i zmienić jej nazwę z b na c:

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

Aby dodać nową kolumnę TIMESTAMP o nazwie d:

Mysql> ALTER TABELA t2 DODAJ d ZNACZNIK CZASU;

Aby dodać indeks do kolumny d i uczynić kolumnę a kluczem podstawowym:

Mysql> ZMIEŃ TABELĘ t2 DODAJ INDEKS (d), DODAJ KLUCZ GŁÓWNY (a);

Aby usunąć kolumnę c:

mysql> ALTER TABELA t2 DROP COLUMN c;

Aby dodać nową kolumnę liczbową AUTO_INCREMENT o nazwie c:

Mysql> ALTER TABELA t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (c);

Należy zauważyć, że kolumna c jest indeksowana, ponieważ kolumny AUTO_INCREMENT muszą być indeksowane, a kolumna c jest zadeklarowana jako NOT NULL, ponieważ indeksowane kolumny nie mogą mieć wartości NULL .

Po dodaniu kolumny AUTO_INCREMENT wartości w tej kolumnie są automatycznie wypełniane kolejnymi numerami (w przypadku dodawania rekordów). Pierwszy numer sekwencyjny można ustawić, wydając polecenie SET INSERT_ID=# przed ALTER TABLE lub używając opcji AUTO_INCREMENT = # table. Patrz rozdział 5.5.6 Składnia polecenia SET.

Jeśli kolumna AUTO_INCREMENT dla tabel MyISAM nie zmieni się, numer kolejny pozostaje taki sam. Jeśli usuniesz kolumnę AUTO_INCREMENT, a następnie dodasz kolejną kolumnę AUTO_INCREMENT, liczby zaczną się ponownie od 1 .

Jeśli Twój projekt posiada tabele, których rozmiar liczony jest w gigabajtach, a żeby zmienić strukturę takiej tabeli musisz zatrzymać wszystkie usługi na kilka godzin - ten artykuł będzie dla Ciebie.

Dany: tabela o rozmiarze kilkudziesięciu gigabajtów danych. Zadaniem jest zmiana struktury tabeli.

Od razu pobiegnę do przodu, metoda będzie działać tylko na tabelach transakcyjnych. Jeśli masz tablicę MyISAM na dziesiątki gigabajtów, to jest jak w tym dowcipie – „rozpraw się z własnymi problemami”. Przykład zostanie podany dla tabeli InnoDB.

Powiedzmy, że nasza struktura tabeli wygląda tak:

CREATE TABLE `users` (`id` int(11) NOT NULL AUTO_INCREMENT, `email` varchar(40) NOT NULL DEFAULT "", `password_hash` char(32) NOT NULL DEFAULT "", `registration_date` int(11) NOT NULL DEFAULT "0", PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
Chcemy dodać do tej tabeli pole last_login.

Jakie mamy opcje.

Czołowo

ALTER TABLE `users` ADD COLUMN `last_login` int(11) NOT NULL DEFAULT 0;
Ta opcja świetnie sprawdza się w małych projektach, w których rozmiar tabeli rzadko przekracza 50 000 rekordów. Ta opcja nam nie odpowiada. ALTER będzie wykonywany zbyt długo i przez cały ten czas tabela będzie zablokowana zarówno do pisania, jak i do czytania. W związku z tym usługa będzie musiała zostać na ten czas zatrzymana.

Włącz mózg

Nie możesz w ogóle dotykać stołu, ale zrób osobne `users_lastvisits`:
CREATE TABLE `users_lastvisits` (`user_id` int(11) NOT NULL, `last_login` int(11) NOT NULL DEFAULT "0", PRIMARY KEY (`user_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Teraz możesz JOIN z tabelą last_login we wszystkich zapytaniach, w których potrzebny jest last_login. Oczywiście będzie działać wolniej, a dodawanie JOIN w zapytaniach to także dodatkowy czas, ale generalnie to czasami wystarczy i można na tym poprzestać.

A jednak – trzeba dodać pole

Możesz podnieść replikację master-slave, zrobić ALTER na serwerze slave, a następnie zamienić je. Szczerze mówiąc, nigdy tego nie robiłem, może jest to łatwiejsze niż następna metoda, ale nie zawsze można podnieść replikację.

Moja droga jest następująca

Tworzymy nową tabelę z docelową strukturą, na pierwszej tabeli wykonujemy triggery, które będą logowały wszystkie zmiany, jednocześnie zaczynamy przenosić dane z pierwszej tabeli do drugiej, a na koniec „wlewamy” zmienione dane i zmień nazwy tabel.

Przygotowujemy więc 2 tabele - pierwszą o pożądanej strukturze, drugą do rejestrowania zmian.
CREATE TABLE `_users` (`id` int(11) NOT NULL AUTO_INCREMENT, `email` varchar(40) NOT NULL DEFAULT "", `password_hash` char(32) NOT NULL DEFAULT "", `registration_date` int(11) NOT NULL DEFAULT "0", `lastvisit` int(11) NOT NULL DEFAULT 0, KLUCZ PODSTAWOWY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `users_updated_rows` (`id` int(11) NOT NULL AUTO_INCREMENT, `row_id` int(11) NOT NULL DEFAULT "0", `action` enum("zaktualizowane", "usunięte") NOT NULL DEFAULT "zaktualizowane" , KLUCZ PODSTAWOWY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Skonfiguruj wyzwalacze:
DELIMITER ;; UTWÓRZ WYZWALANIE users_after_delete PO USUNIĘCIU użytkowników DLA KAŻDEGO WIERSZU ROZPOCZNIJ WSTAWIANIE users_updated_rows WARTOŚCI (0, OLD.id, "usunięte"); KONIEC;; UTWÓRZ WYZWALANIE users_after_insert PO WSTAWIĆ DLA użytkowników DLA KAŻDEGO WIERSZU ROZPOCZNIJ WSTAWIANIE users_updated_rows WARTOŚCI (0, NEW.id, "zaktualizowane"); KONIEC;; UTWÓRZ WYZWALANIE users_after_update PO ZAKTUALIZOWANIU użytkowników DLA KAŻDEGO WIERSZU ZACZNIJ JEŚLI (STARY.id != NOWY.id) NASTĘPNIE WSTAW W users_updated_rows WARTOŚCI (0, STARY.id, "usunięte"); KONIEC JEŚLI; INSERT INTO users_updated_rows VALUES (0, NEW.id, "zaktualizowane"); KONIEC;; DELIMITER ;
Teraz zaczynamy nalewać. Aby to zrobić, musisz otworzyć 2 połączenia z bazą danych. W jednym nastąpi przepełnienie, w drugim konieczne będzie chwilowe zablokowanie stołu do pisania.
mysql> LOCK TABLES użytkownicy WRITE; Zapytanie OK, dotyczy 0 wierszy (0,00 s) mysql> -- wyzwalacze powinny już być ustawione mysql> TRUNCATE users_updated_rows; Zapytanie OK, wpływ 0 wierszy (0,17 s) mysql> -- uruchom mysql> transfusion w innej konsoli INSERT INTO _users SELECT id, email, hash_hasła, data_rejestracji, 0 FROM users; mysql> -- ponownie w pierwszej konsoli mysql> UNLOCK TABLES;
To wszystko, teraz, gdy stół jest wlewany, mamy czas na zastanowienie się, w jaki sposób wlejemy dane, które zmieniły się od początku wlewania. Nie ma tu nic skomplikowanego - nie dam skryptu, wystarczy pobrać jeden rekord z tabeli users_updated_rows w kolejności, w jakiej zostały dodane (sortuj według klucza podstawowego) i zaktualizować lub usunąć go w tabeli _users;

Tak więc wylewanie stołu już się zakończyło, musisz wlać resztę danych. Rozpoczynamy skrypt. Skrypt musi działać ciągle i aktualizować wszystkie wpisy, które są dodawane do logu, gdy wyleje wszystkie dane, trzeba zmienić nazwy tabel:
mysql> OBCIĄGNIĘĆ users_updated_rows; Zapytanie OK, wpływ 0 wierszy (0,16 s) mysql> RENAME TABLE users TO __users, _users TO users; Zapytanie OK, dotyczy 0 wierszy (0,11 s)
Warto zauważyć, że w tym momencie możliwa jest niewielka utrata danych. zapytania nie są wykonywane atomowo. Jeśli jest to krytyczne, lepiej na chwilę wyłączyć usługę, aby nie było żadnych żądań zmian. Możesz na przykład odebrać użytkownikowi uprawnienia do zapisu i wykonywać polecenia dla innego użytkownika.

Jeśli wszystko zostanie wykonane poprawnie, dane nie zostaną utracone i praktycznie nie nastąpią przerwy w działaniu usługi. Właśnie tego potrzebowaliśmy. W ten sam sposób możesz przenieść dane na inny serwer, zmieni się tylko sposób przesyłania. Zamiast
mysql> INSERT INTO _users SELECT id, e-mail, hash_hasła, data_rejestracji, 0 FROM użytkowników;
musisz przelać przez mysqldump:
$ mysqldump -h host1 użytkowników db --single-transaction -ecQ | pv | mysql -h host2
W ten sposób udało mi się przenieść tabelę o rozmiarze 60Gb i 400 mln wierszy na inny serwer bez zatrzymywania pracy usług w około 12 godzin.

Nawiasem mówiąc, rower wymyślone przez Facebook i zadzwoniłem

Aby użyć instrukcji ALTER TABLE, potrzebne są uprawnienia ALTER , INSERT i CREATE do tabeli.

Opcja IGNORE jest rozszerzeniem MySQL do ANSI SQL92. Kontroluje działanie ALTER TABLE, gdy w nowej tabeli znajdują się zduplikowane unikalne klucze. Jeśli opcja IGNORE nie jest określona, ​​proces jest przerywany dla tej kopii i wycofywany. Jeśli określono IGNORE, to dla wierszy ze zduplikowanymi unikalnymi kluczami używany jest tylko pierwszy wiersz, a reszta jest odrzucana.

W jednej instrukcji ALTER TABLE można uruchomić wiele instrukcji ADD , ALTER , DROP i CHANGE . Jest to rozszerzenie MySQL do ANSI SQL92, w którym dozwolone jest tylko jedno z wyrażeń wymienionych w pojedynczej instrukcji ALTER TABLE.

Opcje CHANGE nazwa_kolumny , DROP nazwa_kolumny i DROP INDEX są również rozszerzeniami MySQL dla ANSI SQL92.

Opcja MODIFY jest rozszerzeniem Oracle do polecenia ALTER TABLE.

Opcjonalne słowo COLUMN reprezentuje „biały szum” i może zostać pominięte.

Używając ALTER TABLE nazwa_tabeli RENAME TO nowa_nazwa bez żadnych innych opcji, MySQL po prostu zmienia nazwy plików odpowiadających podanej tabeli. W takim przypadku nie ma potrzeby tworzenia tabeli tymczasowej. .

Instrukcja create_definition dla ADD i CHANGE używa tej samej składni, co dla CREATE TABLE . Zauważ, że ta składnia zawiera nazwę kolumny, a nie tylko jej typ. .

Nazwę kolumny można zmienić za pomocą instrukcji CHANGE nazwa_kolumny create_definition . Aby to zrobić, musisz określić starą i nową nazwę kolumny oraz jej bieżący typ. Na przykład, aby zmienić nazwę kolumny INTEGER z a na b , możesz wykonać następujące czynności:

mysql> ALTER TABELA t1 ZMIANA a b INTEGER;

Jeśli zmienisz typ kolumny, ale nie jej nazwę, składnia wyrażenia CHANGE nadal wymaga określenia obu nazw kolumn, nawet jeśli są takie same. Na przykład:

mysql> ALTER TABELA t1 ZMIANA b b BIGINT NIE NULL;

Jednak od MySQL 3.22.16a można również użyć instrukcji MODIFY do zmiany typu kolumny bez zmiany jej nazwy:

mysql> ZMIEŃ TABELĘ t1 MODYFIKUJ b BIGINT NIE NULL;

Używając CHANGE lub MODIFY do zmniejszenia długości kolumny, na której budowany jest indeks (na przykład indeks na pierwszych 10 znakach kolumny VARCHAR), nie można skrócić kolumny niż liczba indeksowanych znaków.

Zmieniając typ kolumny za pomocą CHANGE lub MODIFY, MySQL próbuje jak najbardziej poprawnie przekonwertować dane na nowy typ.

W MySQL w wersji 3.22 i nowszych możesz użyć FIRST lub ADD ... AFTER nazwa_kolumny, aby dodać kolumnę na danej pozycji w wierszu tabeli. Domyślnie kolumna jest dodawana na końcu. Począwszy od MySQL 4.0.1, możesz również użyć słów kluczowych FIRST i AFTER w opcjach CHANGE lub MODIFY.

Opcja ALTER COLUMN ustawia nową wartość domyślną dla kolumny lub usuwa starą. Jeśli stara wartość domyślna zostanie usunięta, a kolumna może mieć wartość NULL, to nową wartością domyślną będzie NULL. Jeśli kolumna nie może być NULL , MySQL przypisuje wartość domyślną, jak opisano w sekcji.

Opcja DROP INDEX usuwa indeks. Jest to rozszerzenie MySQL do ANSI SQL92. .

Jeśli kolumny zostaną usunięte z tabeli, zostaną one również usunięte z dowolnego indeksu, którego są częścią. Jeśli wszystkie kolumny tworzące indeks zostaną usunięte, to ten indeks również zostanie usunięty.

Jeśli tabela zawiera tylko jedną kolumnę, nie można jej usunąć. Zamiast tego możesz usunąć podaną tabelę za pomocą polecenia DROP TABLE.

Opcja DROP PRIMARY KEY usuwa indeks podstawowy. Jeśli taki indeks nie istnieje w danej tabeli, to pierwszy indeks UNIQUE w tej tabeli jest usuwany. (MySQL oznacza pierwszy unikalny klucz UNIKATOWY jako KLUCZ PODSTAWOWY, jeśli żaden inny KLUCZ PODSTAWOWY nie został wyraźnie określony.) Gdy do tabeli dodawany jest UNIQUE INDEX lub PRIMARY KEY, jest on przechowywany przed pozostałymi nieunikalnymi kluczami, aby zduplikowane klucze można było zidentyfikować tak szybko, jak to możliwe.

Opcja ORDER BY umożliwia utworzenie nowej tabeli z wierszami w określonej kolejności. Należy zauważyć, że utworzona tabela nie zachowa tej kolejności wierszy po wstawieniu i usunięciu. W niektórych przypadkach ta funkcja może ułatwić sortowanie w MySQL, jeśli tabela ma układ kolumn, który chciałbyś mieć w przyszłości. Ta opcja jest przydatna głównie wtedy, gdy znasz z góry określoną kolejność, w jakiej wiersze będą najczęściej żądane. Użycie tej opcji po znaczących przekształceniach tabeli zapewnia lepszą wydajność.

Podczas używania polecenia ALTER TABLE w tabelach MyISAM wszystkie nieunikalne indeksy są tworzone w osobnym pakiecie (podobnie jak REPAIR). Dzięki temu ALTER TABLE będzie szybsze, gdy istnieje wiele indeksów.

Począwszy od MySQL 4.0, powyższą funkcję można włączyć jawnie. Polecenie ALTER TABLE… DISABLE KEYS blokuje MySQL przed aktualizacją nieunikatowych indeksów w tabelach MyISAM. Następnie można użyć polecenia ALTER TABLE... ENABLE KEYS, aby odtworzyć brakujące indeksy. Ponieważ MySQL robi to za pomocą specjalnego algorytmu, który jest znacznie szybszy niż wstawianie kluczy jeden po drugim, blokowanie kluczy może znacznie przyspieszyć w przypadku dużych tablic wstawiania.

Używając funkcji mysql_info() C API, możesz określić, ile rekordów zostało skopiowanych i (używając IGNORE) ile rekordów zostało usuniętych z powodu zduplikowanych unikalnych wartości klucza.

Wyrażenia FOREIGN KEY , CHECK i REFERENCES w rzeczywistości nic nie robią we wszystkich typach tabel z wyjątkiem InnoDB. InnoDB obsługuje DODAJ OGRANICZENIE KLUCZA OBCEGO (...) REFERENCJE... (...) . Zauważ, że InnoDB nie zezwala na nazwa_indeksu. . Wsparcie dla składni FOREIGH KEY jest zapewnione tylko ze względu na kompatybilność, aby ułatwić przenoszenie kodu z innych serwerów SQL i uruchamianie aplikacji, które tworzą tabele z odwołaniami. .