0 , NULL, empty string, Default values - MySql Questions

Question

1. Does bool value false == 0 and true == 1
2. Does Null value in varchar, int, date fields == 0?
3. When by default in mysql values == 0 or NULL or empty string?

Answer 1

Internally 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