I am looking for the syntax to add a column to a MySQL database with a default value of 0
Add a comment
|
10 Answers
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.
5 Comments
Pseudonym
I was particularly struck by how good this answer is, both concise and thorough, wish I could upvote it again
Clint Eastwood
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
1man
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;
Adithya Sai
"ALTER TABLE table1 ADD COLUMN foo INT DEFAULT 0;" - In this 'COLUMN' keyword is not required
Seth
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.Like this?
ALTER TABLE `tablename` ADD `new_col_name` INT NOT NULL DEFAULT 0;
1 Comment
Cyril Jacquart
if default is >0, add quotes: ALTER TABLE
tablename ADD new_col_name INT NOT NULL DEFAULT '1';table users (user_id int unsigned PK, username varchar(32))
alter table users add column verified tinyint unsigned default 0
Comments
ALTER TABLE my_table ADD COLUMN new_field TinyInt(1) DEFAULT 0;
1 Comment
Nico Haase
Can you explain that further? Why did you need to add a new answer to this question?
Simply add default 0 at the end of your ALTER TABLE <table> ADD COLUMN <column> <type> statement
Comments
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`;
Comments
You can try this,
ALTER TABLE table_name ADD column_name INT DEFAULT 0;
Comments
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;
1 Comment
vladiastudillo
AFTER
bar should be *after INTIf 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.
Comments
Try This :)
ALTER TABLE TABLE_NAME ADD COLUMN_NAME INT NOT NULL DEFAULT 0;
