SQLServer、Mysql、Oracle数据库分区方案参考_oracle有和sqlserver类似的分区存储吗-优快云博客

本文链接：https://blog.youkuaiyun.com/kyq_1024yahoocn/article/details/83615493

本文详细介绍了SQLServer、Mysql和Oracle三种数据库的分区策略，包括分区表的创建、分区函数和方案的定义，以及如何通过存储过程实现自动分区。针对每种数据库，提供了具体的SQL语句和示例，帮助读者理解和实施按月分区。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQLServer、Mysql、Oracle按月分区方案参考

第一部分：SQLServer分区表相关

SQLServer表分区比较特殊，表分区需要将索引列关联到分区方案，分区方案又需要依赖分区函数来划分数据到文件组，而一个文件组又可以包含多个文件。所以一个合理的SQLServer分区，需要自行创文件组、文件、分区函数和分区方案。以下给出相关的参考SQL以及按月分区实例

文件文件组相关

-- 创建存储文件的文件夹，按年存储，按月分区
exec sys.xp_create_subdir 'D:\SQLServerDATA\E_TD\2018';
-- 创建文件组
ALTER DATABASE acrossprovince ADD FILEGROUP [E_TD_2018];
-- 删除文件组
ALTER DATABASE acrossprovince REMOVE FILEGROUP E_TD_2018;
-- 查看文件组信息
select * from sys.filegroups where name='E_TD_2018';
-- 创建文件
IF NOT EXISTS(select 1 from sys.database_files where name='E_TD_201810')
	ALTER DATABASE acrossprovince ADD FILE ( NAME = N'E_TD_201810', FILENAME = N'D:\SQLServerAcrossProvince\E_TD\2018\E_TD_201810.ndf',SIZE = 5mb,FILEGROWTH = 5mb) TO FILEGROUP [E_TD_2018]
GO
-- 删除文件
ALTER DATABASE acrossprovince REMOVE FILE E_TD_201810;
-- 查看文件信息
select * from sys.database_files;

分区方案和分区函数相关

-- 删除分区方案
IF EXISTS(SELECT 1 from sys.partition_schemes where name='E_TD_PS') DROP PARTITION SCHEME  E_TD_PS
GO
-- 删除分区函数
IF EXISTS(SELECT 1 FROM sys.partition_functions where name='E_TD_PF') DROP PARTITION FUNCTION E_TD_PF
GO
-- 创建分区函数，该函数创建的年份应该是很久远的入到primary库的数据
create PARTITION function E_TD_PF(datetime)
as range left for values('2017/01/01')
GO
-- 创建分区方案,初始数据存入PRIMARY文件组，后续通过变更分区来更改文件存储信息
CREATE PARTITION SCHEME E_TD_PS AS PARTITION E_TD_PF ALL TO (
	[PRIMARY]
);
GO
-- 修改分区方案和分区函数
alter partition scheme E_TD_PS next used [E_TD_201701]
alter partition function  E_TD_PF() split range('2017/02/01')

表相关

CREATE TABLE [dbo].[E_TD](
	[id] [varchar](37) NOT NULL,
	[createTime] [datetime] NOT NULL,
	[backup7] [varchar](50) NULL,
	[backup8] [varchar](50) NULL,
	[backup9] [varchar](50) NULL,
	[backup10] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
	[id] ASC,
	[createTime] ASC
)) ON E_TD_PS(createTime)
GO

分区实例：创建分区表，并创建一次性创建一年12个月分区的存储过程，该存储过程可以每年12月1日执行一次从而达到自动分区

-- 创建分区函数，该函数创建的年份应该是很久远的入到primary库的数据
create PARTITION function E_TD_PF(datetime)
as range left for values('2017/01/01')
GO
-- 创建分区方案,初始数据存入PRIMARY文件组，后续通过变更分区来更改文件存储信息
CREATE PARTITION SCHEME E_TD_PS AS PARTITION E_TD_PF ALL TO (
	[PRIMARY]
);
GO
-- 创建表，应用上述的分区方案，分区字段必须是索引字段
CREATE TABLE [dbo].[E_TD](
	[id] [varchar](37) NOT NULL,
	[createTime] [datetime] NOT NULL,
	[backup8] [varchar](50) NULL,
	[backup9] [varchar](50) NULL,
	[backup10] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
	[id] ASC,
	[createTime] ASC
)) ON E_TD_PS(createTime)
GO

