解决方案 »
- 这些查询sql从那来的“SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM”
- 求sql语句合并数据行,
- 请问如何用存储过程实现如下查询?!
- 如何能知道存储过程中捕获的是什么异常?
- 求一句MYSQL
- MySQL实现类似Oracle的JOB功能
- find_in_set是否效率更高哦
- 关于jdbc连接mysql的问题。。。
- [高分求助!!]关于mysql 的C API问题
- 数据库多级分类如何设计?可以扩展无限级别的
- MyISAM高并发insert delete 该如何处理
- Can't send long data for non-string/non-binary data types (parameter: 0)
错误信息如下
[SQL]DECLARE @tableName CHAR(20)
set @tableName=CONCAT((select date_format(now(),'%Y')),(select date_format(now(),'%c')),'realdata')
create table @tableName
(
RTDId int not null PRIMARY key,
NIIp varchar(15) not null,
State int not null,
MonitorTime datetime not null,
StateInfo varchar(200) not NULL
)engine=innodb default charset=utf8 auto_increment=1;
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @tableName CHAR(20)
set @tableName=CONCAT((select date_format(now(),'%' at line 1
定时每天检查各个设备类型的历史数据表,如果历史数据表的所在分区已经
接近当前日期,则为此设备类型追加分区
*/-- 得到按月分区的日期值
delimiter ;
drop function if exists fnGetPartitionDateForMonth;
delimiter ;;
create function fnGetPartitionDateForMonth() returns INT
begin
declare v_today datetime default date_add(now(), INTERVAL 2 month);
return year(v_today) * 100 + month(v_today);
end;;-- 得到按季度分区的日期值
delimiter ;
drop function if exists fnGetPartitionDateForQuarter;
delimiter ;;
create function fnGetPartitionDateForQuarter() returns int
begin
declare v_today datetime default date_add(now(), interval 3 month);
declare v_month int;
set v_month = month(v_today);
if v_month = 1 or v_month = 2 or v_month = 3 then
set v_today = DATE_ADD(v_today, INTERVAL (4 - v_month) month);
elseif v_month = 4 or v_month = 5 or v_month = 6 THEN
set v_today = DATE_ADD(v_today, INTERVAL (7 - v_month) month);
elseif v_month = 7 or v_month = 8 or v_month = 9 THEN
set v_today = date_add(v_today, INTERVAL (10 - v_month) month);
ELSE
set v_today = date_add(v_today, INTERVAL (13 - v_month) month);
end if;
return year(v_today) * 100 + month(v_today);
end;;-- 得到按半年分区的日期值
delimiter ;
drop function if exists fnGetPartitionDateForHalfYear;
delimiter ;;
create function fnGetPartitionDateForHalfYear() returns int
begin
declare v_today datetime default date_add(now(), interval 6 month);
declare v_month int;
set v_month = month(v_today);
if v_month <= 6 THEN
set v_today = date_add(v_today, INTERVAL (7 - v_month) month);
else
set v_today = DATE_ADD(v_today, INTERVAL (13 - v_month) month);
end if;
return year(v_today) * 100 + month(v_today);
end;;-- 维护按年分区
delimiter ;
drop function if exists fnGetPartitionDateForYear;
delimiter ;;
create function fnGetPartitionDateForYear() returns int
begin
declare v_today datetime default date_add(now(), INTERVAL 2 year);
return year(v_today) * 100;
end;;
delimiter ;
drop procedure if exists spMaintainPartitions;
delimiter ;;
create procedure spMaintainPartitions()
BEGIN
declare v_sql varchar(2000);
declare v_cnt int;
declare v_deviceTypeId int;
declare v_tablename varchar(50);
declare v_tablename_analog varchar(50);
declare v_tablename_digital varchar(50);
declare v_partitionType int;
declare v_fileDir varchar(1000);
declare v_tablenames varchar(1000) default '';
declare v_intDate int;
declare v_partitionName varchar(100);
declare done int default 0;
declare c_deviceType cursor
for select Id, TableName, PartitionType, DataFileDir
from tbDeviceType
where Generated = 1;
declare continue handler for not found set done = 1;
insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`)
Values(Now(), 'spMaintainPartitions start......');
open c_deviceType;
deviceType_loop: LOOP
fetch c_deviceType into v_deviceTypeId, v_tablename, v_partitionType, v_fileDir;
set v_fileDir = replace(v_fileDir, '\\', '/');
if locate(':', v_fileDir) > 0 and locate(':/', v_fileDir) = 0 then
set v_fileDir = replace(v_fileDir, ':', ':/');
end if;
if done = 1 then
leave deviceType_loop;
end if;
set v_intDate = null;
if v_partitionType = 1 then
set v_intDate = fnGetPartitionDateForMonth();
ELSEIF v_partitionType = 2 THEN
set v_intDate = fnGetPartitionDateForQuarter();
ELSEIF v_partitionType = 3 then
set v_intDate = fnGetPartitionDateForHalfYear();
elseif v_partitionType = 4 then
set v_intDate = fnGetPartitionDateForYear();
end if;
if v_intDate is null then
insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`)
values(Now(), Concat('DeviceTypeId = ', cast(v_deviceTypeId As char(10)), ' did not define paritition schedule'));
else
set v_partitionName = concat('p', cast(v_intDate as char(6)));
-- 模拟量表
set v_tablename_analog = concat(v_tablename, '_Analog');
select count(*) into v_cnt
from information_schema.`TABLES` where `TABLE_SCHEMA` = database() and `table_name` = v_tablename_analog;
if v_cnt > 0 then select count(*) into v_cnt
from
information_schema.`PARTITIONS`
where
TABLE_SCHEMA = database() and table_name = v_tablename_analog and partition_name = v_partitionName;
if v_cnt = 0 then
set v_sql = CONCAT('alter table ', v_tablename_analog, ' add partition (partition ', v_partitionName, ' values less than (', cast(v_intDate as char(6)), ') data directory = ''', v_fileDir, ''' index directory = ''', v_fileDir , ''')');
insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`)
Values(Now(), concat('sql = ''', v_sql));
set @sql = v_sql;
prepare cmd from @sql;
execute cmd;
deallocate prepare cmd;
insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`)
values(Now(), concat('execute complete: ', v_sql));
end if;
end if;
-- 数字量表
set v_tablename_digital = concat(v_tablename, '_Digital');
select count(*) into v_cnt
from information_schema.`TABLES` where `TABLE_SCHEMA` = database() and `table_name` = v_tablename_digital;
if v_cnt > 0 then select count(*) into v_cnt
from
information_schema.`PARTITIONS`
where
TABLE_SCHEMA = database() and table_name = v_tablename_digital and partition_name = v_partitionName;
if v_cnt = 0 then
set v_sql = CONCAT('alter table ', v_tablename_digital, ' add partition (partition ', v_partitionName, ' values less than (', cast(v_intDate as char(6)), ') data directory = ''', v_fileDir, ''' index directory = ''', v_fileDir , ''')');
insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`)
Values(Now(), concat('sql = ''', v_sql));
set @sql = v_sql;
prepare cmd from @sql;
execute cmd;
deallocate prepare cmd;
insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`)
values(Now(), concat('execute complete: ', v_sql));
end if;
end if;
end if;
end loop deviceType_loop;
close c_deviceType;
END;;delimiter ;drop event if exists e_DataPartitionMaintain;
create event e_DataPartitionMaintain
on SCHEDULE every 60 Second
on completion PRESERVE
do call spMaintainPartitions();set global event_scheduler = on;