180510.最近踩过和听过的sql的坑

1. in的巨坑

CREATE TABLE #tmp1 (id INT, value int)
CREATE TABLE #tmp2 (id1 INT, value int)
INSERT INTO #tmp1 VALUES  ( 1,1),(2,2),(3,3)
INSERT INTO #tmp2 VALUES  ( 4,1),(5,2),(6,3)
SELECT * FROM #tmp1 WHERE id IN (SELECT id FROM #tmp2)

DROP TABLE #tmp1,#tmp2

#tmp2没有id这个列，但是 where id in (select id from #tmp2)没有报错，而且执行成功。sql废掉了联合查询的关键字查找(见下图)，select 那句等同于没有where过滤。如果是delete，坑死你没商量。

 

2. uniqueidentifier的坑

DECLARE @id UNIQUEIDENTIFIER
SET @id = '78DC3F2C-50E3-49FA-B96C-B98CB5D2DE0D select * from #tmp1'
SELECT @id

手动拼接sql时，踩出来的雷。少了一个反单引号。然后，就没有然后了。后面的逻辑全部作废。

 

3. like 中英文字符的坑

DECLARE @test VARCHAR(300) = 'test2,test'
SELECT CASE WHEN @test LIKE '%test2，%' THEN 1 ELSE 0 END

 

搜索中文逗号，英文逗号的也会返回为true

 

4. try...catch + tran 的坑

CREATE TABLE #tb1(id VARCHAR(30),v DATETIME)
CREATE TABLE #tb2(id VARCHAR(30),v DATETIME)

INSERT INTO #tb1 VALUES ('11',GETDATE()),('12',GETDATE()),('13',GETDATE()),('11',GETDATE())
INSERT INTO #tb2 VALUES ('21',GETDATE()),('22',GETDATE()),('23',GETDATE()),('21',GETDATE())

--DROP TABLE #tb1,#tb2,#tb3

DECLARE @dt DATETIME = DATEADD(SECOND,-2,GETDATE())
PRINT @dt
INSERT INTO #tb1
VALUES ('11',GETDATE()),('12',GETDATE()),('13',GETDATE()),('11',GETDATE())

BEGIN TRY
    BEGIN TRAN test

    INSERT INTO #tb2 VALUES ('21',GETDATE()),('22',GETDATE()),('23',GETDATE()),('21',GETDATE())

    SELECT 1/0

    COMMIT TRAN test
END TRY
BEGIN CATCH

    DELETE FROM #tb1　　　　　　　　　　--会被回滚
    ROLLBACK TRAN
    DELETE FROM #tb1 WHERE v>@dt　　　--放在事务后面会生效

    PRINT ERROR_MESSAGE();

END CATCH


SELECT TOP 100 * FROM #tb1
SELECT TOP 100 * FROM #tb2

在catch里面修复其他表的数据，放在rollback语句的前面和后面，结果不同。