MySQL beddelka ku dar si loo kordhiyo jagooyin badan oo goob? Sharaxaad faahfaahsan oo ku saabsan isticmaalka bayaanka tiirka wax ka beddelka

MySQL Beddel ku dar si loo kordhiyo boosaska badan ee garoonka? Sharaxaad faahfaahsan oo ku saabsan isticmaalka bayaanka tiirka wax ka beddelka

MySQL ALTER amarka

Marka aan u baahanahay in aan wax ka beddelno magaca miiska xogta ama wax ka beddelka goobaha miiska xogta, waxaan u baahanahay inaan isticmaalno amarka MySQL ALTER.

Kahor intaadan bilaabin casharkan, aynu samayno shax magaceedu yahay: 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)

Tirtir, ku dar ama wax ka beddel meelaha miiska

Amarka soo socdaa wuxuu isticmaalayaa amarka ALTER oo leh DROP clause si uu u rido tiirka i ee shaxda kore:

mysql> ALTER TABLE testalter_tbl  DROP i;

DROP looma isticmaali karo in lagu tirtiro garoon haddii ay jirto hal goob oo keliya oo kaga hadhay shaxda xogta.

Qodobka ADD waxa loo isticmaalaa MySQL si loogu daro tiirarka shaxda xogta Tusaalaha soo socda wuxuu ku darayaa goobta i ee miiska testalter_tbl wuxuuna qeexayaa nooca xogta:

mysql> ALTER TABLE testalter_tbl ADD i INT;

Kadib fulinta amarka kor ku xusan, goobta i si toos ah ayaa loogu daraa dhamaadka goobaha miiska xogta.

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)

Haddii aad u baahan tahay inaad sheegto goobta goobta cusub, waxaad isticmaali kartaa ereyga muhiimka ah ee ugu horreeya ee ay bixiso MySQLMeeleyntatiirka koowaad), KA DIB Magaca goobta (loo dhigay goob ka dib).

Isku day qoraalkan BEDELKA AH ee soo socda, iyo ka dib fulinta si guul leh, isticmaal SHOW COLUMNS si aad u aragto isbeddelada qaab dhismeedka miiska:

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;

Erayada muhiimka ah ee FIRST iyo AFTER waxa kaliya oo lagu isticmaalaa jumlada ADD, markaa haddii aad rabto inaad dib u dejiso booska miiska xogta, waxaad u baahan tahay inaad isticmaasho DROP si aad u tirtirto goobta ka dibna isticmaal ADD si aad ugu darto goobta una dejiso booska.

Wax ka beddel nooca goobta iyo magaca

Haddii aad u baahan tahay inaad wax ka beddesho nooca goobta iyo magaca, waxaad isticmaali kartaa BEDEL ama QODOBKA ISBEDEL ee amarka ALTER.

Tusaale ahaan, si aad u bedesho nooca goobta c ee CHAR(1) una badasho CHAR(10), fuli amarka soo socda:

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

Qodobka ISBEDELKA, eraygu aad buu u kala duwan yahay.Isla markiiba ka dib erayga muhiimka ah ISBEDEL waa magaca goobta aad rabto inaad wax ka beddesho, ka dibna sheeg magaca goobta cusub iyo nooca.Isku day tusaalahan soo socda:

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

Saamaynta Miiska BEDELKA ee Qiimayaasha Asal-ka ah iyo Wax-qabad la'aan

Markaad wax ka beddesho goobta, waxaad qeexi kartaa inaad ku darto kaliya ama inaad dejiso qiimaha caadiga ah.

Tusaalaha soo socdaa wuxuu qeexayaa in goobta j aysan NULL ahayn oo qiimaha caadiga ah uu yahay 100.

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

Haddii aadan dejin qiimaha caadiga ah, MySQL waxay si toos ah u dejin doontaa goobta NULL si caadi ah.

Wax ka beddel qiimaha caadiga ah ee goobta

Waxaad isticmaali kartaa ALTER si aad u bedesho qiimaha caadiga ah ee goobta, isku day tusaalooyinka soo socda:

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)

Waxa kale oo aad isticmaali kartaa amarka ALTER oo leh qodobka DROP si aad meesha uga saarto qiimaha caadiga ah ee goobta, sida tusaalahan soo socda:

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:

Wax ka beddelka nooca miiska xogta waxa lagu samayn karaa iyadoo la isticmaalayo amarka ALTER iyo faqradda TYPE.Isku day tusaalahan soo socda, halkaas oo aan ku beddelno nooca miiska testalter_tbl una beddelno MYISAM:

Xusuusin:Si aad u aragto shaxda xogta nooca miiska, waxaad isticmaali kartaa SHOW SHAXDA XAALADDA XAALADDA.

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)

Wax ka beddel magaca miiska

Haddii aad u baahan tahay inaad wax ka beddesho magaca shaxda xogta, waxaad isticmaali kartaa faqradda dib u MAGACAALKA ee qoraalka BEDELKA AH si aad sidaas u samayso.

Isku day tusaalaha soo socda si aad u magacawdo shaxda xogta testalter_tbl una beddelo alter_tbl:

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

Amarka ALTER waxa kale oo loo isticmaali karaa in lagu abuuro oo la tirtiro tusmooyinka miisaska xogta MySQL, kuwaas oo aanu ku soo bandhigi doono cutubyada soo socda.

isticmaalka kale beddelo

Wax ka beddel mishiinka kaydinta: u beddel myisam

alter table tableName engine=myisam;

Ka saar xannibaadaha furaha ajnabiga ah: keyName waa furaha ajnabiga ah oo loo yaqaan

alter table tableName drop foreign key keyName;

Mawqifka qaraabada ah ee goobta wax laga beddelay: halkan name1 waa goobta aad rabto inaad wax ka beddesho, type1 waa nooca asalka ah ee goobta, oo waxaad dooran kartaa mid ka mid ah hore iyo ka dib, taas oo ah inay caddahay, marka hore ayaa la dhigayaa, ka dibna waa la dhigay ka dib goobta magaca2

alter table tableName modify name1 type1 first|after name2;