ALTER TABLE - ADD clauses for columns

These clauses of the MySQL statement, ALTER TABLE add columns to a MySQL table. At a minimum, with this syntax you list the name of a column to add and its datatype.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

ALTER [IGNORE] TABLE table
ADD [COLUMN] column definition [FIRST|AFTER column] |
ADD [COLUMN] (column definition,...)

Explanation

These clauses of the MySQL statement, ALTER TABLE add columns to a MySQL table. The same column definitions found in a CREATE TABLE statement are used in this statement. Basically, this MySQL statement lists the name of the column followed by the column datatype and the default value or other relevant components. The COLUMN keyword is optional and has no effect.

By default, an added column is appended to the end of the table. To insert a new column at the beginning of a MySQL table, use the FIRST keyword at the end of the ADD COLUMN clause. To insert it after a particular existing column, use the AFTER keyword followed by the name of the column after which the new column is to be inserted.

Examples

Below is an example of this MySQL statement:

ALTER TABLE workreq
ADD COLUMN req_type CHAR(4) AFTER req_date,
ADD COLUMN priority CHAR(4) AFTER req_date;

In this example, two columns are added after the existing req_date column. The clauses are executed in the order that they are given.Therefore, req_type is placed after req_date. Then priority is added after req_date and before req_type. Notice that more than one clause can be given in one ALTER TABLE statement; just separate them with commas.