Potso ea MySQL e sebetsana joang le data e kopitsoang?Sefa ho tlosa polelo ea data e phetoang

MySQLLipotso li sebetsana joang le lintlha tse peli?Sefa ho tlosa polelo ea data e phetoang

MySQL Ho sebetsana le lintlha tse peli

Ho ka 'na ha e-ba le lirekoto tse kopitsoang litafoleng tse ling tsa data tsa MySQL.Maemong a mang, re lumella boteng ba data e kopitsoang, empa ka linako tse ling hape re hloka ho hlakola lintlha tsena tse kopitsoang.

Khaolong ena, re tla hlahisa mokhoa oa ho thibela data e kopitsoang tafoleng ea data le mokhoa oa ho hlakola data e kopitsoang tafoleng ea data.

Thibela phetiso ea data litafoleng

O ka beha sebaka se boletsoeng tafoleng ea data ea MySQL joalo ka TŠEBELETSO TLHOKO kapa FEELA (e ikhethang) Index ho netefatsa bo ikhethang ba data.

Ha re leke mohlala: Ha ho na li-index le linotlolo tsa mantlha tafoleng e ka tlase, kahoo tafole e lumella lirekoto tse ngata tse kopitsoang.

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

Haeba u batla ho seta masimo first_name le last_name ka har'a tafole, data e ke ke ea phetoa, u ka seta mokhoa oa konopo oa mantlha oa habeli ho seta bo ikhethang ba data. e ka se be NULL, empa e ka hlophisoa ho NOT NULL.Ka mokoa o latelang:

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

Haeba re beha index e ikhethang, joale ha re kenya data e kopitsoang, polelo ea SQL e tla hloleha ho sebetsa ka katleho le ho lahlela phoso.

Phapang pakeng tsa INSERT IGNORE INTO le INSERT INTO ke hore INSERT IGNORE e hlokomoloha lintlha tse seng li ntse li le teng ka har'a database.Ka tsela ena, lintlha tse teng ho database li ka bolokoa, 'me morero oa ho kenya data ka lekhalo o ka finyelloa.

Mohlala o latelang o sebelisa INSERT IGNORE INTO, e sebetsang ntle le phoso ebile e sa kenye lintlha tse peli tafoleng ea data:

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)

KENYA IGNORE HO Ha o kenya data, ka mor'a hore ho behoe ho ikhethang ha rekoto, haeba lintlha tse kopitsoang li kentsoe, ha ho phoso e tla khutlisoa, empa ke temoso feela e tla khutlisoa.'Me U HLAHELE HO SEBELISA haeba ho na le rekoto ea mantlha kapa e ikhethang, e hlakole pele.Kenya rekoto e ncha.

Mokhoa o mong oa ho beha bo ikhethang ba data ea hau ke ho kenyelletsa index ea UNIQUE joalo ka:

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

Lipalo-palo li kopiletsa lintlha

Ka tlase re tla bala palo ea lirekoto tse kopitsoang tsa first_name le last_name tafoleng:

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

Polelo ea potso e kaholimo e tla khutlisa palo ea lirekoto tse habeli tafoleng ea person_tbl.Ka kakaretso, ho botsa boleng bo habeli, etsa se latelang:

• Etsa qeto ea hore na ke kholomo efe e nang le boleng bo ka bang teng habeli.
• Sebelisa COUNT(*) kholomong ea khetho ea lenane ho thathamisa likholomo tseo.
• Likholomo tse thathamisitsoeng ho GROUP BY clause.
• Temana ea HAVING e beha palo ea ho pheta-pheta ho feta 1.

sefa likopi tsa data

Haeba o hloka ho bala lintlha tse ikhethang, o ka sebelisa DISTINCT poleloana e reng KHETHA ho sefa likopi tsa data.

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

U ka sebelisa GROUP BY ho bala lintlha tse ikhethang tse tsoang tafoleng:

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

hlakola data

Haeba u batla ho hlakola data e habeli tafoleng ea data, u ka sebelisa polelo e latelang ea 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;

Ehlile, o ka eketsa INDEX (index) le PRIMAY KEY (senotlolo sa mantlha) tafoleng ea data ho hlakola lirekoto tse habeli tafoleng.Mekhoa e kang e ka tlase:

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