mysql存储过程 分表统计_mysql 存储过程：把昨天的数据找出来并保存到对应的月份分表...

DELIMITER $$

CREATE PROCEDURE insertDataEveryday()

BEGIN

#把昨天的数据找出来插入到对应的月份分表

DECLARE v_tableName, v_time_begin,v_time_end,v_year,v_month ,v_month_current,v_yesterday VARCHAR(32);

DECLARE v_sql VARCHAR(2000);

DECLARE v_count int;

#获取系统时间

select year(CURRENT_DATE) into v_year;

#上个月

#select substr( DATE_ADD(CURDATE(),INTERVAL -1 MONTH),6,2)into v_month;

#本月

select substr( CURDATE(),6,2) into v_month_current;

#昨天

select DATE_SUB(curdate(),INTERVAL 1 DAY) into v_yesterday;

#要插入数据的月份

select REPLACE(substr(DATE_SUB(curdate(),INTERVAL 1 DAY),1,7),'-','') into v_month;

#获取时间起止

set v_time_begin=CONCAT('''',v_yesterday,' 00:00:00','''');

set v_time_end=CONCAT('''',v_yesterday,' 23:59:59','''');

#获取表名

set v_tableName=CONCAT('smp_equip_realtimedata_',v_month);

#插入数据

set v_sql=CONCAT('insert into ', v_tableName,' (id,create_by,create_date,update_by,update_date,remarks,del_flag,equip_id,equip_code,equip_name,equip_type,func_code,data_lenth,uv_a,uv_b,uv_c,ia_a,ia_b,ia_c,leakage_current,temperature_i,temperature_ii,temperature_iii,temperature_iv,ta_trans_ratio,alarm_leakage_current,alarm_overload,alarm_short_circuit,alarm_overvoltage,alarm_under_voltage,alarm_broken_circuit,alarm_open_phase,alarm_phase_stagger,alarm_electric_arc,alarm_smoke_detector,alarm_flammable_gas,alarm_toxic_gas,alarm_temp_i,alarm_temp_ii,alarm_temp_iii,alarm_temp_iv,di1,di2,di3,di4,di5,di6,di7,di8,do1,do2,do3,do4,do5,do6,do7,do8,data_time,fault_leakage_current,fault_temp_i,fault_temp_ii,fault_temp_iii,fault_temp_iv,pt,active_energy,reactive_energy) select id,create_by,create_date,update_by,update_date,remarks,del_flag,equip_id,equip_code,equip_name,equip_type,func_code,data_lenth,uv_a,uv_b,uv_c,ia_a,ia_b,ia_c,leakage_current,temperature_i,temperature_ii,temperature_iii,temperature_iv,ta_trans_ratio,alarm_leakage_current,alarm_overload,alarm_short_circuit,alarm_overvoltage,alarm_under_voltage,alarm_broken_circuit,alarm_open_phase,alarm_phase_stagger,alarm_electric_arc,alarm_smoke_detector,alarm_flammable_gas,alarm_toxic_gas,alarm_temp_i,alarm_temp_ii,alarm_temp_iii,alarm_temp_iv,di1,di2,di3,di4,di5,di6,di7,di8,do1,do2,do3,do4,do5,do6,do7,do8,data_time,fault_leakage_current,fault_temp_i,fault_temp_ii,fault_temp_iii,fault_temp_iv,pt,active_energy,reactive_energy from smp_equip_realtimedata a where a.create_date>= ',v_time_begin, ' and a.create_date<= ',v_time_end);

set @v_sql=v_sql; #--注意很重要，将连成成的字符串赋值给一个变量(可以之前没有定义，但要以@开头)

prepare stmt from @v_sql;#--预处理需要执行的动态SQL，其中stmt是一个变量

EXECUTE stmt; #执行SQL语句

deallocate prepare stmt; #释放掉预处理段

END$$

DELIMITER ;