Mysql-常用存储过程

DELIMITER //

CREATE PROCEDURE CompactTable(IN tableName VARCHAR(255), IN primaryKey VARCHAR(255))
BEGIN
    -- 拼接创建临时表的 SQL 语句
    SET @sql = CONCAT('CREATE TEMPORARY TABLE temp_ids AS SELECT ', primaryKey, ', @row_number:=@row_number + 1 as new_id FROM ', tableName, ' JOIN (SELECT @row_number:=0) r ORDER BY ', primaryKey);

    -- 打印拼接后的 SQL 语句
    SELECT @sql;

    -- 真正生效的 SQL 语句
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    -- 拼接更新原始表的 SQL 语句
    SET @sql = CONCAT('UPDATE ', tableName, ', temp_ids SET ', tableName, '.', primaryKey, ' = temp_ids.new_id WHERE ', tableName, '.', primaryKey, ' = temp_ids.', primaryKey);

    -- 打印拼接后的 SQL 语句
    SELECT @sql;

    -- 真正生效的 SQL 语句
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    -- 删除临时表
    DROP TEMPORARY TABLE IF EXISTS temp_ids;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE BackupTable(IN tableName VARCHAR(255))
BEGIN
    -- 获取时间
    SELECT DATE_FORMAT(NOW(), '%Y%m%d%H%i%s') INTO @time;
    
    -- 需要备份的表
    SET @table = tableName;
    
    -- 拼接SQL
    SET @sql = CONCAT('CREATE TABLE backup_',@time,'_',@table,' LIKE ',@table,';');
    
    -- 打印拼接后的SQL
    SELECT @sql;
    
    -- 真正生效的SQL语句
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
    
    -- 拼接SQL
    SET @sql = CONCAT('INSERT INTO backup_',@time,'_',@table,' SELECT * FROM ',@table,';');
    
    -- 打印拼接后的SQL
    SELECT @sql;
    
    -- 真正生效的SQL语句
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE CheckMissingFields(
    IN db_name VARCHAR(64),
    IN tbl_name VARCHAR(64)
)
READS SQL DATA
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE col_name VARCHAR(64);
    DECLARE col_type VARCHAR(64);
    DECLARE missing_count INT DEFAULT 0;
    DECLARE result_parts TEXT DEFAULT '';
    DECLARE comma_flag BOOLEAN DEFAULT FALSE;

    -- 游标：获取字段名和类型（排除主键、自增）
    DECLARE col_cursor CURSOR FOR
        SELECT COLUMN_NAME, DATA_TYPE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = db_name
          AND TABLE_NAME = tbl_name
          AND COLUMN_KEY NOT IN ('PRI')
          AND EXTRA NOT LIKE '%auto_increment%'
        ORDER BY ORDINAL_POSITION;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 临时表用于接收动态查询的计数结果
    DROP TEMPORARY TABLE IF EXISTS tmp_missing_count;
    CREATE TEMPORARY TABLE tmp_missing_count (cnt INT);

    OPEN col_cursor;

    read_loop: LOOP
        FETCH col_cursor INTO col_name, col_type;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 构建缺失条件（按类型区分）
        IF col_type IN ('char', 'varchar', 'text', 'tinytext', 'mediumtext', 'longtext', 'enum', 'set') THEN
            SET @cond = CONCAT('`', col_name, '` IS NULL OR `', col_name, '` = ''''');
        ELSEIF col_type IN ('datetime', 'date', 'time', 'timestamp', 'year') THEN
            SET @cond = CONCAT('`', col_name, '` IS NULL');
        ELSE
            SET @cond = CONCAT('`', col_name, '` IS NULL');
        END IF;

        -- 动态统计缺失行数
        SET @sql = CONCAT(
            'INSERT INTO tmp_missing_count SELECT COUNT(*) FROM `', db_name, '`.`', tbl_name,
            '` WHERE ', @cond
        );

        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        -- 获取计数
        SELECT cnt INTO missing_count FROM tmp_missing_count LIMIT 1;

        -- 如果有缺失，加入结果
        IF missing_count > 0 THEN
            IF comma_flag THEN
                SET result_parts = CONCAT(result_parts, '、', col_name, '(', missing_count, ')');
            ELSE
                SET result_parts = CONCAT(col_name, '(', missing_count, ')');
                SET comma_flag = TRUE;
            END IF;
        END IF;

        TRUNCATE tmp_missing_count;
    END LOOP;

    CLOSE col_cursor;
    DROP TEMPORARY TABLE tmp_missing_count;

    -- 输出最终结果
    IF result_parts = '' THEN
        SELECT '所有字段均无缺失数据' AS result;
    ELSE
        -- 将最后一个顿号替换为“与”
        SET @last_comma_pos = LOCATE('、', REVERSE(result_parts));
        IF @last_comma_pos > 0 THEN
            SET @prefix = LEFT(result_parts, CHAR_LENGTH(result_parts) - @last_comma_pos);
            SET @suffix = RIGHT(result_parts, @last_comma_pos - 1);
            SET result_parts = CONCAT(@prefix, '与', @suffix);
        END IF;
        SELECT CONCAT('部分记录行缺少', result_parts, '字段数据') AS result;
    END IF;
END$$

DELIMITER ;