Momo taupū pātengi raraunga MySQL/hanga/whakamahia te huinga ALTER te whakamahi tauākī i MySQL

MySQLmomo taupū/hanga/whakamahi paheko whakarerekē i roto iMySQLte whakamahi korero whakahau

Nga tohu tohu MySQL

Ko te whakaturanga o te taurangi MySQL he mea tino nui mo te mahi pai o MySQL, a ka taea e te taurangi te whakapai ake i te tere o te whakahoki mai i a MySQL.

Hei tauira, ki te mea he Lamborghini a MySQL me te hoahoa whaitake me te whakamahi i nga tohu tohu, katahi ko MySQL kaore he tohu tohu me nga tohu tohu he motopaika tangata.

Kua wehea te taupū ki te taupū tīwae kotahi me te taupū hiato.Taupū tīwae-kotahi, arā, he tīwae kotahi anake te taupū, ka taea e te ripanga te maha o nga tohu tohu-kotahi, engari ehara tenei i te taupū hiato.Taupū hiato, ara, kei te kotahi taupū nga pou maha.

I te wa e hanga ana i te taurangi, me tino mohio ko te taurangi he ahuatanga e pa ana ki te patai SQL (te tikanga he ahuatanga o te rara WHERE).

Otirā, he ripanga hoki te taupū, ka tiakina e te ripanga te kī matua me ngā āpure taupū, ka tohu ki ngā rekoata o te ripanga hinonga.

Ko nga korero i runga ake nei mo nga painga o te whakamahi i nga tohu, engari ko te whakamahi nui o nga tohu ka arahi ki te mahi kino.Na reira, ka whai hapa ano te taurangi: ahakoa ka tino whakapai ake te taurangi i te tere o te patai, ka whakaitihia te tere o te whakahou i te ripanga, penei i te INSERT, UPDATE and DELETE on the table.Na te mea i te wa e whakahou ana i te ripanga, kaore a MySQL e penapena i nga raraunga anake, engari ka tiakina ano hoki te konae tohu.

Te tohu i te konae taurangi ka pau te mokowā kōpae.

taupū noa

hanga taupū

Koinei te tohu tino taketake, kaore he here.Ka taea te hanga i nga huarahi e whai ake nei:

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

Mo nga momo CHAR me VARCHAR, ka iti ake te roa i te roa o te mara; mo nga momo BLOB me TEXT, me tohu te roa.

Whakakē te hanganga ripanga (tāpiri taupū)

ALTER table tableName ADD INDEX indexName(columnName)

Tauwhāiti tika ina hanga te ripanga

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

Wetereo hei taka i te taupū

DROP INDEX [indexName] ON mytable; 

taupū ahurei

He rite ki te taurangi noa o mua, ko te rereketanga: ko te uara o te pou taurangi me ahurei, engari ka whakaaetia nga uara kore.I roto i te take o te taupū hiato, me ahurei te huinga o nga uara pou.Ka taea te hanga i nga huarahi e whai ake nei:

hanga taupū

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

Whakarerekē te hanganga ripanga

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

Tauwhāiti tika ina hanga te ripanga

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

Tāpiri me te tango taupū mā te tono ALTER

E wha nga huarahi hei taapiri i te taurangi ki te ripanga raraunga:

• TAHUA TABLE tbl_ingoa Tāpiri Kī TUATAHI (rarangi_tiwae): Ka taapirihia e tenei tauākī he kī matua, ko te tikanga me ahurei nga uara taupū, kaore e taea te NULL.
• TAHUA TABLE tbl_ingoa Tāpirihia te ingoa_taupū ahurei (rarangi_tiwae): Ko te uara o te taurangi i hangaia e tenei tauākī me ahurei (haunga te NULL, ka puta pea te NULL i nga wa maha).
• TAHUA TABLE tbl_ingoa ADD INDEX index_name (column_list): Tāpirihia he taupū noa, ka puta te uara taupū i nga wa maha.
• TAHUA TABLE tbl_ingoa ADD FULLTEXT index_ingoa (raarangi_tiwae):Ka whakapūtā te tauākī i te taupū hei FULLTEXT mō te taupū kuputuhi-katoa.

Ko te tauira e whai ake nei ko te taapiri i te taurangi ki te ripanga.

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

Ka taea hoki e koe te whakamahi i te rara DROP i runga i te whakahau ALTER hei whakataka i nga tohu.Whakamātauria te tauira e whai ake nei ki te taka i te taupū:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

Tāpiri me te tango i nga taviri tuatahi ma te whakamahi i te tono ALTER

Ka taea e te matua matua te mahi i runga i te pou kotahi. I te taapiri i te taurangi matua matua, me whakarite e koe ko te matua matua ehara i te NULL ma te taunoa (NOT NULL).Ko nga tauira e whai ake nei:

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

Ka taea hoki e koe te muku i tetahi matua matua me te whakahau ALTER:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

Me whakapūtā noa koe i te Kī TUATAHI ina taka ana te kī matua, engari ka taka te taupū, me mohio koe ki te ingoa taupū.

whakaatu korero taupū

Ka taea e koe te whakamahi i te whakahau SHOW INDEX hei whakarārangi i ngā mōhiohio taupū e hāngai ana ki te ripanga.Ka taea te whakahōputu i ngā pārongo putanga mā te tāpiri \G.

Whakamātauria ngā tauira e whai ake nei:

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