Adding a new SQL column with a default value
SqlMysqlSql Problem Overview
I am looking for the syntax to add a column to a MySQL database with a default value of 0
Sql Solutions
Solution 1 - Sql
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.
Solution 2 - Sql
Like this?
ALTER TABLE `tablename` ADD `new_col_name` INT NOT NULL DEFAULT 0;
Solution 3 - Sql
table users (user_id int unsigned PK, username varchar(32))
alter table users add column verified tinyint unsigned default 0
Solution 4 - Sql
Simply add default 0
at the end of your ALTER TABLE <table> ADD COLUMN <column> <type>
statement
Solution 5 - Sql
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`;
Solution 6 - Sql
You can try this,
ALTER TABLE table_name ADD column_name INT DEFAULT 0;
Solution 7 - Sql
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.
Solution 8 - Sql
> Try This :)
ALTER TABLE TABLE_NAME ADD COLUMN_NAME INT NOT NULL DEFAULT 0;
Solution 9 - Sql
ALTER TABLE my_table ADD COLUMN new_field TinyInt(1) DEFAULT 0;
Solution 10 - Sql
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;