Ngaba umbuzo weMySQL uyiphatha njani idatha ephindwe kabini?Hluza ukususa ingxelo yedatha ephindwe kabini

MySQLIngaba imibuzo iyiphatha njani idatha ephindwe kabini?Hluza ukususa ingxelo yedatha ephindwe kabini

MySQL Ukuphatha idatha ephindwe kabini

Kunokubakho iirekhodi eziphindwe kabini kwiitafile zedatha ye-MySQL Kwezinye iimeko, sivumela ubukho bedatha ephindwe kabini, kodwa ngamanye amaxesha kufuneka sicime le datha ephindwe kabini.

Kwesi sahluko, siya kwazisa indlela yokuthintela ukuphindaphinda idatha kwitheyibhile yedatha kunye nendlela yokucima idatha ephindwe kabini kwitheyibhile yedatha.

Thintela impinda yedatha kwiitheyibhile

Unokuseta indawo ekhankanyiweyo kwitafile yedatha ye-MySQL njenge ISIQINISO ESIYINTLOKO okanye EYODWA (eyohlukileyo) Isalathiso sokuqinisekisa ukungafani kwedatha.

Makhe sizame umzekelo: Akukho zizalathisi kunye nezitshixo eziphambili kwitheyibhile engezantsi, ngoko ke itheyibhile ivumela iirekhodi eziphindwe kabini.

CREATE TABLE person_tbl
(
    first_name CHAR(20),
    last_name CHAR(20),
    sex CHAR(10)
);

Ukuba ufuna ukuseta amasimi first_name kunye ne last_name kwitheyibhile, idata ayinakuphindwa, ungacwangcisa imowudi yesitshixo esiphindiweyo ukuseta okwahlukileyo kwedatha Ukuba ucwangcisa iqhosha eliphambili eliphindwe kabini, ixabiso elingagqibekanga lelo qhosha. ayinakuba NULL, kodwa inokusetwa ku NOT NULL.Ngoku landelayo:

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

Ukuba sibeka isalathiso esisodwa, ngoko xa sifaka idatha ephindwe kabini, ingxelo ye-SQL iya kusilela ukuphumeza ngempumelelo kwaye iphose imposiso.

Umahluko phakathi kwe-INSERT IGNORE INTO kunye ne-INSERT INTO kukuba i-INSERT IGNORE iyayihoya idatha esele ikhona kwi-database.Ukuba akukho datha kwi-database, iya kufaka idatha entsha, kwaye ukuba kukho idatha, iya kudlula le data.Ngale ndlela, idatha ekhoyo kwi-database ingagcinwa, kwaye injongo yokufaka idatha kwi-gap inokufezekiswa.

Lo mzekelo ulandelayo usebenzisa INSERT IGNORE INTO, eyenza ngaphandle kwempazamo kwaye ngaphandle kokufaka impinda yedatha kwitheyibhile yedatha:

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)

FAKA UKUNGANIKWA KWEXESHA Xa ufaka idatha, emva kokusetwa okukodwa kwerekhodi, ukuba idatha ephindwe kabini ifakiwe, akukho mpazamo iya kubuyiselwa, kodwa isilumkiso kuphela esiya kubuyiselwa.Kwaye BUYISE endaweni ukuba kukho irekhodi eliphambili okanye elikhethekileyo, licime kuqala.Faka irekhodi entsha.

Enye indlela yokuseta okwahlukileyo kwedatha yakho kukongeza isalathiso se-UNIQUE ngolu hlobo:

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10)
   UNIQUE (last_name, first_name)
);

Iinkcukacha-manani ziphindaphinda idatha

Ngezantsi siza kubala inani leerekhodi eziphindiweyo zegama_lokuqala kunye negama_lokugqibela kwitheyibhile:

mysql> SELECT COUNT(*) as repetitions, last_name, first_name
    -> FROM person_tbl
    -> GROUP BY last_name, first_name
    -> HAVING repetitions > 1;

Le ngxelo yombuzo ingentla iyakubuyisela inani leerekhodi eziphindiweyo kwitheyibhile yomntu_tbl.Ngokubanzi, ukubuza amaxabiso aphindwe kabini, yenza oku kulandelayo:

• Qinisekisa ukuba ngowuphi umhlathi oqulathe amaxabiso aphindwayo anokubakho.
• Sebenzisa COUNT(*) kuluhlu lokukhetha uluhlu ukudwelisa loo mihlathi.
• Imihlathi edweliswe kwiQELA NGEGAtya.
• Igatya elithi HAVING limisela inani lokuphindaphinda okukhulu kuno-1.

Isihluzi sedatha ephindwe kabini

Ukuba ufuna ukufunda idatha ekhethekileyo, ungasebenzisa igama elingundoqo le-DISTINCT kwi-SELECT statement ukucoca idatha ephindwe kabini.

mysql> SELECT DISTINCT last_name, first_name
    -> FROM person_tbl;

Unokusebenzisa IQELA NGEQELA ukufunda idatha eyodwa kwitafile:

mysql> SELECT last_name, first_name
    -> FROM person_tbl
    -> GROUP BY (last_name, first_name);

khuphela idatha

Ukuba ufuna ukucima impinda yedatha kwitheyibhile yedatha, ungasebenzisa le ngxelo ilandelayo yeSQL:

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
    ->                  FROM person_tbl;
    ->                  GROUP BY (last_name, first_name, sex);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

Ngokuqinisekileyo, unokongeza i-INDEX (isalathisi) kunye ne-PRIMAY KEY (iqhosha eliphambili) kwitheyibhile yedatha ukucima iirekhodi eziphindwe kabini kwitheyibhile.Iindlela ezingezantsi:

mysql> ALTER IGNORE TABLE person_tbl
    -> ADD PRIMARY KEY (last_name, first_name);