În aia ghid de studiu vei invata sa folosesti Instrucțiunea MySQL ALTER TABLE pentru a adăuga o coloană, a schimba o coloană, a elimina o coloană, a redenumi o coloană sau a redenumi un tabel (cu sintaxă și exemple).
Descriere
Instrucțiunea MySQL ALTER TABLE folosit pentru a adăuga, modifica sau elimina coloane dintr-un tabel. Instrucțiunea MySQL ALTER TABLE este de asemenea folosită pentru a redenumi un tabel.
Adăugați coloană la tabel
Sintaxă
Sintaxa pentru adăugarea unei coloane la un tabel MySQL (folosind instrucțiunea ALTER TABLE) este:
ALTER TABLE nume_tabel
PRIMUL | AFTER nume_coloană este opțional. Îi spune MySQL unde în tabel este creată coloana. Dacă acest parametru nu este specificat, atunci noua coloană va fi adăugată la sfârșitul tabelului.
Exemplu
Să ne uităm la un exemplu care arată cum să adăugați o coloană la un tabel MySQL folosind instrucțiunea ALTER TABLE.
De exemplu:
MySQL
ALTER TABLE contacts ADD last_name varchar(40) NOT NULL AFTER contact_id;
Contacte ALTER TABLE DUPĂ contact_id; |
Acest exemplu MySQL ALTER TABLE va adăuga o coloană numită last_name la tabelul de contacte. Acesta va fi creat ca o coloană NOT NULL și va apărea în tabel după câmpul contact_id.
Adăugați mai multe coloane într-un tabel
Sintaxă
Sintaxa pentru adăugarea mai multor coloane la un tabel MySQL (folosind instrucțiunea ALTER TABLE) este:
ALTER TABLE nume_tabel
ADAUGĂ numele_coloană_nouă definiție_coloană
ADAUGĂ numele_coloană_nouă definiție_coloană
[ PRIMUL | DUPĂ numele_coloană ],
…
;
table_name este numele tabelului de schimbat.
new_column_name este numele noii coloane de adăugat la tabel.
column_definition - tipul de date și definiția coloanei (NULL sau NOT NULL etc.).
PRIMUL | AFTER nume_coloană este opțional. Îi spune MySQL unde în tabel este creată coloana. Dacă acest parametru nu este specificat, noua coloană va fi adăugată la sfârșitul tabelului.
Exemplu
Să ne uităm la un exemplu care arată cum să adăugați mai multe coloane la un tabel MySQL folosind instrucțiunea ALTER TABLE.
De exemplu:
MySQL
ALTER TABLE contacts ADD last_name varchar(40) NOT NULL AFTER contact_id, ADD first_name varchar(35) NULL AFTER last_name;
Contacte ALTER TABLE ADD last_namevarchar(40)NOT NULL DUPĂ contact_id, ADD first_namevarchar(35)NULL DUPA prenume; |
Acest exemplu ALTER TABLE va adăuga două coloane la tabelul de contacte, last_name și first_name .
Câmpul last_name va fi creat ca o coloană varchar (40) NOT NULL și va apărea în tabelul de contacte după coloana contact_id. Coloana first_name va fi creată ca o coloană NULL varchar(35) și va apărea în tabel după coloana last_name.
Schimbați o coloană dintr-un tabel
Sintaxă
Sintaxa pentru schimbarea unei coloane într-un tabel MySQL (folosind instrucțiunea ALTER TABLE) este:
ALTER TABLE nume_tabel
[ PRIMUL | AFTER nume_coloană ];
table_name este numele tabelului de schimbat.
Exemplu
Să ne uităm la un exemplu care arată cum să schimbați o coloană într-un tabel MySQL folosind instrucțiunea ALTER TABLE.
De exemplu:
MySQL
ALTER TABLE contacte MODIFY last_name varchar(50) NULL;
Contacte ALTER TABLE MODIFY last_namevarchar(50)NULL ; |
Acest exemplu ALTER TABLE va schimba coloana numită last_name pentru a fi un tip de date varchar(50) și va seta coloana la valori NULL.
Schimbați mai multe coloane dintr-un tabel
Sintaxă
Sintaxa pentru a schimba mai multe coloane într-un tabel MySQL (folosind instrucțiunea ALTER TABLE) este:
ALTER TABLE nume_tabel
MODIFICAȚI numele_coloană definiția_coloanei
[ PRIMUL | DUPĂ numele_coloană ],
MODIFICAȚI numele_coloană definiția_coloanei
[ PRIMUL | DUPĂ numele_coloană ],
…
;
table_name este numele tabelului de schimbat.
column_name este numele coloanei de schimbat în tabel.
column_definition - s-a modificat tipul de date și definiția coloanei (NULL sau NOT NULL etc.).
PRIMUL | AFTER nume_coloană este opțional. Acesta spune MySQL unde în tabel este plasată coloana dacă doriți să-i schimbați poziția.
Exemplu
Să ne uităm la un exemplu care arată cum să schimbați mai multe coloane într-un tabel MySQL folosind instrucțiunea ALTER TABLE.
MySQL
ALTER TABLE contacts MODIFY last_name varchar(55) NULL AFTER contact_type, MODIFY first_name varchar(30) NOT NULL;
Contacte ALTER TABLE MODIFY last_namevarchar(55)NULL DUPĂ tipul_contact, MODIFY first_namevarchar(30)NOT NULL ; |
Acest exemplu ALTER TABLE va schimba două coloane din tabelul de contacte, last_name și first_name .
Câmpul last_name va fi schimbat într-o coloană NULL varchar(55) și va apărea în tabel după coloana contact_type. Coloana first_name va fi schimbată într-o coloană varchar (30) NOT NULL (și nu va schimba poziția în definiția tabelului de contacte, deoarece nu este specificat FIRST | AFTER).
Eliminarea unei coloane dintr-un tabel
Sintaxă
Sintaxa pentru a elimina o coloană dintr-un tabel în MySQL (folosind instrucțiunea ALTER TABLE) este:
De exemplu:
ALTER TABLE nume_tabel
DROP COLUMN nume_coloană;
table_name este numele tabelului de schimbat.
column_name este numele coloanei de eliminat din tabel.
Exemplu
Să ne uităm la un exemplu care arată cum să eliminați o coloană dintr-un tabel în MySQL folosind instrucțiunea ALTER TABLE.
De exemplu:
MySQL
Contacte ALTER TABLE DROP COLUMN tip_contact;
Se pot distinge următoarele niveluri de restricții de verificare:
- nivel de atribut (coloană),
- nivel tuplu (șir),
- nivel de relație (tabele).
Într-o constrângere la nivel de coloană, valoarea unei singure coloane individuale este verificată, cu alte cuvinte, într-o constrângere de acest tip există o referire la o singură coloană a tabelului a cărei definiție conține această restricție. Pentru a da un exemplu de astfel de constrângere, să revenim la schema „Computer Firm”. În tabelul Produs, coloana tip poate conține una dintre cele trei valori. Putem împiedica introducerea oricărei alte informații în această coloană cu următoarea constrângere:
VERIFICARE (tastați IN ("imprimantă", "pc", "laptop"))
Să facem un pas înapoi pentru a ne familiariza cu instrucțiunea ALTER TABLE, care ne va permite să schimbăm structura unui tabel fără a fi nevoie să-l recreăm de fiecare dată. Acest lucru este cu atât mai important cu cât poate fi necesară o modificare a structurii atunci când tabelul conține deja date.
Cu instrucțiunea ALTER TABLE, puteți adăuga sau elimina coloane, valori implicite și constrângeri.
ÎN în prezent suntem interesați să adăugăm o constrângere pe coloana de tip, așa că mai întâi dăm sintaxa operatorului pentru adăugarea unei constrângeri:
ALTER TABLE
ADĂUGAȚI CONSTRINGERE ;
Să adăugăm acum constrângerea noastră și să vedem cum funcționează.
ALTER TABLE Produs
ADD CONSTRAINT chk_type CHECK (tastați IN ("pc", "laptop" , "printer" ) );
Pentru a ne asigura că constrângerea funcționează conform așteptărilor, să încercăm să adăugăm un nou tip de model:
INSERT INTO Product VALUES ("A" , 1122 , "notebook" ) ;
După cum era de așteptat, ca răspuns vom primi un mesaj de eroare:
Declarația INSERT a intrat în conflict cu constrângerea CHECK „chk_type”. Conflictul a apărut în baza de date „learn”, tabelul „dbo.product”, coloana „type”. Declarația a fost încheiată.
(Instrucțiunea INSERT a intrat în conflict cu constrângerea CHECK „chk_type”. Conflictul a apărut în baza de date „learn”, tabelul „dbo.product”, coloana „type”. Această instrucțiune a fost anulată.)
După cum ați putea ghici, o constrângere la nivel de rând conține referințe la mai multe coloane. În acest caz, restricția este verificată separat pentru fiecare linie modificată. Linia poate fi adăugată (sau schimbată) dacă constrângerea nu este încălcată.
De exemplu, să interzicem producătorului Z să producă altceva decât imprimante.
ALTER TABLE Produs
ADD constraint chk_maker_Z CHECK ((maker="Z" AND type= "printer" ) SAU maker "Z" );
Deci constrângerea verifică dacă modelul din tabelul Produs trebuie să fie o imprimantă a producătorului Z (maker="Z" și type= "printer") sau a oricărui alt producător (dar nu Z).
Dacă încercăm să adăugăm modelul producătorului Z PC,
ALTER TABLE nume_tbl alter_spec [, alter_spec ...] alter_specification: ADD create_definition sau ADD (create_definition, create_definition,...) sau ADD INDEX (index_col_name,...) sau ADD PRIMARY KEY (index_col_name,...) sau ADD UNIQUE (index_col_name,...) sau ADD FULLTEXT (index_col_name,...) sau ADD FOREIGN KEY index_name (index_col_name,...) sau ALTER col_name (SET DEFAULT literal | DROP DEFAULT) sau CHANGE old_col_name create_definition sau MODIFY create_definition sau DROP col_name sau DROP PRIMARY KEY sau DROP INDEX index_name sau DISABLE KEYS sau ENABLE KEYS sau RENAME new_tbl_name sau ORDER BY col sau table_optionsInstrucțiunea ALTER TABLE oferă posibilitatea de a schimba structura unui tabel existent. De exemplu, puteți adăuga sau elimina coloane, puteți crea sau elimina indecși sau puteți redenumi coloanele sau tabelul în sine. De asemenea, puteți modifica comentariul pentru tabel și tipul acestuia. Vezi secțiunea.
Dacă o instrucțiune ALTER TABLE este folosită pentru a schimba definiția unui tip de coloană, dar DESCRIBE tbl_name indică faptul că coloana nu s-a schimbat, atunci MySQL poate ignora modificarea din unul dintre motivele descrise în secțiunea 6.5.3.1 Modificări silențioase ale definițiilor coloanelor. De exemplu, dacă încercați să schimbați o coloană VARCHAR în CHAR, MySQL va continua să folosească VARCHAR dacă tabelul conține alte coloane cu lungime variabilă.
Instrucțiunea ALTER TABLE creează o copie temporară a tabelului original în timpul rulării. Modificarea necesară este făcută pe copie, apoi tabelul original este abandonat și cel nou este redenumit. Acest lucru se face astfel încât toate actualizările, cu excepția celor eșuate, să intre automat în noul tabel. În timpul execuției ALTER TABLE, tabelul original este citit de alți clienți. Operațiunile de actualizare și scriere pe acest tabel sunt suspendate până când un nou tabel este gata.
Trebuie remarcat faptul că atunci când utilizați orice altă opțiune pentru ALTER TABLE, alta decât RENAME, MySQL va crea întotdeauna un tabel temporar, chiar dacă datele nu trebuie strict copiate (de exemplu, la schimbarea numelui unei coloane). Intenționăm să remediem acest lucru în viitor, totuși, deoarece ALTER TABLE nu se face atât de des, noi (dezvoltatorii MySQL) nu îl considerăm o prioritate ridicată. Pentru tabelele MyISAM, puteți crește viteza de recreare a porțiunii de index (care este cea mai lentă parte a procesului de recuperare a tabelului) setând variabila myisam_sort_buffer_size la o valoare suficient de mare.
- Pentru a utiliza instrucțiunea ALTER TABLE, aveți nevoie de privilegiile ALTER , INSERT și CREATE pe tabel.
- Opțiunea IGNORE este o extensie MySQL la ANSI SQL92. Controlează modul în care funcționează ALTER TABLE atunci când există chei unice duplicat într-un tabel nou. Dacă opțiunea IGNORE nu este specificată, atunci procesul este anulat pentru această copie și este anulat. Dacă este specificat IGNORE, atunci pentru rândurile cu chei unice duplicate, se folosește doar primul rând, iar restul sunt eliminate.
- Puteți rula mai multe instrucțiuni ADD , ALTER , DROP și CHANGE într-o singură instrucțiune ALTER TABLE. Aceasta este o extensie MySQL la ANSI SQL92 unde este permisă doar una dintre expresiile menționate într-o singură instrucțiune ALTER TABLE.
- Opțiunile CHANGE col_name , DROP col_name și DROP INDEX sunt, de asemenea, extensii MySQL la ANSI SQL92.
- Opțiunea MODIFY este o extensie Oracle a comenzii ALTER TABLE.
- Cuvântul opțional COLUMN reprezintă „zgomot alb” și poate fi omis.
- Când utilizați ALTER TABLE table_name RENAME TO new_name fără alte opțiuni, MySQL pur și simplu redenumește fișierele corespunzătoare tabelului dat. În acest caz, nu este nevoie să creați un tabel temporar. Vezi secțiunea 6.5.5 Sintaxa instrucțiunii RENAME TABL E.
- Declarația create_definition pentru ADD și CHANGE folosește aceeași sintaxă ca și pentru CREATE TABLE. Rețineți că această sintaxă include numele coloanei, nu doar tipul acesteia. Vezi secțiunea 6.5.3 Sintaxa instrucțiunii CREATE TABLE.
- O coloană poate fi redenumită folosind instrucțiunea CHANGE nume_coloană create_definition . Pentru a face acest lucru, trebuie să specificați numele vechi și noi ale coloanei și tipul ei actual. De exemplu, pentru a redenumi o coloană INTEGER de la a la b, puteți face următoarele: mysql> ALTER TABLE t1 CHANGE a b INTEGER; Dacă modificați tipul unei coloane, dar nu și numele acesteia, sintaxa expresiei CHANGE necesită în continuare specificarea ambelor nume de coloană, chiar dacă sunt aceleași. De exemplu: mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL; Cu toate acestea, începând cu MySQL 3.22.16a, puteți folosi și instrucțiunea MODIFY pentru a schimba tipul unei coloane fără a o redenumește: mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
- Când utilizați CHANGE sau MODIFY pentru a reduce lungimea unei coloane pe care este construit un index (de exemplu, un index pe primele 10 caractere ale unei coloane VARCHAR), nu puteți face coloana mai scurtă decât numărul de caractere indexate.
- Când se schimbă tipul unei coloane folosind CHANGE sau MODIFY, MySQL încearcă să convertească datele în noul tip cât mai corect posibil.
- În MySQL versiunea 3.22 și ulterioară, puteți folosi FIRST sau ADD ... AFTER column_name pentru a adăuga o coloană la o anumită poziție într-un rând de tabel. În mod implicit, coloana este adăugată la sfârșit. Începând cu MySQL 4.0.1, puteți utiliza și cuvintele cheie FIRST și AFTER în opțiunile CHANGE sau MODIFY.
- Opțiunea ALTER COLUMN setează o nouă valoare implicită pentru o coloană sau elimină una veche. Dacă vechea valoare implicită este eliminată și coloana dată poate fi NULL, noua valoare implicită va fi NULL. Dacă coloana nu poate fi NULL , atunci MySQL atribuie o valoare implicită așa cum este descris în secțiunea 6.5.3 Sintaxa instrucțiunii CREATE TABLE.
- Opțiunea DROP INDEX elimină un index. Aceasta este o extensie MySQL la ANSI SQL92. Vezi secțiunea 6.5.8 Sintaxa instrucțiunii DROP INDEX.
- Dacă coloanele sunt eliminate dintr-un tabel, acele coloane sunt, de asemenea, eliminate din orice index din care fac parte. Dacă toate coloanele care alcătuiesc un index sunt eliminate, atunci și acel index este eliminat.
- Dacă tabelul conține o singură coloană, atunci acea coloană nu poate fi ștearsă. În schimb, puteți arunca tabelul dat folosind comanda DROP TABLE.
- Opțiunea DROP PRIMARY KEY elimină indexul primar. Dacă nu există un astfel de index pe tabelul dat, atunci primul index UNIC din acel tabel este eliminat. (MySQL marchează prima cheie UNICĂ unică ca CHEIE PRIMARĂ dacă nu a fost specificată în mod explicit nicio CHEIE PRIMARĂ.) Atunci când un INDEX UNIC sau CHEIE PRIMARĂ este adăugat la un tabel, acesta este stocat înaintea celorlalte chei neunice, astfel încât cheile duplicate să poată fi identificate cât mai curând posibil.
- Opțiunea ORDER BY vă permite să creați un nou tabel cu rânduri în ordinea specificată. Rețineți că tabelul creat nu va păstra această ordine de rânduri după inserări și ștergeri. În unele cazuri, această caracteristică poate face sortarea mai ușoară în MySQL dacă tabelul are un aranjament de coloane pe care ați dori să o aveți în viitor. Această opțiune este utilă în principal dacă cunoașteți dinainte o anumită ordine în care vor fi solicitate predominant rândurile. Utilizarea acestei opțiuni după transformări semnificative ale tabelului are ca rezultat o performanță mai bună.
- Când utilizați comanda ALTER TABLE pe tabelele MyISAM, toți indecșii neunici sunt creați într-un pachet separat (similar cu REPAIR). Acest lucru va face ALTER TABLE mai rapid atunci când există mai mulți indecși.
- Începând cu MySQL 4.0, caracteristica de mai sus poate fi activată în mod explicit. Comanda ALTER TABLE... DISABLE KEYS blochează MySQL să actualizeze indecși non-unici pe tabelele MyISAM. Puteți utiliza apoi comanda ALTER TABLE ... ENABLE KEYS pentru a recrea indecșii lipsă. Deoarece MySQL face acest lucru cu un algoritm special, care este mult mai rapid decât introducerea cheilor una câte una, blocarea cheilor poate fi o accelerare semnificativă pe matrice mari de inserții.
- Folosind funcția mysql_info() C API, puteți determina câte înregistrări au fost copiate și (folosind IGNORE) câte înregistrări au fost șterse din cauza valorilor chei unice duplicate.
- Expresiile FOREIGN KEY , CHECK și REFERENCES nu fac nimic. Sunt incluse doar din motive de compatibilitate, pentru a facilita portarea codului de la alții Servere SQLși rulează aplicații care creează tabele cu legături. Vezi secțiunea 1.9.4 Diferențele dintre MySQL și ANSI SQL92.
Următoarele exemple arată câteva dintre utilizările comenzii ALTER TABLE. Exemplul începe cu tabelul t1, care este creat astfel:
Mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));
Pentru a redenumi un tabel din t1 în t2:
mysql> ALTER TABLE t1 RENAME t2;
Pentru a schimba tipul unei coloane din INTEGER în TINYINT NOT NULL (păstrând același nume) și schimbați tipul coloanei b din CHAR(10) în CHAR(20) și redenumiți-o din b în c:
Mysql> ALTER TABLE t2 MODIFICA a TINYINT NOT NULL, CHANGE b c CHAR(20);
Pentru a adăuga o nouă coloană TIMESTAMP numită d:
Mysql> ALTER TABLE t2 ADD d TIMESTAMP;
Pentru a adăuga un index la coloana d și a face din coloana a cheia primară:
Mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);
Pentru a elimina coloana c:
mysql> ALTER TABLE t2 DROP COLUMN c;
Pentru a adăuga o nouă coloană numerică AUTO_INCREMENT numită c:
Mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (c);
Rețineți că coloana c este indexată deoarece coloanele AUTO_INCREMENT trebuie indexate, iar coloana c este declarată NOT NULL deoarece coloanele indexate nu pot fi NULL .
Când adăugați o coloană AUTO_INCREMENT, valorile din acea coloană sunt completate automat cu numere secvențiale (când sunt adăugate înregistrările). Primul număr de secvență poate fi setat prin lansarea comenzii SET INSERT_ID=# înainte de ALTER TABLE sau folosind opțiunea tabel AUTO_INCREMENT = #. Vezi secțiunea 5.5.6 Sintaxa comenzii SET.
Dacă coloana AUTO_INCREMENT pentru tabelele MyISAM nu se modifică, atunci numărul de secvență rămâne același. Dacă aruncați o coloană AUTO_INCREMENT și apoi adăugați o altă coloană AUTO_INCREMENT, numerele vor începe din nou la 1 .
Dacă proiectul dvs. are tabele a căror dimensiune este calculată în gigaocteți, iar pentru a modifica structura unui astfel de tabel trebuie să opriți toate serviciile timp de câteva ore - acest articol va fi pentru dvs.
Dat: un tabel cu o dimensiune de câteva zeci de gigaocteți de date. Sarcina este de a schimba structura tabelului.
Voi alerga imediat, metoda va funcționa numai pe tabelele tranzacționale. Dacă aveți un tabel MyISAM pentru zeci de gigaocteți, atunci este ca în gluma aceea - „tratați-vă propriile probleme”. Un exemplu va fi dat pentru un tabel InnoDB.
Să presupunem că structura tabelului nostru este astfel:
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", CHEIE PRIMARĂ (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
Dorim să adăugăm un câmp last_login la acest tabel.
Ce optiuni avem.
Față în față
ALTER TABLE `users` ADD COLUMN `last_login` int(11) NOT NULL DEFAULT 0;Opțiunea funcționează excelent în proiecte mici în care dimensiunea tabelului depășește rar 50.000 de înregistrări. Opțiunea nu ne convine. ALTER va fi executat prea mult timp și în tot acest timp tabelul va fi blocat atât pentru scriere, cât și pentru citire. În consecință, serviciul va trebui oprit pentru această perioadă.
Porniți creierul
De altfel, nu puteți atinge masa deloc, dar faceți un „users_lastvisits” separat:CREATE TABLE `users_lastvisits` (`user_id` int(11) NU NULL, `last_login` int(11) NU NULL DEFAULT "0", CHEIE PRIMARĂ (`user_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Acum vă puteți alătura cu tabelul last_login în toate interogările în care este nevoie de last_login. Desigur, va funcționa mai lent, iar adăugarea JOIN în interogări este, de asemenea, timp suplimentar, dar, în general, acest lucru este uneori suficient și vă puteți opri în acest moment.
Și totuși - trebuie să adăugați un câmp
Puteți crește replicarea master-slave, faceți ALTER pe serverul slave și apoi le schimbați. Sincer să fiu, nu am făcut niciodată asta, poate este mai ușor decât următoarea metodă, dar nu este întotdeauna posibil să crești replicarea.Calea mea este următoarea
Creăm un nou tabel cu o structură finală, facem declanșatoare pe primul tabel care va înregistra toate modificările, în același timp începem să transferăm date de la primul tabel la al doilea, iar la sfârșit „turnăm” cele modificate. date și redenumiți tabelele.Așadar, pregătim 2 tabele - primul cu structura dorită, al doilea pentru înregistrarea modificărilor.
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, CHEIE PRIMARĂ (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `users_updated_rows` (`id` int(11) NU NULL AUTO_INCREMENT, `row_id` int(11) NU NULL DEFAULT "0", `action` enum("actualizat","șters") NU NULL DEFAULT "actualizat" , CHEIE PRIMARĂ (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Configurați declanșatoarele:
DELIMITOR ;; CREATE TRIGGER users_after_delete DUPĂ ȘTERGERE PE utilizatori PENTRU FIECARE RÂND ÎNCEPE INSERT ÎN VALORI users_updated_rows (0, OLD.id, „șters”); SFÂRȘIT;; CREATE TRIGGER users_after_insert DUPĂ INSERT PE utilizatori PENTRU FIECARE RÂND BEGIN INSERT INTO users_updated_rows VALORI (0, NEW.id, „actualizat”); SFÂRȘIT;; CREATE TRIGGER users_after_update DUPĂ UPDATE PE utilizatori PENTRU FIECARE RÂND ÎNCEPE DACĂ (OLD.id != NEW.id) APOI INSERT ÎN users_updated_rows VALORI (0, OLD.id, „șters”); END IF; INSERT INTO users_updated_rows VALUES(0, NEW.id, „actualizat”); SFÂRȘIT;; DELIMITOR ;
Acum începem să turnăm. Pentru a face acest lucru, trebuie să deschideți 2 conexiuni la baza de date. Într-una, debordarea va continua, în cealaltă, va fi necesar să blocați masa pentru un timp pentru scris.
mysql> LOCK TABLES utilizatorii SCRIE; Interogare OK, 0 rânduri afectate (0,00 sec) mysql> -- declanșatoarele ar trebui deja setate mysql> TRUNCATE users_updated_rows; Interogare OK, 0 rânduri afectate (0,17 sec) mysql> -- rulați mysql> transfuzie într-o altă consolă INSERT INTO _users SELECT id, email, password_hash, registration_date, 0 FROM utilizatori; mysql> -- din nou în prima consolă mysql> UNLOCK TABLES;
Gata, acum, în timp ce tabelul este turnat, avem timp să ne gândim cum vom turna datele care s-au schimbat de la începutul turnării. Nu este nimic complicat aici - nu voi da scriptul, trebuie doar să luați o înregistrare din tabelul users_updated_rows în ordinea în care au fost adăugate (sortați după cheia primară) și să o actualizați sau să o ștergeți în tabelul _users;
Deci, turnarea mesei s-a încheiat deja, trebuie să turnați restul datelor. Începem scenariul. Scriptul trebuie să ruleze constant și să actualizeze toate intrările care sunt adăugate în jurnal, când toarnă toate datele, trebuie să redenumiți tabelele:
mysql> TRUNCATE users_updated_rows; Interogare OK, 0 rânduri afectate (0,16 sec) mysql> RENUMIREA utilizatorilor TABEL LA __utilizatori, _utilizatori la utilizatori; Interogare OK, 0 rânduri afectate (0,11 sec)
Este de remarcat faptul că în acest moment este posibilă o mică pierdere de date. interogările nu sunt executate atomic. Dacă acest lucru este critic, este mai bine să opriți serviciul pentru o perioadă, astfel încât să nu existe solicitări de modificare. Puteți, de exemplu, să eliminați permisiunile de scriere de la un utilizator și să executați comenzi sub alt utilizator.
Dacă totul este făcut corect, datele nu se vor pierde și practic nu vor exista întreruperi în serviciu. Care este ceea ce aveam nevoie. În același mod, puteți transfera date pe alt server, doar metoda de transfer se va schimba. În loc de
mysql> INSERT INTO _users SELECT id, email, password_hash, registration_date, 0 FROM utilizatori;
trebuie să turnați prin mysqldump:
$ mysqldump -h gazdă1 utilizatori db --single-transaction -ecQ | pv | mysql -h gazdă2
În acest fel, am reușit să transfer un tabel de 60Gb în dimensiune și 400 de milioane de rânduri pe un alt server fără a opri munca serviciilor în aproximativ 12 ore.
Apropo, bicicleta inventat de Facebookși a sunat
Pentru a utiliza instrucțiunea ALTER TABLE, aveți nevoie de privilegiile ALTER , INSERT și CREATE pe tabel.
Opțiunea IGNORE este o extensie MySQL la ANSI SQL92. Controlează modul în care funcționează ALTER TABLE atunci când există chei unice duplicat într-un tabel nou. Dacă opțiunea IGNORE nu este specificată, atunci procesul este anulat pentru această copie și este anulat. Dacă este specificat IGNORE, atunci pentru rândurile cu chei unice duplicate, este folosit doar primul rând, iar restul sunt eliminate.
Puteți rula mai multe instrucțiuni ADD , ALTER , DROP și CHANGE într-o singură instrucțiune ALTER TABLE. Aceasta este o extensie MySQL la ANSI SQL92 unde este permisă doar una dintre expresiile menționate într-o singură instrucțiune ALTER TABLE.
Opțiunile CHANGE col_name , DROP col_name și DROP INDEX sunt, de asemenea, extensii MySQL la ANSI SQL92.
Opțiunea MODIFY este o extensie Oracle a comenzii ALTER TABLE.
Cuvântul opțional COLUMN reprezintă „zgomot alb” și poate fi omis.
Când utilizați ALTER TABLE table_name RENAME TO new_name fără alte opțiuni, MySQL pur și simplu redenumește fișierele corespunzătoare tabelului dat. În acest caz, nu este nevoie să creați un tabel temporar. .
Declarația create_definition pentru ADD și CHANGE folosește aceeași sintaxă ca și pentru CREATE TABLE. Rețineți că această sintaxă include numele coloanei, nu doar tipul acesteia. .
O coloană poate fi redenumită folosind instrucțiunea CHANGE nume_coloană create_definition . Pentru a face acest lucru, trebuie să specificați numele vechi și noi ale coloanei și tipul ei actual. De exemplu, pentru a redenumi o coloană INTEGER de la a la b, puteți face următoarele:
mysql> ALTER TABLE T1 MODIFICARE a b INTEGER;
Dacă modificați tipul unei coloane, dar nu și numele acesteia, sintaxa expresiei CHANGE necesită în continuare specificarea ambelor nume de coloană, chiar dacă sunt aceleași. De exemplu:
mysql> ALTER TABLE T1 MODIFICARE b b BIGINT NU NUL;
Cu toate acestea, începând cu MySQL 3.22.16a, puteți utiliza și instrucțiunea MODIFY pentru a schimba tipul unei coloane fără a o redenumește:
mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
Când utilizați CHANGE sau MODIFY pentru a reduce lungimea unei coloane pe care este construit un index (de exemplu, un index pe primele 10 caractere ale unei coloane VARCHAR), nu puteți face coloana mai scurtă decât numărul de caractere indexate.
Când se schimbă tipul unei coloane folosind CHANGE sau MODIFY, MySQL încearcă să convertească datele în noul tip cât mai corect posibil.
În MySQL versiunea 3.22 și ulterioară, puteți folosi FIRST sau ADD ... AFTER column_name pentru a adăuga o coloană la o anumită poziție într-un rând de tabel. În mod implicit, coloana este adăugată la sfârșit. Începând cu MySQL 4.0.1, puteți utiliza și cuvintele cheie FIRST și AFTER în opțiunile CHANGE sau MODIFY.
Opțiunea ALTER COLUMN setează o nouă valoare implicită pentru o coloană sau elimină una veche. Dacă vechiul implicit este eliminat și coloana poate fi NULL , atunci noua implicită va fi NULL . Dacă coloana nu poate fi NULL , atunci MySQL atribuie o valoare implicită așa cum este descris în secțiune.
Opțiunea DROP INDEX elimină un index. Aceasta este o extensie MySQL la ANSI SQL92. .
Dacă coloanele sunt eliminate dintr-un tabel, acele coloane sunt, de asemenea, eliminate din orice index din care fac parte. Dacă toate coloanele care alcătuiesc un index sunt eliminate, atunci și acel index este eliminat.
Dacă tabelul conține o singură coloană, atunci acea coloană nu poate fi ștearsă. În schimb, puteți arunca tabelul dat folosind comanda DROP TABLE.
Opțiunea DROP PRIMARY KEY elimină indexul primar. Dacă nu există un astfel de index pe tabelul dat, atunci primul index UNIC din acel tabel este eliminat. (MySQL marchează prima cheie UNICĂ unică ca CHEIE PRIMARĂ dacă nu a fost specificată în mod explicit nicio CHEIE PRIMARĂ.) Atunci când un INDEX UNIC sau CHEIE PRIMARĂ este adăugat la un tabel, acesta este stocat înaintea celorlalte chei neunice, astfel încât cheile duplicate să poată fi identificate cât mai curând posibil.
Opțiunea ORDER BY vă permite să creați un nou tabel cu rânduri în ordinea specificată. Rețineți că tabelul creat nu va păstra această ordine de rânduri după inserări și ștergeri. În unele cazuri, această caracteristică poate face sortarea mai ușoară în MySQL dacă tabelul are un aranjament de coloane pe care ați dori să o aveți în viitor. Această opțiune este utilă în principal dacă cunoașteți dinainte o anumită ordine în care vor fi solicitate predominant rândurile. Utilizarea acestei opțiuni după transformări semnificative ale tabelului are ca rezultat o performanță mai bună.
Când utilizați comanda ALTER TABLE pe tabelele MyISAM, toți indecșii neunici sunt creați într-un pachet separat (similar cu REPAIR). Acest lucru va face ALTER TABLE mai rapid atunci când există mai mulți indecși.
Începând cu MySQL 4.0, caracteristica de mai sus poate fi activată în mod explicit. Comanda ALTER TABLE... DISABLE KEYS blochează MySQL să actualizeze indecși non-unici pe tabelele MyISAM. Puteți utiliza apoi comanda ALTER TABLE ... ENABLE KEYS pentru a recrea indecșii lipsă. Deoarece MySQL face acest lucru cu un algoritm special, care este mult mai rapid decât introducerea cheilor una câte una, blocarea cheilor poate fi o accelerare semnificativă pe matrice mari de inserții.
Folosind funcția mysql_info() C API, puteți determina câte înregistrări au fost copiate și (folosind IGNORE) câte înregistrări au fost șterse din cauza valorilor chei unice duplicate.
Expresiile FOREIGN KEY , CHECK și REFERENCES nu fac nimic în toate tipurile de tabel, cu excepția InnoDB. InnoDB acceptă ADD CONSTRAINT FOREIGN KEY (...) REFERINȚE ... (...) . Rețineți că InnoDB nu permite index_name . . Suportul pentru sintaxa FOREIGH KEY este oferit numai din motive de compatibilitate, pentru a facilita portarea codului de pe alte servere SQL și rularea aplicațiilor care creează tabele cu referințe. .
