Hoʻohui ʻia ʻo MySQL alter e hoʻonui i nā kūlana kahua? ʻO ka wehewehe kikoʻī o ka hoʻohana ʻana i ka ʻōlelo kolamu hoʻololi

MySQL hoʻololi hoʻohui e hoʻonui i nā kūlana kahua he nui? ʻO ka wehewehe kikoʻī o ka hoʻohana ʻana i ka ʻōlelo kolamu hoʻololi

MySQL kauoha ALTER

Ke pono mākou e hoʻololi i ka inoa papa ʻikepili a i ʻole e hoʻololi i nā kahua papa ʻikepili, pono mākou e hoʻohana i ke kauoha MySQL ALTER.

Ma mua o ka hoʻomaka ʻana i kēia kumu aʻo, e hana mākou i kahi papa i kapa ʻia: 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)

Holoi, hoʻohui a hoʻololi paha i nā kahua pākaukau

Ke hoʻohana nei kēia kauoha i ke kauoha ALTER me ka paukū DROP e hoʻokuʻu i ke kolamu i o ka papa i hana ʻia ma luna.

mysql> ALTER TABLE testalter_tbl  DROP i;

ʻAʻole hiki ke hoʻohana iā DROP e holoi i kahi kahua inā hoʻokahi wale nō kahua i koe i ka papa ʻikepili.

Hoʻohana ʻia ka paukū ADD ma MySQL e hoʻohui i nā kolamu i ka papa ʻikepili.

mysql> ALTER TABLE testalter_tbl ADD i INT;

Ma hope o ka hoʻokō ʻana i ke kauoha ma luna, ua hoʻohui ʻia ke kahua i ka hope o nā kahua papaʻaina.

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)

Inā pono ʻoe e kuhikuhi i kahi o ke kahua hou, hiki iā ʻoe ke hoʻohana i ka huaʻōlelo FIRST i hāʻawi ʻia e MySQL (setKūlana hoʻonohonohokolamu mua), AFTER inoa kahua (hoʻonoho ʻia ma hope o kahi kahua).

E ho'āʻo i kēia ʻōlelo ALTER TABLE, a ma hope o ka hoʻokō kūleʻa ʻana, e hoʻohana i ka SHOW COLUMNS e nānā i nā loli i ka hoʻolālā papa:

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;

Hoʻohana wale ʻia nā huaʻōlelo FIRST a AFTER ma ka paukū ADD, no laila inā makemake ʻoe e hoʻonohonoho hou i ke kūlana o kahi kahua papa ʻikepili pono ʻoe e hoʻohana i ka DROP e wehe i ke kahua a laila ADD e hoʻohui i ke kahua a hoʻonohonoho i ke kūlana.

Hoʻololi i ke ʻano kahua a me ka inoa

Inā pono ʻoe e hoʻololi i ke ʻano kahua a me ka inoa, hiki iā ʻoe ke hoʻohana i ka paukū MODIFY a i ʻole CHANGE ma ke kauoha ALTER.

No ka laʻana, e hoʻololi i ke ʻano o ke kahua c mai CHAR(1) i CHAR(10), e hoʻokō i kēia kauoha:

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

Me ka paukū CHANGE, ʻokoʻa loa ka syntax.Ma hope koke iho o ka hua'ōlelo CHANGE ka inoa o ke kahua āu e makemake ai e hoʻololi, a laila e kuhikuhi i ka inoa kahua hou a me ke ʻano.E ho'āʻo i kēia laʻana:

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

Ka hopena o ka ALTER TABLE ma nā Waiwai Null a me Default

Ke hoʻololi ʻoe i kahi kahua, hiki iā ʻoe ke kuhikuhi inā e hoʻokomo wale a i ʻole e hoʻonohonoho i kahi waiwai paʻamau.

Hōʻike kēia hiʻohiʻona i kēlā kahua j ʻaʻole NULL a ʻo ka helu paʻamau ʻo 100.

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

Inā ʻaʻole ʻoe e hoʻonoho i kahi waiwai paʻamau, e hoʻonohonoho ʻokoʻa ʻo MySQL i ke kahua i NULL ma ka paʻamau.

Hoʻololi i ka waiwai paʻamau kahua

Hiki iā ʻoe ke hoʻohana iā ALTER e hoʻololi i ka waiwai paʻamau o kahi kahua, e hoʻāʻo i kēia mau laʻana:

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)

Hiki iā ʻoe ke hoʻohana i ke kauoha ALTER me ka paukū DROP e wehe i ka waiwai paʻamau o kahi kahua, e like me kēia hiʻohiʻona:

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:

Hiki ke hoʻololi i ke ʻano papa ʻikepili me ke kauoha ALTER a me ka paukū TYPE.E ho'āʻo i kēia laʻana, kahi e hoʻololi ai mākou i ke ʻano o ka pākaukau testalter_tbl iā MYISAM:

Kākau:No ka nānā ʻana i ke ʻano papa ʻikepili, hiki iā ʻoe ke hoʻohana i ka ʻōlelo 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)

Hoʻololi i ka inoa papa

Inā pono ʻoe e hoʻololi i ka inoa o ka papa ʻikepili, hiki iā ʻoe ke hoʻohana i ka paukū RENAME ma ka ʻōlelo ALTER TABLE e hana pēlā.

E ho'āʻo i kēia laʻana e hoʻololi i ka papa ʻikepili testalter_tbl i alter_tbl:

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

Hiki ke hoʻohana ʻia ke kauoha ALTER no ka hana ʻana a me ka holoi ʻana i nā kuhikuhi ma nā papa MySQL, a mākou e uhi ai i nā mokuna aʻe.

hoʻololi i nā hoʻohana ʻē aʻe

Hoʻololi i ka ʻenekini mālama: hoʻololi i ka myisam

alter table tableName engine=myisam;

Wehe i ka palena kī haole: keyName he inoa kī haole

alter table tableName drop foreign key keyName;

ʻO ke kūlana pili o ke kahua i hoʻololi ʻia: eia ka inoa1 ka māla āu e makemake ai e hoʻololi, ʻo type1 ke ʻano kumu mua o ke kahua, a hiki iā ʻoe ke koho i kekahi o ka mua a ma hope, pono e maopopo, kau mua ʻia, a ma hope kau ma hope o ka inoa2 kahua

alter table tableName modify name1 type1 first|after name2;