使用 BIGINT 的 TSQL 算术溢出 [英] TSQL Arithmetic overflow using BIGINT

问题描述

有人可以为我澄清为什么在下面的示例中尝试设置变量 @a 时会出现错误吗?

Can someone clarify for me why do I get an error when I try to set the variable @a in the example below?

DECLARE @a BIGINT
SET @a = 7*11*13*17*19*23*29*31
/*
ERROR:
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
*/

直到现在我能想到的是，在内部，SQL 开始计算计算乘法并将临时结果放入 INT，然后将其转换为 BIGINT.

What I could figure out til now is that, internaly, SQL starts doing the math evaluating the multiplication and placing the temporary result into a INT then it casts it to a BIGINT.

但是，如果我将 1.0 * 添加到我的数字列表中，则没有错误，因此我相信这次 SQL 使用浮点数作为临时结果，然后将其转换为 BIGINT

However, if I add a 1.0 * to my list of numbers, there is no error, hence I believe that for this time SQL uses float as a temporary result, then cast it to BIGINT

DECLARE @b BIGINT
SET @b =   1.0  *  7*11*13*17*19*23*29*31
/*
NO ERROR
*/

坦率地说，我看不出代码有什么问题……就是这么简单……

Frankly, I don't see anything wrong with the code... it's so simple...

[ 我使用的是 SQL 2008 ]

[ I am using SQL 2008 ]

感谢 Nathan 提供链接.这是我不知道的好信息，但我仍然不明白为什么我会收到错误以及为什么我需要技巧"来获得这样的简单脚本.

Thanks Nathan for the link. That's good information I didn't know about, but I still don't understand why do I get the error and why do I have do "tricks" to get a simple script like this working.

这是我作为程序员应该知道如何处理的事情吗?

Is it something that I should know how to deal with as a programmer?

或者，这是一个错误，如果是这样，我会认为这个问题已经结束.

Or, this a bug and, if so, I will consider this question closed.

推荐答案

当您进行这样的计算时，各个数字的存储大小刚好足以容纳该数字，即: numeric(1,0).看看这个:

When you're doing calculations like this, the individual numbers are stored just large enough to hold that number, ie: numeric(1,0). Check this out:

注意
当您使用 +、-、*、/, 或 % 算术运算符执行隐式或显式int、smallint、tinyint 的转换，或 bigint 常量值浮点数、实数、十进制或数字数据类型，SQL Server 的规则在计算数据时适用表达式的类型和精度结果不同取决于是否查询是否自动参数化.

Caution
When you use the +, -, *, /, or % arithmetic operators to perform implicit or explicit conversion of int, smallint, tinyint, or bigint constant values to the float, real, decimal or numeric data types, the rules that SQL Server applies when it calculates the data type and precision of the expression results differ depending on whether the query is autoparameterized or not.

因此，类似的表达在查询有时会产生不同的结果.当查询不是自动参数化，常数值首先转换为数字，其精度刚好足以容纳常量的值，之前转换为指定的数据类型.例如，常数值 1 是转换为数字 (1, 0)，然后常数值 250 转换为数字 (3, 0).

Therefore, similar expressions in queries can sometimes produce different results. When a query is not autoparameterized, the constant value is first converted to numeric, whose precision is just large enough to hold the value of the constant, before converting to the specified data type. For example, the constant value 1 is converted to numeric (1, 0), and the constant value 250 is converted to numeric (3, 0).

当查询被自动参数化时，常量值总是被转换为转换为之前的数字 (10, 0)最后的数据类型.当...的时候/运营商参与，不仅可以结果类型的精度因人而异类似的查询，但结果值也可以不同.例如，自动参数化的结果值包含表达式的查询SELECT CAST (1.0/7 AS float) 将与结果值不同相同的查询不是自动参数化，因为结果自动参数化查询的将是截断以适应数字 (10,0) 数据类型.想要查询更多的信息关于参数化查询，请参阅简单的参数化.

When a query is autoparameterized, the constant value is always converted to numeric (10, 0) before converting to the final data type. When the / operator is involved, not only can the result type's precision differ among similar queries, but the result value can differ also. For example, the result value of an autoparameterized query that includes the expression SELECT CAST (1.0 / 7 AS float) will differ from the result value of the same query that is not autoparameterized, because the results of the autoparameterized query will be truncated to fit into the numeric (10, 0) data type. For more information about parameterized queries, see Simple Parameterization.

http://msdn.microsoft.com/en-us/library/ms187745.aspx

这不是 SQL Server 中的错误.在同一页面上，它指出:

This isn't a bug in SQL Server. From that same page, it states:

int 数据类型是 SQL Server 中的主要整数数据类型.

The int data type is the primary integer data type in SQL Server.

和

SQL Server 不会自动将其他整数数据类型(tinyint、smallint 和 int)提升为 bigint.

SQL Server does not automatically promote other integer data types (tinyint, smallint, and int) to bigint.

这是定义的行为.作为程序员，如果您有理由相信您的数据会溢出数据类型，则需要采取预防措施来避免这种情况.在这种情况下，只需将这些数字之一转换为 BIGINT 即可解决问题.

This is defined behavior. As a programmer, if you have reason to believe that your data will overflow the data type, you need to take precautions to avoid that situation. In this case, simply converting one of those numbers to a BIGINT will solve the problem.

DECLARE @a BIGINT
SET @a = 7*11*13*17*19*23*29*CONVERT(BIGINT, 31)

这篇关于使用 BIGINT 的 TSQL 算术溢出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！