Ukutshintsha kweMySQL yongeza ukwandisa izikhundla ezininzi zentsimi? Ingcaciso ebanzi yokusetyenziswa kohlengahlengiso lwenkcazo yekholamu

MySQL tshintsha ukongeza ukwandisa izithuba ezininzi zentsimi? Ingcaciso ebanzi yokusetyenziswa kohlengahlengiso lwenkcazo yekholamu

MySQL ALTER umyalelo

Xa sifuna ukuguqula igama letafile yedatha okanye ukuguqula iindawo zetafile yedatha, kufuneka sisebenzise umyalelo we-MySQL ALTER.

Phambi kokuba siqale esi sifundo, masenze itheyibhile ebizwa ngokuba: testalter_tbl.

root@host# mysql -u root -p password;
Enter password:*******
mysql> use chenweiliang;
Database changed
mysql> create table testalter_tbl
    -> (
    -> i INT,
    -> c CHAR(1)
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Cima, yongeza okanye ulungise iindawo zetafile

Lo myalelo ulandelayo usebenzisa i ALTER umyalelo nge DROP igatya ukulahla i kholamu yetafile eyenziwe ngasentla:

mysql> ALTER TABLE testalter_tbl  DROP i;

I-DROP ayinakusetyenziswa ukucima indawo ukuba kukho indawo enye eseleyo kwitafile yedatha.

Igatya le-ADD lisetyenziswa kwi-MySQL ukongeza iikholamu kwitheyibhile yedatha Lo mzekelo ulandelayo wongeza umhlaba we-i kwitafile testalter_tbl kwaye ichaza uhlobo lwedatha:

mysql> ALTER TABLE testalter_tbl ADD i INT;

Emva kokuphumeza lo myalelo ungasentla, intsimi yongezwa ngokuzenzekelayo ekupheleni kwemimandla yetafile yedatha.

mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Ukuba ufuna ukukhankanya indawo yendawo entsha, ungasebenzisa igama elingundoqo KUQALA elinikezwe nguMySQL (setaUkubekwa endaweniikholamu yokuqala), EMVA kwegama lomhlaba (seta emva kommandla).

Zama le ngxelo ilandelayo ye-ALTER TABLE, kwaye emva kokuphunyezwa ngempumelelo, sebenzisa BONISA IMIKHOLAU ukujonga utshintsho kulwakhiwo lwetheyibhile:

ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

Amagama angundoqo OKUQALA NASEMVA asetyenziswa kuphela kwigatya le-ADD, ngoko ke ukuba ufuna ukuseta kwakhona indawo yendawo yetafile yedatha, kufuneka usebenzise i-DROP ukucima intsimi kwaye usebenzise i-ADD ukongeza intsimi kwaye usete indawo.

Lungisa uhlobo lwendawo kunye negama

Ukuba ufuna ukulungisa udidi lwentsimi kunye negama, ungasebenzisa i-MODIFY okanye TSHINTSHA igatya kumyalelo we ALTER.

Umzekelo, ukutshintsha udidi lwentsimi c ukusuka kwi-CHAR(1) ukuya kwi-CHAR(10), phumeza lo myalelo ulandelayo:

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

Ngegatya UTSHINTSHO, i-syntax yahluke kakhulu.Ngoko nangoko emva kwe TSHINTSHA igama elingundoqo ligama lentsimi ofuna ukuyilungisa, kwaye ke uchaze igama elitsha lendawo kunye nohlobo.Zama lo mzekelo ulandelayo:

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

Isiphumo se-ALTER TABLE kwiNqobo kunye neMigangatho eMiselweyo

Xa ulungisa umhlaba, ungakhankanya ukuba uquke kuphela okanye ukuseta ixabiso elimiselweyo.

Lo mzekelo ulandelayo uxela ukuba umhlaba j AYIKHO NULL kwaye ixabiso elimiselweyo li-100.

mysql> ALTER TABLE testalter_tbl 
    -> MODIFY j BIGINT NOT NULL DEFAULT 100;

Ukuba akuyi kumisela ixabiso elingagqibekanga, i-MySQL iya kucwangcisa ngokuzenzekelayo umhlaba kwi-NULL ngokungagqibekanga.

Guqula ixabiso elimiselweyo lomhlaba

Ungasebenzisa ALTER ukutshintsha ixabiso elingagqibekanga lomhlaba, zama le mizekelo ilandelayo:

mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | 1000    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Ungasebenzisa kwakhona umyalelo we-ALTER kunye negatya elithi DROP ukususa ixabiso elimiselweyo lomhlaba, njengakumzekelo ulandelayo:

mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
Changing a Table Type:

Ukuguqula uhlobo lwetafile yedatha kunokwenziwa kusetyenziswa i-ALTER umyalelo kunye negatya le-TYPE.Zama lo mzekelo ulandelayo, apho sitshintsha udidi lwetafile testalter_tbl ukuya kwiMYISAM:

Qaphela:Ukujonga uhlobo lwetheyibhile yedatha, ungasebenzisa ingxelo BONISA IMEKO YETHEYIBHILE.

mysql> ALTER TABLE testalter_tbl ENGINE = MYISAM;
mysql>  SHOW TABLE STATUS LIKE 'testalter_tbl'\G
*************************** 1. row ****************
           Name: testalter_tbl
           Type: MyISAM
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 25769803775
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2007-06-03 08:04:36
    Update_time: 2007-06-03 08:04:36
     Check_time: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Guqula igama letafile

Ukuba ufuna ukulungisa igama letheyibhile yedatha, ungasebenzisa igatya le-RENAME kwingxelo ye-ALTER TABLE ukwenza njalo.

Zama lo mzekelo ulandelayo ukuze uthiye ngokutsha itafile yedatha testalter_tbl ukuya ku alter_tbl:

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

Umyalelo we-ALTER ungasetyenziselwa ukudala kunye nokucima izalathisi kwiitafile zedatha ye-MySQL, esiza kuzisa kwizahluko ezilandelayo.

tshintsha ezinye izinto

Guqula i-injini yogcino: yilungise kwi-myisam

alter table tableName engine=myisam;

Susa isithintelo sesitshixo sangaphandle: Igama lesitshixo sisitshixo sasemzini

alter table tableName drop foreign key keyName;

Indawo ezalanayo yentsimi elungisiweyo: apha igama1 yintsimi ofuna ukuyiguqula kancinane, uhlobo1 luhlobo loqobo lommandla, kwaye ungakhetha enye yeyokuqala nasemva, ekufuneka icace, eyokuqala ibekwe kuqala, kwaye emva koko. ibekwe emva kwendawo yegama2

alter table tableName modify name1 type1 first|after name2;