数据库存储过程-生产实践

CREATE TABLE log_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    log_message VARCHAR(255),
    log_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //
CREATE PROCEDURE create_table_loan_bairong_several_v2_with_date_suffix()
BEGIN
    DECLARE current_month INT;
    DECLARE current_year INT;
    DECLARE table_name_var VARCHAR(255);
		DECLARE ddl_sql TEXT;

    -- 声明一个变量来存储日志信息
    DECLARE log_message VARCHAR(255);
		SET time_zone = 'Asia/Shanghai';
    -- 设置日志信息
    SET log_message = concat('存储过程开始执行：' , NOW());
    -- 插入日志信息到日志表中（假设有一个名为 log_table 的日志表）
    INSERT INTO log_table (log_message, log_timestamp)
    VALUES (log_message, NOW());

    SET current_month = MONTH(CURRENT_DATE());
    SET current_year = YEAR(CURRENT_DATE());
    
    SET table_name_var = CONCAT('loan_bairong_several_v2_', current_year, '_', LPAD(current_month, 2, '0'));
		SET ddl_sql = '(
        `id` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ''主键id'',
        `name_clear` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ''明文姓名'',
        `mobile_md5` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ''手机号md5'',
        `id_card_md5` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ''身份证号md5'',
        `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ''姓名'',
        `mobile` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ''手机号'',
        `id_card` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ''身份证号'',
        `als_d7_id_nbank_else_allnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按身份证号查询，近7天在非银机构-其他申请次数'',
        `als_d7_id_nbank_oth_allnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按身份证号查询，近7天在非银机构-其他申请次数'',
        `als_d7_id_nbank_allnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按身份证号查询，近7天在非银机构申请次数'',
        `als_m1_id_nbank_oth_allnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按身份证号查询，近1个月在非银机构-其他申请次数'',
        `als_m1_id_nbank_else_allnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按身份证号查询，近1个月在非银机构-其他申请次数'',
        `als_m3_cell_min_inteday` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按手机号查询，近3个月申请最小间隔天数'',
        `als_d7_id_nbank_ca_allnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按身份证号查询，近7天在非银机构-现金类分期机构申请次数'',
        `als_d7_id_nbank_nsloan_allnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按身份证号查询，近7天在非银机构-持牌网络小贷机构申请次数'',
        `als_m6_cell_nbank_finlea_orgnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按手机号查询，近6个月在非银机构-持牌融资租赁机构申请机构数'',
        `als_m1_id_af_allnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按身份证号查询，近1个月申请汽车金融的次数'',
        `als_m3_id_nbank_autofin_allnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按身份证号查询，近3个月在非银机构-持牌汽车金融机构申请次数'',
        `als_m6_id_nbank_autofin_allnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按身份证号查询，近6个月在非银机构-持牌汽车金融机构申请次数'',
        `als_m1_id_nbank_autofin_allnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按身份证号查询，近1个月在非银机构-持牌汽车金融机构申请次数'',
        `als_d7_id_nbank_orgnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按身份证号查询，近7天在非银机构申请机构数'',
        `als_m1_id_nbank_orgnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按身份证号查询，近1个月在非银机构申请机构数'',
        `als_m3_id_nbank_orgnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按身份证号查询，近3个月在非银机构申请机构数'',
        `request_day_v2` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ''请求数据中心的天'',
        `als_d15_id_nbank_oth_allnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按身份证号查询，近15天在非银机构-其他申请次数'',
        `als_m3_id_nbank_oth_allnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按身份证号查询，近3个月在非银机构-其他申请次数'',
        `als_m6_id_nbank_oth_allnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按身份证号查询，近6个月在非银机构-其他申请次数'',
        `als_m12_id_nbank_oth_allnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按身份证号查询，近12个月在非银机构-其他申请次数'',
        `als_m12_cell_nbank_else_orgnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按手机号查询，近12个月在非银机构-其他申请机构数'',
        `als_m12_cell_bank_orgnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按手机号查询，近12个月在银行机构申请机构数'',
        `als_m6_cell_nbank_else_orgnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按手机号查询，近6个月在非银机构-其他申请机构数'',
        `als_m3_cell_nbank_else_orgnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按手机号查询，近3个月在非银机构-其他申请机构数'',
        `als_m12_cell_nbank_sloan_allnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按手机号查询，近12个月在非银机构-持牌小贷机构申请次数'',
        `als_m12_cell_nbank_oth_orgnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按手机号查询，近12个月在非银机构-其他申请机构数'',
        `als_m6_cell_nbank_tot_mons` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按手机号查询，近6个月在非银机构有申请记录月份数'',
        `als_m12_cell_bank_allnum` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按手机号查询，近12个月在银行机构申请次数'',
        `als_fst_cell_bank_inteday` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT ''0'' COMMENT ''按手机号查询，距最早在银行机构申请的间隔天数'',
        `request_time_v2` datetime NOT NULL COMMENT ''请求数据中心的时间'',
        `request_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ''请求流水号'',
        `request_used_report_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ''本次请求解析用到的报告id'',
        `request_is_history_data` char(2) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ''0不是历史数据，1是历史数据'',
        `report_request_day` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
        `report_request_time` datetime DEFAULT NULL,
        PRIMARY KEY (`id`) USING BTREE,
        KEY `idx_name` (`name_clear`),
        KEY `idx_mobile_md5` (`mobile_md5`),
        KEY `idx_id_card_md5` (`id_card_md5`),
        KEY `idx_request_day_v2` (`request_day_v2`),
        KEY `idx_request_time_v2` (`request_time_v2`),
        KEY `idx_request_id` (`request_id`) USING BTREE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=''用户多头借贷信息'';;';
    
    IF NOT EXISTS (SELECT table_name FROM information_schema.tables WHERE table_name = table_name_var) THEN
				-- 设置日志信息
				SET log_message = concat('存储过程执行中：' ,table_name_var);
				SET time_zone = 'Asia/Shanghai';
				-- 插入日志信息到日志表中
				INSERT INTO log_table (log_message, log_timestamp)
				VALUES (log_message, NOW());
		
        SET @sql = CONCAT(
				'CREATE TABLE ', 
				table_name_var, 
				ddl_sql
				);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
			
    END IF;
		
		    -- 设置日志信息
    SET log_message = concat('存储过程执行完成：' , table_name_var);
		SET time_zone = 'Asia/Shanghai';
    -- 插入日志信息到日志表中
    INSERT INTO log_table (log_message, log_timestamp)
    VALUES (log_message, NOW());
		
END;
//
DELIMITER ;

DELIMITER //
CREATE EVENT create_table_loan_bairong_several_v2_event
ON SCHEDULE EVERY 1 MONTH
STARTS TIMESTAMP(CURRENT_DATE(), '00:00:00')
DO
BEGIN
    CALL create_table_loan_bairong_several_v2_with_date_suffix();
END;
//
DELIMITER ;