Why does mysql 'select where clause' query wrongly?

Question

my sql clause:

SELECT * FROM dev_userdb.ry_usercard where UserIdentity=610528194506235115

The UserIdentity is type of varchar(64).When I execute the query,the result is not what I want,why?

Answer 1

The UserIdentity is type of varchar(64)

Then your query should be using quotes around the value you're querying, e.g.

...userIdentity = '601528194506235115'

The reason for the result you get is explained by the MySQL manual's description of equality comparisons:

The following rules describe how conversion occurs for comparison operations:

• If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. For NULL <=> NULL, the result is true. No conversion is needed.

• If both arguments in a comparison operation are strings, they are compared as strings.

• If both arguments are integers, they are compared as integers.

• Hexadecimal values are treated as binary strings if not compared to a number.

• If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. Note that this is not done for the arguments to IN()! To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.

• A single-row subquery from a table or tables is not considered a constant. For example, if a subquery returns an integer to be compared to a DATETIME value, the comparison is done as two integers. The integer is not converted to a temporal value. To compare the operands as DATETIME values, use CAST() to explicitly convert the subquery value to DATETIME.

• If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.

• In all other cases, the arguments are compared as floating-point (real) numbers.

Your comparison ends up using that lat rule, floating-point number comparison.

The floating-point numbers used by MySQL are IEEE-754 single-precision ("float") or double-precision ("double") binary floating point; presumably it's using doubles for the above (you'd think they'd say).

IEEE-754 double-precision binary floating point only has roughly 15 digits of decimal precision. Specifically, only some integers greater than 9007199254740992 can be represented (9007199254740993 cannot be, for instance); 601528194506235115 is well into the range where there are large gaps between representable integers. If you try to store 601528194506235115 in a double, the value you get is 601528194506235100; that's also what you get when you try to store 601528194506235117 and 601528194506235118 in doubles. So after '601528194506235117', '601528194506235118', and 601528194506235115 are all converted to doubles in order to compare them in your query, they're all 601528194506235100, and so they're all equal. (FWIW, the next representable integer after 601528194506235100 is 601528194506235300.)

JavaScript's numbers are also doubles, so we can see the effect using JavaScript here on site:

var table = [
  '42',
  '754',
  '601528194506235117',
  '601528194506235118',
  '27'
];
var matches = table.filter(function(num) {
  return +num === 601528194506235115;
});
console.log("matching entries:");
console.log(matches);

Answer 2

UserIdentity type is varchar(64), so you should use quotes around the value.

select * from table_name where UserIdentity = 'value';