MySQL Realizează tranzacții ACID și integritate referențială

Orice aplicație care include o bază de date trebuie să respecte caracteristicile ACID, în aplicațiile și managerii de baze de date profesionale conceptul ACID se referă la caracteristicile sau proprietățile care garantează că tranzacțiile din bazele de date sunt efectuate în condiții de siguranță și de încredere. Mai exact, ACID înseamnă Atomicitate, Consistență, Izolare și Durabilitate.

O tranzacție într-o bază de date este, de exemplu, inserarea unei înregistrări sau, dacă o vedem ca un model de afaceri, Adăugare client nou, Modificare produs. Tranzacțiile produc întotdeauna o modificare, inserează, modifică, șterge, o interogare nu este o tranzacție, deoarece nu produce modificări.
Specificarea proprietăților ACID

Atomicitate

Proprietatea este cea care garantează și verifică dacă tranzacția a fost efectuată sau nu și această proprietate indică faptul că dacă o operațiune nu este efectuată completă, atunci aceasta este anulată, de exemplu, să presupunem că introducem o înregistrare și că serverul se blochează, o înregistrare este înregistrat la mijloc, apoi din cauza atomicității această înregistrare nu va fi înregistrată.
Un alt exemplu dacă se face o plată online și suma este dedusă din contul nostru, dar plata eșuează sau sistemul se blochează, atunci tranzacția este anulată și baza de date nu o înregistrează.

Coerență

Proprietatea garantează executarea tranzacțiilor care se pot încheia fără probleme. Acest concept are legătură cu integritatea bazei de date. Împiedică schimbarea datelor și pierderea sensului sau lipsa de referință, de exemplu, un client nu poate fi șters în timp ce a făcut o achiziție la un moment dat. Dacă doriți să ștergeți clientul, trebuie mai întâi să ștergeți toate facturile și datele referitoare la clientul respectiv.

Izolare

Proprietatea este cea care garantează că, dacă două sau mai multe tranzacții au loc în același timp, acestea vor fi executate una după alta și dacă sunt executate în paralel, fiecare o va face independent de cealaltă pentru a evita posibilele erori.

Durabilitate

