Ko te whakarereke a MySQL hei whakanui ake i nga tuunga mara maha? He whakamaramatanga mo te whakamahinga o te tauākī tīwae whakarerekē

MySQL huri i te taapiri hei whakanui ake i nga tuunga mara maha? He whakamaramatanga mo te whakamahinga o te tauākī tīwae whakarerekē

MySQL whakahau ALTER

Ina hiahia tatou ki te whakarereke i te ingoa ripanga raraunga, ki te whakarereke ranei i nga mara ripanga raraunga, me whakamahi te whakahau MySQL ALTER.

I mua i te tiimata i tenei akoranga, me hanga he ripanga ingoa: 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)

Mukua, taapiri, whakarereke ranei i nga mara ripanga

Ko te whakahau e whai ake nei ka whakamahi i te whakahau ALTER me te rara DROP ki te whakataka i te tīwae i o te ripanga i hangaia i runga ake nei:

mysql> ALTER TABLE testalter_tbl  DROP i;

Kāore e taea te whakamahi DROP ki te muku āpure mēnā kotahi noa te āpure e toe ana ki te ripanga raraunga.

Ka whakamahia te rara ADD i MySQL hei taapiri i nga pou ki te ripanga raraunga. Ko te tauira e whai ake nei ka taapiri i te mara i ki te ripanga testalter_tbl me te tautuhi i te momo raraunga:

mysql> ALTER TABLE testalter_tbl ADD i INT;

Whai muri i te mahi i te whakahau i runga ake nei, ka taapiri aunoatia te mara i ki te mutunga o nga mara ripanga raraunga.

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)

Mena ka hiahia koe ki te tautuhi i te waahi o te mara hou, ka taea e koe te whakamahi i te kupu matua TUATAHI kua whakaratohia e MySQL (whakaturiaWhakatūngatīwae tuatahi), AFTER ingoa āpure (whakaturia i muri i te āpure).

Whakamātauria te tauākī ALTER TABLE e whai ake nei, ā, i muri i te tutukinga angitu, whakamahia nga WHAKAATU KORE hei tiro i nga huringa o te hanganga ripanga:

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;

Ko nga kupu matua TUATAHI me muri ka whakamahia anake i roto i te rara ADD, na, ki te hiahia koe ki te tautuhi ano i te waahi o te waahi ripanga raraunga, me whakamahi tuatahi koe i te DROP ki te muku i te mara ka whakamahi i te ADD hei taapiri i te mara me te tautuhi i te waahi.

Whakakē momo āpure me te ingoa

Mena ka hiahia koe ki te whakarereke i te momo mara me te ingoa, ka taea e koe te whakamahi i te rara MODIFY, CHANGE ranei i te whakahau ALTER.

Hei tauira, hei huri i te momo apure c mai i CHAR(1) ki CHAR(10), mahia te whakahau e whai ake nei:

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

Ki te whiti CHANGE, he tino rereke te wetereo.I muri tonu mai i te kupu CHANGE ko te ingoa o te mara e hiahia ana koe ki te whakarereke, katahi ka tohua te ingoa mara hou me te momo.Whakamātauria te tauira e whai ake nei:

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

Te Painga o te ALTER TABLE i runga i nga Uara Null me te Taunoa

Ina whakarerekē koe i tetahi mara, ka taea e koe te whakapūtā me whakauru anake, me whakarite ranei he uara taunoa.

Ko te tauira e whai ake nei e tohu ana ko te mara j NOT NULL me te uara taunoa ko te 100.

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

Ki te kore koe e tautuhi i te uara taunoa, ka tautuhi aunoa a MySQL i te mara ki te NULL ma te taunoa.

Whakakē uara taunoa āpure

Ka taea e koe te whakamahi i te ALTER ki te huri i te uara taunoa o te mara, ngana nga tauira e whai ake nei:

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)

Ka taea hoki te whakamahi i te whakahau ALTER me te rara DROP hei tango i te uara taunoa o tetahi mara, penei i te tauira e whai ake nei:

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:

Ka taea te whakarereke i te momo ripanga raraunga ma te whakamahi i te whakahau ALTER me te rara TYPE.Whakamātauria te tauira e whai ake nei, ka huri i te momo o te ripanga testalter_tbl ki MYISAM:

Tuhipoka:Hei tiro i te momo ripanga raraunga, ka taea e koe te whakamahi i te korero 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)

Whakakē ingoa ripanga

Mena ka hiahia koe ki te whakarereke i te ingoa o te ripanga raraunga, ka taea e koe te whakamahi i te rara RENAME i te tauākī ALTER TABLE ki te mahi.

Whakamātauria te tauira e whai ake nei hei whakaingoa anō i te ripanga raraunga testalter_tbl ki alter_tbl:

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

Ka taea hoki te whakamahi i te whakahau ALTER ki te hanga me te whakakore i nga tohu tohu i runga i nga ripanga raraunga MySQL, ka whakauruhia e matou ki nga upoko e whai ake nei.

whakarereke i etahi atu whakamahinga

Whakarerekehia te miihini rokiroki: whakarereke ki te myisam

alter table tableName engine=myisam;

Tangohia te herenga matua kee: ko keyName he ingoa ingoa kee

alter table tableName drop foreign key keyName;

Te tūnga whanaunga o te āpure kua whakarerekētia: i konei ko te ingoa1 te mara hei whakarereke, ko te momo1 te momo taketake o te mara, ka taea te kowhiri i te tuatahi me muri, ka kitea, ko te tuatahi ka tuuhia i te tuatahi, ka whai muri ka tuhia ki muri i te ingoa2 mara

alter table tableName modify name1 type1 first|after name2;