-- SQLServer函数：根据表名和指定年份，一次创建指定表一年的分区表,一个月一个分区
IF EXISTS(select 1 from sys.objects where name='CREATE_PARTITION')
	drop proc CREATE_PARTITION
GO

CREATE proc CREATE_PARTITION
	@v_tablename varchar(100),@v_year int
AS

BEGIN
	DECLARE
	@v_year_str varchar(6),
	@v_ymonth varchar(32),
	@v_curdate date,
	@v_i int,
	@v_root_data_dir varchar(100),
	@v_data_dir varchar(100),
	@v_dynamic_sql varchar(4000);

	SET @v_tablename = UPPER(@v_tablename);
	SET @v_year_str = CONVERT(varchar(6),@v_year);
	SET @v_curdate = CONVERT(date,@v_year_str+'/01/01',111);
	SET @v_i = 1;
	SET @v_root_data_dir = 'D:\SQLServerAcrossProvince';
	SET @v_data_dir = @v_root_data_dir+'\'+@v_tablename+'\'+@v_year_str;

	-- 创建存放文件的子路径，每年一个，该存储过程可以多次调用
	exec sys.xp_create_subdir @v_data_dir;

	while @v_i<=12
		BEGIN
			SET @v_ymonth = CONVERT(varchar(6), @v_curdate, 112);
			-- 一个文件组一个文件，如果文件组存在，则文件也存在
			IF NOT EXISTS(select 1 from sys.filegroups where name=@v_tablename+'_'+@v_ymonth)
				BEGIN
					-- 创建文件组和文件
					SET @v_dynamic_sql='ALTER DATABASE acrossprovince ADD FILEGROUP ['+@v_tablename+'_'+@v_ymonth+']'
					EXEC(@v_dynamic_sql)
					SET @v_dynamic_sql='ALTER DATABASE acrossprovince ADD FILE ( NAME = N'''+@v_tablename+'_'+@v_ymonth+''', FILENAME = N'''+@v_root_data_dir+'\'+@v_tablename+'\'+@v_year_str+'\'+@v_tablename+'_'+@v_ymonth+'.ndf'',SIZE = 5mb,FILEGROWTH = 5mb) TO FILEGROUP ['+@v_tablename+'_'+@v_ymonth+']'
					EXEC(@v_dynamic_sql)
					-- 使用对文件组和文件进行应用,先改方案，再改函数
					SET @v_dynamic_sql='alter partition scheme '+@v_tablename+'_PS next used ['+@v_tablename+'_'+@v_ymonth+']'
					EXEC(@v_dynamic_sql)
					SET @v_dynamic_sql = 'alter partition function  '+@v_tablename+'_PF() split range('''+CONVERT(varchar(32),DATEADD(MONTH,1,@v_curdate),111)+''')'
					EXEC(@v_dynamic_sql)
				END
			SET @v_i=@v_i+1
			SET @v_curdate = DATEADD(MONTH,1,@v_curdate)
		END


END

第二部分：Mysql分区

mysql分区不如SQLServer分区复杂，以下直接给实例SQL参考

分区实例

-- 创建分区表
DROP TABLE IF EXISTS E_TD;
CREATE TABLE `E_TD` (
  `id` varchar(37) NOT NULL,
  `createTime` datetime(3),
  `backup1` varchar(50),
  `backup9` varchar(50),
  `backup10` varchar(50),
  PRIMARY KEY (`id`,createTime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
partition by range(TO_DAYS(createTime))(
	partition E_TD_20181001 values less than (to_days('2018-11-01')),
	partition E_TD_20181101 values less than (to_days('2018-12-01'))
);
-- 创建分区存储过程，该存储过程接收表名，开始日期，结束日期参数，然后根据参数创建开始日期所属月份到结束日期所属月份的下一个月的表分区。
-- 此存储过程可以每月或每年执行一次从而达到自动分区
DROP PROCEDURE IF EXISTS create_partition;
create PROCEDURE create_partition(in_tabName VARCHAR(68),in_startDate DATE,in_endDate date)
	COMMENT '为表增加分区，改分区为rang分区，范围为一个月一个分区，参数为：表名，开始日期，结束日期'
BEGIN
    DECLARE maxpdate int DEFAULT 0;
    DECLARE i date;
    DECLARE var_cou int DEFAULT 0;
    first_lable:BEGIN
    SET var_cou=0;

  	-- 查看当前表是否是分区表
    SELECT COUNT(*) INTO var_cou
    FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE `table_name`=in_tabName ;

    IF var_cou =0 THEN
        SELECT CONCAT(in_tabName,'  该表不是分区表，不能进行新增操作');
        LEAVE first_lable;
    END IF;

		-- 清洗参数，将in_startDate和in_endDate转换为当月第一天，且把endDate多加一个月
		set in_startDate=CONCAT(DATE_FORMAT(in_startDate,'%Y-%m'),'-01');
		set i=in_startDate;
		set in_endDate=DATE_ADD(CONCAT(DATE_FORMAT(in_endDate,'%Y-%m'),'-01'),INTERVAL 1 MONTH);
    WHILE i<=in_endDate do
        -- 查询当前分区表是否存在
        SELECT count(*) INTO maxpdate
        FROM INFORMATION_SCHEMA.PARTITIONS
        WHERE `table_name`=in_tabName and RIGHT(PARTITION_NAME,8)=DATE_FORMAT(i,'%Y%m%d');
       if maxpdate =0 then

          set @sqlStat1=concat('alter table ',in_tabName,' add partition (partition ',in_tabName,'_',DATE_FORMAT(i,'%Y%m%d'),' values less than(');
				  set @sqlStat=concat(@sqlStat1,TO_DAYS(DATE_ADD(i,INTERVAL 1 MONTH)),'))');
          PREPARE stmt FROM @sqlStat;
          SELECT @sqlStat;
          EXECUTE stmt;
          DEALLOCATE PREPARE stmt;
       end if;
       set i = date_add(i, interval 1 month);

    end while;
  END first_lable;
end

第三部分：Oracle分区

oracle分区类似mysql分区，比较简单，以下直接给出实例参考SQL

分区实例

CREATE TABLE "E_TD" (
  "id" VARCHAR2(37) NOT NULL ,
  "createTime" date NOT NULL ,
  "backup1" VARCHAR2(50) DEFAULT NULL ,
  "backup2" VARCHAR2(50) DEFAULT NULL ,
  "backup10" VARCHAR2(50) DEFAULT NULL ,
  PRIMARY KEY ("id","createTime")
)
PARTITION BY range("createTime")
(
	partition E_TD_20181001	values less than(to_date('20181101','YYYYMMDD')),
	partition E_TD_20181101	values less than(to_date('20181201','YYYYMMDD'))
);
-- 创建分区存储过程，该存储过程接收表名，开始日期，结束日期参数，然后根据参数创建开始日期所属月份到结束日期所属月份的下一个月的表分区。
-- 此存储过程可以每月或每年执行一次从而达到自动分区
CREATE OR REPLACE
PROCEDURE CREATE_PARTITION(in_tabName in VARCHAR2,i_startDate in DATE,i_endDate in DATE)
	AS
	maxpdate int :=0;
	i date;
	var_cou int :=0;
	sqlStatl VARCHAR2(4000);
	in_startDate date:=TO_DATE(TO_CHAR(i_startDate,'YYYYMM')||'01','YYYYMMDD');
	in_endDate date:=ADD_MONTHS(TO_DATE(TO_CHAR(i_endDate,'YYYYMM')||'01','YYYYMMDD'),1);
BEGIN
	var_cou:=0;
	i:=in_startDate;
	-- 查看当前表是否是分区表
	SELECT COUNT(1) INTO var_cou FROM USER_TAB_PARTITIONS WHERE TABLE_NAME=in_tabName;
	IF var_cou =0 THEN
		-- dbms_output.put_line(in_tabName||'  该表不是分区表，不能进行新增操作');
		return;
	END IF;WHILE(i<=in_endDate)
		LOOP
			SELECT count(1) INTO maxpdate FROM USER_TAB_PARTITIONS WHERE TABLE_NAME=in_tabName and SUBSTR(PARTITION_NAME,-8)=to_char(i,'YYYYMMDD');IF maxpdate=0 then
				-- dbms_output.put_line('alter table '||in_tabName||' add partition '||SUBSTR(in_tabName,0,21)||'_'||to_char(i,'YYYYMMDD')||' values less than(to_date('''||to_char(i,'YYYYMMDD')||''',''YYYYMMDD''))');
				EXECUTE immediate 'alter table '||in_tabName||' add partition '||SUBSTR(in_tabName,0,21)||'_'||to_char(i,'YYYYMMDD')||' values less than(to_date('''||to_char(ADD_MONTHS(i,1),'YYYYMMDD')||''',''YYYYMMDD''))';END IF;i:=ADD_MONTHS(i,1);END LOOP;END;