当前位置：首页 > news >正文

SqlServer 按时间-日期自动分表

news 来源：原创 2024/9/20 9:34:01

1. 创建主表

首先，创建主表 MainTable。

CREATE TABLE MainTable (ID INT PRIMARY KEY,Data VARCHAR(100),CreateTime DATETIME
);

2. 创建触发器

在高并发环境下，可能会出现多个插入操作同时触发触发器，导致多个事务尝试同时创建同一个分表的情况。需要确保在创建分表时使用事务和锁定机制来防止并发冲突。

2.1 insert 触发器

CREATE TRIGGER AutoSplitTable
ON MainTable
AFTER INSERT
AS
BEGINSET NOCOUNT ON;DECLARE @YearMonth CHAR(6);DECLARE @TableName NVARCHAR(50);DECLARE @DynamicSQL NVARCHAR(MAX);-- 声明变量用于游标中的每一行DECLARE @ID INT;DECLARE @Data VARCHAR(100);DECLARE @CreateTime DATETIME;-- 使用游标遍历插入的数据行DECLARE InsertedCursor CURSOR FORSELECT FORMAT(CreateTime, 'yyyyMM') AS YearMonth, ID, Data, CreateTimeFROM inserted;OPEN InsertedCursor;FETCH NEXT FROM InsertedCursor INTO @YearMonth, @ID, @Data, @CreateTime;WHILE @@FETCH_STATUS = 0BEGINSET @TableName = N'MainTable_' + @YearMonth;-- 检查目标分表是否存在，如果不存在则创建IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = @TableName)BEGINBEGIN TRANSACTION;BEGIN TRY-- 再次检查以确保分表不会被其他并发事务创建IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = @TableName)BEGINSET @DynamicSQL = N'CREATE TABLE ' + @TableName + ' (ID INT PRIMARY KEY,Data VARCHAR(100),CreateTime DATETIME)';EXEC sp_executesql @DynamicSQL;ENDCOMMIT TRANSACTION;END TRYBEGIN CATCHROLLBACK TRANSACTION;THROW;END CATCHEND-- 动态插入数据到目标分表SET @DynamicSQL = N'INSERT INTO ' + @TableName + ' (ID, Data, CreateTime)VALUES (@ID, @Data, @CreateTime)';EXEC sp_executesql @DynamicSQL, N'@ID INT, @Data VARCHAR(100), @CreateTime DATETIME', @ID, @Data, @CreateTime;FETCH NEXT FROM InsertedCursor INTO @YearMonth, @ID, @Data, @CreateTime;ENDCLOSE InsertedCursor;DEALLOCATE InsertedCursor;
END;

2.2 update 触发器

CREATE TRIGGER AutoSplitTable_Update
ON MainTable
AFTER UPDATE
AS
BEGINSET NOCOUNT ON;DECLARE @YearMonth CHAR(6);DECLARE @TableName NVARCHAR(50);DECLARE @DynamicSQL NVARCHAR(MAX);DECLARE @ID INT;DECLARE @Data VARCHAR(100);DECLARE @CreateTime DATETIME;DECLARE UpdatedCursor CURSOR FORSELECT FORMAT(CreateTime, 'yyyyMM') AS YearMonth, ID, Data, CreateTimeFROM inserted;OPEN UpdatedCursor;FETCH NEXT FROM UpdatedCursor INTO @YearMonth, @ID, @Data, @CreateTime;WHILE @@FETCH_STATUS = 0BEGINSET @TableName = N'MainTable_' + @YearMonth;-- 检查目标分表是否存在，如果不存在则创建IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = @TableName)BEGINBEGIN TRANSACTION;BEGIN TRY-- 再次检查以确保分表不会被其他并发事务创建IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = @TableName)BEGINSET @DynamicSQL = N'CREATE TABLE ' + @TableName + ' (ID INT PRIMARY KEY,Data VARCHAR(100),CreateTime DATETIME)';EXEC sp_executesql @DynamicSQL;ENDCOMMIT TRANSACTION;END TRYBEGIN CATCHROLLBACK TRANSACTION;THROW;END CATCHEND-- 动态更新目标分表的数据SET @DynamicSQL = N'UPDATE ' + @TableName + ' SET Data = @Data, CreateTime = @CreateTimeWHERE ID = @ID';EXEC sp_executesql @DynamicSQL, N'@ID INT, @Data VARCHAR(100), @CreateTime DATETIME', @ID, @Data, @CreateTime;FETCH NEXT FROM UpdatedCursor INTO @YearMonth, @ID, @Data, @CreateTime;ENDCLOSE UpdatedCursor;DEALLOCATE UpdatedCursor;
END;

2.3 delete 触发器

CREATE TRIGGER AutoSplitTable_Delete
ON MainTable
AFTER DELETE
AS
BEGINSET NOCOUNT ON;DECLARE @YearMonth CHAR(6);DECLARE @TableName NVARCHAR(50);DECLARE @DynamicSQL NVARCHAR(MAX);DECLARE @ID INT;DECLARE @CreateTime DATETIME;DECLARE DeletedCursor CURSOR FORSELECT FORMAT(CreateTime, 'yyyyMM') AS YearMonth, ID, CreateTimeFROM deleted;OPEN DeletedCursor;FETCH NEXT FROM DeletedCursor INTO @YearMonth, @ID, @CreateTime;WHILE @@FETCH_STATUS = 0BEGINSET @TableName = N'MainTable_' + @YearMonth;-- 动态删除目标分表的数据SET @DynamicSQL = N'DELETE FROM ' + @TableName + ' WHERE ID = @ID';EXEC sp_executesql @DynamicSQL, N'@ID INT', @ID;FETCH NEXT FROM DeletedCursor INTO @YearMonth, @ID, @CreateTime;ENDCLOSE DeletedCursor;DEALLOCATE DeletedCursor;
END;

测试触发器

通过向 MainTable 插入数据来测试触发器的效果：

-- 插入数据
INSERT INTO MainTable (ID, Data, CreateTime)
VALUES (1, 'Sample Data 1', '2023-01-15'),(2, 'Sample Data 2', '2023-02-20'),(3, 'Sample Data 3', '2023-03-25');-- 检查分表
SELECT * FROM DetailTable_202301;
SELECT * FROM DetailTable_202302;
SELECT * FROM DetailTable_202303;