Wikipedia:Reference desk/Archives/Computing/2016 October 22

Welcome to the Wikipedia Computing Reference Desk Archives
The page you are currently viewing is an archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages.

October 22

Force strict integer comparison in MYSQL

I have a query like this:

SELECT * FROM my_table WHERE my_field = 95

The type of my_field is INT. Now oddly, that query returns a positive result for all my_field's whose values start with the digits "95" (like 9562, 950, etc). I really don't understand why in the world it would behave like that, but whatever. I just need the comparison to be strict. I tried googling for a solution but I guess I'm using the wrong search terms. Any ideas?98.20.57.207 (talk) 21:38, 22 October 2016 (UTC)

That truly is bizarre behavior, more like what I would expect if you said "WHERE my_field like '95*' " if my_field was a string. But, you could try "WHERE my_field > 94 AND my_field < 96". StuRat (talk) 22:07, 22 October 2016 (UTC)

I tried this minimal example:

CREATE DATABASE foo;
USE foo;

CREATE TABLE my_table (name VARCHAR(40), my_field INT);
INSERT INTO my_table VALUES('a', 9);
INSERT INTO my_table VALUES('b', 95);
INSERT INTO my_table VALUES('c', 951);
INSERT INTO my_table VALUES('d', 9512);

SELECT 'whole table' AS '';
SELECT * from my_table;

SELECT 'just the 95s' AS '';
SELECT * FROM my_table WHERE my_field = 95;

DROP DATABASE foo;

When I run that on MySQL 5.7.15 I get:

   whole table
   name	my_field
   a	9
   b	95
   c	951
   d	9512
   
   just the 95s
   name	my_field
   b	95

which is sensible, and isn't consistent with what you're seeing. So MySQL works, by default, as one might expect. -- Finlay McWalter··–·Talk 22:40, 22 October 2016 (UTC)

Thanks for all the help guys. I finally figured out what the problem was - PEBKAC! Funniest part is that I was using a function I wrote myself called "query" which just so happened to be preceded by the very bold comment:

// IMPORTANT: Not for use with SELECT statements (for those, use the found(), table(), row(), and field() functions)

So there you have it. Yeah, might want to rename that one "non_select_query" or something, I guess...98.20.57.207 (talk) 01:05, 23 October 2016 (UTC)