Proprietatea este responsabilă pentru garantarea efectuării unei tranzacții și modificările efectuate de tranzacție sunt permanente, chiar și în cazul oricărei probleme, cum ar fi lipsa de energie electrică sau defecțiuni ale sistemului.
MySQL acceptă formatul InnoDB, care este o formă de stocare a datelor pentru MySQL, inclus ca format de tabel standard în toate distribuțiile MySQL. Acest format acceptă tranzacții de tip ACID și integritate referențială.
Vom face câteva exemple de implementare ACID cu Mysql, interogările ar putea fi apoi implementate în orice limbaj de programare. În această bază de date, fiecare utilizator va avea un nivel de privilegii și acțiuni pe care le poate executa în sistemul companiei. Ne vom concentra doar pe acea funcționalitate.
Vom începe prin crearea unei baze de date CompanyDB de la phpmyadmin, vom crea un tabel de utilizatori și vom selecta motorul de stocare InnoDB.

 - Structura tabelului pentru tabela `utilizatori` CREARE TABEL DACĂ NU EXISTE` utilizatori` („ userid` int (10) NOT NULL, `name` varchar (150) DEFAULT NULL) MOTOR = InnoDB AUTO_INCREMENT = 4 DEFAULT CHARSET = latin1;

Adăugăm câteva date în tabelul utilizatorilor

 INSERAȚI ÎN "utilizatori" ("userid", "nume") VALORI (1, "Carlos Alberte"), (2, "Pablo Callejos"), (3, "Ana Bolena");

În acest caz, creăm cheia principală a tabelului de utilizatori, care va fi userid

 - Indici ai tabelului „users` ALTER TABLE` users` ADAUGAȚI CHEIA PRIMARĂ („ userid ”);

În continuare vom crea tabelul nivelurilor

 - Structura tabelului pentru tabelul `nivele` CREARE TABEL DACĂ NU EXISTE` niveluri` (` levelid` int (11) NOT NULL, `level` varchar (50) DEFAULT NULL) MOTOR = InnoDB AUTO_INCREMENT = 4 DEFAULT CHARSET = latin1;

Adăugăm câteva date în tabelul nivelurilor

 INSERAȚI ÎN "nivele" ("levelid", "level") VALORI (1, "Basic"), (2, "Intermediar"), (3, "Advanced");

Apoi creăm tabelul de privilegii, unde vom indica nivelul permisiunilor fiecărui utilizator

 - Structura tabelului pentru tabelul `privilegii` CREAȚI TABELUL DACĂ NU EXISTE` privilegii` (` idprivilege` int (11) NOT NULL, `idlevel` int (11) NOT NULL DEFAULT '0',` idusuario` int (11) NOT NULL ) MOTOR = InnoDB AUTO_INCREMENT = 4 DEFAULT CHARSET = latin1;

Adăugăm câteva date în tabelul de privilegii

 INSERAȚI ÎN „privilegii” („privilegeid`,„ levelid`, „userid`) VALORI (1, 1, 1), (2, 2, 3), (3, 1, 2);

Apoi adăugăm relațiile din editorul SQL, atribuie o cheie străină care leagă privilegiile și tabela utilizatorilor prin ID-ul utilizatorului și cheia externă care leagă privilegiile cu nivelurile prin ID-ul nivelului.

 - Filtre pentru tabelul "privilegii" ALTER TABLE "privilegii" ADĂUGARE CONSTRAINT "cheie străină" `(„ userid`);

Apoi consultăm baza de date pentru a vedea dacă datele sunt corecte

 SELECȚIONEAZĂ utilizatori.nume, nivele.nivel DE LA utilizatori, nivele, privilegii UNDE privilegii.idlevel = nivele.levelid ANDutilizatori.utilizator = privilegii.utilizator

Să vedem cum funcționează în continuare, încercăm să inserăm privilegiile unui utilizator și nivel care nu există.
Declarația va fi următoarea INSERT INTO privilegii VALUES (privilegeid, userid, levelid); Prin urmare

 INSERAȚI ÎN VALOARE privilegii (6, 8,10);

Acest lucru dă o eroare, deoarece cheia externă a userid din tabelul de privilegii ar crea o inconsecvență dacă adăugăm un utilizator care nu există în tabelul utilizatorilor, aici evităm eroarea, cu formatul MySam datele ar fi fost salvate fără probleme.
În continuare vom încerca să ștergem un utilizator din tabelul de utilizatori:

 ȘTERGEȚI DE `utilizatori` UNDE userid = 3

La executarea instrucțiunii SQL ne va da o eroare, deoarece clientul nu poate fi șters deoarece are date în alte tabele în acest caz clientul cu id 3 se află în tabelul de privilegii, dacă dorim să-l ștergem trebuie mai întâi să îl eliminăm din toate mesele și după masa clientului.

Pentru a șterge acest tip de înregistrări cu chei străine, se folosește cea numită ștergere CASCADĂ, în care toate înregistrările legate de o anumită interogare vor fi șterse în toate tabelele în care au relații cheie străine. Pentru a efectua această tranzacție folosim funcția PE ȘTERGEREA CASCADEI.
Primul lucru va fi să acordați permisiunea de a șterge în cascadă, amintiți-vă că la pornirea implicită tipul de referință este RESTRICȚIE ceea ce indică faptul că datele din acel câmp al tabelului nu pot fi actualizate sau șterse, aceasta din motive de securitate, orice propoziție executată nu va putea efectua nicio tranzacție, așa că modificăm permisiunile de modificare și ștergere.

 ALTER TABLE `privilegii` ADAUGĂ CONSTRAINT` privilegii fk` CHEIE STRĂINĂ („ userid`) REFERENȚE` utilizatori` („userid”) PE ȘTERGEREA CASCADEI LA ACTUALIZAREA CASCADEI;

Realizăm din nou interogarea SQL

 ȘTERGEȚI DE `utilizatori` UNDE userid = 3

Apoi putem efectua interogarea sql pentru a verifica dacă nu mai este în niciun tabel:

 SELECȚIONEAZĂ utilizatori.nume, niveluri.nivel DE LA utilizatori, niveluri, privilegii UNDE privilegii.idlevel = nivele.idivel ȘI utilizatori.utilizator = privilegii.utilizator

Utilizatorul 3 a fost eliminat din tabelul de utilizatori și din tabelul de privilegii, deoarece userid-ul avea o cheie străină acolo.
Acest lucru este același pentru Actualizări atunci când se modifică, poate fi în cascadă pentru a menține integritatea referențială în Mysql și relația dintre tabele.
Să vedem ce se întâmplă dacă adăugăm date incorecte în insertul lanțului, de exemplu adăugăm un utilizator, dar când adăugăm privilegii obținem un id greșit

 INSERAȚI ÎN VALORILE utilizatorilor (5, „Julia Montaña”); INSERARE ÎN privilegii („privilegeid`,„ levelid`, „userid`) VALORI (6, 2, 6);

În acest caz, utilizatorul va fi salvat, dar nu și privilegiile sale, deoarece ID 6 nu există în tabelul utilizatorilor.V-a plăcut și ați ajutat acest tutorial?Puteți recompensa autorul apăsând acest buton pentru a-i oferi un punct pozitiv