What is the difference between IS TRUE and =True query conditions in MySQL?

Detailed explanation of the differences between MySQL IS TRUE and = TRUE query conditions

When processing Boolean values ​​(BOOLEAN), there may be differences in the query results of IS TRUE and = TRUE . This is mainly due to the different ways in which MySQL handles boolean values ​​and numeric values. Let's explain this difference with an example.

Suppose there is a table named user that contains a field named is_deleted , of type tinyint(1) . The table data is as follows:

id	name	is_deleted
1	Alice	0
2	bob	1
3	carol	127

IS TRUE query:

When the following query is executed:

 SELECT * FROM `user` WHERE is_deleted IS TRUE;

The result contains records with is_deleted values ​​1 and 127:

id	name	is_deleted
2	bob	1
3	carol	127

IS TRUE determines whether Boolean is true or false. In MySQL, any non-zero value is considered true.

= TRUE query:

Execute the following query:

 SELECT * FROM `user` WHERE is_deleted = TRUE;

The result only contains records with is_deleted value of 1:

id	name	is_deleted
2	bob	1

= TRUE performs a numerical comparison, and TRUE is regarded as a numerical value of 1 by MySQL.

Summarize:

In MySQL, IS TRUE and = TRUE behave differently when processing tinyint(1) type boolean fields:

• IS TRUE : Treat any non-zero value as true (TRUE).
• = TRUE : Only value 1 is considered true (TRUE).

Therefore, which query method to choose depends on your needs. If you want to treat all nonzero values ​​as true, use IS TRUE ; if you want to treat the value 1 as true, use = TRUE . It should be noted that to avoid ambiguity, it is recommended to use an explicit numerical comparison ( is_deleted = 1 ) to represent the Boolean TRUE instead of using = TRUE .

The above is the detailed content of What is the difference between IS TRUE and =True query conditions in MySQL?. For more information, please follow other related articles on the PHP Chinese website!