Ukushintsha kwe-MySQL kwengeza ukuze kwandiswe izikhundla eziningi zenkambu? Incazelo enemininingwane yokusetshenziswa kwesitatimende sekholomu yokuguqula

MySQL shintsha engeza ukuze ukhuphule izikhundla eziningi zenkambu? Incazelo enemininingwane yokusetshenziswa kwesitatimende sekholomu yokuguqula

MySQL ALTER umyalo

Uma sidinga ukuguqula igama lethebula ledatha noma siguqule izinkambu zethebula ledatha, sidinga ukusebenzisa umyalo we-MySQL ALTER.

Ngaphambi kokuqala lesi sifundo, masidale ithebula eliqanjwe ngokuthi: 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)

Susa, engeza noma lungisa izinkambu zethebula

Umyalo olandelayo usebenzisa umyalo we-ALTER nesigatshana esithi DROP ukuze uwise ikholomu engu-i yetafula elidalwe ngenhla:

mysql> ALTER TABLE testalter_tbl  DROP i;

I-DROP ayikwazi ukusetshenziselwa ukususa inkambu uma kunenkambu eyodwa kuphela kuthebula ledatha.

Isigatshana esithi ADD sisetshenziswa ku-MySQL ukwengeza amakholomu kuthebula ledatha. Isibonelo esilandelayo sengeza inkambu ye-i kuthebula elithi testalter_tbl futhi sichaza uhlobo lwedatha:

mysql> ALTER TABLE testalter_tbl ADD i INT;

Ngemva kokwenza umyalo ongenhla, inkambu ye-i yengezwa ngokuzenzakalelayo ekupheleni kwezinkambu zethebula ledatha.

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)

Uma udinga ukucacisa indawo yenkambu entsha, ungasebenzisa igama elingukhiye LOKUQALA elinikezwe i-MySQL (setUkubekaikholomu yokuqala), NGEMVA kwegama lenkambu (setha ngemva kwenkambu).

Zama isitatimende esilandelayo ALTER TABLE, futhi ngemva kokwenza ngempumelelo, sebenzisa SHOW COLUMNS ukuze ubuke izinguquko esakhiweni sethebula:

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 angukhiye LOKUQALA kanye NASEMVA asetshenziswa kuphela esigatshaneni esithi ENGEZA, ngakho-ke uma ufuna ukusetha kabusha indawo yenkambu yethebula ledatha, udinga kuqala usebenzise okuthi DROP ukuze ususe inkambu bese usebenzisa okuthi ADD ukuze wengeze inkambu bese usetha indawo.

Lungisa uhlobo lwenkambu negama

Uma udinga ukulungisa uhlobo lwenkundla negama, ungasebenzisa isigatshana esithi MODIFY noma CHANGE kumyalo othi ALTER.

Isibonelo, ukuze uguqule uhlobo lwenkambu c isuka ku-CHAR(1) iye ku-CHAR(10), yenza umyalo olandelayo:

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

Ngesigatshana esithi CHANGE, i-syntax ihluke kakhulu.Ngokushesha ngemva kokuthi SHINTSHA igama elingukhiye yigama lenkambu ofuna ukuyishintsha, bese usho igama lenkambu entsha kanye nohlobo.Zama isibonelo esilandelayo:

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

Umthelela we-ALTER TABLE kokuthi Amanani Angenalutho Nakuzenzakalelayo

Uma ulungisa inkambu, ungacacisa ukuthi ufake kuphela noma usethe inani elizenzakalelayo.

Isibonelo esilandelayo sicacisa ukuthi inkambu j AYIYONA futhi inani elizenzakalelayo lingu-100.

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

Uma ungasethi inani elizenzakalelayo, i-MySQL izosetha ngokuzenzakalelayo inkambu ku-NULL ngokuzenzakalelayo.

Shintsha inani elizenzakalelayo lenkambu

Ungasebenzisa i-ALTER ukuze uguqule inani elizenzakalelayo lenkambu, zama izibonelo ezilandelayo:

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 futhi umyalo we-ALTER nesigatshana esithi DROP ukuze ususe inani elimisiwe lenkambu, njengakusibonelo esilandelayo:

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:

Ukushintsha uhlobo lwethebula ledatha kungenziwa kusetshenziswa umyalo othi ALTER nesigatshana esithi TYPE.Zama isibonelo esilandelayo, lapho sishintsha khona uhlobo lwethebula elithi testalter_tbl libe yi-MYISAM:

Qaphela:Ukuze ubuke uhlobo lwethebula ledatha, ungasebenzisa isitatimende SHOW TABLE STATUS.

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)

Lungisa igama lethebula

Uma udinga ukulungisa igama lethebula ledatha, ungasebenzisa isigatshana esithi RENAME esitatimendeni ALTER TABLE ukwenza kanjalo.

Zama isibonelo esilandelayo ukuze uqambe kabusha ithebula ledatha testalter_tbl libe yi-alter_tbl:

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

Umyalo we-ALTER ungaphinda usetshenziselwe ukudala nokususa izinkomba kumathebula edatha ye-MySQL, esizoyethula ezahlukweni ezilandelayo.

shintsha okunye ukusetshenziswa

Lungisa injini yokugcina: iguqule ibe yi-myisam

alter table tableName engine=myisam;

Susa umgoqo wokhiye wangaphandle: KeyName isibizo sikakhiye wangaphandle

alter table tableName drop foreign key keyName;

Isikhundla esihlobene senkambu eguquliwe: lapha igama1 yinkambu okufanele ilungiswe, uhlobo1 uhlobo lwangempela lwensimu, okokuqala nangemuva kungakhethwa, okufanele kucace, okokuqala kubekwe kuqala, futhi ngemuva kubekwe ngemuva kwegama2 inkambu

alter table tableName modify name1 type1 first|after name2;