E fa'afefea ona fa'aogaina e MySQL fesili fa'alua fa'amaumauga?Filifili e aveese fa'amatalaga fa'amatalaga fa'alua

MySQLE fa'afefea ona fa'atautaia e su'esu'ega fa'amatalaga fa'alua?Filifili e aveese fa'amatalaga fa'amatalaga fa'alua

MySQL Taulimaina o fa'amaumauga fa'alua

Atonu e iai fa'amaumauga fa'alua i nisi o laulau fa'amaumauga MySQL. I nisi tulaga, matou te fa'atagaina le i ai o fa'amatalaga fa'alua, ae o nisi taimi e mana'omia fo'i le tapeina o nei fa'amatalaga fa'alua.

I totonu o lenei mataupu, o le a matou faʻaalia pe faʻafefea ona puipuia faʻamatalaga faʻalua i le laulau faʻamaumauga ma le auala e tape ai faʻamatalaga faʻalua i le laulau faʻamaumauga.

Taofi fa'amatalaga fa'alua i laulau

E mafai ona e setiina le fanua faʻapitoa ile laulau faʻamaumauga MySQL pei KI UTAMAI pe TUSI (tulaga ese) Fa'asinomaga e fa'amautinoa ai le tulaga ese o fa'amaumauga.

Se'i o tatou fa'ata'ita'i se fa'ata'ita'iga: E leai ni fa'ailoga ma ki fa'amuamua i le laulau o lo'o i lalo, o lea e fa'atagaina ai e le laulau le tele o fa'amaumauga fa'alua.

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

Afai e te manaʻo e seti le fanua first_name ma le igoa mulimuli i le laulau, e le mafai ona toe faia le faʻamaumauga, e mafai ona e setiina le faʻailoga autu lua e seti ai le tulaga ese o faʻamaumauga. e le mafai ona NULL, ae mafai ona seti i le NOT NULL.E pei ona ta'ua i lalo:

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

Afai matou te setiina se faʻailoga tulaga ese, ona faʻapipiʻi lea o faʻamatalaga faʻalua, o le faʻamatalaga SQL o le a le manuia le faʻatinoina ma lafo se mea sese.

O le eseesega i le va o le INSERT IGNORE INTO ma le INSERT INTO o le INSERT IGNORE e le amanaʻiaina faʻamaumauga ua uma ona i ai i totonu o le database, afai e leai se faʻamatalaga i totonu o le database, o le a faʻapipiʻi faʻamatalaga fou, ma afai e iai faʻamaumauga, o le a faaseʻeina nei faʻamatalaga.I lenei auala, e mafai ona faʻasaoina faʻamaumauga o loʻo i ai i totonu o faʻamaumauga, ma e mafai ona ausia le faʻamoemoe o le faʻaofiina o faʻamatalaga i le va.

O le faʻataʻitaʻiga o loʻo i lalo e faʻaaogaina INSERT IGNORE INTO, lea e faʻatinoina e aunoa ma se mea sese ma e le faʻapipiʻi faʻamatalaga faʻalua i le laulau faʻamaumauga:

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 Pe a faʻapipiʻi faʻamatalaga, pe a uma ona faʻatulagaina le tulaga ese o le faʻamaumauga, afai e faʻapipiʻi faʻamatalaga faʻalua, e leai se mea sese e toe faʻafoʻi, ae naʻo se lapataiga o le a toe faʻafoʻi.Ma SUIA I totonu pe a iai se faʻamaumauga muamua pe tulaga ese, tape muamua.Fa'aofi se fa'amaumauga fou.

O le isi auala e seti ai le tulaga ese o au faʻamatalaga o le faʻaopoopoina lea o se faʻailoga UNIQUE pei o lenei:

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

Fa'amaumauga fa'alua fa'amaumauga

I lalo ifo o le a matou faitauina le numera o faʻamaumauga faʻalua o le igoa muamua ma le igoa mulimuli i le laulau:

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

Ole faʻamatalaga ole fesili o loʻo i luga o le a toe faʻafoʻi ai le numera o faʻamaumauga faʻalua i le laulau person_tbl.I se tulaga lautele, e fesiligia mo faʻalua faʻatau, fai mea nei:

• Su'e po'o fea koluma o lo'o iai ni fa'atusa fa'atusa.
• Fa'aoga le COUNT(*) ile koluma filifili lisi e lisi ai na koluma.
• Koluma o lo'o lisiina ile GROUP BY clause.
• O le fuaiupu HAVING e setiina le numera o toe fai e sili atu i le 1.

faamama fa'amatalaga fa'alua

Afai e te manaʻomia le faitauina o faʻamatalaga tulaga ese, e mafai ona e faʻaogaina le DISTINCT keyword i le SELECT faʻamatalaga e faamama ai faʻamatalaga faʻalua.

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

E mafai fo'i ona e fa'aoga GROUP BY e faitau ai fa'amatalaga tulaga ese mai se laulau:

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

aveese fa'amaumauga

Afai e te manaʻo e tape faʻamatalaga faʻalua i le laulau faʻamaumauga, e mafai ona e faʻaogaina le faʻamatalaga SQL lea:

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;

Ioe, e mafai foi ona e faaopoopo INDEX (index) ma PRIMAY KEY (primary key) i le laulau fa'amaumauga e tape ai fa'amaumauga fa'alua i le laulau.Metotia e pei ona taua i lalo:

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