MySQL alter faʻaopoopo e faʻateleina ai le tele o avanoa fanua? Fa'amatalaga au'ili'ili o le fa'aogaina o le fa'amatalaga o koluma sui

MySQL sui fa'aopoopo e fa'aopoopo ai le tele o avanoa fa'atosina? Fa'amatalaga au'ili'ili o le fa'aogaina o le fa'amatalaga o koluma sui

MySQL ALTER poloaiga

A tatou manaʻomia le suia o le igoa o le laulau faʻamaumauga pe suia le laulau laulau faʻamaumauga, e manaʻomia ona tatou faʻaogaina le MySQL ALTER poloaiga.

Aʻo leʻi amataina lenei aʻoaʻoga, seʻi o tatou fatuina se laulau e igoa: 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)

Ave'ese, fa'aopoopo pe sui fanua laulau

O le faʻatonuga o loʻo i lalo e faʻaaoga ai le tulafono ALTER ma le DROP fuaiupu e tuʻu ai le koluma o le laulau na faia i luga:

mysql> ALTER TABLE testalter_tbl  DROP i;

E le mafai ona fa'aoga le DROP e tape ai se fanua pe afai e na'o le tasi le fanua o totoe i le laulau fa'amatalaga.

O lo'o fa'aoga le fuaiupu ADD i MySQL e fa'aopoopo ai koluma i le laulau fa'amaumauga.O le fa'ata'ita'iga lea e fa'aopoopoina ai le i fanua i le laulau testalter_tbl ma fa'amatala le ituaiga fa'amatalaga:

mysql> ALTER TABLE testalter_tbl ADD i INT;

A maeʻa ona faʻatinoina le faʻatonuga o loʻo i luga, o le i fanua e otometi lava ona faʻaopoopo i le pito o faʻamaumauga o laulau faʻamaumauga.

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)

Afai e te manaʻo e faʻamaonia le nofoaga o le fanua fou, e mafai ona e faʻaogaina le upu FIRST saunia e MySQL (settulagakoluma muamua), AFTER igoa ole fanua (seti i tua o se fanua).

Taumafai le faʻamatalaga ALTER TABLE o loʻo i lalo, ma a maeʻa le faʻatinoga, faʻaaoga SHOW COLUMNS e vaʻai ai suiga i le fausaga o le laulau:

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;

O le FIRST ma le AFTER keywords e naʻo le faʻaaogaina i le ADD fuaiupu, o lea afai e te manaʻo e toe faʻafoʻi le tulaga o se laulau faʻamaumauga, e tatau ona e faʻaaoga muamua le DROP e tape ai le fanua ona faʻaaoga lea o le ADD e faʻaopoopo le fanua ma seti le tulaga.

Suia ituaiga fanua ma igoa

Afai e te manaʻomia le suia o le ituaiga fanua ma le igoa, e mafai ona e faʻaogaina le MODIFY poʻo le CHANGE fuaiupu i le ALTER command.

Mo se faʻataʻitaʻiga, ia suia le ituaiga o fanua c mai le CHAR (1) i le CHAR (10), faʻatino le poloaiga lenei:

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

Faatasi ai ma le fuaiupu CHANGE, o le syntax e matua ese lava.O le taimi lava e uma ai le CHANGE keyword o le igoa o le fanua e te manaʻo e sui, ona faʻamaonia lea o le igoa fou ma le ituaiga.Taumafai le fa'ata'ita'iga lenei:

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

A'afiaga ole ALTER TABLE ile Null ma le Fa'ailoga Tau

A e suia se fanua, e mafai ona e faʻamaonia pe faʻapipiʻi naʻo pe seti se tau faʻaletonu.

O le faʻataʻitaʻiga o loʻo taʻua i lalo o loʻo faʻamaoti mai ai o le fanua j e LE NULL ma o le tau le aoga o le 100.

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

Afai e te le setiina se tau le aoga, MySQL o le a otometi lava ona setiina le fanua i le NULL e ala i le faaletonu.

Suia le tau le aoga o fanua

E mafai ona e fa'aogaina le ALTER e sui ai le tau le aoga o se fanua, fa'ata'ita'i fa'ata'ita'iga 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)

E mafai foʻi ona e faʻaogaina le tulafono ALTER ma le DROP fuaiupu e aveese ai le tau le aoga o se fanua, pei o le faʻataʻitaʻiga lea:

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:

Suia le ituaiga laulau faʻamatalaga e mafai ona faia e faʻaaoga ai le ALTER poloaiga ma le TYPE fuaiupu.Taumafai le faʻataʻitaʻiga o loʻo i lalo, lea matou te suia ai le ituaiga o le laulau testalter_tbl i le MYISAM:

Manatua:Ina ia va'ai i le ituaiga laulau fa'amatalaga, e mafai ona e fa'aogaina le fa'amatalaga 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)

Suia le igoa ole laulau

Afai e te manaʻomia le suia o le igoa o le laulau faʻamaumauga, e mafai ona e faʻaogaina le RENAME fuaiupu i le ALTER TABLE faʻamatalaga e fai ai.

Fa'ata'ita'i le fa'ata'ita'iga lea e toe fa'aigoa ai le laulau fa'amaumauga testalter_tbl i alter_tbl:

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

E mafai fo'i ona fa'aoga le fa'atonuga a le ALTER e fatu ma tape ai fa'ailoga i luga o laulau fa'amaumauga MySQL, lea o le a matou fa'ailoa i isi mataupu.

suia isi fa'aoga

Suia le afi teu oloa: sui i le myisam

alter table tableName engine=myisam;

Ave'ese le fa'agata mai fafo: keyName o se igoa fa'aigoa mai fafo

alter table tableName drop foreign key keyName;

Le tulaga faʻatatau o le fanua faʻaleleia: o le igoa1 o le fanua e tatau ona suia, type1 o le ituaiga muamua o le fanua, muamua ma mulimuli ane e mafai ona filifilia, lea e tatau ona manino, muamua tuʻu muamua, ma a uma ona tuʻu i tua o le igoa2 fanua

alter table tableName modify name1 type1 first|after name2;