SQL Server |字符串比较
我面临一些奇怪的问题,想了解其背后的原因。
我们有两个数据库服务器 A 和 B。在这两个服务器上我们都有我们的应用程序数据库(相同的架构但不同的记录)
问题: 我们有一个 SqlQuery
Select * from Person where memberId=123456
该查询运行完美并返回服务器 A 上选择的行。 但在不同的服务器 B 上进行相同的查询不会返回任何记录。
但是如果我将查询修改为
Select * from Person where memberId='123456'
(注意单引号)
现在它会返回正确的记录。
memberId 的数据类型为 nchar(100) 。从技术上讲,我知道我应该使用单引号来比较它。
但只是想了解为什么会发生这种情况?
更新 : 1)两者具有完全相同的架构。 2)两者都有相同的记录
实际代码:
实际上这个查询是动态创建的,然后使用
declare @sql varchar(2000)
set @sql = 'SELECT * FROM PersonTrivia where memberId='+ @MemberId
print @sql
exec (@sql)
这个参数@MemberId是varchar(250)执行
I am facing some strange issue and want to understand the reason behind this.
We have two database servers Say A and B. On both of these servers we have our application database (Same schema but different records)
Problem :
We have a SqlQuery
Select * from Person where memberId=123456
This query runs perfectly and return the rows selected on server - A.
But the same query on a different server-B doesnt return any records.
But if i modify my query to
Select * from Person where memberId='123456'
( notice the single quotes)
Now it returns me proper records.
DataType of memberId is nchar(100) . Technically i understand that i should compare it using the single quotes.
But just want to understand why is this happening??
Update :
1) Both have exactly the same schema.
2) Both have same records
Actual Code :
Actually this query is a dynamically created and then executed using
declare @sql varchar(2000)
set @sql = 'SELECT * FROM PersonTrivia where memberId='+ @MemberId
print @sql
exec (@sql)
and this parameter @MemberId is varchar(250)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
![扫码二维码加入Web技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
查询是否没有返回任何记录或者出现错误?
看起来您可以在 nchar 字段中输入数字,但是,第一次添加字符时,您将无法再查询“整数”......或者至少看起来是这样。
Does the query return no records or it gives you an error?
It looks like you can enter numbers into a nchar field, however, the first time you add a character you won't be able to query for "integers" anymore... or at least it seems so.
我很想知道你是否能提出另一个原因来解释为什么会发生这种情况,但我开始怀疑它。我相当确定您正在某处捕获或抑制错误。
请考虑以下情况:
上面的查询将返回 (1) 结果和错误。因此,如果您的代码如下所示:
您将得到的结果将是
MemberID = 2222222222
。如果您使用 SQLTRY...CATCH
块,也可能会发生这种情况。但是,如果我们更改记录的顺序1:在引发异常之前,您基本上会得到 (0) 个结果。最后,如果您将查询更改为:
...您将得到 (1) 记录和错误。
我的建议是查明您是否以及为何抑制错误。我的总体建议是不要将字符字段与整数进行比较并依赖隐式转换。
1.聚集索引不保证行顺序。在本次测试中几乎肯定会出现这种情况,但值得指出。
I will be very interested to know if you come up with another reason for why this is occurring, but I am starting to doubt it. I am fairly certain you are catching or suppressing an error somewhere.
Consider the following:
The query above will return (1) result AND an error. So, if your code looked like the following:
The result you would get would be
MemberID = 2222222222
. This could also occur if you were using SQLTRY...CATCH
blocks. However, if we change the order1 of the records:You will essentially get (0) results before the exception is thrown. Finally, if you changed your query to:
... you'd get (1) record and an error.
My advice would be to find out if and why you are suppressing an error. My overall advice is to not compare a character field to an integer and rely on an implicit conversion.
1. Clustered indexes don't guarantee row order. It almost certainly will in this test, but it was worth pointing out.
显然
memberId
在比较之前已转换为 int (我假设第二台机器上的查询执行没有错误?)。所以我的第一个猜测是,这是 Sql Server 文化特定的机器,即memberId
可以在第一台机器上转换为 int,而不能在其他机器上。或者,由于记录不同(?),第二台机器上只有一些“错误”记录。然而最后一件事应该会导致运行时错误。Obviously
memberId
is converted to int before comparison (I assume there is no errors in query exec on 2nd machine?). So my first guess would be that this is machine of Sql Server culture specific, i.e.memberId
can be converted to int on first machine and can't on other. Or, since records differ (?) there is just some "wrong" records on 2nd machine. However last thing should result in runtime error.