Me pehea te patai a MySQL ki te whakahaere i nga raraunga taarua?Tātari hei tango i te tauākī raraunga tārite

MySQLMe pehea nga patai e whakahaere ana i nga raraunga taarua?Tātari hei tango i te tauākī raraunga tārite

MySQL Te whakahaere i nga raraunga taarua

He rekoata taarua pea kei etahi ripanga raraunga MySQL. I etahi wa, ka whakaaehia e matou te noho o nga raraunga taarua, engari i etahi wa ka hiahia ano matou ki te muku i enei raraunga taarua.

I tenei upoko, ka whakaatu me pehea te aukati i nga raraunga taarua i te ripanga raraunga me te whakakore i nga raraunga taarua i te ripanga raraunga.

Aukati i nga raraunga taarua ki nga ripanga

Ka taea e koe te tautuhi i te mara kua tohua ki te ripanga raraunga MySQL hei Kī TUATAHI ranei UNIQUE (ahurei) Taurangi hei whakarite i te ahurei o nga raraunga.

Me ngana tetahi tauira: Karekau he tohu tohu me nga taviri tuatahi i te ripanga i raro nei, na te ripanga ka taea nga rekoata takirua maha.

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

Ki te hiahia koe ki te tautuhi i nga mara tuatahi_ingoa_ingoa_whakamutunga ki te ripanga, kaore e taea te tukurua nga raraunga, ka taea e koe te whakarite i te aratau taviri matua takirua hei whakarite i te ahurei o te raraunga. kaore e taea te NULL, engari ka taea te tautuhi ki te NOT NULL.E whai ake nei:

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

Mena ka tautuhia he taurangi ahurei, katahi ka whakauruhia he raraunga taarua, ka kore te tauākī SQL e tutuki pai ka maka he hapa.

Ko te rereketanga i waenga i te INSERT IGNORE INTO me te INSERT INTO ko te INSERT IGNORE ka kore e aro ki nga raraunga kua noho ki roto i te paataka, mena karekau he raraunga kei roto i te paataka raraunga, ka whakauruhia he raraunga hou, a, mena he raraunga, ka pekehia enei raraunga.I tenei ara, ka taea te pupuri i nga raraunga o te waahi kei roto i te papaarangi, ka taea te whakatutuki i te kaupapa o te whakauru raraunga ki te waahi.

Ko te tauira e whai ake nei e whakamahi ana i te INSERT IGNORE INTO, ka mahia kaore he hapa, karekau hoki e whakauru i nga raraunga taarua ki te ripanga raraunga:

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 I te wa e whakauru ana i nga raraunga, i muri i te tautuhi i te ahurei o te rekoata, ki te whakauruhia nga raraunga taarua, kaore he hapa e whakahokia, engari he whakatupato anake ka whakahokia.A WHAKAARO ki roto ki te mea he rekoata tuatahi, ahurei ranei, mukua i te tuatahi.Whakauruhia he rekoata hou.

Ko tetahi atu huarahi ki te tautuhi i te ahurei o o raraunga ko te taapiri i tetahi taurangi UNIQUE penei:

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

Tatauranga tarite raraunga

Kei raro nei ka tatauhia te maha o nga rekoata takirua o te ingoa_mua_ingoa_whakamutunga i te ripanga:

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

Ko te tauākī uiui i runga ake ka whakahoki mai i te maha o ngā rekoata tārite i te ripanga person_tbl.I te nuinga o te waa, ki te uiui mo nga uara taarua, mahia enei e whai ake nei:

• Whakatauhia ko tehea te pou kei roto nga uara taarua.
• Whakamahia te COUNT(*) i roto i te rarangi tīpako tīwae hei whakarārangi i aua tīwae.
• Ngā tīwae kua whakarārangitia i roto i te rara RĀPĀ.
• Ka whakatauhia e te rara HAVING te maha o nga tukurua nui ake i te 1.

tātari raraunga tārite

Mena ka hiahia koe ki te panui i nga raraunga ahurei, ka taea e koe te whakamahi i te kupumatua DISTINCT i roto i te tauākī SELECT ki te tātari raraunga tārite.

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

Ka taea hoki te whakamahi GROUP BY ki te panui raraunga ahurei mai i te ripanga:

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

tango raraunga

Mena kei te hiahia koe ki te muku i nga raraunga taarua i te ripanga raraunga, ka taea e koe te whakamahi i te korero SQL e whai ake nei:

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;

Ae ra, ka taea ano e koe te taapiri i te INDEX (taupu) me te KEY TUATAHI (matua matua) ki te ripanga raraunga hei muku i nga rekoata takirua i te ripanga.Nga tikanga penei i raro nei:

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