Geek Answers Handbook

MySQL column = 0 returns true

TL / DR: I run this query = "Select * from test where id = 0"and return all rows.

Here is my code below:

 CREATE TABLE IF NOT EXISTS `test` (
   `id` varchar(20) NOT NULL,
   `desc` varchar(100) NOT NULL,
   UNIQUE KEY `id` (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 INSERT INTO `test` (`id`, `desc`) VALUES
 ('AA', 'AA Desc'),
 ('BB', 'BB Desc');

  SELECT count(*) FROM test WHERE id = 0

In my opinion, it should not return rows, but it returns all rows in the table. Am I missing something? Any help and explanation would be most welcome.

+4
source share
4 answers

You are doing arithmetic comparison in a text box. MySQL will force the values ​​in your identifier column to a numeric value and compare it to zero. AA, under duress, is zero.

+6
source

You used the varchar data type and inserted records such as AA,BB

SELECT count(*) FROM test WHERE id = '0'

+1
source 
SELECT COUNT(*) FROM test WHERE id = '0'

, , 0.

+1

You give idvarchar (20) NOT NULL as a row in the table and check as a number. You should check SELECT COUNT(*) FROM test WHERE id = '0'if id is varchar (20) and SELECT COUNT(*) FROM test WHERE id = 0When id is int

-1
source

More articles:


All Articles