A brief discussion on several situations where MySQL returns Boolean types

mysql returns Boolean type

insert image description here

In the first case, return directly

select id='22aa' from mytest where age=202 returns 1 which can be encapsulated as true
select count(*)=1 from mytest where age=202 returns 1 which can be encapsulated as true
select count(*)=0 from mytest where age=202 returns 0 and can be encapsulated as false
select count(*)<3 from mytest where age=202 returns 1 which can be encapsulated as true
select count(*)<=1 from mytest where age=202 returns 1 which can be encapsulated as true
select name="aa" from mytest where age=10 When name is null, sql will not report an error, and the returned result is also null. Refer to the sql 3 code in the second case and an error will be reported.

Summarize:

This situation is similar to the judgment statement in Java. It’s just that in Java, = means assignment, so == is used for judgment, while in MySQL, set is used for assignment and = is used directly for judgment.

In the second case, returning 0 or 1 can also achieve the purpose

select enable from mytest where age=202 returns 1 which can be packaged as true
select count(*) from mytest returns 4 which can be encapsulated as Boolean type, but is false
select enable from mytest where age=201 returns null. Cannot be encapsulated as Boolean type. The code will report an error directly. select id from mytest where age=202 returns '22aa'. Can be encapsulated as Boolean type, but it is false.
select id from mytest where age=202 returns 'true' Can be encapsulated as Boolean type, but is true
select id from mytest where age=202 returns 'false' which can be encapsulated as Boolean type, false
//Special case select * from mytest Error Expected one result (or null) to be returned by selectOne(), but found: 4
select * from mytest where age=202 returns a set of data false 2019-08-28 202 15 1 , which can be encapsulated as false
select * from mytest where age=202 returns a set of data true 2019-08-28 202 15 1 , which can be encapsulated as true
select * from mytest where age=202 returns a set of data aaaa2019-08-28 202 15 1 , which can be encapsulated as false

Summarize:

Mybatis converts based on the number of records queried (1=true, 0=false)

Points to note: If multiple records (greater than 1) are found, but false is returned, this is exactly the opposite of what we expect. Here, you can use other methods to make judgments by returning the number of records, or you can ensure that the records are unique in the database. You can also directly use the first case to solve it.

According to the test of SQL statements 4, 5, and 6, if the string is "true", it can be encapsulated as true, if it is "false", it can be encapsulated as false, and the strings of other situations are all false.

(This is just a guess, not accurate. You need to check on the MySQL official website. If the returned field is a string, what rules are used to convert it to Boolean? I guess it is similar to the string-to-Boolean method in Java: Boolean.valueOf("aaa") //false, the method is as follows)

insert image description here

insert image description here As for the situation where SQL statements 8, 9, and 10 return a group but only one piece of data is accepted, why the id value is used for encapsulation remains to be studied further.

MySQL Boolean Type Pitfalls

In MySQL, Boolean is just an alias for tinyint(1), which means that there is no real bool type in MySQL. However, SQLAlchemy did not detect this when generating SQL, which led to a problem. When the bool type is used as a query condition, the index cannot be used, resulting in table scanning:

> SELECT COUNT(*) FROM message WHERE message.is_national = 1 AND message.updated_at > '2020-01-01 00:00:00' AND message.deleted_at IS NULL;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set
Time: 0.018s
> SELECT COUNT(*) FROM message WHERE message.is_national is true AND message.updated_at > '2020-01-01 00:00:00' AND message.deleted_at IS NULL;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set
Time: 2.162s

Pay attention to the time of the first and second rows. It is obvious that the second row does not use the index. Let's take a look at the results of EXPLAIN to see the answer:

> EXPLAIN SELECT COUNT(*) FROM message WHERE message.is_national = 1 AND message.updated_at > '2020-01-01 00:00:00' AND message.de
        leted_at IS NULL;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | message | ref | ix_message_updated_at,idx_updated_at_is_national,ix_message_is_national | ix_message_is_national | 1 | const | 1 | Using where |

> EXPLAIN SELECT COUNT(*) FROM message WHERE message.is_national is true AND message.updated_at > '2020-01-01 00:00:00' AND messageag
        e.deleted_at IS NULL;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | message | ALL | ix_message_updated_at,idx_updated_at_is_national | <null> | <null> | <null> | a very large number | Using what
re |

To this, I just want to say, it’s a rip-off!

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.

You may also be interested in:

Mysql sets boolean type operations
Mybatis connects to MySQL database Tinyint is a boolean type detailed explanation
How to store false or true in MySQL

<<: CSS3 solution to the problem of freezing on mobile devices (animation performance optimization)

>>: User experience of portal website redesign