MySQL alter e eketsa ho eketsa maemo a mangata a masimong? Tlhaloso e qaqileng ea tšebeliso ea phetoho ea kholomo

MySQL fetola eketsa ho eketsa maemo a mangata tšimong? Tlhaloso e qaqileng ea tšebeliso ea phetoho ea kholomo

MySQL ALTER taelo

Ha re hloka ho fetola lebitso la tafole ea data kapa ho fetola likarolo tsa tafole ea data, re hloka ho sebelisa taelo ea MySQL ALTER.

Pele re qala thuto ena, ha re theheng tafole e bitsoang: 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)

Hlakola, eketsa kapa fetola likarolo tsa tafole

Taelo e latelang e sebelisa taelo ea ALTER e nang le poleloana ea DROP ho theola ikholomo ea tafole e entsoeng ka holimo:

mysql> ALTER TABLE testalter_tbl  DROP i;

DROP e ke ke ea sebelisoa ho hlakola lebala haeba ho setse sebaka se le seng feela tafoleng ea data.

Temana ea ADD e sebelisoa ho MySQL ho kenya likholomo tafoleng ea data.Mohlala o latelang o eketsa karolo ea i ho tafole testalter_tbl mme o hlalosa mofuta oa data:

mysql> ALTER TABLE testalter_tbl ADD i INT;

Ka mor'a ho phethahatsa taelo e ka holimo, tšimo ea i e kenyelletsoa ka bo eona qetellong ea masimo a tafole ea data.

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)

Haeba o hloka ho hlakisa sebaka sa tšimo e ncha, o ka sebelisa lentsoe la sehlooho PELE le fanoeng ke MySQL (setBoemokholomo ea pele), KA MOR'A lebitso la sebaka (seta ka mor'a lebala).

Leka polelo e latelang ea ALTER TABLE, 'me ka mor'a ts'ebetso e atlehileng, sebelisa SHOW COLUMNS ho bona liphetoho mohahong oa tafole:

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;

Mantsoe a sehlooho a FIRST le AFTER a sebelisoa feela ho ADD clause, kahoo haeba u batla ho tsosolosa boemo ba sebaka sa tafole ea data, u lokela ho sebelisa DROP ho hlakola tšimo ebe u sebelisa ADD ho eketsa tšimo le ho beha boemo.

Fetola mofuta oa sebaka le lebitso

Haeba o hloka ho fetola mofuta oa tšimo le lebitso, o ka sebelisa MODIFY kapa CHANGE poleloana ho taelo ea ALTER.

Mohlala, ho fetola mofuta oa tšimo ho tloha ho CHAR(1) ho ea ho CHAR(10), etsa taelo e latelang:

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

Ka poleloana ea CHANGE, syntax e fapane haholo.Hang ka mor'a hore CHANGE keyword ke lebitso la tšimo eo u batlang ho e fetola, ebe u bolela lebitso le lecha la tšimo le mofuta.Leka mohlala o latelang:

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

Phello ea ALTER TABLE ho Null le Melato e Default

Ha o fetola sebaka, o ka hlakisa hore na o kenyellelitse feela kapa hore na o behe boleng ba kamehla.

Mohlala o latelang o bolela hore tšimo j HA SE NULL mme boleng ba kamehla ke 100.

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

Haeba u sa behe boleng ba kamehla, MySQL e tla ipehela tšimo ho NULL ka ho feletseng.

Fetola boleng ba karolo ea kamehla

U ka sebelisa ALTER ho fetola boleng bo sa feleng ba tšimo, leka mehlala e latelang:

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)

U ka boela ua sebelisa taelo ea ALTER ka poleloana ea DROP ho tlosa boleng ba kamehla ba tšimo, joalo ka mohlala o latelang:

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:

Ho fetola mofuta oa tafole ea data ho ka etsoa ho sebelisoa taelo ea ALTER le poleloana ea TYPE.Leka mohlala o latelang, moo re fetolang mofuta oa tafole testalter_tbl ho MYISAM:

Tlhokomeliso:Ho sheba mofuta oa tafole ea data, u ka sebelisa polelo ea 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)

Fetola lebitso la tafole

Haeba o hloka ho fetola lebitso la tafole ea data, o ka sebelisa poleloana ea RENAME ho polelo ea ALTER TABLE ho etsa joalo.

Leka mohlala o latelang ho reha bocha tafole ea data testalter_tbl ho alter_tbl:

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

Taelo ea ALTER e ka boela ea sebelisoa ho theha le ho hlakola li-index ho litafole tsa data tsa MySQL, tseo re tla li hlahisa likhaolong tse latelang.

fetola lisebelisoa tse ling

Fetola enjene ea polokelo: e fetole ho myisam

alter table tableName engine=myisam;

Tlosa lithibelo tsa linotlolo tsa kantle ho naha: keyName ke lebitso la lebitso la senotlolo sa kantle ho naha

alter table tableName drop foreign key keyName;

Boemo bo lekanyelitsoeng ba sebaka se fetotsoeng: mona name1 ke sebaka seo u batlang ho se fetola, mofuta1 ke mofuta oa pele oa tšimo, 'me u ka khetha e' ngoe ea pele le ka morao, e lokelang ho hlaka, pele e behoa pele, 'me ka mor'a moo e behiloeng ka mor'a sebaka sa lebitso2

alter table tableName modify name1 type1 first|after name2;