SQL - 安全地将 BIGINT 向下转换为 INT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7097171/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL - safely downcast BIGINT to INT
提问by Olson.dev
I have a CSV I'm importing into our database. One of the "columns" contains data that shouldbe an INT but some rows have numbers that only fall in the BIGINT range (because they're test data from one of our partners). We store INT internally and have no desire to change.
我有一个 CSV 导入到我们的数据库中。其中一个“列”包含应该是 INT 的数据，但有些行的数字仅落在 BIGINT 范围内（因为它们是来自我们合作伙伴之一的测试数据）。我们在内部存储 INT，并没有改变的愿望。
I want to safely downcast from BIGINT to INT. By safely, I mean no errors should be raised if an arithmetic overflow happens. If the cast/conversion succeeds, I want my script to go on. If it fails, I want it to short-circuit. I can't seem to figure out the proper syntax. This is what I've got:
我想安全地从 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
I've thought about comparing @UserIDBigInt to the valid range of an INT (-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)), but I really don't like that approach. That's my fallback. I was hoping for some language constructs or built-in functions I could use. If I absolutely have to compare to the valid range, are there at least some built-in constants (like C#'s int.MinValue & int.MaxValue)?
我曾考虑将 @UserIDBigInt 与 INT 的有效范围（-2^31 (-2,147,483,648) 到 2^31-1 (2,147,483,647)）进行比较，但我真的不喜欢这种方法。那是我的退路。我希望有一些我可以使用的语言结构或内置函数。如果我绝对必须与有效范围进行比较，是否至少有一些内置常量（如 C# 的 int.MinValue 和 int.MaxValue）？
EDIT: Corrected typo.
编辑：更正了错字。
采纳答案by Chains
Add these to your script:
将这些添加到您的脚本中：
SET ARITHABORT OFF;
SET ARITHIGNORE ON;
This will convert any overflow values to NULL.
这会将任何溢出值转换为 NULL。
More info here: http://msdn.microsoft.com/en-us/library/ms184341.aspx
更多信息在这里：http: //msdn.microsoft.com/en-us/library/ms184341.aspx
回答by Andriy M
Cast your bigintto varbinary, then store the lower half to @UserIDand check the upper half:
投射bigint到varbinary，然后将下半部分存储到@UserID并检查上半部分：
if the upper half is all 0's and the lower half represents a non-negative value,
@UserIDthen contains the correctintvalue;if the upper half is all 1's and
@UserIDis negative, it's all right too;otherwise there's an arithmetic overflow.
如果上半部分全是 0，下半部分代表一个非负值，
@UserID则包含正确的int值；如果上半部分全是1并且
@UserID是负数，那也没关系；否则会出现算术溢出。
Here's an implementation:
这是一个实现：
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
回答by Olson.dev
I'm not sure this is the best answer but it is one I came up with earlier on my own. It is possible to catch the exception/error and gracefully continue execution.
我不确定这是最好的答案，但它是我早些时候自己想出来的。可以捕获异常/错误并优雅地继续执行。
Example:
例子：
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'
回答by Thundersquirrel
You could also convert the value to a string, trim it to length and convert to int. not the best way, but a safe easy way for sure
您还可以将值转换为字符串，将其修剪为长度并转换为 int。不是最好的方法，但肯定是一种安全简单的方法
