CODEWITHSUNDEEP
mysql
Strawberry
Strawberry

Reputation: 33945

Unexpected behaviour INT NOT NULL DEFAULT -1

I rarely work with negative numbers (except personal finances) so perhaps that's why there's a gap in my knowledge here...

Consider the following, prompted by a response to a question asked by another user in SO (How to achieve default value if column value is NULL?):

-- Mysql Version 5.5.16
-- sql_mode = ''

DROP TABLE prices;

CREATE TABLE prices (price_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,price INT SIGNED NOT NULL DEFAULT -1);

INSERT INTO prices (price) VALUES (' '),(''),(NULL);
INSERT INTO prices (price_id) VALUES (NULL);

SELECT * FROM prices;

Expected output:
+----------+-------+
| price_id | price |
+----------+-------+
|        1 |    -1 | 
|        2 |    -1 | 
|        3 |    -1 |
|        4 |    -1 |
+----------+-------+

Actual output:
+----------+-------+
| price_id | price |
+----------+-------+
|        1 |     0 |
|        2 |     0 |
|        3 |     0 |
|        4 |    -1 |
+----------+-------+

Why?

Intermediate answer: In a nutshell, it seems that if you want to be sure of inserting the default value (when sql_mode is not set), either omit the column from the INSERT or explicitly INSERT a DEFAULT value, i.e.: INSERT INTO prices (price) VALUES(DEFAULT); To me, this goes against the spirit of a DEFAULT value !?!?

Upvotes: 1

Views: 979

Answers (2)

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

If you use Mysql STRICT MODE then the result could be different.

Currently you are providing a value NULL, the server tries to map this value to the closest INT value. The server is not using the default value of -1 because it is taking NULL as a valid value.

Upvotes: 1

Alfabravo
Alfabravo

Reputation: 7569

Seems like:

a.) If you provide a NULL value to a not null numeric field (not autoincrementing), the default is zero.

b.) If you dont provide a value (as in the last row), you use the given default value (-1)

http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html

Upvotes: 1

Related Questions