MySQL DEFAULT() Function Explained

In MySQL, the DEFAULT() function returns the default value for a given column in the database.

If the column doesn’t have a default value, an error is returned.

Syntax

The syntax goes like this:

DEFAULT(col_name)

Where col_name is the name of the database column for which to return the default value.

Example

Here’s an example to demonstrate how the function works.

Suppose we create a table like this:

CREATE TABLE guest_meals (
  guest_id INT NOT NULL,
  meal VARCHAR(255) DEFAULT 'Salad',
  special_request VARCHAR(255),
  PRIMARY KEY (guest_id)
);

Here, we set a default value for the meal column. Specifically, we used DEFAULT 'Salad' to set the default meal to Salad.

This uses the DEFAULT clause of the CREATE TABLE statement. While related to our purpose here (it sets the default value for a column), it’s not to be confused with the DEFAULT() function that this article is about.

Now let’s insert some rows:

INSERT INTO guest_meals (guest_id) VALUES (1);
INSERT INTO guest_meals (guest_id, meal) VALUES (2, 'Fish');
INSERT INTO guest_meals (guest_id, meal) VALUES (3, 'Burrito');
INSERT INTO guest_meals (guest_id, meal, special_request) VALUES (4, 'Pasta', 'Hot');
INSERT INTO guest_meals (guest_id, special_request) VALUES (5, 'Vegan');

Some of these entries explicitly state which meal the guest wants while others don’t. The ones that don’t will just use the default meal (Salad).

So now let’s select those rows from the table. We will include the DEFAULT() function in our selection:

SELECT 
    guest_id,
    DEFAULT(meal),
    meal,
    DEFAULT(special_request),
    special_request
FROM guest_meals;

Result:

+----------+---------------+---------+--------------------------+-----------------+
| guest_id | DEFAULT(meal) | meal    | DEFAULT(special_request) | special_request |
+----------+---------------+---------+--------------------------+-----------------+
|        1 | Salad         | Salad   | NULL                     | NULL            |
|        2 | Salad         | Fish    | NULL                     | NULL            |
|        3 | Salad         | Burrito | NULL                     | NULL            |
|        4 | Salad         | Pasta   | NULL                     | Hot             |
|        5 | Salad         | Salad   | NULL                     | Vegan           |
+----------+---------------+---------+--------------------------+-----------------+

The DEFAULT(meal) column returns the default values, and the meal column returns the actual values that were inserted.

Similarly, the DEFAULT(special_request) column returns the default values for that column, and the special_request column returns the actual values that were inserted.

Regarding the special_request column, we didn’t actually set a default value for this column and so its default value is NULL. Note that this is only possible on columns that can contain NULL values. On nullable columns that don’t contain an explicit DEFAULT value, MySQL defines the column with an explicit DEFAULT NULL clause.

We can verify this with the SHOW CREATE TABLE statement:

SHOW CREATE TABLE guest_meals;

Result:

+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| guest_meals | CREATE TABLE `guest_meals` (
  `guest_id` int NOT NULL,
  `meal` varchar(255) DEFAULT 'Salad',
  `special_request` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`guest_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

We can see that the special_request column has an explicit DEFAULT NULL clause, even though we didn’t specify that when we created the table. This explicit DEFAULT NULL clause was put there by MySQL.

However, if we’d defined the column as NOT NULL, then it wouldn’t have an explicit DEFAULT clause, and we’d get an error when trying to retrieve its (non-existent) default value. More on this below.

Explicitly Inserting the Default Value

We can use the DEFAULT keyword to explicitly insert the default value into a column.

Here’s an example:

INSERT INTO guest_meals (guest_id, meal, special_request) 
VALUES (6, DEFAULT, DEFAULT);

Now let’s select that row:

SELECT 
    guest_id,
    DEFAULT(meal),
    meal,
    DEFAULT(special_request),
    special_request
FROM guest_meals
WHERE guest_id = 6;

Result:

+----------+---------------+-------+--------------------------+-----------------+
| guest_id | DEFAULT(meal) | meal  | DEFAULT(special_request) | special_request |
+----------+---------------+-------+--------------------------+-----------------+
|        6 | Salad         | Salad | NULL                     | NULL            |
+----------+---------------+-------+--------------------------+-----------------+

In both cases, it inserted the default value for the respective column. For the meal column, this was Salad. For the special_request column, this was NULL.

Non-Nullable Columns

As mentioned, using the DEFAULT() function to get the default value from a NOT NULL column that doesn’t have a default value results in an error.

Example:

SELECT DEFAULT(guest_id)
FROM guest_meals;

Result:

ERROR 1364 (HY000): Field 'guest_id' doesn't have a default value

When we created the table, we didn’t provide a default value for the guest_id column, and we also set it to NOT NULL. Because of this, MySQL didn’t set an explicit DEFAULT clause against the table (like it did with the nullable column), and so we get an error when trying to return its (non-existent) default value.

Integer Columns using `AUTO_INCREMENT`

For integer columns using AUTO_INCREMENT, the return value is 0.

As an example, let’s create a table with an AUTO_INCREMENT column:

CREATE TABLE guest (
  guest_id INT NOT NULL AUTO_INCREMENT,
  guest_name VARCHAR(255) NOT NULL,
  PRIMARY KEY (guest_id)
);

The guest_id column uses an INT (integer) data type, and it’s set to AUTO_INCREMENT.

Now insert some rows:

INSERT INTO guest (guest_name) VALUES ('Homer');
INSERT INTO guest (guest_name) VALUES ('Bart');
INSERT INTO guest (guest_name) VALUES ('Marge');

Now, let’s return the default value of the guest_id column:

SELECT 
    DEFAULT(guest_id),
    guest_id
FROM guest;

Result:

+-------------------+----------+
| DEFAULT(guest_id) | guest_id |
+-------------------+----------+
|                 0 |        1 |
|                 0 |        2 |
|                 0 |        3 |
+-------------------+----------+

The DEFAULT(guest_id) column shows that the default value is 0 for all rows. The guest_id column shows the actual value that was inserted (which is was generated by AUTO_INCREMENT).

Syntax

Example

Explicitly Inserting the Default Value

Non-Nullable Columns

Integer Columns using AUTO_INCREMENT

Integer Columns using `AUTO_INCREMENT`