Uhlobo lwenkomba yesizindalwazi se-MySQL/dala/sebenzisa inhlanganisela ALTER ukusetshenziswa kwesitatimende ku-MySQL

MySQLuhlobo lwenkomba/dala/sebenzisa shintsha inhlanganiselaMySQLukusetshenziswa kwesitatimende somyalo

Izinkomba ze-MySQL

Ukusungulwa kwenkomba ye-MySQL kubaluleke kakhulu ekusebenzeni kahle kwe-MySQL, futhi inkomba ingathuthukisa kakhulu isivinini sokubuyisa se-MySQL.

Isibonelo, uma i-MySQL eklanywe kahle futhi enenkomba iyi-Lamborghini, khona-ke i-MySQL ngaphandle kwedizayini enenkomba iyibhayisikili elinantathu lomuntu.

Inkomba ihlukaniswe yaba inkomba yekholomu eyodwa kanye nenkomba eyinhlanganisela.Inkomba yekholomu eyodwa, okungukuthi, inkomba iqukethe ikholomu eyodwa kuphela, ithebula lingaba nezinkomba eziningi zekholomu eyodwa, kodwa lokhu akuyona inkomba eyinhlanganisela.Inkomba eyinhlanganisela, okungukuthi, inkomba eyodwa iqukethe amakholomu amaningi.

Lapho udala inkomba, udinga ukwenza isiqiniseko sokuthi inkomba iyisimo esisebenza embuzweni we-SQL (ngokuvamile njengesimo sesigaba somushwana WHERE).

Eqinisweni, inkomba nayo iyithebula, ithebula ligcina ukhiye oyinhloko nezinkambu zenkomba, futhi likhomba amarekhodi ethebula lebhizinisi.

Okungenhla konke kumayelana nezinzuzo zokusebenzisa izinkomba, kodwa ukusetshenziswa ngokweqile kwezinkomba kuzoholela ekuhlukunyezweni.Ngakho-ke, inkomba izophinde ibe nokushiyeka kwayo: nakuba inkomba ithuthukisa kakhulu isivinini sombuzo, izonciphisa isivinini sokubuyekeza itafula, njenge-INSERT, UPDATE kanye DELETE yetafula.Ngoba lapho ubuyekeza ithebula, i-MySQL ayigcini nje ngokugcina idatha, kodwa futhi igcina ifayela lenkomba.

Ukukhomba ifayela lenkomba elidla isikhala sediski.

inkomba evamile

dala inkomba

Lena inkomba eyisisekelo kakhulu, ayinayo imingcele.Ingadalwa ngezindlela ezilandelayo:

CREATE INDEX indexName ON mytable(username(length)); 

Ezinhlotsheni ze-CHAR ne-VARCHAR, ubude bungaba ngaphansi kobude bangempela benkambu; ezinhlotsheni ze-BLOB ne-TEXT, ubude kufanele bucaciswe.

Lungisa isakhiwo sethebula (engeza inkomba)

ALTER table tableName ADD INDEX indexName(columnName)

Cacisa ngokuqondile lapho udala ithebula

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);  

I-syntax yokulahla inkomba

DROP INDEX [indexName] ON mytable; 

inkomba eyingqayizivele

Kufana nenkomba evamile yangaphambilini, umehluko uwukuthi: inani lekholomu yenkomba kufanele lihluke, kodwa amanani angenalutho avunyelwe.Esimeni senkomba eyinhlanganisela, inhlanganisela yamanani ekholomu kufanele ihluke.Ingadalwa ngezindlela ezilandelayo:

dala inkomba

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

Lungisa isakhiwo setafula

ALTER table mytable ADD UNIQUE [indexName] (username(length))

Cacisa ngokuqondile lapho udala ithebula

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
UNIQUE [indexName] (username(length))  
 
);  

Engeza futhi ususe izinkomba usebenzisa umyalo we-ALTER

Kunezindlela ezine zokwengeza inkomba kuthebula ledatha:

• ALTER TABLE tbl_name ENGEZA UKHIYE OYINHLOKO (uhlu_lwekholomu): Lesi sitatimende sengeza ukhiye oyinhloko, okusho ukuthi amanani wenkomba kumele ahluke futhi awakwazi ukuba NULL.
• ALTER TABLE tbl_name ENGEZA UNIQUE index_name (uhlu_lwekholomu): Inani lenkomba edalwe yilesi sitatimende kufanele lihluke (ngaphandle kokuthi NULL, NULL ingase ivele izikhathi eziningi).
• ALTER TABLE tbl_name ENGEZA INDEX index_name (column_list): Engeza inkomba evamile, inani lenkomba lingavela izikhathi eziningi.
• ALTER TABLE tbl_name ENGEZA FULLTEXT index_name (column_list):Isitatimende sicacisa inkomba njenge-FULLTEXT yokukhomba umbhalo ogcwele.

Isibonelo esilandelayo esokwengeza inkomba etafuleni.

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

Ungasebenzisa futhi isigatshana esithi DROP kumyalo we-ALTER ukuze uwise izinkomba.Zama lesi sibonelo esilandelayo ukuze ulahle inkomba:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

Engeza futhi ususe okhiye abayinhloko usebenzisa umyalo we-ALTER

Ukhiye oyinhloko ungasebenza kukholomu eyodwa kuphela. Lapho ungeza inkomba yokhiye oyinhloko, udinga ukuqinisekisa ukuthi ukhiye oyinhloko awuNNULL ngokuzenzakalelayo (HAYI NULL).Izibonelo zimi kanje:

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

Ungaphinda ususe ukhiye oyinhloko ngomyalo othi ALTER:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

Udinga kuphela ukucacisa UKHIYE OYINHLOKO lapho uwisa ukhiye oyinhloko, kodwa uma ulahla inkomba, kufanele wazi igama lenkomba.

khombisa imininingwane yenkomba

Ungasebenzisa umyalo othi BONISA INHLOKO ukuze ufake ohlwini imininingwane yenkomba efanele etafuleni.Imininingwane yokuphumayo ingafomethwa ngokungeza \G.

Zama izibonelo ezilandelayo:

mysql> SHOW INDEX FROM table_name; \G
........