在MySQL中隐式转换数字 [英] Implicit conversion of a numeric in mysql

问题描述

表结构:

CREATE TABLE `test_table` (
  `pk_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `password` varchar(128) NOT NULL,
  `version` bigint(20) NOT NULL,
  PRIMARY KEY (`pk_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18446744073709551601 DEFAULT CHARSET=utf8;

数据:

/*
-- Query: SELECT * FROM test_table
-- Date: 2017-09-15 16:51
*/
INSERT INTO `test_table` (`pk_id`,`name`,`password`,`version`) VALUES (10545342555594296735,'testabc','testabc',5);
INSERT INTO `test_table` (`pk_id`,`name`,`password`,`version`) VALUES (10545342555594297155,'testdef','testdef',0);
INSERT INTO `test_table` (`pk_id`,`name`,`password`,`version`) VALUES (10545342555594298139,'testghi','testghi',0);
INSERT INTO `test_table` (`pk_id`,`name`,`password`,`version`) VALUES (18446744073709551601,'testjkl','testjkl',0);
INSERT INTO `test_table` (`pk_id`,`name`,`password`,`version`) VALUES (18446744073709551602,'testmno','testmno',0);
INSERT INTO `test_table` (`pk_id`,`name`,`password`,`version`) VALUES (18446744073709551603,'testpqr','testpqr',0);

运行sql:

SELECT 
    *
FROM
    test_table
WHERE
    CAST(pk_id AS CHAR) = 18446744073709551601;

结果:

# pk_id, name, password, version
'18446744073709551601', 'testjkl', 'testjkl', '0'
'18446744073709551602', 'testmno', 'testmno', '0'
'18446744073709551603', 'testpqr', 'testpqr', '0'

我对隐式转换感到困惑，发生了什么?我检查了

I am confused about implicit conversion,What happened?I've checked out the official website about implicit conversion rules.But I still don't understand.Someone can help me.

推荐答案

您可以检查链接以获取有关类型转换的更多信息.

You could check this link for more information about type conversion.

以下规则描述了比较操作如何发生转换:

The following rules describe how conversion occurs for comparison operations:

1-如果一个或两个参数均为NULL，则比较的结果为NULL，除了NULL安全的< =>相等比较运算符外.对于NULL< => NULL，结果为true.无需转换.

1 - 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.

2-如果比较操作中的两个参数都是字符串，则将它们作为字符串进行比较.

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

3-如果两个参数都是整数，则将它们作为整数进行比较.

3 - If both arguments are integers, they are compared as integers.

4-如果不将十六进制值与数字进行比较，则将其视为二进制字符串.

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

5-如果参数之一是TIMESTAMP或DATETIME列，而另一个参数是常量，则在执行比较之前，该常量将转换为时间戳.这样做是为了使ODBC更友好.请注意，对于IN()的参数，则不会这样做！为了安全起见，在进行比较时，请始终使用完整的日期时间，日期或时间字符串.例如，要在将BETWEEN与日期或时间值一起使用时获得最佳结果，请使用CAST()将这些值显式转换为所需的数据类型.

5 - 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.

6-来自一个或多个表的单行子查询不被视为常量.例如，如果子查询返回要与DATETIME值进行比较的整数，则比较将作为两个整数完成.整数不转换为时间值.要将操作数作为DATETIME值进行比较，请使用CAST()将子查询值显式转换为DATETIME.

6 - 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.

7-如果参数之一是十进制值，则比较取决于另一个参数.如果另一个参数是十进制或整数值，则将这些参数作为十进制值进行比较；如果另一个参数是浮点值，则将它们作为浮点值进行比较.

7 - 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.

8-在所有其他情况下，将参数作为浮点数(实数)进行比较.

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

在您的情况下，将参数作为浮点数(实数)进行比较.使用浮点数(或转换为浮点数的值)的比较是近似值，因为此类数字不精确.这可能会导致结果不一致.

In your case, the arguments are compared as floating-point (real) numbers. Comparisons that use floating-point numbers (or values that are converted to floating-point numbers) are approximate because such numbers are inexact. This might lead to results that appear inconsistent.

您可以检查下面的查询将为VALUE1，VALUE2，VALUE3和VALUE4生成相同的结果.这就是为什么您的查询返回这样的结果的原因.

You could check below query will generate same result for VALUE1, VALUE2, VALUE3 and VALUE4. That why your query's returned such that result.

SELECT 
    '18446744073709551601' + 0E0 AS VALUE1,
    '18446744073709551602' + 0E0 AS VALUE2,
    '18446744073709551603' + 0E0 AS VALUE3,
     18446744073709551601 + 0E0 AS VALUE4

如果您希望查询的行为符合预期，则应将两个参数都更改为字符串

If you want the query behaves as expected, you should change both arguments to string

CAST(pk_id AS CHAR) = '18446744073709551601';

这篇关于在MySQL中隐式转换数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！