MySQL alter wedzera kuwedzera nzvimbo dzakawanda dzemunda? Tsananguro yakadzama yekushandiswa kwechinyorwa chekoramu

MySQL shandura wedzera kuwedzera nzvimbo dzakawanda dzemunda? Tsananguro yakadzama yekushandiswa kwechinyorwa chekoramu

MySQL ALTER command

Patinenge tichida kugadzirisa zita retafura yedata kana kugadzirisa minda yetafura yedata, tinoda kushandisa MySQL ALTER command.

Tisati tatanga chidzidzo ichi, ngatigadzire tafura yakanzi: 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)

Delete, wedzera kana shandura tafura minda

Murairo unotevera unoshandisa iyo ALTER command ine DROP clause kudonhedza i column yetafura yakagadzirwa pamusoro:

mysql> ALTER TABLE testalter_tbl  DROP i;

DROP haigone kushandiswa kudzima ndima kana paine chikamu chimwe chete chasara mutafura yedata.

ADD clause inoshandiswa muMySQL kuwedzera makoramu patafura yedata.Muenzaniso unotevera unowedzera i ndima patafura testalter_tbl uye unotsanangura rudzi rwedata:

mysql> ALTER TABLE testalter_tbl ADD i INT;

Mushure mekuita murairo uri pamusoro, iyo i munda inowedzerwa otomatiki kumagumo eiyo data tafura minda.

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)

Kana iwe uchida kutsanangura nzvimbo yemunda mutsva, unogona kushandisa izwi rekutanga FIRST rakapihwa neMySQL (setPositioningkoramu yekutanga), PASHURE pezita remunda (rakaiswa mushure memunda).

Edza zvinotevera ALTER TABLE chirevo, uye mushure mekuita kwakabudirira, shandisa SHOW COLUMNS kuona shanduko muchimiro chetafura:

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;

Mazwi eKUTANGA uye AFTER anoshandiswa chete muchikamu cheADD, saka kana iwe uchida kugadzirisa nzvimbo ye data table field unofanira kushandisa DROP kubvisa munda uye wozo ADD kuwedzera munda uye kuisa nzvimbo.

Shandura munda rudzi uye zita

Kana iwe uchida kugadzirisa rudzi rwemunda uye zita, unogona kushandisa iyo MODIFY kana CHANGE clause mune ALTER command.

Semuenzaniso, kushandura rudzi rwemunda c kubva kuCHAR(1) kuenda kuCHAR(10), ita murairo unotevera:

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

Nechisungo cheCHINJA, syntax yakasiyana zvakanyanya.Pakarepo mushure meiyo CHANGE keyword ndiro zita remunda waunoda kugadzirisa, uye wozotsanangura zita idzva remunda uye mhando.Edza muenzaniso unotevera:

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

Mhedzisiro yeALTER TABLE paNull uye Default Values

Paunenge uchigadzirisa munda, unogona kutsanangura kana uchisanganisira chete kana kuti kuisa kukosha kweiyo default.

Muenzaniso unotevera unotsanangura kuti munda j hausi NULL uye kukosha kweiyo 100.

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

Kana iwe ukasaisa kukosha kweiyo default, MySQL inongoisa iyo munda kuNULL nekukasira.

Shandura munda default value

Unogona kushandisa ALTER kushandura kukosha kweiyo munda, edza iyi inotevera mienzaniso:

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)

Iwe unogona zvakare kushandisa iyo ALTER kuraira neiyo DROP clause kubvisa iyo default kukosha kwemunda, semumuenzaniso unotevera:

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:

Kugadzirisa rudzi rwetafura yedata kunogona kuitwa uchishandisa ALTER command uye TYPE clause.Edza unotevera muenzaniso, patinoshandura mhando yetafura testalter_tbl kuita MYISAM:

Ongorora:Kuti uone mhando yetafura yedata, unogona kushandisa SHOW TABLE STATUS statement.

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)

Shandura zita retafura

Kana iwe uchida kugadzirisa zita retafura yedata, unogona kushandisa RENAME clause mune ALTER TABLE statement kuti uite kudaro.

Edza muenzaniso unotevera kutumidza zita retafura testalter_tbl kuti alter_tbl:

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

Iyo ALTER command inogona kushandiswawo kugadzira nekudzima indexes pamatafura eMySQL, yatichavhara muzvitsauko zvinotevera.

kuchinja mamwe mashandisirwo

Shandura injini yekuchengetedza: shandura kuti ive myisam

alter table tableName engine=myisam;

Bvisa kiyi yekune imwe nyika inomanikidza: keyName is a foreign key alias

alter table tableName drop foreign key keyName;

Nzvimbo yehukama yemunda wakagadziridzwa: pano zita1 ndiyo munda waunoda kugadzirisa, type1 ndiyo yepakutanga mhando yemunda, uye iwe unogona kusarudza imwe yekutanga uye mushure, iyo inofanirwa kuve pachena, yekutanga inoiswa pekutanga, uye mushure yakaiswa mushure mezita2 munda

alter table tableName modify name1 type1 first|after name2;