Mubvunzo weMySQL unobata sei duplicate data?Sefa kuti ubvise duplicate data statement

MySQLMibvunzo inoita sei duplicate data?Sefa kuti ubvise duplicate data statement

MySQL Kubata duplicate data

Panogona kunge paine marekodhi mune mamwe matafura edata reMySQL Mune zvimwe zviitiko, tinobvumira kuvepo kwedata rakadzokororwa, asi dzimwe nguva tinodawo kudzima aya akadhindwa data.

Muchitsauko chino, isu tichasuma nzira yekudzivirira yakapetwa data mutafura yedata uye nzira yekudzima iyo yakapetwa data mutafura yedata.

Dzivirira zvakapetwa data mumatafura

Iwe unogona kuseta iyo yakatsanangurwa munda muMySQL data tafura se PRIMARY KEY kana UNIQUE (yakasarudzika) Index kuti ive nechokwadi chekusiyana kwedata.

Ngatiedzei muenzaniso: Iko hakuna ma index uye makiyi ekutanga mutafura iri pazasi, saka tafura inobvumira akawanda akadhindwa marekodhi.

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

Kana uchida kuseta minda first_name and last_name mutafura, data haigone kudzokororwa, unogona kuseta iyo double primary kiyi modhi yekuseta iyo data yakasiyana.Kana ukaseta kaviri primary kiyi, iyo default value yekiyi iyoyo. haigone kuve NULL, asi inogona kusetwa kuti NOT NULL.Sezvizvi:

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

Kana tikaseta yakasarudzika indekisi, saka kana tichiisa duplicate data, chirevo cheSQL chinotadza kuita zvinobudirira uye kukanda chikanganiso.

Musiyano uripo pakati peINSERT IGNORE INTO neINSERT INTO ndewekuti INSERT IGNORE inofuratira data yagara iripo mudhatabhesi.Kana pasina data mudhatabhesi inoisa data nyowani, uye kana paine data, inodarika data iyi.Nenzira iyi, iyo data iripo mudhatabhesi inogona kuchengetedzwa, uye chinangwa chekuisa data mugap chinogona kuwanikwa.

Muenzaniso unotevera unoshandisa INSERT IGNORE INTO, iyo inoita pasina kukanganisa uye isingaise yakapetwa data mutafura yedata:

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 MUKATI Pakuisa data, mushure mekunge iyo yakasarudzika yerekodhi yaiswa, kana duplicate data ikaiswa, hapana chikanganiso chinodzoserwa, asi yambiro chete inodzoserwa.Uye REPLACE IN kana paine yekutanga kana yakasarudzika rekodhi, tanga wadzima.Isa rekodhi itsva.

Imwe nzira yekuseta iyo yakasarudzika yedata rako kuwedzera UNIQUE index seizvi:

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 inodzokorora data

Pazasi isu tichaverenga nhamba yezvakapetwa marekodhi ekutanga_name nerekupedzisira_name mutafura:

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

Chirevo chemubvunzo chiri pamusoro chichadzosa nhamba yemarekodhi mumunhu_tbl tafura.Kazhinji, kubvunza nezve duplicate values, ita zvinotevera:

• Sarudza kuti ndeipi chikamu chine makoshero angango fanana.
• Shandisa COUNT(*) mukoramu sarudza rondedzero kunyora iwo makoramu.
• Makolamu akanyorwa muGROUP BY clause.
• Iyo HAVING clause inoisa nhamba yedzokororo yakakura kupfuura 1.

sefa duplicate data

Kana iwe uchida kuverenga yakasarudzika data, unogona kushandisa DISTINCT kiyi mune iyo SELECT statement kusefa duplicate data.

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

Iwe unogona zvakare kushandisa GROUP BY kuverenga yakasarudzika data kubva patafura:

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

deduplicate data

Kana iwe uchida kudzima duplicate data mutafura yedata, unogona kushandisa inotevera SQL chirevo:

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;

Ehe, iwe unogona zvakare kuwedzera INDEX (index) uye PRIMAY KEY (kiyi yekutanga) mutafura yedata kudzima marekodhi akadhindwa patafura.Nzira dziri pasi apa:

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