How do I add a column with a default value in MySQL?

---

You can use the following syntax in MySQL to add a column to a table with a default value:

ALTER TABLE athletes ADD COLUMN rebounds INT DEFAULT 0;

This particular example adds an integer column named rebounds with a default value of 0 to the table named athletes.

The following example shows how to use this syntax in practice.

Example: Add Column with Default Value in MySQL

Suppose we create the following table named athletes that contains information about various basketball players:

-- create table 
CREATE TABLE athletes (
  athleteID INT PRIMARY KEY,
  team TEXT NOT NULL,
  points INT NOT NULL
);

-- insert rows into table
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Warriors', 14);
INSERT INTO athletes VALUES (0003, 'Nuggets', 37);
INSERT INTO athletes VALUES (0004, 'Lakers', 19);
INSERT INTO athletes VALUES (0005, 'Celtics', 26);

-- view all rows in table
SELECT * FROM athletes;

Output:

+-----------+----------+--------+
| athleteID | team     | points |
+-----------+----------+--------+
|         1 | Mavs     |     22 |
|         2 | Warriors |     14 |
|         3 | Nuggets  |     37 |
|         4 | Lakers   |     19 |
|         5 | Celtics  |     26 |
+-----------+----------+--------+

Suppose that we would like to add a new column to the table named rebounds with a default value of 0.

We can use the following syntax to do so:

-- add rebounds column to table with default value of 0
ALTER TABLE athletes ADD COLUMN rebounds INT DEFAULT 0;

-- view all rows in updated table
SELECT * FROM athletes;

Output:

+-----------+----------+--------+----------+
| athleteID | team     | points | rebounds |
+-----------+----------+--------+----------+
|         1 | Mavs     |     22 |        0 |
|         2 | Warriors |     14 |        0 |
|         3 | Nuggets  |     37 |        0 |
|         4 | Lakers   |     19 |        0 |
|         5 | Celtics  |     26 |        0 |
+-----------+----------+--------+----------+

Notice that each of the rows in the new rebounds column contains a value of 0.

You can just as easily add a new character column to the table with a default string value as well.

For example, we can use the following syntax to instead add a new column named conference to the table with a default value of West:

-- add conference column to table with default value of West
ALTER TABLE athletes ADD COLUMN conference VARCHAR(25) DEFAULT 'West';

-- view all rows in updated table
SELECT * FROM athletes;

Output:

+-----------+----------+--------+------------+
| athleteID | team     | points | conference |
+-----------+----------+--------+------------+
|         1 | Mavs     |     22 | West       |
|         2 | Warriors |     14 | West       |
|         3 | Nuggets  |     37 | West       |
|         4 | Lakers   |     19 | West       |
|         5 | Celtics  |     26 | West       |
+-----------+----------+--------+------------+

Notice that each of the rows in the new conference column contains a value of West.

Additional Resources

The following tutorials explain how to perform other common tasks in MySQL: