最近遇到一个误更新数据的问题，使用apexsql log做挖掘事务日志的时候，发现apexsql log生成的redo script跟原始sql是有区别的。它们并不是完全一致的。只是逻辑上等价而已。如下所示，我们做一个测试，如下所示，创建一个表后，并模拟忘记添加条件，出现误删除数据的情况
select * into kerry_test from sys.objects;
delete from kerry_test
然后我们用apexsql log挖掘事务日志，你会看到有很多对应的delete记录，而且对应的redo script跟原始sql是不一样的。如下所示，
下面是其中两个redo script（为了方便查看，对脚本进行了格式化）
--delete (0003f73c:000065fc:000d) done at 2019-06-13 10:35:56.876 by xxx\xxxx in transaction 0000:0adf73ea (committed)
begin transaction;
delete from [dbo].[kerry_test]
where /*** warning: where clause for this statement was generated for a table with no primary key and no clustered index ***/
[name] = n'sysfgfrag' collate chinese_prc_ci_as
and [object_id] = 19
and [principal_id] is null
and [schema_id] = 4
and [parent_object_id] = 0
and [type] = n's ' collate latin1_general_ci_as_ks_ws
and [type_desc] = n'system_table' collate latin1_general_ci_as_ks_ws
and [create_date] = '20160101 10:46:55.060'
and [modify_date] = '20160101 10:46:55.100'
and [is_ms_shipped] = 1
and [is_published] = 0
and [is_schema_published] = 0;
if @@rowcount <= 1
commit transaction;
else
begin
rollback transaction;
print 'error: statement affected more than one row. all the changes were rolled back.';
end;
--delete (0003f73c:000065fc:000a) done at 2019-06-13 10:35:56.876 by xxx\xxx in transaction 0000:0adf73ea (committed)
begin transaction;
delete from [dbo].[kerry_test]
where /*** warning: where clause for this statement was generated for a table with no primary key and no clustered index ***/
[name] = n'sysseobjvalues' collate chinese_prc_ci_as
and [object_id] = 9
and [principal_id] is null
and [schema_id] = 4
and [parent_object_id] = 0
and [type] = n's ' collate latin1_general_ci_as_ks_ws
and [type_desc] = n'system_table' collate latin1_general_ci_as_ks_ws
and [create_date] = '20160101 10:47:02.050'
and [modify_date] = '20160101 10:47:02.057'
and [is_ms_shipped] = 1
and [is_published] = 0
and [is_schema_published] = 0;
if @@rowcount <= 1
commit transaction;
else
begin
rollback transaction;
print 'error: statement affected more than one row. all the changes were rolled back.';
end;
如果有聚集索引，你会看到没有where条件的update、delete操作都会生成很多个对应的redo script，对应的where条件则是聚集索引字段。如下测试所示：
select *
into kerry_test2
from sys.objects;
create clustered index pk_kerry_test2 on kerry_test2(object_id);
update kerry_test2
set name = 'kerry';
delete from kerry_test2;
下面是对应的update、delete操作的redo script
--update (0003f73c:0000700a:001f) done at 2019-06-13 11:27:26.036 by xxx\xxx in transaction 0000:0adf7404 (committed)
begin transaction;
update [dbo].[kerry_test2]
set [name] = n'kerry' collate chinese_prc_ci_as
where [object_id] = 3;
if @@rowcount <= 1
commit transaction;
else
begin
rollback transaction;
print 'error: statement affected more than one row. all the changes were rolled back.';
end;
-- delete (0003f73c:00007482:00d9) done at 2019-06-13 11:27:35.406 by xxx\xxx in transaction 0000:0adf7405 (committed)
begin transaction;
delete from [dbo].[kerry_test2]
where [object_id] = 836302139;
if @@rowcount <= 1
commit transaction;
else
begin
rollback transaction;
print 'error: statement affected more than one row. all the changes were rolled back.';
end;
这里只是简单记录一下这个事情，千万不要以为redo script跟原始sql是完全一致的。如果遇到这种问题，如何判断呢？可以查看transaction id，以及对应的begin time , end time,如下所示，你会发现它们是一致的