Can 'false' match some string in mysql?

Question

I have a table like this:

CREATE TABLE IF NOT EXISTS `session` ( `id` int(11) NOT NULL AUTO_INCREMENT, `token` varchar(32) NOT NULL, `profile` varchar(1000) NOT NULL, `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=41 ; 

and some data in this table:

 (38, '395d5feaf28df01aafe0781a7f34acbe', 'a:3:{s:2:"id";s:1:"2";s:8:"username";s:7:"wanmeng";s:12:"created_time";s:19:"2011-11-18 19:37:33";}', '2011-12-03 14:14:35'), (39, '0e0ca06ed9ad86937f190eb9544ac935', 'a:3:{s:2:"id";s:1:"1";s:8:"username";s:6:"delphi";s:12:"created_time";s:19:"2011-11-18 13:29:40";}', '2011-12-03 14:28:36'), (31, '3cba76b97cf123009632bdaa5a306385', 'a:3:{s:2:"id";s:1:"1";s:8:"username";s:6:"delphi";s:12:"created_time";s:19:"2011-11-18 13:29:40";}', '2011-12-02 15:50:21'), (30, 'fa356333dd3ee8f1b18b8bf0a827e34c', 'a:3:{s:2:"id";s:1:"1";s:8:"username";s:6:"delphi";s:12:"created_time";s:19:"2011-11-18 13:29:40";}', '2011-12-01 15:32:47') 

When I execute the query: SELECT * FROM session WHERE token = false, I expect the result to not be returned, but mysql returns the results:

 39 0e0ca06ed9ad86937f190eb9544ac935 a:3:{s:2:"id";s:1:"1";s:8:"username";s:6:"delphi";... 2011-12-03 22:28:36 30 fa356333dd3ee8f1b18b8bf0a827e34c a:3:{s:2:"id";s:1:"1";s:8:"username";s:6:"delphi";... 2011-12-01 23:32:47 

It seems that the boolean value "false" may correspond to some varchar, but is there any connection between "fa356333dd3ee8f1b18b8bf0a827e34c" and "false", why is this so?

Answer 1

In MYSQL, FALSE is not a boolean; it is an integer, more precisely zero. MySQL actually has no logical column types (it has BOOL and BOOLEAN , but they are simple aliases for TINYINT ). So your request is synonymous with:

 SELECT * FROM session WHERE token = 0 

Since token is a VARCHAR, MySQL must convert your strings to a number. Run this query and you will get an idea of ​​the rules:

 SELECT 0 + "0001", 0 + "123abc", 0 + "abc123" 

As a result, fa356333dd3ee8f1b18b8bf0a827e34c goes to 0 because it starts with a letter, thus a match.

Answer 2

Is there a reason why you are not testing token is null or token like '' ? Comparing varchar with false is a bad idea, regardless of whether the database will catch your error or not ... varchar is never false, and false is not zero.

Answer 3

If you want to compare the boolean with varchar, MySQL must do an implicit type conversion. MySQL passes the varchar value to a boolean, checking only the first character.

If the character is a character or a number 0, then the string is evaluated to 0, thus False.
If the character is a number other than 0, then the string is calculated equal to 1, therefore True (and does not appear in your result set)

The only thing to do is not to compare the string with the boolean, because it does not make any sense.

Answer 4

It should be a comment, but I don’t have enough reputation ... so ..

DelphiQin, you said in a comment that you pass the result of the CI method "get ()" to the request parameter. I was there, and I decided by simply translating the variable into a string before moving on to the query:

 (string)$my_var 

This way it will use '' (empty string) instead of boolean false.

I hope this helps people with the same problem.