mysql – Adding a new SQL column with a default value

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

Reference

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.

Like this?

ALTER TABLE `tablename` ADD `new_col_name` INT NOT NULL DEFAULT 0;

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

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

alter table users add column verified tinyint unsigned default 0

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`;

You can try this,

ALTER TABLE table_name ADD column_name INT DEFAULT 0;

Try This 🙂

ALTER TABLE TABLE_NAME ADD COLUMN_NAME INT NOT NULL DEFAULT 0;

ALTER TABLE my_table ADD COLUMN new_field TinyInt(1) DEFAULT 0;

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.

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;