Return the default value for a column. This function retrieves the value assigned to a column if no explicit value is provided during insertion.
DEFAULT(col_name)Returns the default value for a table column. If the column has no default value (and is not NULLABLE - NULLABLE fields have a NULL default), an error is returned.
For integer columns using , 0 is returned.
When using DEFAULT as a value to set in an or statement, you can use the bare keyword DEFAULT without the parentheses and argument to refer to the column in context. You can only use DEFAULT as a bare keyword if you are using it alone without a surrounding expression or function.
Select only non-default values for a column:
Update values to be one greater than the default value:
When referring to the default value exactly in UPDATE or INSERT, you can omit the argument:
This page is licensed: GPLv2, originally from
SELECT i FROM t WHERE i != DEFAULT(i);UPDATE t SET i = DEFAULT(i)+1 WHERE i < 100;INSERT INTO t (i) VALUES (DEFAULT);
UPDATE t SET i = DEFAULT WHERE i < 100;CREATE OR REPLACE TABLE t (
i INT NOT NULL AUTO_INCREMENT,
j INT NOT NULL,
k INT DEFAULT 3,
l INT NOT NULL DEFAULT 4,
m INT,
PRIMARY KEY (i)
);
DESC t;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| i | int(11) | NO | PRI | NULL | auto_increment |
| j | int(11) | NO | | NULL | |
| k | int(11) | YES | | 3 | |
| l | int(11) | NO | | 4 | |
| m | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
INSERT INTO t (j) VALUES (1);
INSERT INTO t (j,m) VALUES (2,2);
INSERT INTO t (j,l,m) VALUES (3,3,3);
SELECT * FROM t;
+---+---+------+---+------+
| i | j | k | l | m |
+---+---+------+---+------+
| 1 | 1 | 3 | 4 | NULL |
| 2 | 2 | 3 | 4 | 2 |
| 3 | 3 | 3 | 3 | 3 |
+---+---+------+---+------+
SELECT DEFAULT(i), DEFAULT(k), DEFAULT (l), DEFAULT(m) FROM t;
+------------+------------+-------------+------------+
| DEFAULT(i) | DEFAULT(k) | DEFAULT (l) | DEFAULT(m) |
+------------+------------+-------------+------------+
| 0 | 3 | 4 | NULL |
| 0 | 3 | 4 | NULL |
| 0 | 3 | 4 | NULL |
+------------+------------+-------------+------------+
SELECT DEFAULT(i), DEFAULT(k), DEFAULT (l), DEFAULT(m), DEFAULT(j) FROM t;
ERROR 1364 (HY000): Field 'j' doesn't have a default value
SELECT * FROM t WHERE i = DEFAULT(i);
Empty set (0.001 sec)
SELECT * FROM t WHERE j = DEFAULT(j);
ERROR 1364 (HY000): Field 'j' doesn't have a default value
SELECT * FROM t WHERE k = DEFAULT(k);
+---+---+------+---+------+
| i | j | k | l | m |
+---+---+------+---+------+
| 1 | 1 | 3 | 4 | NULL |
| 2 | 2 | 3 | 4 | 2 |
| 3 | 3 | 3 | 3 | 3 |
+---+---+------+---+------+
SELECT * FROM t WHERE l = DEFAULT(l);
+---+---+------+---+------+
| i | j | k | l | m |
+---+---+------+---+------+
| 1 | 1 | 3 | 4 | NULL |
| 2 | 2 | 3 | 4 | 2 |
+---+---+------+---+------+
SELECT * FROM t WHERE m = DEFAULT(m);
Empty set (0.001 sec)
SELECT * FROM t WHERE m <=> DEFAULT(m);
+---+---+------+---+------+
| i | j | k | l | m |
+---+---+------+---+------+
| 1 | 1 | 3 | 4 | NULL |
+---+---+------+---+------+