CODEWITHSUNDEEP
mysqlsql
Michael Phelps
Michael Phelps

Reputation: 3591

DEFAULT doesn't work in SET data

Help me please

CREATE TABLE RET (anim SET('dog','pig','voon') DEFAULT 'pig');

Insert :

INSERT INTO RET VALUES('root')  //empty string! Why? DEFAULT doesn't work!

Thanks.

Upvotes: 1

Views: 1071

Answers (3)

peterm
peterm

Reputation: 92785

It works as it should. Change your schema like this for example

CREATE TABLE RET (id int, anim SET('dog','pig','voon') DEFAULT 'pig');

And then omit anim column in your insert

INSERT INTO ret (id) VALUES(1);

Or use DEFAULT keyword

INSERT INTO ret (id, anim) VALUES(2, DEFAULT);

Output:

| ID | ANIM |
-------------
|  1 |  pig |
|  2 |  pig |

Here is SQLFiddle demo

Upvotes: 6

Bill Karwin
Bill Karwin

Reputation: 562348

+1 to @peterm's answer, but FWIW here are a couple of other ways you can illustrate the behavior:

INSERT INTO ret (anim) VALUES (DEFAULT);
INSERT INTO ret () VALUES ();

The reason why inserting the value 'root' results in an empty string instead of the default value is that default does not kick in when you specify a value.

The value 'root' isn't one of the valid entries in the definition of the SET, but under default behavior MySQL "truncates" the invalid value(s), and insert an empty set. It generates a warning when it truncates the invalid value, but it does not block the INSERT from happening.

You can set strict mode to disable the automatic truncation. This turns warnings into errors, and the INSERT will fail.

mysql> SET SQL_MODE=STRICT_ALL_TABLES;
mysql> INSERT INTO RET VALUES('root');
ERROR 1265 (01000): Data truncated for column 'anim' at row 1

Upvotes: 3

Joe
Joe

Reputation: 6827

DEFAULT doesn't replace an invalid value, it just defines the default value to use if one is not specified in the insert.

Upvotes: 5

Related Questions