Ụdị nchịkọta data MySQL / mepụta / jiri nchikota ALTER nkwupụta ojiji na MySQL

MySQLụdị index/mepụta/jiri combo gbanwee n'imeMySQLojiji nkwupụta iwu

Ndekọ MySQL

Nhazi nke MySQL index dị ezigbo mkpa maka ịrụ ọrụ nke ọma nke MySQL, na ndeksi nwere ike melite ọsọ ọsọ nke MySQL.

Ọmụmaatụ, ọ bụrụ na MySQL nwere ezi uche imewe na ojiji nke index bụ a Lamborghini, mgbe ahụ MySQL enweghị indexes na indexes bụ mmadụ tricycle.

A na-ekewa ndeksi ahụ ka ọ bụrụ nrịbama otu kọlụm na index composite.Ndekọ otu kọlụm, ya bụ, ndeksi nwere naanị otu kọlụm, tebụl nwere ike ịnwe ọtụtụ ntụnye otu kọlụm, mana nke a abụghị ndenye ihe mejupụtara.Ndekọ ihe mejupụtara, ya bụ, ndeksi nwere ọtụtụ ogidi.

Mgbe ị na-emepụta ndeksi, ịkwesịrị ijide n'aka na ndenye aha bụ ọnọdụ etinyere na ajụjụ SQL (n'ozuzu dịka ọnọdụ nke okwu WHERE).

N'ezie, ndepụta ndeksi bụkwa tebụl nke na-echekwa mkpịsị ugodi isi na mpaghara ndeksi ma na-atụ aka na ndekọ nke tebụl ụlọ ọrụ.

Ihe ndị a dị n'elu na-ekwu maka uru dị n'iji index eme ihe, mana iji index na-emebiga ihe ókè ga-eduga ná mmegbu.Ya mere, index ga-enwekwa adịghị ike ya: ọ bụ ezie na index na-eme ka ọsọ ọsọ ọsọ dịkwuo mma, ọ ga-ebelata ọsọ nke imelite tebụl, dị ka INSERT, UPDATE na ehichapụ na tebụl.N'ihi na mgbe ị na-emelite tebụl, MySQL ọ bụghị naanị na-echekwa data, kamakwa na-echekwa faịlụ index.

Ịdepụta faịlụ ndeksi na-eri ohere diski.

nkịtị index

mepụta index

Nke a bụ index kasị isi, ọ dịghị ihe mgbochi.Enwere ike ịmepụta ya n'ụzọ ndị a:

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

Maka ụdị CHAR na VARCHAR, ogologo nwere ike ịbụ ihe na-erughị ogologo oge nke ubi ahụ; maka ụdị BLOB na TEXT, a ghaghị ịkọwa ogologo oge.

Megharịa nhazi tebụl (tinye ndeksi)

ALTER table tableName ADD INDEX indexName(columnName)

Ezipụta ozugbo mgbe ị na-eke tebụl

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

Syntax iji dobe ndeksi

DROP INDEX [indexName] ON mytable; 

index pụrụ iche

Ọ dị ka index nkịtị gara aga, ihe dị iche bụ: uru nke kọlụm index ga-abụ ihe pụrụ iche, mana a na-ahapụ ụkpụrụ efu.N'ihe banyere ndetu ihe mejupụtara, nchikota nke ụkpụrụ kọlụm ga-abụrịrị ihe pụrụ iche.Enwere ike ịmepụta ya n'ụzọ ndị a:

mepụta index

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

Gbanwee nhazi okpokoro

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

Ezipụta ozugbo mgbe ị na-eke tebụl

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

Tinye ma wepụ ndeksi site na iji iwu ALTER

Enwere ụzọ anọ iji tinye ndetu na tebụl data:

• ALTER TABLE tbl_name gbakwunye igodo isi (ndepụta_kọlụm): Nkwupụta a na-agbakwụnye igodo isi, nke pụtara na ụkpụrụ ndeksi ga-abụrịrị ihe pụrụ iche na enweghị ike ịbụ NULL.
• ALTER TABLE tbl_name tinye UNIQUE index_aha (ndepụta kọlụm): Uru nke ndeksi nke nkwupụta a mebere ga-abụrịrị ihe pụrụ iche (belụsọ NULL, NULL nwere ike ịpụta ọtụtụ oge).
• ALTER TABLE tbl_name tinye INDEX index_aha (ndepụta_kọlụm): Tinye ndeksi nkịtị, uru index nwere ike ịpụta ọtụtụ oge.
• ALTER TABLE tbl_name tinye FULLTEXT index_aha (ndepụta kọlụm):Nkwupụta ahụ na-akọwapụta ndeksi dị ka FULLTEXT maka ntinye ederede zuru oke.

Ihe atụ na-esonụ bụ ịgbakwunye ndeksi na tebụl.

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

Ị nwekwara ike iji ahịrịokwu DROP na iwu ALTER iji dobe index.Gbalịa mee ihe atụ a ka idobe ndeksi:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

Tinye ma wepụ isi igodo site na iji iwu ALTER

Isi igodo nwere ike ime naanị otu kọlụm. Mgbe ị na-agbakwunye ndeksi igodo bụ isi, ị ga-ahụ na igodo isi abụghị FULL na ndabara (Ọbụghị FULL).Ọmụmaatụ bụ ndị a:

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

Ị nwekwara ike ihichapụ isi igodo site na iji iwu ALTER:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

Naanị mkpa ka ị kọwapụta igodo PRIMARY mgbe ị na-atụba igodo isi, mana mgbe ị na-atụba ndepụta ndeksi, ị ga-amarịrị aha ndeksi.

gosi index ozi

Ị nwere ike iji iwu SHOW INDEX depụta ozi index dị mkpa na tebụl.Enwere ike ịhazi ozi mmepụta site na ịgbakwunye \G.

Gbalịa ihe atụ ndị a:

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