0, NULL, empty string, default values ​​- MySql questions

Question

• Bool value false == 0 and true == 1
• Null value in varchar, int, date fields == 0?
• When is the default value in mysql == 0 or NULL or an empty string?

Answer 1

Question : Does bool have 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 absence of any value whatsoever.
You need to use special operators if you want to compare the NULL value.

Question : when is the default value in mysql == 0 or NULL or an empty string?

Without default restrictions, columns that allow NULL will contain NULL if no value was provided - regardless of the data type (DATETIME, numeric, text, etc.). Columns that do not allow NULL return an error until a value corresponding to the data type of the column is specified. Default restrictions allow you to specify a default value if someone is trying to insert a NULL value in a column, or if the column was omitted from an INSERT statement.

Answer 2

Internally in MySQL there are no bool values. Expressions that appear to return boolean values ​​actually return an integer of 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 is not equal to 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 about whether MySQL supports a boolean value. This example shows that even the BOOL type in the column is still 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 

Answer 3

In SQL NULL, a missing value is displayed. In MySQL, explicit NULL can also represent the following pseudo-sequence value, and implicit NULL can be the implicit default value (zero or empty string) defined by MySQL.