MySQL ACID-transacties en referentiële integriteit uitvoeren

Elke applicatie die een database bevat, moet voldoen aan ACID-kenmerken, in de applicaties en professionele databasebeheerders verwijst het ACID-concept naar de kenmerken of eigenschappen die garanderen dat de transacties in de databases veilig en configureerbaar worden uitgevoerd. In het bijzonder staat ACID voor Atomiciteit, Consistentie, Isolatie en Duurzaamheid.

Een transactie binnen een database is bijvoorbeeld het invoegen van een record of, als we het als businessmodel zien, Nieuwe klant toevoegen, Product wijzigen. Transacties produceren altijd een wijziging, invoegen, wijzigen, verwijderen, een query is geen transactie omdat deze geen wijzigingen produceert.
ACID-eigenschappen specificeren

Atomiciteit

Het is de eigenschap die garandeert en verifieert of de transactie is uitgevoerd of niet en deze eigenschap geeft aan dat als een bewerking niet volledig wordt uitgevoerd, deze wordt geannuleerd, bijvoorbeeld stel dat we een record invoegen en de server crasht, een record wordt opgenomen in het midden, dan wordt dit record vanwege atomiciteit niet opgenomen.
Een ander voorbeeld als er een online betaling wordt gedaan en het bedrag wordt van onze rekening afgeschreven maar de betaling mislukt of het systeem crasht, dan wordt de transactie geannuleerd en registreert de database het niet.

Samenhang

Het is de eigenschap die garandeert dat transacties die probleemloos kunnen worden uitgevoerd, worden uitgevoerd. Dit concept heeft te maken met de integriteit van de database. Het voorkomt dat de gegevens worden gewijzigd en betekenis verliezen of zonder enige referentie zijn, een klant kan bijvoorbeeld niet worden verwijderd terwijl hij op een bepaald moment een aankoop heeft gedaan. Als u de klant wilt verwijderen, moet u eerst alle facturen en gegevens met betrekking tot die klant verwijderen.

Isolatie

Het is de eigenschap die garandeert dat als twee of meer transacties tegelijkertijd plaatsvinden, ze na elkaar worden uitgevoerd en als ze parallel worden uitgevoerd, zal elk onafhankelijk van elkaar doen om mogelijke fouten te voorkomen.

Duurzaamheid

Het is het eigendom dat verantwoordelijk is voor het garanderen dat een transactie is uitgevoerd en dat de wijzigingen die door de transactie zijn aangebracht permanent zijn, zelfs in het geval van een probleem zoals een gebrek aan elektriciteit of systeemstoringen.
MySQL ondersteunt het InnoDB-formaat, een vorm van gegevensopslag voor MySQL, opgenomen als een standaard tabelformaat in alle MySQL-distributies. Dit formaat ondersteunt transacties van het ACID-type en referentiële integriteit.
We gaan enkele voorbeelden doen van ACID-implementatie met Mysql, de vragen kunnen dan in elke programmeertaal worden geïmplementeerd. In deze database heeft elke gebruiker een bepaald niveau van privileges en acties die ze kunnen uitvoeren in het systeem van het bedrijf. We zullen ons alleen op die functionaliteit concentreren.
We beginnen met het maken van een database BedrijfDB van phpmyadmin, dan gaan we een gebruikerstabel maken en de InnoDB-opslagengine selecteren.

 - Tabelstructuur voor tabel `gebruikers` MAAK TAFEL ALS NIET BESTAAT` gebruikers` (`userid` int (10) NOT NULL,` name` varchar (150) DEFAULT NULL) ENGINE = InnoDB AUTO_INCREMENT = 4 STANDAARD CHARSET = latin1;

We voegen wat gegevens toe aan de gebruikerstabel

 INSERT IN `users` (` userid`, `name`) WAARDEN (1, 'Carlos Alberte'), (2, 'Pablo Callejos'), (3, 'Ana Bolena');

In dit geval maken we de primaire sleutel van de gebruikerstabel die userid zal zijn

 - Indexen van de tabel `users` ALTER TABLE` users` ADD PRIMARY KEY (`userid`);

Vervolgens zullen we de niveaustabel maken

 - Tabelstructuur voor tabel `levels` CREATE TABLE IF NOT EXISTS` levels` (`levelid` int (11) NOT NULL,` level` varchar (50) DEFAULT NULL) ENGINE = InnoDB AUTO_INCREMENT = 4 DEFAULT CHARSET = latin1;

We voegen wat gegevens toe aan de niveaustabel

 INSERT INTO `levels` (` levelid`, `level`) WAARDEN (1, 'Basic'), (2, 'Intermediate'), (3, 'Advanced');

Vervolgens maken we de privilegetabel, waarin we het machtigingsniveau van elke gebruiker aangeven

 - Tabelstructuur voor tabel `privileges` CREATE TABLE IF NOT EXISTS` privileges` (`idprivilege` int (11) NOT NULL,` idlevel` int (11) NOT NULL DEFAULT '0', `idusuario` int (11 ) NOT NULL ) MOTOR = InnoDB AUTO_INCREMENT = 4 STANDAARD CHARSET = latin1;

