Creating and Managing Columns

Columns are usually created when a table is created. As application needs change, however, it is not uncommon to add a column or change how it is defined.

Even trivial modifications to a column will cause a table to be rebuilt. Always have backups before changing a column. For multi-gigabyte tables, the rebuild might take a significant amount of time. Consider making the change during a period of downtime or on a copy of the table.

Adding Columns

ALTER TABLE table_name       ADD COLUMN [column_definition]; ALTER TABLE book       ADD COLUMN ISBN VARCHAR(10) NOT NULL; 

Columns can easily be added to existing tables.

Take a column definition that is appropriate for a CREATE TABLE statement and prepend ALTER TABLE table_name COLUMN to it.

The first query shows the general form of the query. The second query shows a specific example of the query, where an ISBN column is being added to a book table.

Changing Column Definitions (and Names)

ALTER TABLE table_name       CHANGE COLUMN column_name [column_definition]; ALTER TABLE book       CHANGE COLUMN ISBN isbn VARCHAR(10) NOT NULL;

Changing the definition of a column (including giving a column a new name) is similar to the process of adding a column. There is a minor difference in syntax: Use ALTER TABLE ... CHANGE COLUMN instead of ALTER TABLE ... ADD COLUMN. The column definition must contain the column name before its type and attributes, so if you are changing the definition of a column but not its name, the column name appears twice in the statement.

The first query shows the general syntax for the query, while the second query shows a specific query where you change the name and width of the ISBN column of the book table.

Deleting Columns

ALTER TABLE table_name DROP COLUMN column_name;

Deleting a column from a table is a simple process. Keep in mind the usual caveats about commands that delete things from the database: You can't undo the deletion, and people might depend on the thing you are deleting.

Tip

Deleting a column also deletes any indexes that include that column.

Identifiers

MySQL identifiers, such as database and column names, are case sensitive under most circumstances. To keep things simple, it is best to always use lowercase for these identifiers. To force MySQL to behave consistently across all platforms, set the lower_case_table_names configuration variable to 1. This forces all case-sensitive identifiers to be treated as lowercase. For more information on identifier names, see the MySQL manual at http://mysql.com/identifier case sensitivity.