Dropping, Adding, or Repositioning a Column

8.2.1 Problem

You want to get rid of a table column, add a new column, or move a column around within a table.

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

8.2.3 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 the first column, or AFTER col_name to indicate that the new column should be placed after col_name. Try the following ALTER TABLE statements, using SHOW COLUMNS after each one to see what effect each one has:

ALTER TABLE mytbl DROP i;
ALTER TABLE mytbl ADD i INT FIRST;
ALTER TABLE mytbl DROP i;
ALTER TABLE mytbl ADD i INT AFTER c;

The FIRST and AFTER specifiers work only with the ADD clause. This means that if you want to reposition an existing column within a table, you first must DROP it and then ADD it at the new position.

Column Repositioning and TIMESTAMP Columns

Be careful if you reposition columns in a table that contains more than one TIMESTAMP column. The first TIMESTAMP column has special properties not shared by the others. (See Recipe 5.32, which describes the differences.) If you change the order of the TIMESTAMP columns, you'll change the way your table behaves.

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net