MySQL 5.7 alter table add column

John D K -

Adding new table is most common DDL operation that I'm using during my DBA career. There are some tricks that can be useful for you. So I'll show them in several examples:

Add column to table basic syntax

This is the basic syntax for adding new column in MySQL (and in standard SQL):

ALTER TABLE tablename ADD columnname datatype;

Add column example

Adding new column with datatype VARCHAR( 10 ) and name fiscalEnd to table fiscal

ALTER TABLE fiscal ADD fiscalEnd VARCHAR( 10 );

Add column after other column

You can also add new column after one that you specify in the query(if you don't specified by default it will be add the new column at the end:

ALTER TABLE fiscal ADD fiscalEnd VARCHAR( 10 ) after year;

Add column more options

Adding date column which accept NULL with default value NULL:

ALTER TABLE `fiscal`
	ADD COLUMN `fisRes` DATE NULL DEFAULT NULL AFTER `result`;

Add more than one column

If you want to add several columns in MySQL you can use this syntax:

ALTER TABLE `fiscal`
	ADD COLUMN `startDate` DATE NULL DEFAULT '2018-02-22' AFTER `fisRes`,
	ADD COLUMN `endTime` TIME NULL DEFAULT '22:00' AFTER `startDate`;
  • adding column startDate with default 2018-02-22
  • adding column endTime with default 22:00

Table used in this examples:

CREATE TABLE fiscal
	(`id` int, `year` int(4), `flag1` varchar(5), `flag2` varchar(3), `flag3` varchar(3), `result` int(2))
;