297

I am looking for the syntax to add a column to a MySQL database with a default value of 0

Reference

Jess Stone
  • 677
  • 8
  • 21
Matt Elhotiby
  • 43,028
  • 85
  • 218
  • 321

10 Answers10

493

Try this:

ALTER TABLE table1 ADD COLUMN foo INT DEFAULT 0;

From the documentation that you linked to:

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
   alter_specification [, alter_specification] ...

alter_specification:
    ...
    ADD [COLUMN] (col_name column_definition,...)
    ...

To find the syntax for column_definition search a bit further down the page:

column_definition clauses use the same syntax for ADD and CHANGE as for CREATE TABLE. See Section 12.1.17, “CREATE TABLE Syntax”.

And from the linked page:

column_definition:  
   data_type [NOT NULL | NULL] [DEFAULT default_value]
   [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]  
   [COMMENT 'string']  
   [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]  
   [STORAGE {DISK|MEMORY|DEFAULT}]  
   [reference_definition]

Notice the word DEFAULT there.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • 4
    I was particularly struck by how good this answer is, both concise and thorough, wish I could upvote it again – Pseudonym Dec 15 '15 at 14:30
  • 1
    boolean and bool are synonyms for `TINYINT(1)` which is way more efficient than using `INT`, bear that in mind when using this "correct" answer – Clint Eastwood Apr 13 '16 at 19:21
  • 1
    In case you need to add a boolean column with a default value of False, you can use: ALTER TABLE table1 ADD COLUMN foo boolean not null default 0; – 1man May 02 '16 at 16:54
  • "ALTER TABLE table1 ADD COLUMN foo INT DEFAULT 0;" - In this 'COLUMN' keyword is not required – Adithya Sai Feb 14 '18 at 08:35
  • 1
    Consider adding `NOT NULL`. Unless specified, a column with a default value can still be NULL, which often (but not always) defeats the purpose of the default value. – Seth Mar 10 '22 at 14:45
46

Like this?

ALTER TABLE `tablename` ADD `new_col_name` INT NOT NULL DEFAULT 0;
Lekensteyn
  • 64,486
  • 22
  • 159
  • 192
7

table users (user_id int unsigned PK, username varchar(32))

alter table users add column verified tinyint unsigned default 0
Jon Black
  • 16,223
  • 5
  • 43
  • 42
6
ALTER TABLE my_table ADD COLUMN new_field TinyInt(1) DEFAULT 0;
Pang
  • 9,564
  • 146
  • 81
  • 122
Sandeep Sherpur
  • 2,418
  • 25
  • 27
5

Simply add default 0 at the end of your ALTER TABLE <table> ADD COLUMN <column> <type> statement

Eton B.
  • 6,121
  • 5
  • 31
  • 43
4

This will work for ENUM type as default value 

ALTER TABLE engagete_st.holidays add column `STATUS` ENUM('A', 'D') default 'A' AFTER `H_TYPE`;
vpgodara
  • 329
  • 2
  • 2
3

You can try this,

ALTER TABLE table_name ADD column_name INT DEFAULT 0;
itzmebibin
  • 9,199
  • 8
  • 48
  • 62
2

If you are learning it's helpful to use a GUI like SQLyog, make the changes using the program and then see the History tab for the DDL statements that made those changes.

Steen Schütt
  • 1,355
  • 1
  • 17
  • 31
Max Toro
  • 28,282
  • 11
  • 76
  • 114
2

Another useful keyword is FIRST and AFTER if you want to add it in a specific spot in your table. 

ALTER TABLE `table1` ADD COLUMN `foo` AFTER `bar` INT DEFAULT 0;
Roman Rabinovich
  • 868
  • 6
  • 13
1

Try This :)

ALTER TABLE TABLE_NAME ADD COLUMN_NAME INT NOT NULL DEFAULT 0;
MAnoj Sarnaik
  • 1,592
  • 16
  • 16