Select Name from tblAnimal where Id=1
退货:我的宠物
Select Id from tblAnimal where Name='My Pet' --I copy/pasted this value in SSMS!
返回空变更集(为什么?
经过一番研究,我发现它可以是Char(10)和Char(13)。所以,我想,当我复制粘贴我的宠物时,它会自动将 Char(10) 转换为 Windows 空间环境?
因为这个:
Select id from tblAnimal where Name like '%My%'
返回 1而
select id from Animal where Name like '%My %'
不返回任何内容。
问题。
1. 为什么会这样?
2. 如何解决这个问题(生产数据库,几百万条记录)
3. 如何防止这种情况发生?
要清理数据,请将 CR/LF 对替换为空格:
UPDATE dbo.table
SET column = REPLACE(REPLACE(column, CHAR(10), ''), CHAR(13), ' ')
WHERE CHARINDEX(CHAR(10), column) + CHARINDEX(CHAR(13), column) > 0;
为了首先解决有关防止这些数据的问题,您当然可以防止它,这就是预防应该开始的地方 - 尽可能接近数据,因为您对人们可以将垃圾塞入表的所有外部方式的控制较少。假设您已经有一个表:
CREATE TABLE dbo.blat(col VARCHAR(32));
添加约束很简单:
ALTER TABLE dbo.blat ADD CONSTRAINT ck_col_NoCRLF
CHECK(CHARINDEX(CHAR(10), col) + CHARINDEX(CHAR(13), col) = 0);
现在:
INSERT dbo.blat(col) SELECT NULL;
工程。
INSERT dbo.blat(col) SELECT 'howdy';
工程。
INSERT dbo.blat(col) SELECT 'howdy
partner';
失败,并显示:
消息 547,级别 16,状态 0,第 1 行
插入语句与 CHECK 约束"ck_col_NoCRLF"冲突。冲突发生在数据库"test",表"dbo.blat",列"col"中。
该语句已终止。
您也可以考虑添加各种其他特殊字符 - CHAR(9)
(制表符)也是常见的字符。
字符 10 和字符 13 是换行符和回车符 - 它们不是空格,即使它们在网格结果中以空格形式出现。 如果在 SSMS 中查看结果,最好告诉它以文本(ctrl+T 或工具栏上)的形式查看结果。 如果这样做,您将看到结果是否真的是:
My Pet
或
My
Pet
网格的结果将显示为My Pet
在一行上,因为这就是网格的工作方式。 "文本"结果将显示新行,即使对于单个记录也是如此,因此您可以更好地准确查看表中的数据。