MySQL转换-1返回18446744073709551615 [英] MySQL casting of -1 returns 18446744073709551615

问题描述

我似乎找不到这个解释，我很确定它以前按预期工作。

  SELECT CAST（-1 AS UNSIGNED INTEGER）; 
  

预期：0

结果：18446744073709551615

有什么变化，或者这是一个MySQL错误？

[UPDATE]
好​​，我想我找到了一个原因它似乎在之前工作：

  SELECT CAST（-1.0 AS UNSIGNED INTEGER）; 
 + -------------------------------- + 
 | CAST（-1.0 AS UNSIGNED INTEGER）| 
 + -------------------------------- + 
 | 0 | 
 + -------------------------------- + 
  

现在，有人可以解释一下这个原因造成的区别吗？。其实，我在文档中找到了！

如果任一操作数是浮点值，结果是一个
浮点值，并且不受

.com / doc / refman / 5.5 / en / cast-functions.html> docs ：

MySQL支持算术包括有符号和无符号64位值。如果使用数字运算符（例如+或 - ），并且其中一个操作数是无符号整数，则默认情况下，结果为无符号（参见第11.6.1节算术运算符）。您可以通过使用SIGNED或UNSIGNED转换运算符分别将值转换为已签名或未签名的64位整数来覆盖此操作。

  mysql> SELECT CAST（1-2 AS UNSIGNED）
  - > 18446744073709551615 
 mysql>选择CAST（CAST（1-2 AS UNSIGNED）AS SIGNED）; 
  - > -1 
  

基本上， c> MySQL 如何处理 0xFFFFFFFFFFFFFFFF 。 -1 签名时， 18446744073709551615 未签名时。

I can't seem to find an explanation for this and I'm pretty sure that it has previously worked as expected.

SELECT CAST(-1 AS UNSIGNED INTEGER);

Expected: 0
Result: 18446744073709551615

Has something changed, or is this a MySQL bug?

[UPDATE] OK, I think that I found a reason why it had appeared to work before:

SELECT CAST(-1.0 AS UNSIGNED INTEGER);
+--------------------------------+
| CAST(-1.0 AS UNSIGNED INTEGER) |
+--------------------------------+
|                              0 |
+--------------------------------+

Now, can someone please explain the difference that this causes?.. Actually, I found in the docs!

If either operand is a floating-point value, the result is a floating-point value and is not affected by the preceding rule.

解决方案

From the docs:

MySQL supports arithmetic with both signed and unsigned 64-bit values. If you are using numeric operators (such as + or -) and one of the operands is an unsigned integer, the result is unsigned by default (see Section 11.6.1, "Arithmetic Operators"). You can override this by using the SIGNED or UNSIGNED cast operator to cast a value to a signed or unsigned 64-bit integer, respectively.

mysql> SELECT CAST(1-2 AS UNSIGNED)
       -> 18446744073709551615
mysql> SELECT CAST(CAST(1-2 AS UNSIGNED) AS SIGNED);
       -> -1

Basically, with you cast you tell MySQL how to treat 0xFFFFFFFFFFFFFFFF. It's -1 when signed, 18446744073709551615 when unsigned.

这篇关于MySQL转换-1返回18446744073709551615的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！