Profitați de puterea MySQL cu comenzile sale avansate - Buzut

Am văzut într-un articol anterior cum să gestionez MySQL din linia de comandă. Așa că am învățat cum să creăm, modificăm, ștergem tabele și baze de date. De asemenea, am văzut cum să citim, să scriem, să ștergem și să sortăm date în aceste tabele. Aceste operațiuni constituie cea mai mare parte a muncii pe care o avem de făcut în bazele de date SQL.

Cu toate acestea, uneori trebuie să gestionăm date complexe, a căror organizare necesită o prelucrare mai extinsă. Prin urmare, vom vorbi despre index, chei primare, chei străine sau căutări FULLTEXT. Atâtea metode care ne vor ajuta să organizăm și să gestionăm scheme complexe de date.

Indexul coloanei

Indexurile sunt utilizate pentru a accelera interogările și sunt esențiale pentru crearea de chei, pe care le vom vedea mai târziu. Când este creat un index, motorul SQL stochează acest index într-o structură de date separată. Această structură de date conține valorile sortate special pentru aceste date. În acest fel, motorul SQL este capabil să localizeze foarte eficient datele căutate.

Prin urmare, indexurile permit accelerarea interogărilor care utilizează criterii de căutare, cum ar fi WHERE .

Pe de altă parte, indexurile ocupă spațiu în memorie și încetinesc INSERT și UPDATE, deoarece trebuie să scrieți indexul de fiecare dată. Apoi, este necesar să alegeți cu înțelepciune coloanele pentru a indexa și privilegia coloanele pe care se vor efectua adesea căutări, de exemplu.

Înainte de a intra în detaliile indexurilor și de a le crea, știți că este bineînțeles posibil să le afișați:

În acest sens, este posibil să facem un index pe mai multe coloane în același timp și putem folosi acest index chiar dacă nu facem o căutare pe toate coloanele indexate. Aceasta se numește degetul arătător din stânga.

Aveți un tabel de membri:

• numele de familie
• Nume
• pseudo

Faceți un index pe toate trei în același timp. Indexul va sorta numele de familie, apoi prenumele, apoi porecla. Deci, dacă doriți să căutați numai pe nume, ordinea va fi exact aceeași ca și când am fi creat un index numai pe nume.

La fel, dacă facem o căutare pe numele și prenumele, ordinea nu se mișcă încă în comparație cu un index din aceste două coloane. Pe de altă parte, dacă dorim să facem o căutare numai pe pseudo sau numai pe prenume, nu va funcționa.

De fapt, poreclele sunt ordonate după numele și prenumele care le preced în coloanele din stânga, înainte de a fi ele însele sortate alfabetic. De aceea se numește „din stânga”, funcționează atâta timp cât căutați toate coloanele din stânga. În caz contrar, trebuie să creați un alt index.

Mai multe tipuri de INDEX

UNIC

După cum sugerează și numele, se asigură că nu poate exista o singură valoare în tabel o singură dată. Acesta este, de exemplu, indexul pe care îl folosim în CHEIA PRIMARĂ .

TEXT COMPLET

Acest index permite căutări puternice pe conținut text. Acest tip de index funcționează numai pe câmpuri de tip TEXT, CHAR și VARCHAR (altfel nu mai este text!). Vom vedea apoi ce permite.

Notă: Nu este posibilă utilizarea tehnicii „index stâng” cu FULLTEXT. În plus, acest indice este disponibil numai cu motorul myisam.

SPATIAL

Acest indice este puțin specific, deoarece se referă la date spațiale. Dacă aveți nevoie de mai multe informații, RTFM;)

Crearea indexurilor

De asemenea, puteți crea indexuri direct în coloane. Ar trebui să utilizați cuvântul CHEIE sau UNIC dacă doriți un index unic. Această sintaxă este mai limitată, deoarece puteți defini indexuri pe o singură coloană la un moment dat și nici nu puteți specifica o limită de dimensiune în index (de exemplu, primele caractere ale unui caracter).

Adăugarea după crearea tabelului

Uneori, nu ne gândim la toate imediat și am dori să adăugăm un index pe un tabel deja existent. TABEL ALTER știi ?

