How to Add a Default Value to an Existing Column in MySQL

Introduction

In this quick tutorial, you will learn how to add a default value to an existing column in MySQL.

Prerequisites

You always need to be careful, when changing the structure of an existing column in your database. Make sure to always have backups of your database before making such changes.

For MySQL, you could use the following script to generate a backup of your database:

Add a default value to an existing

When making changes to a column in your database, you can use the ALTER TABLE statement followed by the ALTER COLUMN statement.

The syntax is the following:

ALTER TABLE name_of_the_table ALTER name_of_the_column SET DEFAULT your_new_default_value;

Let's say that we had a table called users and a column called is_admin. We want all new users to have the is_admin value set to 0. To do so we would use the following query:

ALTER TABLE users ALTER COLUMN is_admin SET DEFAULT 0;

Now if you were to use the DESCRIBE users; statement you will get the following output:

describe users;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
...
| is_admin | int          | NO   |     | 0       |                |
...
+----------+--------------+------+-----+---------+----------------+

Add a default value to a new column

In case that you wanted to actually create a new table with a default value, you would again use the ALTER TABLE statement followed by the ADD keyword and the name of the column that you wanted to create.

Syntax:

ALTER TABLE `name_of_the_table` ADD `name_of_the_column` INT NOT NULL DEFAULT your_new_default_value;

Let's say that you did not yet have the is_admin column form the example above and wanted to create it with a default value of 0, the query would look like this:

ALTER TABLE `users` ADD `is_admin` INT NOT NULL DEFAULT 0;

Now if you were to create a new record in the users table, it would have a default value without you having to specify it.

Conclusion

This is pretty much it! Now you know how to add a default value to both an existing column and to a new column in MySQL.

To learn more about SQL, make sure to check out this free eBook here:

If you already know the basics and wanted to take your data infrastructure to the next level, I would recommend checking out Materialize!

Materialize is a Streaming Database for Real-time Analytics. Materialize is a reactive database that delivers incremental view updates and it helps developers easily build with streaming data using standard SQL.