Kusintha kwa MySQL kuwonjezera kuti muwonjezere malo angapo? Kufotokozera mwatsatanetsatane kagwiritsidwe ntchito ka mawu osintha pagawo

MySQL sinthani kuwonjezera kuti muwonjezere malo angapo? Kufotokozera mwatsatanetsatane za kagwiritsidwe ntchito ka mawu osintha pagawo

MySQL ALTER lamulo

Pamene tikufunika kusintha dzina la tebulo la deta kapena kusintha magawo a tebulo la deta, tifunika kugwiritsa ntchito MySQL ALTER lamulo.

Tisanayambe phunziro ili, tiyeni tipange tebulo lotchedwa: 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)

Chotsani, onjezani kapena sinthani magawo a tebulo

Lamulo lotsatirali limagwiritsa ntchito lamulo la ALTER ndi chiganizo cha DROP kugwetsa ndime ya tebulo yomwe idapangidwa pamwambapa:

mysql> ALTER TABLE testalter_tbl  DROP i;

DROP singagwiritsidwe ntchito kufufuta gawo ngati patsala gawo limodzi lokha patebulo la data.

Chigamulo cha ADD chimagwiritsidwa ntchito mu MySQL kuwonjezera mizati pa tebulo la deta. Chitsanzo chotsatirachi chikuwonjezera gawo la i pa tebulo testalter_tbl ndikutanthauzira mtundu wa deta:

mysql> ALTER TABLE testalter_tbl ADD i INT;

Pambuyo pochita lamulo ili pamwambapa, gawo la i limawonjezedwa kumapeto kwa magawo a tebulo la 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)

Ngati mukufuna kufotokoza malo atsopano, mungagwiritse ntchito mawu ofunika FIRST operekedwa ndi MySQL (setKuyikandime yoyamba), PAMENE dzina lamunda (kukhazikitsa pambuyo pa gawo).

Yesani mawu otsatirawa a ALTER TABLE, ndipo mutatha kuchita bwino, gwiritsani ntchito SHOW COLUMNS kuti muwone kusintha kwa tebulo:

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;

Mawu ofunikira FIRST and AFTER amangogwiritsidwa ntchito mu ADD clause, kotero ngati mukufuna kukonzanso malo a tebulo la deta muyenera kugwiritsa ntchito DROP kuchotsa munda ndiyeno ADD kuwonjezera munda ndikuyika malo.

Sinthani mtundu wamunda ndi dzina

Ngati mukufuna kusintha mtundu wamunda ndi dzina, mutha kugwiritsa ntchito MODIFY kapena CHANGE clause mu ALTER command.

Mwachitsanzo, kuti musinthe mtundu wa gawo c kuchokera ku CHAR(1) kukhala CHAR(10), pangani lamulo ili:

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

Ndi ndime ya CHANGE, mawuwa ndi osiyana kwambiri.Mwamsanga pambuyo CHANGE mawu ofunika ndi dzina la munda mukufuna kusintha, ndiyeno tchulani latsopano munda dzina ndi mtundu.Yesani chitsanzo ichi:

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

Zotsatira za ALTER TABLE pa Null ndi Default Values

Mukasintha gawo, mutha kufotokoza ngati muphatikizepo zokha kapena muyike mtengo wokhazikika.

Chitsanzo chotsatirachi chikufotokoza kuti munda j SIWONSE ndipo mtengo wokhazikika ndi 100.

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

Ngati simukuyika mtengo wokhazikika, MySQL idzakhazikitsa gawolo kukhala NULL mwachisawawa.

Sinthani mtengo wokhazikika wagawo

Mutha kugwiritsa ntchito ALTER kuti musinthe mtengo wagawo, yesani zitsanzo izi:

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)

Mukhozanso kugwiritsa ntchito lamulo la ALTER ndi ndime ya DROP kuchotsa mtengo wosasintha wa munda, monga chitsanzo chotsatirachi:

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:

Kusintha mtundu wa tebulo la data kutha kuchitidwa pogwiritsa ntchito lamulo la ALTER ndi ndime ya TYPE.Yesani chitsanzo chotsatirachi, pomwe timasintha mtundu wa tebulo testalter_tbl kukhala MYISAM:

Chidziwitso:Kuti muwone mtundu wa tebulo la data, mutha kugwiritsa ntchito mawu akuti 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)

Sinthani dzina la tebulo

Ngati mukufuna kusintha dzina la tebulo la data, mutha kugwiritsa ntchito ndime ya RENAME mu mawu a ALTER TABLE kuti mutero.

Yesani chitsanzo chotsatirachi kuti musinthe dzina la tebulo testalter_tbl kukhala alter_tbl:

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

Lamulo la ALTER litha kugwiritsidwanso ntchito kupanga ndi kuchotsa ma index pa matebulo a MySQL, omwe tidzakambirana m'mitu yotsatira.

kusintha ntchito zina

Sinthani injini yosungira: sinthani kukhala myisam

alter table tableName engine=myisam;

Chotsani makiyi akunja: KeyName ndi makiyi akunja

alter table tableName drop foreign key keyName;

Malo achibale a gawo losinthidwa: apa name1 ndiye gawo lomwe mukufuna kusintha, type1 ndiye mtundu woyambirira wamunda, ndipo mutha kusankha imodzi mwazoyamba ndi pambuyo pake, zomwe ziyenera kuwonekera, zoyamba zimayikidwa koyamba, ndipo pambuyo pake. yoyikidwa pambuyo pa dzina2 munda

alter table tableName modify name1 type1 first|after name2;