MySQL DEFAULT

Last updated: October 19, 2026Oct 19, 2026 4 min read

A DEFAULT value is what MySQL stores in a column when an INSERT doesn't provide one. Defaults keep inserts short and data consistent: every new ticket starts as open, every new row gets a creation time, every counter starts at 0.

1. Defining Default Values

Figure 1: a default supplies a value whenever an INSERT doesn't.

defaults.sql

CREATE TABLE tickets (
  ticket_id   INT AUTO_INCREMENT PRIMARY KEY,
  subject     VARCHAR(80) NOT NULL,
  status      VARCHAR(12) NOT NULL DEFAULT 'open',
  priority    TINYINT     NOT NULL DEFAULT 3,
  reopened    BOOLEAN     NOT NULL DEFAULT FALSE,
  created_at  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP   NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  assignee    VARCHAR(30)
);
INSERT INTO tickets (subject) VALUES ('Cannot log in'), ('Invoice is wrong');
SELECT ticket_id, subject, status, priority, reopened, assignee,
       created_at IS NOT NULL AS has_created_at, updated_at
FROM tickets;

Result: 2 rows

ticket_id	subject	status	priority	reopened	assignee	has_created_at	updated_at
1	Cannot log in	open	3	0	NULL	1	NULL
2	Invoice is wrong	open	3	0	NULL	1	NULL

Only subject was supplied; every other column got its default (or NULL for assignee, which has none).

2. The DEFAULT Keyword in INSERT and UPDATE

default-keyword.sql

INSERT INTO tickets (subject, priority) VALUES ('Refund delayed', DEFAULT);
UPDATE tickets SET priority = 1 WHERE ticket_id = 1;
UPDATE tickets SET priority = DEFAULT WHERE ticket_id = 1;   -- back to 3
SELECT ticket_id, priority FROM tickets;

Result: 3 rows

ticket_id	priority
1	3
2	3
3	3

Inserting an explicit NULL doesn't trigger the default. Into a NOT NULL column it is an error:

query.sql

INSERT INTO tickets (subject, status) VALUES ('Spam', NULL);

Output

ERROR 1048 (23000): Column 'status' cannot be null

3. Automatic Timestamps

ON UPDATE CURRENT_TIMESTAMP sets updated_at whenever any column of the row really changes:

on-update.sql

UPDATE tickets SET status = 'resolved' WHERE ticket_id = 2;
SELECT ticket_id, status, updated_at IS NOT NULL AS was_updated FROM tickets;

Result: 3 rows

ticket_id	status	was_updated
1	open	1
2	resolved	1
3	open	0

Both TIMESTAMP and DATETIME columns support DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP, with optional fractional seconds such as DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3).

4. Expression Defaults (MySQL 8.0.13+)

Wrap an expression in parentheses to use it as a default. This works even for JSON, TEXT and BLOB columns, which can't have literal defaults:

expression-defaults.sql

CREATE TABLE sla (
  ticket_id   INT PRIMARY KEY,
  opened_on   DATE NOT NULL DEFAULT (CURRENT_DATE),
  due_on      DATE NOT NULL DEFAULT (CURRENT_DATE + INTERVAL 3 DAY),
  ref_code    CHAR(36) NOT NULL DEFAULT (UUID()),
  tags        JSON NOT NULL DEFAULT (JSON_ARRAY()),
  notes       TEXT DEFAULT ('none yet')
);
INSERT INTO sla (ticket_id) VALUES (1);
SELECT DATEDIFF(due_on, opened_on) AS days_allowed, CHAR_LENGTH(ref_code) AS uuid_length, tags, notes FROM sla;

Result: 1 row

days_allowed	uuid_length	tags	notes
3	36	[]	none yet

5. Implicit Defaults

If a column has no DEFAULT clause, MySQL treats it as follows:

Column	Left out of INSERT
Nullable (no NOT NULL)	NULL
NOT NULL, strict mode (default)	Error 1364: Field doesn't have a default value
NOT NULL, non-strict mode	The type's implicit default: 0, '' or the zero date, with a warning
AUTO_INCREMENT	The next sequence number

no-default.sql

CREATE TABLE notes (id INT PRIMARY KEY, body VARCHAR(100) NOT NULL);
INSERT INTO notes (id) VALUES (1);

Output

Query OK, 0 rows affected
ERROR 1364 (HY000): Field 'body' doesn't have a default value

6. Changing or Removing a Default

set-default.sql

ALTER TABLE tickets ALTER COLUMN priority SET DEFAULT 2;
INSERT INTO tickets (subject) VALUES ('Feature request');
SELECT ticket_id, priority FROM tickets WHERE subject = 'Feature request';

Result: 1 row

ticket_id	priority
4	2

drop-default.sql

ALTER TABLE tickets ALTER COLUMN priority DROP DEFAULT;
INSERT INTO tickets (subject) VALUES ('Another one');

Output

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
ERROR 1364 (HY000): Field 'priority' doesn't have a default value

ALTER COLUMN … SET/DROP DEFAULT only changes metadata, so it is instant. Changing a default never alters existing rows; it applies to future inserts.

7. Seeing a Column's Default

query.sql

SELECT column_name, column_default, extra
FROM information_schema.columns
WHERE table_schema = 'support' AND table_name = 'tickets'
ORDER BY ordinal_position;

Result: 8 rows

COLUMN_NAME	COLUMN_DEFAULT	EXTRA
ticket_id	NULL	auto_increment
subject	NULL
status	open
priority	NULL
reopened	0
created_at	CURRENT_TIMESTAMP	DEFAULT_GENERATED
updated_at	NULL	on update CURRENT_TIMESTAMP
assignee	NULL

8. Summary

DEFAULT value fills a column when an INSERT leaves it out (or writes DEFAULT).
CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP give automatic created/updated times.
Expression defaults in parentheses (8.0.13+) allow dates, UUIDs and JSON/TEXT defaults.
Explicit NULL bypasses the default; without a default, strict mode rejects missing NOT NULL values.
Change with ALTER COLUMN … SET DEFAULT, remove with DROP DEFAULT.

Was this tutorial helpful?

Help Us Improve

MySQL DEFAULT

1. Defining Default Values

2. The DEFAULT Keyword in INSERT and UPDATE

3. Automatic Timestamps

4. Expression Defaults (MySQL 8.0.13+)

5. Implicit Defaults

6. Changing or Removing a Default

7. Seeing a Column's Default

8. Summary