0

I have the following table in MySQL:

Category.ID [ INT(1), UNSIGNED, NOT NULL, AUTO_INCREMENT ]
1
2

When I run the following query:

SELECT `ID` FROM `Category` WHERE `ID` = '1A';

It returns:

Category.ID
1

I'm running MySQL Ver 14.14 Distrib 5.7.15, for Linux (x86_64).

Can someone explain why MySQL is returning LIKE results, when = is defined?

Is this something than can be 'turned off' in my.cnf?

Thanks.

C Thompson
  • 100
  • 1
  • 5

2 Answers2

2

This has nothing to do with like, this is how MySQL converts data types while evaluating expressions. And no, this cannot be turned off in the config file.

As the documentation says, MySQL converts both the number 1 and the string 1A to floating point numbers. MySQL converts a string to number by checking the characters one by one from left to right and stops where the 1st character cannot be evaluated as part of a number and returns the characters processed so far as the number. In case of the string 1A, the letter A is the first character that MySQL cannot interpret as number, therefore returns 1 as the numeric value.

What you can do to avoid this:

  1. Convert the numeric value to string using cast() or concat(). In this case you will loose the possibility of an index search on the id field.
  2. Check the input parameter in the application first, and if it's not numeric, then do not do the search at all.
Shadow
  • 33,525
  • 10
  • 51
  • 64
  • Thank you for your answer. However, the following query: `SELECT ID FROM Category WHERE ID = CAST('1A' AS CHAR);` still returns a result of `1`. I've tried numerous queries, everything from CAST to CONVERT to REGEX and I don't think it's possible to construct a query here that returns no results, with a WHERE qualifier of `= '1A'`. – C Thompson Sep 15 '16 at 09:47
  • Because you converted the string literal to string, not the numeric `ID` column to string. I wrote: "Convert the **numeric value** to string... – Shadow Sep 15 '16 at 09:52
  • I figured it out, thank you. `SELECT ID FROM Category WHERE CAST(ID as CHAR) = '1A';` – C Thompson Sep 15 '16 at 09:55
  • And as an aside, yes, I know this is best done on the business-side, before constructing the query. It was an exercise in understanding why MySQL was returning results the way it was. This behavior is not consistent with other RDBMS. – C Thompson Sep 15 '16 at 10:03
  • Yes, I agree that this behaviour is inconsistent with other rdbms products. And I do believe that this behaviour should at least be configurable, similarly to other places where MySQL takes a more relaxed approach to the standards. However, this behaviour is well documented in MySQL and has quite widely been discussed over the years. – Shadow Sep 15 '16 at 10:09
0

Your column type is INT(1) so the column is an integer of 1 value length, yet the value you're searching for is '1A', a 2 character VARCHAR. Like PHP, MySQL will cast values as best it can to the columns it's given, you've give it a string (VARCHAR) value but the column is an INT so [in a round about way, long process summarised] it casts the string "1A" to an integer value.

So the value is cast to an int and (string)1A == (int)1

Therefore the result is 

Category.ID
1

The same thing happens in PHP when forcing data types.

Martin
  • 22,212
  • 11
  • 70
  • 132
  • Just for the sake of accuracy, the string is converted to a floating point number, not to int. – Shadow Sep 15 '16 at 09:35
  • Really? wow.... floating numbers are inherently inaccurate. I've found this especially so back in my early SQL days of recording prices with `float` column types coming up with all sorts of fractional variations. – Martin Sep 15 '16 at 09:38
  • Yep, that's true. However, this should not be an issue if the numbers involved are all whole ones. – Shadow Sep 15 '16 at 09:43