MySQL canza ƙara don ƙara yawan matsayi na filin? Cikakkun bayanai game da amfani da bayanin shafi na gyara

MySQL canza ƙara don ƙara yawan matsayi na filin? Cikakkun bayanai game da amfani da bayanin shafi na gyara

MySQL ALTER umarni

Lokacin da muke buƙatar canza sunan tebur ɗin bayanai ko gyara filayen tebur ɗin bayanai, muna buƙatar amfani da MySQL ALTER umurnin.

Kafin fara wannan koyawa, bari mu ƙirƙiri tebur mai suna: 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)

Share, ƙara ko gyara filayen tebur

Umurni mai zuwa yana amfani da umarnin ALTER tare da jigon DROP don sauke i ginshiƙi na teburin da aka ƙirƙira a sama:

mysql> ALTER TABLE testalter_tbl  DROP i;

Ba za a iya amfani da DROP don share filin ba idan akwai saura filin guda ɗaya a teburin bayanai.

Ana amfani da jumlar ADD a cikin MySQL don ƙara ginshiƙai zuwa teburin bayanai. Misalin da ke gaba yana ƙara filin i zuwa teburin testalter_tbl kuma yana bayyana nau'in bayanan:

mysql> ALTER TABLE testalter_tbl ADD i INT;

Bayan aiwatar da umarnin da ke sama, ana ƙara filin i kai tsaye zuwa ƙarshen filayen tebur ɗin bayanai.

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)

Idan kuna buƙatar tantance wurin sabon filin, zaku iya amfani da kalmar FIRST da MySQL ta bayar (saitinMatsayishafi na farko), BAYAN sunan filin (saitin bayan filin).

Gwada bayanin ALTER TABLE mai zuwa, kuma bayan nasarar aiwatarwa, yi amfani da SHOW COLUMNS don ganin canje-canje a tsarin tebur:

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;

Ana amfani da kalmomin FIRST da AFTER kawai a cikin jumlar ADD, don haka idan kuna son sake saita matsayin filin tebur ɗin bayanai kuna buƙatar amfani da DROP don cire filin sannan ADD don ƙara filin kuma saita matsayi.

Gyara nau'in filin da suna

Idan kana buƙatar gyara nau'in filin da suna, zaka iya amfani da KYAUTA ko CHANGE magana a cikin umarnin ALTER.

Misali, don canza nau'in filin c daga CHAR(1) zuwa CHAR(10), aiwatar da umarni mai zuwa:

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

Tare da juzu'in CHANJI, haɗin gwiwar ya bambanta sosai.Nan da nan bayan kalmar CHANJI shine sunan filin da kake son gyarawa, sannan ka saka sabon sunan filin da rubuta.Gwada misali mai zuwa:

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

Tasirin ALTER TABLE akan Ƙimar Marasa da Tsofaffi

Lokacin da kuka canza filin, zaku iya tantance ko don haɗawa kawai ko don saita ƙimar tsoho.

Misalin da ke gaba yana ƙayyadad da cewa filin j BA NULL bane kuma ƙimar tsoho shine 100.

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

Idan baku saita ƙimar tsoho ba, MySQL zai saita filin ta atomatik zuwa NULL ta tsohuwa.

Gyara ƙimar tsohowar filin

Kuna iya amfani da ALTER don canza tsohuwar ƙimar filin, gwada misali mai zuwa:

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)

Hakanan zaka iya amfani da umarnin ALTER tare da jumlar DROP don cire tsohuwar ƙimar filin, kamar a cikin misali mai zuwa:

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:

Ana iya yin gyaggyarawa nau'in tebur ɗin bayanai ta amfani da umarnin ALTER da jumlar TYPE.Gwada misali mai zuwa, inda muke canza nau'in tebur testalter_tbl zuwa MYISAM:

Lura:Don duba nau'in tebur na bayanai, zaku iya amfani da bayanin MATSAYI SHOW TABLE.

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)

Gyara sunan tebur

Idan kana buƙatar canza sunan tebur ɗin bayanai, zaku iya amfani da saɓanin Sake suna a cikin bayanin ALTER TABLE don yin hakan.

Gwada misali mai zuwa don sake sunan teburin bayanai testalter_tbl zuwa alter_tbl:

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

Hakanan za'a iya amfani da umarnin ALTER don ƙirƙira da share fihirisa akan teburan MySQL, waɗanda za mu rufe a surori na gaba.

canza sauran amfani

Gyara injin ajiya: gyara shi zuwa myisam

alter table tableName engine=myisam;

Cire ƙuntataccen maɓalli na ƙasashen waje: maɓalli Sunan laƙabi ne na maɓalli na waje

alter table tableName drop foreign key keyName;

Matsayin dangi na filin da aka gyara: anan name1 shine filin da kake son gyarawa, nau'in 1 shine ainihin nau'in filin, kuma zaka iya zaɓar ɗaya na farko da na baya, wanda ya kamata ya bayyana, farko ana sanya shi farko, bayan haka shine. sanya bayan filin name2

alter table tableName modify name1 type1 first|after name2;