MySQL query ua li cas cov ntaub ntawv duplicate?Lim kom tshem tawm cov ntawv tshaj tawm duplicate

MySQLYuav ua li cas queries lis duplicate cov ntaub ntawv?Lim kom tshem tawm cov ntawv tshaj tawm duplicate

MySQL Tswj cov ntaub ntawv duplicate

Tej zaum yuav muaj cov ntaub ntawv duplicate hauv qee cov ntaub ntawv MySQL. Qee zaum, peb tso cai rau muaj cov ntaub ntawv duplicate, tab sis qee zaum peb kuj yuav tsum rho tawm cov ntaub ntawv duplicate no.

Hauv tshooj no, peb yuav qhia txog yuav ua li cas tiv thaiv cov ntaub ntawv sib npaug hauv cov ntaub ntawv cov ntaub ntawv thiab yuav ua li cas tshem tawm cov ntaub ntawv duplicate hauv cov ntaub ntawv.

Tiv thaiv cov ntaub ntawv duplicate hauv cov ntxhuav

Koj tuaj yeem teeb tsa qhov chaw teev tseg hauv MySQL cov ntaub ntawv li TSEEM CEEB lossis UNIQUE (tseem ceeb) Index los xyuas kom meej lub uniqueness ntawm cov ntaub ntawv.

Cia peb sim ua piv txwv: Tsis muaj indexes thiab cov yuam sij tseem ceeb hauv cov lus hauv qab no, yog li lub rooj tso cai rau ntau cov ntaub ntawv duplicate.

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

Yog tias koj xav teeb tsa lub teb first_name thiab last_name hauv lub rooj, cov ntaub ntawv tsis tuaj yeem rov ua dua, koj tuaj yeem teeb tsa ob lub hom tseem ceeb los teeb tsa qhov tsis sib xws ntawm cov ntaub ntawv. tsis tuaj yeem yog NULL, tab sis tuaj yeem teem rau TSIS TAU.Raws li hauv qab no:

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

Yog tias peb teeb tsa qhov ntsuas tshwj xeeb, tom qab ntawd thaum muab cov ntaub ntawv sib npaug, cov lus SQL yuav ua tsis tiav thiab pov qhov yuam kev.

Qhov txawv ntawm INSERT IGNORE INTO thiab INSERT INTO yog qhov INSERT IGNORE ignores cov ntaub ntawv uas twb muaj lawm nyob rau hauv lub database, Yog hais tias tsis muaj cov ntaub ntawv nyob rau hauv lub database, nws yuav ntxig cov ntaub ntawv tshiab, thiab yog tias muaj cov ntaub ntawv, nws yuav hla cov ntaub ntawv no.Ua li no, cov ntaub ntawv uas twb muaj lawm hauv cov ntaub ntawv tuaj yeem khaws cia, thiab lub hom phiaj ntawm kev ntxig cov ntaub ntawv hauv qhov sib txawv tuaj yeem ua tiav.

Cov piv txwv hauv qab no siv INSERT IGNORE INTO, uas ua haujlwm yam tsis muaj qhov yuam kev thiab tsis ntxig cov ntaub ntawv sib npaug rau hauv cov ntaub ntawv:

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)

INSERT IGNORE INTO Thaum tso cov ntaub ntawv, tom qab lub cim tshwj xeeb ntawm cov ntaub ntawv tau teeb tsa, yog tias cov ntaub ntawv duplicate raug tso, tsis muaj qhov yuam kev yuav raug xa rov qab, tab sis tsuas yog ceeb toom yuav raug xa rov qab.Thiab REPLACE INTO rau hauv yog tias muaj cov ntaub ntawv tseem ceeb lossis tshwj xeeb, rho tawm ua ntej.Ntxig ib cov ntaub ntawv tshiab.

Lwm txoj hauv kev los teeb tsa qhov tshwj xeeb ntawm koj cov ntaub ntawv yog ntxiv UNIQUE index zoo li no:

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

Statistics duplicate cov ntaub ntawv

Hauv qab no peb yuav suav cov lej ntawm cov ntaub ntawv sib npaug ntawm first_name thiab last_name hauv lub rooj:

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

Cov lus nug saum toj no yuav xa rov qab tus lej ntawm cov ntaub ntawv sib npaug hauv lub rooj person_tbl.Feem ntau, txhawm rau nug txog qhov tseem ceeb dua, ua cov hauv qab no:

• Txiav txim siab seb kab ntawv twg muaj cov txiaj ntsig sib npaug.
• Siv COUNT(*) hauv kem xaiv cov npe los sau cov kab ntawv.
• Cov kab uas teev nyob rau hauv GROUP BY clause.
• HAVING clause teev tus naj npawb ntawm kev rov ua dua ntau dua 1.

lim cov ntaub ntawv duplicate

Yog tias koj xav tau nyeem cov ntaub ntawv tshwj xeeb, koj tuaj yeem siv DISTINCT lo lus tseem ceeb hauv SELECT nqe lus los lim cov ntaub ntawv sib npaug.

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

Koj tuaj yeem siv GROUP BY los nyeem cov ntaub ntawv tshwj xeeb los ntawm lub rooj:

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

deduplicate cov ntaub ntawv

Yog tias koj xav rho tawm cov ntaub ntawv duplicate hauv cov ntaub ntawv, koj tuaj yeem siv cov lus hauv qab no SQL:

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;

Tau kawg, koj tuaj yeem ntxiv INDEX (index) thiab PRIMAY KEY (tus yuam sij tseem ceeb) hauv cov ntaub ntawv cov ntaub ntawv kom tshem tawm cov ntaub ntawv sib npaug hauv lub rooj.Cov txheej txheem raws li hauv qab no:

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