MySQL DEFAULT() Function

In MySQL, the DEFAULT() function returns the default value for the specified column.

MySQL DEFAULT() function can only return default value ​​for columns whose default values are defined as a literal value, not for columns whose default values are expressions.

DEFAULT() Syntax

Here is the syntax of the MySQL DEFAULT() function:

DEFAULT(column_name)

Parameters

column_name
Required. A column name.

Return value

MySQL DEFAULT() function returns the default value for the specified column. If no default value is defined for the specified column, an error will be thrown.

DEFAULT() Examples

To demonstrate the usage of the DEFAULT() function , let’s create a table named test_default using the following statement:

CREATE TABLE test_default (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    default_int INT NOT NULL DEFAULT 0,
    default_date DATE NOT NULL DEFAULT (CURRENT_DATE)
);

Here we created a table with 4 columns and the following 2 columns defined a default value:

  • default_int column has a default value 0.
  • default_date column has a default value that will be generated by an expression (CURRENT_DATE).

Then, let’s insert a row into the table:

INSERT INTO test_default (name, default_int)
VALUES ('Tom', 1);

Let’s look at the rows in the table:

SELECT * FROM test_default;

+----+------+-------------+--------------+
| id | name | default_int | default_date |
+----+------+-------------+--------------+
|  1 | Tom  |           1 | 2022-05-09   |
+----+------+-------------+--------------+

Now, let’s use the DEFAULT() function to get the default value of the column default_int:

SELECT DEFAULT(default_int) FROM test_default;

+----------------------+
| DEFAULT(default_int) |
+----------------------+
|                    0 |
+----------------------+

Let’s take a look at the default value of the column default_date using the DEFAULT() function:

SELECT DEFAULT(default_date) FROM test_default;

When we ran this statement, MySQL returned an error: “ERROR 3773 (HY000): DEFAULT function cannot be used with default value expressions”.

This is because the DEFAULT() function can only return the default value of a column whose default value is defined as a literal value.