- Does bool value false == 0 and true == 1
- Does Null value in varchar, int, date fields == 0?
- When by default in mysql values == 0 or NULL or empty string?
views:
70answers:
3Internally in MySQL there are no bool values. Expressions that appear to return booleans actually return an integer 0 or 1:
SELECT (3 < 5)
1
SELECT (3 > 5)
0
SELECT (3 < 4) + (4 < 5)
2
SELECT TRUE
1
TRUE and FALSE are just aliases for 1 and 0.
NULL does not equal any value, not even another NULL.
SELECT NULL = ''
NULL
SELECT NULL = NULL
NULL
If you want to compare with NULL use IS NULL
.
Update: There was some confusion over whether MySQL supports booleans. This example shows that even a BOOL type on a column is still just an integer:
CREATE TABLE table1 (id INT, x BOOL); INSERT INTO table1 VALUES (1, TRUE), (2, FALSE), (3, 3); -- Can you insert 3 into a BOOL type? Yep! SELECT * FROM TABLE1;
Results:
id x
1 1
2 0
3 3
In SQL NULL represents the the absence of a value. In MySQL an explicit NULL may also represent the next value of a pseudo-sequence and an implicit NULL may represent an implicit default value (a zero or empty string) determined by MySQL.
Question: Does bool value false == 0 and true == 1
Yes - you can test using:
SELECT IF(0, 'true', 'false')
...it will return "false", meaning zero indicates false and one indicates true. This example was taken from the MySQL documentation page.
Question: Does Null value in varchar, int, date fields == 0?
No, NULL indicates the lack of any value at all.
You need to use special operators if you want to compare for the value being NULL.
Question: When by default in mysql values == 0 or NULL or empty string?
Without default constraints in place, columns that allow NULL will contain NULL if no value has been provided -- regardless of data type (DATETIME, numeric, text, etc). Columns that do not allow NULL will return an error until a value is provided that matches the data type of the column. Default constraints allow you to specify a default value if someone attempts to insert a NULL value into the column, or the column has been omitted from an INSERT statement.