MySQueaL Resources

ALTER TABLE - ADD clauses for columns Statement

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.

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.

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.