Dropping, Adding, or Repositioning a Column
Problem
You want to get rid of a table column, add a new column, or move a column around within a table.
Solution
Use the DROP
or ADD
clauses of
ALTER
TABLE
to remove or add a
column. To move a column, drop it and then put it back where you want
it.
Discussion
To
remove a column from a table, use
DROP
followed by the column name. This
statement drops the i
column, leaving only the
c
column in mytbl
:
ALTER TABLE mytbl DROP i;
DROP
will not work if the column is the only one
left in the table. (To verify this, try to drop the
c
column from mytbl
after
dropping the i
column; an error will occur.)
To add a column, use
ADD
and specify the column definition. The
following statement restores the i
column to
mytbl
:
ALTER TABLE mytbl ADD i INT;
After issuing this statement, mytbl
will contain
the same two columns that it had when you first created the table,
but will not have quite the same structure. That’s
because new columns are added to the end of the table by default. So
even though i
originally was the first column in
mytbl
, now it is the last one:
mysql> SHOW COLUMNS FROM mytbl;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c | char(1) | YES | | NULL | |
| i | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
To
indicate that you want a column at a specific position within the
table, either use
FIRST
to make it ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.