Adding a new SQL column with a default value

开发者_JAVA百科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;

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

alter table users add column verified tinyint unsigned default 0

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

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

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

You can try this,

ALTER TABLE table_name ADD column_name INT 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;

Try This :)

ALTER TABLE TABLE_NAME ADD COLUMN_NAME INT NOT NULL DEFAULT 0;