Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from MySQL Cookbook

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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata