Mysql-常用存储过程

记录常用的存储过程工具

紧凑表

• tableName 表名
• primaryKey 主键名

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 ;

备份表

• tableName 表名

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 ;