Sidee buu u maamulaa weydiinta MySQL?Shaandhee si aad meesha uga saarto bayaanka xogta nuqul ka mid ah

MySQLSidee ayay su'aalo u qabtaan xogta nuqul ka mid ah?Shaandhee si aad meesha uga saarto bayaanka xogta nuqul ka mid ah

MySQL Qabashada xogta nuqul ka mid ah

Waxaa laga yaabaa inay jiraan diiwaanno nuqul ah oo ku jira miisaska xogta MySQL qaarkood. Xaaladaha qaarkood, waxaan oggolaanaa jiritaanka xog nuqul ah, laakiin mararka qaarkood waxaan sidoo kale u baahanahay inaan tirtirno xogtan nuqulka ah.

Cutubkan, waxaan ku soo bandhigi doonaa sida looga hortago xogta nuqul ka mid ah shaxda xogta iyo sida loo tirtiro xogta nuqul ka mid ah shaxda xogta.

Ka ilaali xogta nuqul ka mid ah shaxda

Waxaad dejin kartaa goobta la cayimay ee miiska xogta MySQL sida FURAHA AASAASIGA AH ama UNIQUE (gaar ah) Tusmada si loo hubiyo kala duwanaanshaha xogta.

Aan isku dayno tusaale: Ma jiraan tusmooyinka iyo furayaasha aasaasiga ah ee shaxda hoose, markaa shaxdu waxa uu ogolaanayaa diiwaanno badan oo nuqul ah.

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

Haddii aad rabto inaad dejiso beeraha first_name iyo final_name ee shaxda, xogta laguma soo celin karo, waxaad dejin kartaa qaabka muhiimka ah ee labalaabka ah si aad u dejiso xogta gaarka ah. Ma noqon karto NULL, laakiin waxaa loo dejin karaa NO NOOL.Sida soo socota:

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

Haddii aan dejinno tusmooyin gaar ah, markaa marka la gelinayo xogta nuqul ka mid ah, bayaanka SQL wuxuu ku guuldareysan doonaa inuu si guul leh u fuliyo oo uu tuuro qalad.

Farqiga u dhexeeya INSERT IGNORE INTO iyo INSERT INTO ayaa ah INSERT IGNORE oo iska indhatira xogtii hore ugu jirtay kaydka xogta, hadii aanay wax xog ah ku jirin kaydka waxa ay galinaysaa xog cusub, hadii ay jirto xogna way ka boodi doontaa xogtan.Sidan, xogta jirta ee kaydka kaydka ayaa lagu ilaalin karaa, waxaana lagu gaadhi karaa ujeeddada xogta la gelinayo farqiga.

Tusaalaha soo socdaa waxa uu isticmaalaa INSERT IGNORE INTO, kaas oo fuliya khalad la'aan oo aan gelin xogta nuqul ka mid ah shaxda xogta:

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)

GELI ISKA GELI Marka xogta la gelinayo, ka dib marka la dejiyo kala duwanaanshaha diiwaanka, haddii xogta nuqulka ah la geliyo, wax qalad ah lama soo celin doono, laakiin digniin kaliya ayaa la soo celin doonaa.Oo BEDEL galka haddii uu jiro diiwaan asaasi ah ama gaar ah, marka hore tirtir.Geli rikoor cusub.

Dariiqa kale ee lagu dejin karo gaarnimada xogtaada waa in lagu daro tusmo UNIQUE sida tan:

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

Tirakoobka xogta nuqul

Hoos waxaan tirin doonaa tirada nuqullada diiwaannada ee first_name iyo final_name ee shaxda:

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

Bayaanka weydiinta sare ayaa soo celin doona tirada diiwaannada nuqulka ah ee ku jira miiska person_tbl.Guud ahaan, si aad u waydiiso qiyamka nuqulka ah, samee waxa soo socda:

• Go'aanso tiirka ka kooban qiyamka nuqul ee suurtogalka ah.
• Adeegso COUNT(*) tiirka dooro liiska si aad u taxdo tiirarkaas.
• Tiirarka ku taxan GROUP BY faqradda.
• QODOBKA LAHAANSHAHA ayaa dejinaya tirada soo noqnoqda ee ka badan 1.

sifee xogta nuqul ka mid ah

Haddii aad u baahan tahay inaad akhrido xog gaar ah, waxaad isticmaali kartaa ereyga muhiimka ah ee DISTINCT ee ku jira bayaanka SELECT si aad u shaandhayso xogta nuqul ka mid ah.

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

Waxa kale oo aad isticmaali kartaa GROUP BY si aad u akhrido xogta gaarka ah ee miiska:

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

xogta laga jaray

Haddii aad rabto inaad tirtirto xogta nuqul ka mid ah shaxda xogta, waxaad isticmaali kartaa bayaanka SQL ee soo socda:

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;

Dabcan, waxaad sidoo kale ku dari kartaa INDEX (index) iyo PRIMAY KEY (furaha aasaasiga ah) ee shaxda xogta si aad u tirtirto diiwaannada nuqulka ah ee miiska.Hababka sida hoos ku qoran:

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