SQL - 安全地将 BIGINT 转换为 INT

Question

我要将一个CSV文件导入到我们的数据库中。其中一个“列”包含应该是INT类型的数据，但有些行的数字仅限于BIGINT范围（因为它们是来自我们合作伙伴的测试数据）。我们在内部存储INT类型，不想进行更改。

我想要安全地从BIGINT转换为INT。所谓安全，就是如果发生算术溢出，不应该引发任何错误。如果转换成功，我希望我的脚本继续执行。如果失败，我希望它可以快速终止。我似乎无法找到正确的语法。这是我得到的:

DECLARE @UserIDBigInt BIGINT = 9723021913; -- actually provided by query param
--Setting within the INT range successfully converts
--SET @UserIDBigInt = 5;
DECLARE @UserID INT = CONVERT(INT, @UserIDBigInt);
--DECLARE @UserID INT = CAST(@UserIDBigInt AS INT);
SELECT @UserIDBigInt
SELECT @UserID
IF @UserID IS NOT NULL BEGIN
    SELECT 'Handle it as reliable data'
END

我想过将@UserIDBigInt与INT的有效范围（-2 ^ 31（-2,147,483,648）到2 ^ 31-1（2,147,483,647））进行比较，但我真的不喜欢这种方法。那是我的备选方案。我希望有一些语言结构或内置函数可以使用。如果我绝对必须与有效范围进行比较，是否至少有一些内置常量可用（例如C＃的int.MinValue和int.MaxValue）？

编辑：更正了拼写错误。

- Olson.dev

---

为什么不在存储过程中将值分配给 INT 变量，然后将该值直接赋回到 BIGINT 变量中，然后将 BIGINT 与原始值（也是 BIGINT）进行比较。如果分配给 INT 时出现溢出，则这些值将不相等。 - Conspicuous Compiler

@引人注目的编译器，我从未说过我正在使用存储过程。无论如何，这样做会引发“算术溢出错误，将表达式转换为数据类型int”的问题，正如我在原始问题中所提到的。 - Olson.dev

请删除一些无人关注的无用标签，并使用RDBMS和版本号进行替换。假设MS SQL Server是唯一我知道可能有帮助的语言特性，它是 SET ARITHABORT OFF; SET ANSI_WARNINGS OFF;。 - Martin Smith

@Olson 原以为这是一个 MySQL 的问题，因为当时没有标签表明你使用的是哪种版本，而在这种情况下，变量的声明将意味着存储过程。我的错。在这种情况下，您需要禁用严格的 SQL 模式（set sql_mode=''），我原以为您已经这样做了。 - Conspicuous Compiler

@Olson - 因为在我发表评论的时候还没有确认你使用的是哪个关系数据库管理系统。回复: Version Denali有一些新功能，比如try_convert，但是目前可能对你并不适用。 - Martin Smith

Answer 1

请将以下内容添加到您的脚本中：

SET ARITHABORT OFF;
SET ARITHIGNORE ON;

这将把任何溢出值转换为NULL。

更多信息请查看：http://msdn.microsoft.com/en-us/library/ms184341.aspx。

- Chains

Answer 2

将您的bigint转换为varbinary，然后存储下半部分到@UserID并检查上半部分：

• 如果上半部分全是0而下半部分表示非负值，则@UserID包含正确的int值；

• 如果上半部分全是1且@UserID为负数，则也是正确的；

• 否则就会出现算术溢出。

下面是一个实现示例：

DECLARE @UserIDBigInt BIGINT = 9723021913;
DECLARE @UserID INT, @HighInt INT;

WITH v AS (SELECT CAST(@UserIDBigInt AS varbinary) AS bin)
SELECT
  @HighInt = SUBSTRING(bin, 1, 4),
  @UserID  = SUBSTRING(bin, 5, 4)
FROM v;

IF (@HighInt = 0 AND @UserID >= 0 OR @HighInt = -1 AND @UserID < 0) BEGIN
    SELECT 'Handle it as reliable data'
END

- Andriy M

Answer 3

我不确定这是否是最佳答案，但这是我之前自己提出的一个方案。可以捕获异常/错误并优雅地继续执行。

示例：

DECLARE @UserIDBigInt BIGINT = 9723021913;
DECLARE @UserID INT;
BEGIN TRY
    SET @UserID = @UserIDBigInt;
END TRY BEGIN CATCH
END CATCH

IF @UserID IS NULL BEGIN
    SELECT 'Handle it as unreliable data'
    RETURN
END

SELECT 'Handle it as reliable data'

- Olson.dev

Answer 4

你也可以将值转换为字符串，将其修剪到所需长度，然后再转换为整数。虽然不是最佳方法，但绝对是一种安全易用的方式。

- Thundersquirrel