MySQL hloov pauv ntxiv kom nce ntau txoj haujlwm? Cov lus piav qhia ntxaws txog kev siv cov lus hloov kho kab lus

MySQL Hloov ntxiv kom nce ntau txoj haujlwm? Cov lus piav qhia ntxaws txog kev siv cov lus hloov kho kab lus

MySQL ALTER hais kom ua

Thaum peb xav tau hloov kho cov ntaub ntawv lub npe lossis hloov kho cov ntaub ntawv cov lus teb, peb yuav tsum siv MySQL ALTER hais kom ua.

Ua ntej pib qhov kev qhia no, cia peb tsim lub rooj hu ua: 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)

Rho tawm, ntxiv lossis hloov cov lus teb

Cov lus txib hauv qab no siv ALTER hais kom ua nrog DROP clause los tso kuv kem ntawm lub rooj tsim saum toj no:

mysql> ALTER TABLE testalter_tbl  DROP i;

DROP tsis tuaj yeem siv los rho tawm ib daim teb yog tias tsuas muaj ib daim teb sab laug hauv cov ntaub ntawv.

ADD clause yog siv hauv MySQL los ntxiv cov kab rau cov ntaub ntawv cov ntaub ntawv.Cov piv txwv hauv qab no ntxiv qhov kuv teb rau lub rooj testalter_tbl thiab txhais cov ntaub ntawv hom:

mysql> ALTER TABLE testalter_tbl ADD i INT;

Tom qab ua tiav cov lus txib saum toj no, lub teb kuv tau muab ntxiv rau qhov kawg ntawm cov ntaub ntawv cov lus teb.

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)

Yog tias koj xav tau qhia qhov chaw ntawm daim teb tshiab, koj tuaj yeem siv lo lus FIRST muab los ntawm MySQL (setTxoj Haujlwmthawj kab), TAM SIM NO lub npe (teem tom qab ib daim teb).

Sim cov lus hauv qab no ALTER TABLE nqe lus, thiab tom qab ua tiav, siv SHOW COLUMNS los saib cov kev hloov pauv hauv cov qauv lus:

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;

FIRST thiab TAG NRHO cov ntsiab lus tsuas yog siv nyob rau hauv ADD clause, yog li yog tias koj xav rov pib dua txoj haujlwm ntawm cov ntaub ntawv cov ntaub ntawv, koj yuav tsum xub siv DROP kom tshem tawm cov teb thiab tom qab ntawd siv ADD ntxiv rau thaj chaw thiab teeb tsa txoj haujlwm.

Hloov hom teb thiab lub npe

Yog tias koj xav hloov kho hom teb thiab lub npe, koj tuaj yeem siv MODIFY lossis CHANGE clause hauv ALTER hais kom ua.

Piv txwv li, hloov hom teb c los ntawm CHAR(1) rau CHAR(10), ua raws li cov lus txib hauv qab no:

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

Nrog rau CHANGE clause, cov syntax txawv heev.Tam sim ntawd tom qab CHANGE lo lus tseem ceeb yog lub npe ntawm daim teb koj xav hloov kho, thiab tom qab ntawd qhia lub npe tshiab thiab hom.Sim ua piv txwv hauv qab no:

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

Qhov cuam tshuam ntawm ALTER TABLE ntawm Null thiab Default Values

Thaum koj hloov kho ib daim teb, koj tuaj yeem qhia seb puas suav nrog lossis seb puas yuav teeb tsa tus nqi qub.

Cov piv txwv hauv qab no qhia tias teb j tsis yog NULL thiab tus nqi pib yog 100.

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

Yog tias koj tsis teeb tsa tus nqi pib, MySQL yuav cia li teeb tsa lub teb rau NULL los ntawm lub neej ntawd.

Hloov kho qhov chaw pib tus nqi

Koj tuaj yeem siv ALTER hloov pauv tus nqi pib ntawm daim teb, sim cov piv txwv hauv qab no:

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)

Koj tuaj yeem siv ALTER hais kom ua nrog DROP clause kom tshem tawm tus nqi pib ntawm daim teb, xws li hauv cov piv txwv hauv qab no:

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:

Kev hloov kho cov ntaub ntawv hom tuaj yeem ua tiav siv ALTER hais kom ua thiab HOM kab lus.Sim ua piv txwv hauv qab no, qhov twg peb hloov hom rooj testalter_tbl rau MYISAM:

Nco tseg:Txhawm rau saib cov ntaub ntawv hom, koj tuaj yeem siv SHOW TABLE STATUS nqe lus.

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)

Hloov lub npe rooj

Yog tias koj xav tau hloov kho lub npe ntawm cov ntaub ntawv cov ntaub ntawv, koj tuaj yeem siv RENAME clause hauv ALTER TABLE nqe lus los ua li ntawd.

Sim ua piv txwv hauv qab no los hloov npe cov ntaub ntawv cov lus testalter_tbl rau alter_tbl:

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

ALTER hais kom ua kuj tuaj yeem siv los tsim thiab tshem tawm qhov ntsuas ntawm MySQL cov ntxhuav, uas peb yuav npog hauv tshooj tom ntej.

Hloov lwm yam kev siv

Hloov kho lub cav cia: hloov nws mus rau myisam

alter table tableName engine=myisam;

Tshem tawm cov yuam sij txawv teb chaws: keyName yog tus yuam sij txawv teb chaws

alter table tableName drop foreign key keyName;

Txoj hauj lwm txheeb ze ntawm qhov hloov kho: ntawm no lub npe 1 yog daim teb uas koj xav hloov kho, hom 1 yog thawj hom ntawm daim teb, thiab koj tuaj yeem xaiv ib qho ntawm thawj thiab tom qab, uas yuav tsum pom tseeb, ua ntej yog muab tso ua ntej, thiab tom qab yog muab tso tom qab lub npe2 teb

alter table tableName modify name1 type1 first|after name2;