我应该在 SQL 中引用数字吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2078942/
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
Should I quote numbers in SQL?
提问by ambiguousmouse
I remember reading about quoting stuff when doing a SQL query and that when you quote something, it becomes a string. I also read that numbers should not be quoted. Now, I can't find that quotation and I need to refresh my memory to see if I should quote numbers.
我记得在做 SQL 查询时读过引用内容，当你引用某些内容时，它会变成一个字符串。我还读到不应引用数字。现在，我找不到那个引文，我需要刷新我的记忆，看看我是否应该引用数字。
采纳答案by Lasse V. Karlsen
You should not quote numbers if you want to treat them as numbers.
如果要将数字视为数字，则不应引用数字。
You're correct by remembering that it makes it a string.
你是正确的，记住它使它成为一个字符串。
SELECT 10 AS x
is perfectly legal and will return (in most database engines) a column of datatype int (or a variation thereof.)
是完全合法的，将返回（在大多数数据库引擎中）数据类型为 int（或其变体）的列。
If you do this:
如果你这样做：
SELECT '10' AS x
instead you'll get a textual data type. This might too be suitable in some cases, but you need to decide whether you want the result as text or as a number.
相反，您将获得文本数据类型。这在某些情况下也可能适用，但您需要决定结果是文本形式还是数字形式。
回答by jspcal
Here's an example, where quoting would produce inconsistent results (in MySQL):
这是一个示例，其中引用会产生不一致的结果（在 MySQL 中）：
select 1 < 1.0; // returns 0
select '1' < '1.0'; // returns 1
That's because the second comparison is performed using the current string collation rather than numerically.
这是因为第二次比较是使用当前的字符串排序规则而不是数字进行的。
It's better not to quote numbers, since that would just be an extra unnecessary step for the database to convert the string literal into a numeric value for comparison and could alter the meaning of comparisons.
最好不要引用数字，因为这只是数据库将字符串文字转换为数值进行比较的一个额外不必要的步骤，并且可能会改变比较的含义。
回答by Ed Avis
This answer is for Microsoft SQL Server, in particular MSSQL 2008 R2.
此答案适用于 Microsoft SQL Server，尤其是 MSSQL 2008 R2。
In handwritten SQL I would never quote numbers (unless inserting a value into a varchar column where the string inserted just happens to be a number). But sometimes when generating SQL programmatically it can make life simpler to just quote everything. (This is in database maintenance scripts or library routines that work on any table, without knowing the column types in advance.)
在手写 SQL 中，我永远不会引用数字（除非将值插入到 varchar 列中，其中插入的字符串恰好是一个数字）。但有时以编程方式生成 SQL 时，只引用所有内容可以使生活更简单。（这是在对任何表工作的数据库维护脚本或库例程中，无需事先知道列类型。）
I wondered whether doing so would impose a performance penalty. If I've used a quoted value in my SQL statement, the server must parse it as a string and then have to convert it to integer. But then, parsing SQL would involve converting strings to integers anyway. And the query parse time is usually only a small fraction of the total.
我想知道这样做是否会造成性能损失。如果我在 SQL 语句中使用了带引号的值，则服务器必须将其解析为字符串，然后必须将其转换为整数。但是，无论如何，解析 SQL 都会涉及将字符串转换为整数。并且查询解析时间通常只是总数的一小部分。
I ran some test statements looking like
我运行了一些测试语句，看起来像
insert into #t values (123, 123, 123), (123, 123, 123)
insert into #t values ('123', '123', '123'), ('123', '123', '123')
but with a larger number of columns in #t, a larger number of value tuples inserted in one go, and each statement repeated many times. I happened to use Perl for that:
但是在 中的列数较多时#t，一次插入的值元组数量较多，并且每个语句重复多次。我碰巧为此使用了 Perl：
$dbh = my_database_connection(); # using DBD::Sybase
$n = 20; # this many value tuples, and also repeated this many times
$v = "'123'";
# $v = 123; # uncomment this to insert without quoting
@cols = 'aa' .. 'zz';
@ds = map { "[$_] int not null" } @cols;
@vs = map { $v } @cols;
$" = ", ";
$dbh->do("create table #t (@ds)");
foreach (1 .. $n) {
$sql = 'insert into #t values ';
$sql .= "(@vs), " foreach 1 .. $n;
$sql =~ s/, \z//;
$dbh->do($sql);
}
but you can trivially write the same benchmark in any language. I ran this several times for the quoted case and for the unquoted, and observed no significant difference in speed. (On my setup a single run took about ten seconds; you can obviously change $nto make it faster or slower.)
但是您可以用任何语言轻松编写相同的基准测试。我对引用的案例和未引用的案例运行了几次，并观察到速度没有显着差异。（在我的设置中，单次运行大约需要 10 秒；您显然可以更改$n以使其更快或更慢。）
Of course, the generated SQL is bigger if it has the redundant quote characters in it. It surely can't be any faster, but it does not appear to be noticeably slower.
当然，如果生成的 SQL 中有多余的引号字符，则生成的 SQL 会更大。它肯定不会更快，但它似乎并没有明显变慢。
Given this result, I will simplify my SQL-generation code to add single quotes around all values, without needing to know the data type of the column to be inserted into. (The code does still need to defend against SQL injection by making sure the input value doesn't itself contain the ' character, or otherwise quoting cleverly.)
鉴于此结果，我将简化我的 SQL 生成代码以在所有值周围添加单引号，而无需知道要插入的列的数据类型。（代码仍然需要通过确保输入值本身不包含 ' 字符或以其他方式巧妙地引用来防御 SQL 注入。）
I still don't recommend quoting numbers in SQL in the normal course of things, but if you find you have to do it, it doesn't appear to cause any harm. Similarly, in generated code I put []around column names whether needed or not, but I consider that unnecessary cruft in handwritten SQL.
我仍然不建议在正常情况下在 SQL 中引用数字，但是如果您发现必须这样做，它似乎不会造成任何伤害。同样，在生成的代码中，[]无论是否需要，我都会在列名周围放置，但我认为手写 SQL 中是不必要的。
回答by Scott Hovestadt
Obviously, don't forget to check to make sure any value you passed is really a number.
显然，不要忘记检查以确保您传递的任何值确实是一个数字。
回答by user1693404
Well, at the risk of igniting a flame, may I respectfully disagree a little here that it is NEVER ok to use single quotes around a numeric value? It seems to me that it SOMETIMES makes sense to use single quotes around a numeric value. If col1 is an INT column, then (using vbscript as an example)
好吧，冒着点燃火焰的风险，我是否可以在这里有点不同意，即永远不能在数值周围使用单引号？在我看来，有时在数值周围使用单引号是有意义的。如果 col1 是一个 INT 列，则（以 vbscript 为例）
sql = "INSERT " & foo & " INTO col1 WHERE ID = 1"
and
和
sql = "INSERT '" & foo & "' INTO col1 WHERE ID = 1"
will BOTH, when sql is executed, insert any integer value of foo correctly. But what if you want 0 to be inserted when foo is not initialized? Using a quoted numeric expression like this prevents an error and handles the null case. Whether or not you think this is good practice, it is certainly true.
将两者，当执行 sql 时，正确插入 foo 的任何整数值。但是如果你想在 foo 没有初始化时插入 0 呢？使用像这样带引号的数字表达式可以防止错误并处理空情况。无论您是否认为这是一种很好的做法，这当然是正确的。
回答by John Saunders
I don't know what you may have read, but don't quote numbers.
我不知道你可能读过什么，但不要引用数字。
回答by Thom Wiggers
eh... no you shouldn't?
呃……不，你不应该吗？
I assume you mean by quoting enclosing in 'like 'this'
我假设你的意思是引用括在'like 'this'
INSERT INTO table (foo) VALUES (999);is perfectly legal as long as foo is an INT type collumn
INSERT INTO table (foo) VALUES('foo');Inserts the string foo into the table. You can't do this on INT type tables of course.
INSERT INTO table (foo) VALUES (999);只要 foo 是 INT 类型列，就完全合法
INSERT INTO table (foo) VALUES('foo');将字符串 foo 插入表中。当然，您不能在 INT 类型表上执行此操作。
