The DEFAULT and the DEFAULT function returns a default value defined for a column. If a default value is not specified for the column, NULL or an error is output. DEFAULT has no parameter, however, the DEFAULT function uses the column name as the input parameter. DEFAULT can be used for the input data of the INSERT statement and the SET clause of the UPDATE statement and the DEFAULT function can be used anywhere.
If any of constraints is not defined or the UNIQUE constraint is defined for the column where a default value is not defined, NULL is returned. If NOT NULL or PRIMARY KEY constraint is defined, an error is returned.
CREATE TABLE info_tbl(id INT DEFAULT 0, name VARCHAR)
INSERT INTO info_tbl VALUES (1,'a'),(2,'b'),(NULL,'c');
3 rows affected.
SELECT id, DEFAULT(id) FROM info_tbl;
UPDATE info_tbl SET id = DEFAULT WHERE id IS NULL;
DELETE FROM info_tbl WHERE id = DEFAULT(id);
INSERT INTO info_tbl VALUES (DEFAULT,'d');