常用MySQL存储过程

经常创建数据库表的时候，会分表，例如创建100张表， 从0表到99表。要么通过PHP,Java,Pytho等程序诘言，写一个for循环很方便的创建了。但要脱离这些只用MySQL来操作，就用到存储过程。简单分享几个操作

1 批量创建表结构 batchAddTable

DELIMITER ;;
CREATE  PROCEDURE `batchAddTable`(IN tbname VARCHAR(100), IN tbcount INT, IN tbstart INT)
BEGIN
    DECLARE table_name VARCHAR(100);
    WHILE tbstart<tbcount DO
        SET table_name=CONCAT(tbname, '_', tbstart);
        BEGIN
            SET @v_sql=CONCAT('CREATE TABLE IF NOT EXISTS `', table_name, '` LIKE `', tbname,'`;');
            PREPARE stmt FROM @v_sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END;
        SET tbstart=tbstart+1;
    END WHILE;    
END;;
DELIMITER ;

调用方法例如,已有原始表Person, 要创建100张分表，像Person_0, Person_1 …, 除表名其他字段都一样：

call batchAddTable('Person', 100, 0);

2 批量修改表结构 batchAlterTable

DELIMITER ;;
CREATE PROCEDURE `batchAlterTable`(IN tbname VARCHAR(100), IN tbalter TEXT, IN tbcount INT, IN tbstart INT)
BEGIN
    DECLARE table_name VARCHAR(100);
    WHILE tbstart<tbcount DO
        SET table_name=CONCAT(tbname, '_', tbstart);
        BEGIN
            SET @v_sql=CONCAT('ALTER TABLE `', table_name, '` ', tbalter);
            PREPARE stmt FROM @v_sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END;
        SET tbstart=tbstart+1;
    END WHILE;    
END;;
DELIMITER ;

调用方法例如

-- 给Person表加一个字段gender, 原来单表的SQL语句是
alter table Person add column gender tinyint(3) default 1;

call batchAlterTable('Person', "alter table Person add column gender tinyint(3) default 1", 100, 0);

3 批量删除表结构 batchDelTable

DELIMITER ;;
CREATE PROCEDURE `batchDelTable`(IN tbname VARCHAR(100), IN tbcount INT, IN tbstart INT)
BEGIN
    DECLARE table_name VARCHAR(100);
    WHILE tbstart<tbcount DO
        SET table_name=CONCAT(tbname, '_', tbstart);
        BEGIN
            SET @v_sql=CONCAT('DROP TABLE IF EXISTS `', table_name, '`;');
            PREPARE stmt FROM @v_sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END;
        SET tbstart=tbstart+1;
    END WHILE;    
END;;
DELIMITER ;

4 批量清空表中数据 batchTruncateTable

DELIMITER ;;
CREATE PROCEDURE `batchTruncateTable`(IN tbname VARCHAR(100), IN tbcount INT, IN tbstart INT)
BEGIN
    DECLARE table_name VARCHAR(100);
    WHILE tbstart<tbcount DO
        SET table_name=CONCAT(tbname, '_', tbstart);
        BEGIN
            SET @v_sql=CONCAT('TRUNCATE TABLE `', table_name, '`;');
            PREPARE stmt FROM @v_sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END;
        SET tbstart=tbstart+1;
    END WHILE;    
END;;
DELIMITER ;

5 批量更新数据 batchUpdateTable

DELIMITER ;;
CREATE PROCEDURE `batchUpdateTable`(IN tbname VARCHAR(100), IN tbalter TEXT, IN tbcount INT, IN tbstart INT)
BEGIN
    DECLARE table_name VARCHAR(100);
    WHILE tbstart<tbcount DO
        SET table_name=CONCAT(tbname, '_', tbstart);
        BEGIN
            SET @v_sql=CONCAT('UPDATE `', table_name, '` ', tbalter);
            PREPARE stmt FROM @v_sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END;
        SET tbstart=tbstart+1;
    END WHILE;    
END;;
DELIMITER ;