Există o altă comandă, care permite să facă același lucru: CREARE INDEX .

Personal, îmi place mai puțin așa, deoarece trebuie să vă amintiți o comandă suplimentară atunci când ALTER TABLE o face foarte bine. În plus, veți vedea mai târziu că CREATE INDEX permite mai puține lucruri decât ALTER TABLE, cum ar fi ștergerea indexului:

Ușor, nu-i așa? ?!

Inserare sau actualizare

Indicii unici vă împiedică să inserați date duplicat. Dacă se încearcă o astfel de inserare, se ridică o eroare. Cu toate acestea, ele deschid o nouă posibilitate: UPSERT, înțelegând UPDATE sau INSERT .

Această comandă vă permite să inserați date numai dacă nu există deja. În caz contrar, puteți alege să nu faceți nimic sau să actualizați datele.

În exemplele următoare, presupunem că există un index unic pe telefon .

În acest exemplu, nimic nu se schimbă, evităm să aruncăm o eroare. Precizăm de fapt că, în cazul unei valori duplicat, ID-ul înregistrării va fi egal cu id-ul, deci nu se face nicio modificare.

Desigur, putem defini valori de modificat.

Aici, în cazul în care valoarea nu există, este creată, dar dacă există, coloanele telefonului și prenumelui sunt actualizate cu valorile la alegerea noastră.

În cele din urmă, puteți efectua actualizări dinamic. Acest lucru se face folosind cuvântul cheie VALUE (VALUE înainte de MariaDB 10.3.3). Cu acest cuvânt cheie, valoarea VALUE (coloană) este egală cu valoarea așa cum ar fi fost inserată. Pe de altă parte, dacă specificați coloană = coloană, atunci aceasta este valoarea curentă. La fel ca în primul exemplu, această posibilitate permite definirea unei expresii după UPDATE .

De data aceasta, dacă numărul de telefon există deja, atunci pur și simplu actualizăm numele și prenumele contactului.

Cercetare FULLTEXT

FULLTEXT este foarte puternic pentru cercetare. Tăie șirurile în cuvinte pentru a le analiza pe rând. Un cuvânt este o secvență de caractere fără spații (oh ?!) Sau două cuvinte separate printr-o subliniere „_” sau un apostrof. Cuvintele compuse separate prin cratime sunt considerate două cuvinte separate.

În plus, pentru a face această cercetare, FULLTEXT ia în considerare următoarele reguli:

• un cuvânt care apare în 50% din rânduri este ignorat (după relevanță),
• un cuvânt mai mic de patru litere este ignorat,
• cuvintele în engleză prea obișnuite sunt ignorate. Esti francez, fara noroc! Dar, este totuși posibil să specificați cuvintele de ignorat.

Căutarea FULLTEXT se face astfel:

Există 3 tipuri de căutări FULLTEXT:

• Căutarea ÎN MOD DE LIMBĂ NATURALĂ. Aceasta este valoarea implicită.
• În modul BOOLEAN de cercetare. Această căutare ignoră regula de 50% discutată mai sus și permite efectuarea căutărilor pe coloane care nu au fost indexate (totuși, atenție la timpul de execuție!). Ultimul punct cu modul boolean, nu există sortare după relevanță în rezultate.
• Căutarea CU EXTENSIE DE INTERROGARE. Aceasta este o cercetare extinsă. Acest tip de căutare face de fapt două căutări într-o singură interogare. Mai întâi trimite șirul căutat unei căutări NATURALE apoi lansează un al doilea cu parametri, nu mai șirul pe care îl căutați, ci rezultatele primei căutări. Acesta este motivul pentru care aceasta este o căutare extinsă.

Pentru a specifica tipul de căutare:

Veți înțelege că indexurile FULLTEXT sunt de mare interes atunci când vine vorba de efectuarea căutărilor pe masă. Din păcate, acest tip de index este prezent doar pe tabelele MyISAM. Și veți vedea mai târziu că InnoDB are multe puncte forte pe care MyISAM nu le are. Pe scurt, va trebui să facem o alegere.

---