Kodi funso la MySQL limagwira bwanji deta yobwereza?Sefa kuti muchotse chibwereza cha data

MySQLKodi mafunso amayankha bwanji zobwereza?Sefa kuti muchotse chibwereza cha data

MySQL Kusamalira deta yobwereza

Pakhoza kukhala zolemba zobwereza m'matebulo ena a MySQL. Nthawi zina, timalola kukhalapo kwa deta yobwereza, koma nthawi zina timafunikanso kuchotsa deta yobwereza.

M'mutu uno, tikuwonetsani momwe mungapewere kubwereza deta mu tebulo la data ndi momwe mungachotsere zomwe zili mu tebulo la data.

Pewani zobwereza zomwe zili m'matebulo

Mutha kukhazikitsa gawo lomwe mwatchulidwa patebulo la data la MySQL ngati PRIMARY KEY kapena UNIQUE (yapadera) Index kuti muwonetsetse kuti deta ndi yapadera.

Tiyeni tiyese chitsanzo: Palibe ma index ndi makiyi oyambira patebulo ili m'munsiyi, kotero tebulo limalola zolemba zingapo.

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

Ngati mukufuna kuyika minda first_name ndi last_name patebulo, datayo siyingabwerezedwe, mutha kukhazikitsa makiyi oyambira awiri kuti muyike kusiyanasiyana kwa datayo. sichingakhale NULL, koma ikhoza kukhazikitsidwa kuti OSATI NULL.Motere:

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

Ngati tiyika cholozera chapadera, ndiye kuti tikayika zobwereza, mawu a SQL adzalephera kuchita bwino ndikuponya cholakwika.

Kusiyana pakati pa INSERT IGNORE INTO ndi INSERT INTO ndikuti INSERT IGNORE imanyalanyaza zomwe zilipo kale mu database.Ngati mulibe data mu database, imayika zatsopano, ndipo ngati pali data, idumpha datayi.Mwa njira iyi, deta yomwe ilipo mu database ikhoza kusungidwa, ndipo cholinga choyika deta mumpata chikhoza kukwaniritsidwa.

Chitsanzo chotsatirachi chimagwiritsa ntchito INSERT IGNORE INTO, yomwe imagwira ntchito popanda kulakwitsa komanso popanda kuyika deta yobwereza patebulo la deta:

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)

LOWANI ZOSAGWIRITSA NTCHITO Pamene mukuyika deta, pambuyo pa kukhazikitsidwa kwapadera kwa zolemba, ngati deta yobwereza yayikidwa, palibe cholakwika chomwe chidzabwezedwe, koma chenjezo lokha lidzabwezedwa.Ndipo BWINO KWAMBIRI mu Ngati pali mbiri yoyambirira kapena yapadera, imachotsedwa kaye.Ikani mbiri yatsopano.

Njira ina yokhazikitsira kusiyanasiyana kwa data yanu ndikuwonjezera index ya UNIQUE monga chonchi:

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

Ziwerengero zibwereza deta

Pansipa tiwerengera kuchuluka kwa zobwereza za first_name ndi last_name patebulo:

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

Mawu omwe ali pamwambawa abweza chiwerengero cha malekodi obwereza patebulo la person_tbl.Nthawi zambiri, kuti mufunse zobwereza, chitani izi:

• Dziwani kuti ndi gawo liti lomwe lili ndi mayendedwe obwereza.
• Gwiritsani ntchito COUNT(*) pamndandanda sankhani mndandanda kuti mulembe zigawozo.
• Mizati yolembedwa mu GROUP BY clause.
• Ndime ya HAVING imayika kuchuluka kwa kubwereza kuposa 1.

zosefera zobwereza

Ngati mukufuna kuwerengera data yapaderadera, mutha kugwiritsa ntchito mawu osakira a DISTINCT mu SELECT statement kuti musefe zomwe zabwerezedwa.

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

Mukhozanso kugwiritsa ntchito GROUP BY kuti muwerenge deta yapadera patebulo:

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

deduplicate deta

Ngati mukufuna kuchotsa zobwereza patebulo la data, mutha kugwiritsa ntchito mawu awa a 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;

Zachidziwikire, mutha kuwonjezeranso INDEX (index) ndi PRIMAY KEY (kiyi yoyambira) patebulo la data kuti muchotse zolemba zomwe zili patebulo.Njira monga pansipa:

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