MySQLi ALTER order is exceptionally valuable when you need to change a name of your table, any table field or on the off chance that you need to add or erase a current segment in a table.
We should start with formation of a table called tutorials_alter.
root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> create table tutorials_alter
-> (
-> i INT,
-> c CHAR(1)
-> );
Query OK, 0 rows affected (0.27 sec)
mysql> SHOW COLUMNS FROM tutorials_alter;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i | int(11) | YES | | NULL | |
| c | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.02 sec)
Dropping, Adding or Repositioning a Column
Assume you need to drop a current segment I from above MySQLi table then you will utilize DROP proviso alongside ALTER order as follows −
mysql> ALTER TABLE tutorials_alter DROP i;
A DROP won't work if the section is the just a single left in the table.
To add a section, use ADD and indicate the segment definition. The accompanying assertion reestablishes the I segment to tutorials_alter −
mysql> ALTER TABLE tutorials_alter ADD i INT;
Subsequent to giving this explanation, testalter will contain the very two segments that it had when you previously made the table, however won't have a remarkable same design. That is on the grounds that new sections are added to the furthest limit of the table as a matter of course. So despite the fact that I initially was the main section in mytbl, presently it is the last one.
mysql> SHOW COLUMNS FROM tutorials_alter;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c | char(1) | YES | | NULL | |
| i | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)
To show that you need a segment at a particular situation inside the table, either utilize FIRST to make it the main segment or AFTER col_name to demonstrate that the new section ought to be set after col_name. Attempt the accompanying ALTER TABLE proclamations, utilizing SHOW COLUMNS after every one to perceive what impact every one has −
Adjust TABLE testalter_tbl DROP I;
Adjust TABLE testalter_tbl ADD I INT FIRST;
Adjust TABLE testalter_tbl DROP I;
Adjust TABLE testalter_tbl ADD I INT AFTER c;
The FIRST and AFTER specifiers work just with the ADD proviso. This implies that on the off chance that you need to reposition a current section inside a table, you initially should DROP it and afterward ADD it at the new position.
Changing a Column Definition or Name
To change a section's definition, use MODIFY or CHANGE provision alongside ALTER order. For instance, to change segment c from CHAR(1) to CHAR(10), do this −
mysql> ALTER TABLE tutorials_alter MODIFY c CHAR(10);
With CHANGE, the linguistic structure is somewhat unique. After the CHANGE catchphrase, you name the section you need to change, at that point determine the new definition, which incorporates the new name. Evaluate the accompanying model:
mysql> ALTER TABLE tutorials_alter CHANGE i j BIGINT;
On the off chance that you presently use CHANGE to change over j from BIGINT back to INT without changing the section name, the assertion will be true to form −
mysql> ALTER TABLE tutorials_alter CHANGE j j INT;
The Effect of ALTER TABLE on Null and Default Value Attributes −
At the point when you MODIFY or CHANGE a segment, you can likewise determine whether the section can contain NULL qualities and what its default esteem is. Indeed, in the event that you don't do this, MySQLi naturally doles out qualities for these credits.
Here is the model, where NOT NULL segment will have esteem 100 naturally.
mysql> ALTER TABLE tutorials_alter
-> MODIFY j BIGINT NOT NULL DEFAULT 100;
On the off chance that you don't use above order, at that point MySQLi will top off NULL qualities in all the sections.
Changing a Column's Default Value
You can change a default an incentive for any section utilizing ALTER order. Evaluate the accompanying model.
mysql> ALTER TABLE tutorials_alter ALTER j SET DEFAULT 1000;
mysql> SHOW COLUMNS FROM tutorials_alter;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c | char(10) | YES | | NULL | |
| j | bigint(20) | NO | | 1000 | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
You can eliminate default imperative from any section by utilizing DROP condition alongside ALTER order.
mysql> ALTER TABLE tutorials_alter ALTER j DROP DEFAULT;
mysql> SHOW COLUMNS FROM tutorials_alter;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c | char(10) | YES | | NULL | |
| j | bigint(20) | NO | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
Changing a Table Type
You can utilize a table sort by utilizing TYPE provision alongside ALTER order.
To discover the current sort of a table, utilize the SHOW TABLE STATUS explanation.
mysql> SHOW TABLE STATUS LIKE 'tutorials_alter'\G
*************************** 1. row ***************************
Name: tutorials_alter
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2017-02-17 11:30:29
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
Renaming a Table
To rename a table, utilize the RENAME alternative of the ALTER TABLE proclamation. Evaluate the accompanying guide to rename tutorials_alter to tutorials_bks.
mysql> ALTER TABLE tutorials_alter RENAME TO tutorials_bks;
You can utilize ALTER order to make and drop INDEX on a MySQL document. We will see this element in next part.