MySQL paarọ ṣafikun lati mu awọn ipo aaye lọpọlọpọ pọ si? Alaye ni kikun ti lilo ti alaye iwe atunṣe

MySQL paarọ afikun lati mu awọn ipo aaye pupọ pọ si? Alaye ni kikun ti lilo ti iyipada alaye iwe

MySQL ALTER pipaṣẹ

Nigba ti a ba nilo lati yi orukọ tabili data pada tabi yi awọn aaye tabili data pada, a nilo lati lo aṣẹ MySQL ALTER.

Ṣaaju ki o to bẹrẹ ikẹkọ yii, jẹ ki a ṣẹda tabili ti a npè ni: 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)

Paarẹ, ṣafikun tabi yipada awọn aaye tabili

Aṣẹ atẹle naa nlo aṣẹ ALTER pẹlu gbolohun ọrọ DROP lati ju iwe i ti tabili ti a ṣẹda loke:

mysql> ALTER TABLE testalter_tbl  DROP i;

A ko le lo DROP lati pa aaye kan rẹ ti aaye kan ba wa ninu tabili data.

ADD gbolohun naa ni a lo ni MySQL lati ṣafikun awọn ọwọn si tabili data. Apẹẹrẹ atẹle n ṣafikun aaye i si tabili testalter_tbl ati asọye iru data naa:

mysql> ALTER TABLE testalter_tbl ADD i INT;

Lẹhin ṣiṣe pipaṣẹ ti o wa loke, aaye i ti wa ni afikun laifọwọyi si opin awọn aaye tabili 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)

Ti o ba nilo lati pato ipo ti aaye tuntun, o le lo koko-ọrọ FIRST ti a pese nipasẹ MySQL (ṣetoIpoiwe akọkọ), LEHIN aaye orukọ (ṣeto lẹhin aaye kan).

Gbiyanju alaye ALTER TABLE atẹle yii, ati lẹhin ipaniyan aṣeyọri, lo SHOW COLUUMNS lati wo awọn ayipada ninu eto tabili:

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;

Awọn koko-ọrọ FIRST ati AFTER nikan ni a lo ni gbolohun ADD, nitorina ti o ba fẹ tun ipo aaye tabili data kan, o nilo lati lo DROP akọkọ lati pa aaye naa lẹhinna lo ADD lati ṣafikun aaye naa ki o ṣeto ipo naa.

Ṣe atunṣe iru aaye ati orukọ

Ti o ba nilo lati yipada iru aaye ati orukọ, o le lo MODIFY tabi gbolohun ọrọ CHANGE ni aṣẹ ALTER.

Fun apẹẹrẹ, lati yi iru aaye c lati CHAR (1) si CHAR (10), ṣiṣẹ pipaṣẹ atẹle:

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

Pẹlu gbolohun CHANGE, sintasi naa yatọ pupọ.Lẹsẹkẹsẹ lẹhin Koko CHANGE ni orukọ aaye ti o fẹ yipada, lẹhinna pato orukọ aaye tuntun ati iru.Gbiyanju apẹẹrẹ wọnyi:

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

Ipa ti ALTER TABLE lori Asan ati Awọn iye Aiyipada

Nigbati o ba yi aaye kan pada, o le pato boya lati ṣafikun nikan tabi boya lati ṣeto iye aiyipada.

Apẹẹrẹ atẹle n tọka si aaye j KO NULL ati pe iye aiyipada jẹ 100.

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

Ti o ko ba ṣeto iye aiyipada, MySQL yoo ṣeto aaye laifọwọyi si NULL nipasẹ aiyipada.

Ṣe atunṣe iye aiyipada aaye

O le lo ALTER lati yi iye aiyipada ti aaye kan pada, gbiyanju awọn apẹẹrẹ wọnyi:

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)

O tun le lo aṣẹ ALTER pẹlu gbolohun ọrọ DROP lati yọ iye aiyipada ti aaye kan kuro, bi ninu apẹẹrẹ atẹle:

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:

Iyipada iru tabili data le ṣee ṣe nipa lilo aṣẹ ALTER ati gbolohun ọrọ TYPE.Gbiyanju apẹẹrẹ atẹle, nibiti a ti yi iru tabili testalter_tbl pada si MYISAM:

Akiyesi:Lati wo iru tabili data, o le lo Gbólóhùn IPIN 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)

Ṣe atunṣe orukọ tabili

Ti o ba nilo lati yi orukọ tabili data pada, o le lo gbolohun ọrọ Tatunkọ ni Gbólóhùn ALTER TABLE lati ṣe bẹ.

Gbiyanju apẹẹrẹ atẹle lati tunrukọ tabili data testalter_tbl si alter_tbl:

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

Aṣẹ ALTER tun le ṣee lo lati ṣẹda ati paarẹ awọn atọka lori awọn tabili data MySQL, eyiti a yoo ṣafihan ni awọn ori atẹle.

paarọ awọn lilo miiran

Ṣe atunṣe ẹrọ ipamọ: yipada si myisam

alter table tableName engine=myisam;

Yọ idinamọ bọtini ajeji kuro: Orukọ bọtini jẹ inagijẹ bọtini ajeji

alter table tableName drop foreign key keyName;

Ipo ibatan ti aaye ti a ṣe atunṣe: nibi name1 ni aaye ti o fẹ yipada, type1 jẹ iru aaye atilẹba, ati pe o le yan ọkan ninu akọkọ ati lẹhin, eyiti o yẹ ki o han, akọkọ ni a gbe ni akọkọ, ati lẹhin naa gbe lẹhin aaye orukọ2

alter table tableName modify name1 type1 first|after name2;