mysql表如何按月分区_sql server 按月对数据表进行分区

use [SIT_L_TMS]

--开启 XP_CMDSHELL；开启创建文件夹权限

GO

SP_CONFIGURE 'SHOW ADVANCED OPTIONS',1

RECONFIGURE

GO

SP_CONFIGURE 'XP_CMDSHELL',1

RECONFIGURE

GO

--数据库名

declare @servername varchar(20) ;

-- 变量赋值

set @servername = 'SIT_L_TMS' ;

--待执行sql

declare @sql varchar(1024) ;

--设置年

declare @year varchar(10);

set @year = CONVERT(varchar(4), GETDATE(), 23);

--月数

declare @summonths int;

set @summonths = 12;

--文件组存放路径

declare @file_path varchar(200);

set @file_path = 'F:\db_group\' + @year;

--判断文件夹是否存在，不存在则创建

declare @TEMP TABLE(A INT,B INT,C INT);--建立虚拟表，用来判断文件夹是否存在

INSERT @TEMP EXEC [MASTER]..XP_FILEEXIST @file_path;

IF NOT EXISTS(SELECT * FROM @TEMP WHERE B=1)

BEGIN

--XP_CMDSHELL不允许使用变量拼接，所以使用exec方法

declare @EX NVARCHAR(255)

SET @EX = 'EXEC XP_CMDSHELL ''MKDIR ' + @file_path + '''';

EXEC(@EX)

END

--文件组名

declare @filegroupname varchar(50);

--文件组路径:完整路径,含文件名

declare @filegrouppath varchar(300);

--分区函数

declare @partition_func varchar(128) ;

set @partition_func = 'l_tms_partition_func';

--分区结构

declare @partition_scheme varchar(128) ;

set @partition_scheme = 'l_tms_partition_scheme';

--时间

declare @datetime varchar(10);

--按时间分区语句

declare @fuction_sql varchar(1024);

--按时间分区结构

declare @scheme_sql varchar(1024);

--建立12个分区

declare @i int;

set @i = 1;

while @i

begin

declare @date varchar(10)

if @i < 10

begin

set @date = '0' + convert(varchar,@i);

end

else

begin

set @date = convert(varchar,@i);

end

-- 添加文件组名;文件组名按日期:例如20180401

set @datetime = @year + @date;

set @filegroupname = 'Group' + @year + @date;

set @sql = 'alter database ' + @servername + ' add filegroup '+ @filegroupname;

print @sql;

exec(@sql);

--将文件组名与文件关联

set @filegrouppath = @file_path + '\' + @filegroupname + '.ndf';

set @sql = 'alter database '+ @servername + ' add file (name=N'''+ @filegroupname +''',filename=N'''+ @filegrouppath +''',size=5Mb,filegrowth=5mb) to filegroup '+ @filegroupname;

print @sql;

exec(@sql);

--拼接按时间分区函数/拼接按时间分区结构

if @i = 1

begin

set @fuction_sql = '''' + @datetime + '01 23:59:59' + '''' + ',';

set @scheme_sql = '['+ @filegroupname +']' + ',';

end

else if @i = 12

begin

set @fuction_sql = @fuction_sql + '''' + @datetime + '01 23:59:59' + '''';

set @scheme_sql = @scheme_sql + '['+ @filegroupname +']';

end

else

begin

set @fuction_sql = @fuction_sql + '''' + @datetime + '01 23:59:59' + '''' + ',';

set @scheme_sql = @scheme_sql + '['+ @filegroupname +']' + ',';

end

set @i = @i + 1;

end

-- 创建分区函数

set @sql = 'create partition function '+ @partition_func +'(DATETIME) as range left for values('+ @fuction_sql+')';

print @sql;

exec(@sql);

-- 创建分区结构(将分区函数和分区组对应起来)

set @sql = 'create partition scheme '+ @partition_scheme +' as partition '+ @partition_func +' to(' + @scheme_sql + ',[Primary])';

print @sql;

exec(@sql);

--将普通表转换为分区表

--删除主键

ALTER TABLE [dbo].[TMS_RealTimeEqptInfo] DROP constraint PK_TMS_RealTimeEqptInfo

--创建主键,但不设为聚集索引

ALTER TABLE [dbo].[TMS_RealTimeEqptInfo] ADD constraint PK_TMS_RealTimeEqptInfo PRIMARY KEY NONCLUSTERED

(

[ID] ASC

)ON [PRIMARY]

--创建一个新的聚集索引，并在该聚集索引中使用分区方案;注意修改分区方案的名字,按字段WriteTime进行分区

CREATE CLUSTERED INDEX CT_RealTimeData on TMS_RealTimeEqptInfo([WriteTime])

ON etm_partition_scheme([WriteTime])