SqlServer 按时间-日期自动分表

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
BEGIN
    SET 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 FOR
    SELECT FORMAT(CreateTime, 'yyyyMM') AS YearMonth, ID, Data, CreateTime
    FROM inserted;

    OPEN InsertedCursor;

    FETCH NEXT FROM InsertedCursor INTO @YearMonth, @ID, @Data, @CreateTime;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @TableName = N'MainTable_' + @YearMonth;

        -- 检查目标分表是否存在，如果不存在则创建
        IF NOT EXISTS (
            SELECT * FROM sys.tables WHERE name = @TableName
        )
        BEGIN
            BEGIN TRANSACTION;
            BEGIN TRY
                -- 再次检查以确保分表不会被其他并发事务创建
                IF NOT EXISTS (
                    SELECT * FROM sys.tables WHERE name = @TableName
                )
                BEGIN
                    SET @DynamicSQL = N'
                        CREATE TABLE ' + @TableName + ' (
                            ID INT PRIMARY KEY,
                            Data VARCHAR(100),
                            CreateTime DATETIME
                        )';
                    EXEC sp_executesql @DynamicSQL;
                END
                COMMIT TRANSACTION;
            END TRY
            BEGIN CATCH
                ROLLBACK TRANSACTION;
                THROW;
            END CATCH
        END

        -- 动态插入数据到目标分表
        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;
    END

    CLOSE InsertedCursor;
    DEALLOCATE InsertedCursor;
END;

 2.2 update 触发器

CREATE TRIGGER AutoSplitTable_Update
ON MainTable
AFTER UPDATE
AS
BEGIN
    SET 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 FOR
    SELECT FORMAT(CreateTime, 'yyyyMM') AS YearMonth, ID, Data, CreateTime
    FROM inserted;

    OPEN UpdatedCursor;

    FETCH NEXT FROM UpdatedCursor INTO @YearMonth, @ID, @Data, @CreateTime;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @TableName = N'MainTable_' + @YearMonth;

        -- 检查目标分表是否存在，如果不存在则创建
        IF NOT EXISTS (
            SELECT * FROM sys.tables WHERE name = @TableName
        )
        BEGIN
            BEGIN TRANSACTION;
            BEGIN TRY
                -- 再次检查以确保分表不会被其他并发事务创建
                IF NOT EXISTS (
                    SELECT * FROM sys.tables WHERE name = @TableName
                )
                BEGIN
                    SET @DynamicSQL = N'
                        CREATE TABLE ' + @TableName + ' (
                            ID INT PRIMARY KEY,
                            Data VARCHAR(100),
                            CreateTime DATETIME
                        )';
                    EXEC sp_executesql @DynamicSQL;
                END
                COMMIT TRANSACTION;
            END TRY
            BEGIN CATCH
                ROLLBACK TRANSACTION;
                THROW;
            END CATCH
        END

        -- 动态更新目标分表的数据
        SET @DynamicSQL = N'
            UPDATE ' + @TableName + ' 
            SET Data = @Data, CreateTime = @CreateTime
            WHERE 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;
    END

    CLOSE UpdatedCursor;
    DEALLOCATE UpdatedCursor;
END;

 2.3 delete 触发器 

CREATE TRIGGER AutoSplitTable_Delete
ON MainTable
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @YearMonth CHAR(6);
    DECLARE @TableName NVARCHAR(50);
    DECLARE @DynamicSQL NVARCHAR(MAX);
 
    DECLARE @ID INT;
    DECLARE @CreateTime DATETIME;

    DECLARE DeletedCursor CURSOR FOR
    SELECT FORMAT(CreateTime, 'yyyyMM') AS YearMonth, ID, CreateTime
    FROM deleted;

    OPEN DeletedCursor;

    FETCH NEXT FROM DeletedCursor INTO @YearMonth, @ID, @CreateTime;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @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;
    END

    CLOSE 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;

结论

通过上述步骤，可以实现基于月份的自动分表策略，自动将插入到 MainTable 的数据按照创建时间分到相应的分表中。触发器和动态 SQL 的结合确保了数据的分表和插入过程的自动化，同时通过事务处理并发插入的情况。

注意

使用触发器进行分表操作可能存在效率问题，特别是在数据插入、更新、删除操作频繁的情况下。这是因为触发器会在每次相关操作时执行，可能导致性能瓶颈和锁定问题。