We voegen wat gegevens toe aan de tabel met privileges

 INSERT INTO `privileges` (` privilegeid`, `levelid`,` userid`) WAARDEN (1, 1, 1), (2, 2, 3), (3, 1, 2);

Vervolgens voegen we de relaties toe vanuit de SQL-editor, ik wijs een externe sleutel toe die de privileges en gebruikerstabel relateert via gebruikers-ID en de externe sleutel die privileges relateert aan niveaus via niveau-ID.

 - Filters voor de tabel `privileges` ALTER TABLE` privileges` ADD CONSTRAINT `levellfk` FOREIGN KEY (` levelid`) REFERENTIES `levels` (` levelid`), ADD CONSTRAINT `privilegesfk` FOREIGN KEY (` userid`) REFERENTIES ` gebruikers ` (`gebruikers-ID`);

Vervolgens raadplegen we de database om te kijken of de gegevens correct zijn

 SELECT user.name, levels.level FROM gebruikers, levels, privileges WHERE privileges.idlevel = levels.levelid ANDusers.userid = privileges.userid

Laten we eens kijken hoe het werkt. Vervolgens proberen we de privileges toe te voegen aan een gebruiker en niveau die niet bestaan.
De instructie is de volgende INSERT INTO privileges VALUES (privilegeid, userid, levelid); Dus

 INSERT IN privileges WAARDEN (6, 8,10);

Dit geeft een fout omdat de externe sleutel van userid in de privilegetabel een inconsistentie zou creëren als we een gebruiker toevoegen die niet bestaat in de gebruikerstabel, hier vermijden we de fout, met het formaat MijnSam de gegevens zouden zonder problemen zijn opgeslagen.
Vervolgens zullen we proberen een gebruiker uit de gebruikerstabel te verwijderen:

 VERWIJDEREN VAN `gebruikers` WAAR gebruikers-ID = 3

Bij het uitvoeren van de SQL-instructie geeft het ons een foutmelding, omdat de client niet kan worden verwijderd omdat deze gegevens in andere tabellen heeft. In dit geval staat de client met id 3 in de privilegetabel, als we deze willen verwijderen, moeten we deze eerst verwijderen vanaf alle tafels en na de klantentafel.

Om dit type records met externe sleutels te verwijderen, wordt de naam delete gebruikt in WATERVAL, waarin alle records met betrekking tot een bepaalde query worden verwijderd in alle tabellen waarin ze externe-sleutelrelaties hebben. Om deze transactie uit te voeren, gebruiken we de functie OP VERWIJDEREN CASCADE.
Het eerste is om toestemming te geven om in cascade te verwijderen, onthoud dat bij het standaard starten het referentietype is BEPERKEN wat aangeeft dat de gegevens in dat veld van de tabel niet kunnen worden bijgewerkt of verwijderd, dit is om veiligheidsredenen, elke zin die wordt uitgevoerd, kan geen transactie uitvoeren, dus we wijzigen de wijzigings- en verwijderingsrechten.

 WIJZIG TABEL `privileges` ADD CONSTRAINT` privilegesfk` BUITENLANDSE SLEUTEL (`userid`) REFERENTIES` gebruikers` (`userid`) BIJ VERWIJDEREN CASCADE BIJ UPDATE CASCADE;

We voeren de SQL-query opnieuw uit

 VERWIJDEREN VAN `gebruikers` WAAR gebruikers-ID = 3

Vervolgens kunnen we de sql-query uitvoeren om te controleren of deze niet langer in een tabel staat:

 SELECT user.name, levels.level VAN gebruikers, levels, privileges WAAR privileges.idlevel = levels.idlevel EN users.userid = privileges.userid

Gebruiker 3 is verwijderd uit de gebruikerstabel en ook uit de privilegetabel, omdat de userid daar een externe sleutel had.
Dit is hetzelfde voor Updates bij het wijzigen, het kan worden gecascadeerd om de referentiële integriteit in Mysql en de relatie tussen tabellen te behouden.
Laten we eens kijken wat er gebeurt als we onjuiste gegevens toevoegen aan de chain insert, we voegen bijvoorbeeld een gebruiker toe, maar als we privileges toevoegen, krijgen we de verkeerde id

 INVOEREN IN WAARDEN van gebruikers (5, 'Julia Montaña'); INSERT INTO privileges (`privilegeid`,` levelid`, `userid`) WAARDEN (6, 2, 6);

In dit geval wordt de gebruiker opgeslagen, maar niet zijn privileges omdat ID 6 niet bestaat in de gebruikerstabel.Vond je deze Tutorial leuk en heb je eraan geholpen?Je kunt de auteur belonen door op deze knop te drukken om hem een ​​positief punt te geven