1

From this question comments,

In the documentation (dev.mysql.com/doc/refman/5.7/en/insert.html), inserting with INSERT INTO tbl_name () VALUES(); is mentioned. It is allowed except for what the doc says : "If strict mode is not enabled, MySQL uses the implicit default value for any column that has no explicitly defined default. If strict mode is enabled, an error occurs if any column has no default value."

We were then wondering what that actually means because with strict mode,

CREATE TABLE foo ( x varchar(255) );
INSERT INTO foo VALUES (x); 

is still permitted, so it seems like strict mode fails. Moreover, the CREATE TABLE implicitly creates a DEFAULT null just as if it was created explicitly.

2

An "Implicit Default Value" seems to be in reference to the type; the "Explicit Default Value" or simply "Default Value" seems to be in reference to the column's DEFAULT,

Also from the docs,

Inserting NULL into a column that has been declared NOT NULL. For multiple-row INSERT statements or INSERT INTO ... SELECT statements, the column is set to the implicit default value for the column data type. This is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types. INSERT INTO ... SELECT statements are handled the same way as multiple-row inserts because the server does not examine the result set from the SELECT to see whether it returns a single row. (For a single-row INSERT, no warning occurs when NULL is inserted into a NOT NULL column. Instead, the statement fails with an error.)

you can see this documented here in "11.7 Data Type Default Values"

Running through a quick check,

SET sql_mode='';
CREATE TABLE foo ( a int NOT NULL );
-- all of these insert the value 0
INSERT INTO foo (a) VALUES (DEFAULT);
INSERT INTO foo (a) VALUES (a);
INSERT INTO foo () VALUES ();

Whipe it and set strict mode,

TRUNCATE TABLE foo;
SET sql_mode='strict_all_tables';

Retry,

INSERT INTO foo (a) VALUES (DEFAULT);
ERROR 1364 (HY000): Field 'a' doesn't have a default value

-- still inserts "implicit default", doesn't gaf about strict mode.
INSERT INTO foo (a) VALUES (a);
Query OK, 1 row affected (0.01 sec)

INSERT INTO foo () VALUES ();
ERROR 1364 (HY000): Field 'a' doesn't have a default value

You'll see now only one row in the table which is a result of the goofy syntax here that is still permitted with "strict mode".

| improve this answer | |
  • 1
    Don't you just love "Goofy syntax ???" – RolandoMySQLDBA Dec 28 '17 at 21:22
  • @RolandoMySQLDBA I actually do =) sometimes.. RDBMs have to have some originality and swagger. – Evan Carroll Dec 28 '17 at 21:24
  • A WTF upvote for the "Goofy syntax". – Rick James Dec 29 '17 at 22:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.