SQL Server 中删除前备份数据的“正确”姿势 |
您所在的位置：网站首页 › sql如何备份表 › SQL Server 中删除前备份数据的“正确”姿势 |
SQL Server 中删除前备份数据的“正确”姿势
|
目录 1、先看完整代码 2、代码解析 A.创建数据备份表，涉及几个关键字段： B.取得要删除的记录数据，放到一个JSON传里 C.将JSON数据写入到A中的表里 D.删除原表中的数据，并返回结果 E. DEMO 调用截图 3、后续：当出现“误删”需要恢复数据是怎么办？ 总结 再使用数据库处理数据时，有时为了防止出现系统逻辑错误或操作人员的误操作，有时需要再删除数据时将数据备份，备份的操作有很多种方法，如：1、记录打上删除标志，但不真实删除，2、或者将数据备份到其他表再删除。每种方法各有利弊，1中数据在同一表中，恢复方便，但查询极为不便，需要增加一个删除标志条件，2中的恢复不变，但数据干净，查询也简单，具体使用那种，取决于中间层或业务逻辑处理的方式。本文介绍的是第2种，特别是JSON格式的数据已经广为各种DBMS支持的前提下。 1、先看完整代码 if (object_id('sp_delete', 'P') is not null) drop proc sp_delete go create procedure sp_delete( @table_name nvarchar(1000), @id varchar(50) ) as declare @m_table_name nvarchar(1000), @m_id varchar(50); declare @sql varchar(max); set @m_table_name = replace(@table_name, '''', ''''''); set @m_id = replace(@id, '''', ''''''); -- 表需要预先建好 /* if not exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'deleted_data') begin set @sql = ' -- drop table deleted_data create table deleted_data ( id varchar(50) not null, table_name varchar(1000) null, row_id varchar(50) null, row_data varchar(max) null, version bigint null, del_flag tinyint null, create_time datetime null, modify_time datetime null, create_user_id varchar(50) null, modify_user_id varchar(50) null, constraint PK_DELETED_DATA_0001 primary key nonclustered (id) ) ' exec( @sql ) end -- */ if not exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = @table_name) begin select 'error' as result, '表[' + @m_table_name + ']不存在' as result_desc end else begin set @sql = ' declare @json varchar(max); set @json = (select * from [' + @m_table_name + '] where id = ''' + @m_id + ''' for json auto); print @json if (@json is not null) begin insert into deleted_data ( id , table_name , row_id , row_data , version , del_flag , create_time , modify_time , create_user_id, modify_user_id ) select NEWID(), ''' + @m_table_name + ''', ''' + @m_id + ''', @json, 1, 0, getdate(), null, ''SYS'', null; --end delete from [' + @m_table_name + '] where id = ''' + @m_id + '''; end select ''success'' as result, ''备份成功!删除'' + cast(@@ROWCOUNT as varchar)+ ''条记录'' as result_desc ' print @sql exec(@sql); end go 2、代码解析假设：每个表有一个id字段 删除记录时，不是直接调用delete from 表 where id = xxx，改为调用以上存储过程： A.创建数据备份表，涉及几个关键字段： id varchar(50) not null, -- ID table_name varchar(1000) null, -- 删除备份的表名称 row_id varchar(50) null, -- 要删除的记录ID row_data varchar(max) null, -- 要删除的记录数据 JSON 格式 create_time datetime null, -- 删除的时间 create_user_id varchar(50) null, -- 谁删除的 B.取得要删除的记录数据，放到一个JSON传里 declare @json varchar(max); set @json = (select * from [' + @m_table_name + '] where id = ''' + @m_id + ''' for json auto); print @json C.将JSON数据写入到A中的表里 -- 表为空才备份+删除 if (@json is not null) begin insert into deleted_data ( id , table_name , row_id , row_data , version , del_flag , create_time , modify_time , create_user_id, modify_user_id ) select NEWID(), ''' + @m_table_name + ''', ''' + @m_id + ''', @json, 1, 0, getdate(), null, ''SYS'', null; --end delete from [' + @m_table_name + '] where id = ''' + @m_id + '''; end D.删除原表中的数据，并返回结果 select ''success'' as result, ''备份成功!删除'' + cast(@@ROWCOUNT as varchar)+ ''条记录'' as result_desc E. DEMO 调用截图
当出现“误删”需要恢复数据是怎么办，如何将备份的JSON格式的数据恢复到原来的表里，请参考我们的另外一篇文章： 使用JSON实现SQL Server少量数据传递（导入导出） https://mp.csdn.net/mp_blog/creation/editor/119721347 总结文中方法数据采用JSON，格式相对简单易懂，也没那么多数据冗余，开发过程不必计较删除标志，易维护，省时省力 |
【本文地址】