日期:2014-05-18  浏览次数:20520 次

今天解答一个帖子时遇到的一个问题。
SQL code
--表T和T2有字段ID和COL, 只是数据类型不同,下面语句想把COL更新为前一条记录的ID,
--但是字段为字符型时,结果不是期望的结果,为什么呢?

--表T
CREATE TABLE T(ID INT, COL INT)
INSERT T(ID)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3

CREATE CLUSTERED INDEX IDX_ID ON T(ID)

DECLARE @COL INT, @COL2 INT

UPDATE T SET @COL=@COL2,
              @COL2=ID,
                             COL=@COL

SELECT * FROM T

DROP TABLE T
/*
ID          COL         
----------- ----------- 
1           NULL
2           1
3           2
*/

--表T2
CREATE TABLE T2(ID VARCHAR(8), COL VARCHAR(8))
INSERT T2(ID)
SELECT '00000001' UNION ALL
SELECT '00000002' UNION ALL
SELECT '00000003'

CREATE CLUSTERED INDEX IDX_ID ON T2(ID)

DECLARE @COL VARCHAR(8), @COL2 VARCHAR(8)

UPDATE T2 SET @COL=@COL2,
              @COL2=ID,
                             COL=@COL

SELECT * FROM T2

DROP TABLE T2
/*
ID       COL      
-------- -------- 
00000001 NULL
00000002 00000002
00000003 00000003
*/


------解决方案--------------------
看看.
------解决方案--------------------
SQL code
CREATE TABLE T2(ID VARCHAR(8), COL VARCHAR(8))
INSERT T2(ID)
SELECT '00000001' UNION ALL
SELECT '00000002' UNION ALL
SELECT '00000003'

CREATE CLUSTERED INDEX IDX_ID ON T2(ID)

DECLARE @COL INT, @COL2 INT

UPDATE T2 SET @COL=@COL2,
              @COL2=ID,
                             COL=CONVERT(INT,@COL)

SELECT * FROM T2

DROP TABLE T2

(所影响的行数为 3 行)


(所影响的行数为 3 行)

ID       COL      
-------- -------- 
00000001 NULL
00000002 1
00000003 2

(所影响的行数为 3 行)

------解决方案--------------------
SQL code
---这样也不行啊
CREATE TABLE T2(ID VARCHAR(8), COL VARCHAR(8))
INSERT T2(ID)
SELECT '00000001' UNION ALL
SELECT '00000002' UNION ALL
SELECT '00000003'

CREATE CLUSTERED INDEX IDX_ID ON T2(ID)

DECLARE @COL VARCHAR(8), @COL2 VARCHAR(8),@col3 varchar(8)

UPDATE T2 SET @col3=isnull(0,null),
              @col=@col3,
              @COL3=@COL2,
              @COL2=ID,
              COL=@COL3

SELECT * FROM T2

DROP TABLE T2
/*
ID       COL
-------- --------
00000001 NULL
00000002 00000002
00000003 00000003

(3 行受影响)
*/

------解决方案--------------------
DECLARE @COL VARCHAR(8), @COL2 VARCHAR(8),@COL3 VARCHAR(8), @COL4 VARCHAR(8)
UPDATE T2 SET @COL=@COL2,
@col3=@col2,
@COL2=ID,
@col4=@col2,
col=@col
select @col,@col3,@COL4

结果:

00000003 00000003 00000003

declare @col int,@col2 int,@col3 int,@col4 int
UPDATE T2 SET @COL=@COL2,
@col3=@col2,
@COL2=ID,
@col4=@col2,
COL=@COL
select @col,@col3,@COL4

结果:

2 2 3

不解中=。=!
------解决方案--------------------
DECLARE @COL INT, @COL2 INT
UPDATE T SET @COL=@COL2, @COL2=ID,COL=@COL

也没什么不好理解的。这种写法本来就很混乱,没人会推荐这样写的。

@col 和 @col2 本身的变化倒是会和你的期望一样顺序变化,但涉及到更新字段col时数据库本身有一些说不清楚的自动优化,自然会有一些说不准的结果。
 @COL=@COL2, @COL2=ID,COL=@COL 这个说不定就推断成了 col=id 的恒等变换了

用来作字符累加等还是不错的

------解决方案--------------------
看执行计划的区别,varchar的有点乱了,多了个中间表达式

int
1-[@aCOL2]=id
2-[@aCOL] = [@aCOL2] 
3-[dbo].[T2].[COL] = [@aCOL]

varchar
1-[@aCOL2]=id
2-[@aCOL] = [@aCOL2]
3-[Expr]=[@COL]
4-[T2].[COL] = [Expr1004]

------解决方案--------------------
猜了一下,
字符串的情况下,SQL自动优化时执行顺序可能变成了如下情况:
(1)@COL2=ID
(2)@COL=@COL2