SQL Server |字符串比较

发布于 2024-12-03 17:38:18 字数 736 浏览 0 评论 0原文

我面临一些奇怪的问题,想了解其背后的原因。

我们有两个数据库服务器 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技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

阳光①夏 2024-12-10 17:38:18

查询是否没有返回任何记录或者出现错误?

看起来您可以在 nchar 字段中输入数字,但是,第一次添加字符时,您将无法再查询“整数”......或者至少看起来是这样。

CREATE TABLE [dbo].[testnchar](
    [id] [nchar](10) NULL,
    [name] [nchar](100) NULL
)
GO

insert testnchar
select 1, 222222

select * from testnchar 
where name = 222222

id         name
---------  --------
1          222222 

insert testnchar
select 1, 'test'

select * from testnchar 
where name = 222222

--Msg 245, Level 16, State 1, Line 1
--Conversion failed when converting the nvarchar value 'test       

delete testnchar 
where name = 'test'

select * from testnchar 
where name = 222222

id         name
---------  --------
1          222222 

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.

CREATE TABLE [dbo].[testnchar](
    [id] [nchar](10) NULL,
    [name] [nchar](100) NULL
)
GO

insert testnchar
select 1, 222222

select * from testnchar 
where name = 222222

id         name
---------  --------
1          222222 

insert testnchar
select 1, 'test'

select * from testnchar 
where name = 222222

--Msg 245, Level 16, State 1, Line 1
--Conversion failed when converting the nvarchar value 'test       

delete testnchar 
where name = 'test'

select * from testnchar 
where name = 222222

id         name
---------  --------
1          222222 
夏天碎花小短裙 2024-12-10 17:38:18

我很想知道你是否能提出另一个原因来解释为什么会发生这种情况,但我开始怀疑它。我相当确定您正在某处捕获或抑制错误。

请考虑以下情况:

CREATE TABLE [Person]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [MemberID] [nchar](200) NULL,
    [Data] [varchar](50) NULL,
    CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )
)

INSERT Person([MemberID],[Data]) VALUES ('1111111111', 'Test1');
INSERT Person([MemberID],[Data]) VALUES ('2222222222', 'Test2');
INSERT Person([MemberID],[Data]) VALUES ('3333333333', 'Test3');
INSERT Person([MemberID],[Data]) VALUES ('NON-NUMERIC', 'Test4');

SELECT * FROM Person WHERE MemberID = 2222222222

上面的查询将返回 (1) 结果错误。因此,如果您的代码如下所示:

command = new SqlCommand( 
     @"SELECT * FROM Person WHERE MemberID = 2222222222", connection );
try
{
    reader = command.ExecuteReader();
    while ( reader.Read() )
    {
        Console.WriteLine( "MemberID = " + reader["MemberID"] );
    }
    // We'll never get here.
    reader.Close();
}
catch { }

您将得到的结果将是 MemberID = 2222222222。如果您使用 SQL TRY...CATCH 块,也可能会发生这种情况。但是,如果我们更改记录的顺序1

TRUNCATE TABLE [Person]

INSERT Person([MemberID],[Data]) VALUES ('NON-NUMERIC', 'Test1');
INSERT Person([MemberID],[Data]) VALUES ('1111111111', 'Test2');
INSERT Person([MemberID],[Data]) VALUES ('2222222222', 'Test3');
INSERT Person([MemberID],[Data]) VALUES ('3333333333', 'Test4');

在引发异常之前,您基本上会得到 (0) 个结果。最后,如果您将查询更改为:

SELECT T.* FROM
(
    SELECT TOP 100 *
    FROM Person
    ORDER BY MemberID
) T
WHERE T.MemberID = 2222222222

...您将得到 (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:

CREATE TABLE [Person]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [MemberID] [nchar](200) NULL,
    [Data] [varchar](50) NULL,
    CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )
)

INSERT Person([MemberID],[Data]) VALUES ('1111111111', 'Test1');
INSERT Person([MemberID],[Data]) VALUES ('2222222222', 'Test2');
INSERT Person([MemberID],[Data]) VALUES ('3333333333', 'Test3');
INSERT Person([MemberID],[Data]) VALUES ('NON-NUMERIC', 'Test4');

SELECT * FROM Person WHERE MemberID = 2222222222

The query above will return (1) result AND an error. So, if your code looked like the following:

command = new SqlCommand( 
     @"SELECT * FROM Person WHERE MemberID = 2222222222", connection );
try
{
    reader = command.ExecuteReader();
    while ( reader.Read() )
    {
        Console.WriteLine( "MemberID = " + reader["MemberID"] );
    }
    // We'll never get here.
    reader.Close();
}
catch { }

The result you would get would be MemberID = 2222222222. This could also occur if you were using SQL TRY...CATCH blocks. However, if we change the order1 of the records:

TRUNCATE TABLE [Person]

INSERT Person([MemberID],[Data]) VALUES ('NON-NUMERIC', 'Test1');
INSERT Person([MemberID],[Data]) VALUES ('1111111111', 'Test2');
INSERT Person([MemberID],[Data]) VALUES ('2222222222', 'Test3');
INSERT Person([MemberID],[Data]) VALUES ('3333333333', 'Test4');

You will essentially get (0) results before the exception is thrown. Finally, if you changed your query to:

SELECT T.* FROM
(
    SELECT TOP 100 *
    FROM Person
    ORDER BY MemberID
) T
WHERE T.MemberID = 2222222222

... 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.

仙女山的月亮 2024-12-10 17:38:18

显然 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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文