SELECT * FROM `size_details` WHERE size_details.standard = 20
Results
SELECT * FROM `size_details` WHERE size_details.standard = '20'
Results
-
1the results are correct :-) . in your first sample you compare the string with a number (20). MySQL tried to convert the string to an integer and stops at the first bad char (x). so the compare is true and in the resultset. in the second you compare string with stringBernd Buffen– Bernd Buffen2021-11-02 21:57:19 +00:00Commented Nov 2, 2021 at 21:57
-
See this demo hereFanoFN– FanoFN2021-11-03 00:27:15 +00:00Commented Nov 3, 2021 at 0:27
-
@BerndBuffen MySQL tried to convert the string to an integer and stops at the first bad char (x). No. Both operands are converted to floating-point before comparing.Akina– Akina2021-11-03 04:38:58 +00:00Commented Nov 3, 2021 at 4:38
Add a comment
|
1 Answer
In your first example the constant you're comparing against is expressed as an integer so MySQL attempts to cast the value from the table to an integer for comparison. It will proceed character by character until it encounters a non-numeric character.
In your example 20x20 will cast to 20 because the conversion stops at x. This passes the comparison.
In your second example both parts of the comparison are strings and '20x20' is not equal to '20' so the comparison fails.
answered Nov 2, 2021 at 22:08
Tangentially Perpendicular
5,4364 gold badges15 silver badges34 bronze badges
2 Comments
Akina
so MySQL attempts to cast the value from the table to an integer for comparison. No. Both operands are converted to floating-point (real) before comparing. See Type Conversion in Expression Evaluation - the most last rule is applied.
Tangentially Perpendicular
@akina thanks for the link. I spent about 20 minutes hunting for that. Clearly my Google-foo was lacking today.