Umbuzo we-MySQL uyiphatha kanjani idatha eyimpinda?Hlunga ukuze ususe isitatimende sedatha esiyimpinda

MySQLImibuzo iyisingatha kanjani idatha eyimpinda?Hlunga ukuze ususe isitatimende sedatha esiyimpinda

MySQL Iphatha idatha eyimpinda

Kungase kube namarekhodi ayimpinda kwamanye amathebula edatha ye-MySQL. Kwezinye izimo, sivumela ukuba khona kwedatha eyimpinda, kodwa ngezinye izikhathi sidinga ukususa le datha eyimpinda.

Kulesi sahluko, sizokwethula indlela yokuvimbela idatha eyimpinda kuthebula ledatha nokuthi ungayisusa kanjani idatha eyimpinda kuthebula ledatha.

Vimbela idatha eyimpinda kumathebula

Ungasetha inkambu eshiwo kuthebula ledatha le-MySQL njenge UKHIYE OYINHLOKO noma OKUHLUKILE (okuhlukile) Inkomba yokuqinisekisa ukuhluka kwedatha.

Ake sizame isibonelo: Azikho izinkomba nokhiye abayinhloko kuthebula elingezansi, ngakho ithebula livumela amarekhodi ayimpinda amaningi.

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

Uma ufuna ukusetha izinkambu igama_lokuqala nesibongo kuthebula, idatha ayikwazi ukuphinda, ungasetha imodi yokhiye oyinhloko ukuze usethe ukuhluka kwedatha. Uma usetha ukhiye oyinhloko okabili, inani elizenzakalelayo lalowo khiye. ayikwazi ukuba NULL, kodwa ingasethwa ukuze ithi NOT NULL.Ngokulandelayo:

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)
);

Uma sisetha inkomba eyingqayizivele, lapho-ke sifaka idatha eyimpinda, isitatimende se-SQL sizohluleka ukuqalisa ngempumelelo futhi siphonse iphutha.

Umehluko phakathi kwe-INSERT IGNORE IN nokuthi INSERT INTO wukuthi INSERT IGNORE iyiziba idatha esivele ikhona kusizindalwazi. Uma ingekho idatha kusizindalwazi, izofaka idatha entsha, futhi uma kunedatha, izokweqa le datha.Ngale ndlela, idatha ekhona ku-database ingagcinwa, futhi inhloso yokufaka idatha kugebe ingafinyelelwa.

Isibonelo esilandelayo sisebenzisa i-INSERT IGNORE INTO, esebenzisa ngaphandle kwephutha futhi ayifaki idatha eyimpinda kuthebula ledatha:

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 INDLELA YOKUZIBA Lapho ufaka idatha, ngemva kokusetha okuhlukile kwerekhodi, uma idatha eyimpinda ifakiwe, alikho iphutha elizobuyiswa, kodwa kuphela isexwayiso esizobuyiswa.Bese UBUSELELA KU-Uma kukhona irekhodi eliyinhloko noma eliyingqayizivele, lizosuswa kuqala.Faka irekhodi elisha.

Enye indlela yokusetha ukuhluka kwedatha yakho ukungeza inkomba UNIQUE kanje:

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

Izibalo ziyimpinda yedatha

Ngezansi sizobala inombolo yamarekhodi ayimpinda yegama_lokuqala kanye nesibongo_sethebula:

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

Isitatimende sombuzo esingenhla sizobuyisela inombolo yamarekhodi ayimpinda kuthebula lomuntu_tbl.Ngokuvamile, ukuze ubuze amanani ayimpinda, yenza okulandelayo:

• Nquma ukuthi iyiphi ikholomu equkethe amanani okungenzeka ayimpinda.
• Sebenzisa COUNT(*) ohlwini lokukhetha lwekholomu ukuze ufake kuhlu lawo makholomu.
• Amakholomu asohlwini lwe-GROUP BY isigatshana.
• Isigatshana esithi HAVING sibeka inani lezimpinda ezingaphezu kuka-1.

hlunga idatha eyimpinda

Uma udinga ukufunda idatha eyingqayizivele, ungasebenzisa igama elingukhiye elithi DISTINCT esitatimendeni esithi KHETHA ukuze uhlunge idatha eyimpinda.

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

Ungasebenzisa futhi i-GROUP BY ukuze ufunde idatha eyingqayizivele etafuleni:

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

khipha idatha

Uma ufuna ukususa impinda yedatha kuthebula ledatha, ungasebenzisa isitatimende se-SQL esilandelayo:

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;

Kunjalo, ungakwazi futhi ukwengeza I-INDEX (inkomba) kanye NO-PRIMAY KEY (ukhiye oyinhloko) kuthebula ledatha ukuze ususe amarekhodi ayimpinda kuthebula.Izindlela ezingezansi:

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