mysql 可以重复执行的表结构修改存储过程
当多个数据库要执行同一个 sql，但是在其中有一个数据库失败需要重新执行，那么就要保证执行的数据库是可以重复执行的了，下面就是可以重复执行的存储过程，收藏起来
DELIMITER;;
CREATE PROCEDURE `AddColumnIfNotExists` (
tableName VARCHAR ( 100 ),
columnName VARCHAR ( 100 ),
dbType VARCHAR ( 100 )) BEGIN
DECLARE _tableCount INT;
DECLARE _columnCount INT;
SET _tableCount = ( SELECT COUNT( 1 ) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ( SELECT SCHEMA ()) AND TABLE_NAME = tableName );
SET _columnCount = ( SELECT COUNT( 1 ) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ( SELECT SCHEMA ()) AND TABLE_NAME = tableName AND COLUMN_NAME = columnName );
IF _tableCount = 1 AND _columnCount = 0 THEN
SET @_sqlText = CONCAT( ' ALTER TABLE `', tableName, '` ADD COLUMN `', columnName, '` ', dbType, ' NULL;' );
PREPARE stmt1
FROM @_sqlText;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END IF;
END;;
DELIMITER;
DELIMITER;;
CREATE PROCEDURE `CreateIndexIfNotExists` (
tableName VARCHAR ( 100 ),
columnName VARCHAR ( 100 )) BEGIN
DECLARE
_tableCount INT;
DECLARE
_indexCount INT;
SET _tableCount = ( SELECT COUNT( 1 ) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ( SELECT SCHEMA ()) AND TABLE_NAME = tableName );
SET _indexCount = (
SELECT
count( 1 )
FROM
information_schema.statistics
WHERE
TABLE_SCHEMA = (
SELECT SCHEMA
())
AND TABLE_NAME = tableName
AND INDEX_NAME = concat( 'IX_', columnName ));
IF
_tableCount = 1
AND _indexCount = 0 THEN
SET @_sqlText = CONCAT( ' CREATE INDEX `IX_', columnName, '` ON `', tableName, '`(`', columnName, '` ASC);' );
PREPARE stmt1
FROM
@_sqlText;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END IF;
END;;
DELIMITER;
DELIMITER;;
CREATE PROCEDURE `CreateIndexIfNotExistsWithColumns` ( IN tableName VARCHAR ( 200 ), IN columnName VARCHAR ( 200 ) ) BEGIN
DECLARE
_tableCount INT;
DECLARE
_indexCount INT;
SET _tableCount = ( SELECT COUNT( 1 ) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ( SELECT SCHEMA ()) AND TABLE_NAME = tableName );
SET _indexCount = (
SELECT
count( 1 )
FROM
information_schema.statistics
WHERE
TABLE_SCHEMA = (
SELECT SCHEMA
())
AND TABLE_NAME = tableName
AND INDEX_NAME = concat( 'IX_', columnName ));
IF
_tableCount = 1
AND _indexCount = 0 THEN
SET @_sqlText = CONCAT( ' CREATE INDEX `IX_', columnName, '` ON `', tableName, '`(', columnName, ');' );
PREPARE stmt1
FROM
@_sqlText;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END IF;
END;;
DELIMITER;
DELIMITER;;
CREATE PROCEDURE `CreateUniqueIndexIfNotExistsWithColumns` (
IN tableName VARCHAR ( 200 ),
IN indexName VARCHAR ( 200 ),
IN columnName VARCHAR ( 200 )) BEGIN
DECLARE
_tableCount INT;
DECLARE
_indexCount INT;
SET _tableCount = ( SELECT COUNT( 1 ) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ( SELECT SCHEMA ()) AND TABLE_NAME = tableName );
SET _indexCount = (
SELECT
COUNT( 1 )
FROM
information_schema.statistics
WHERE
TABLE_SCHEMA = (
SELECT SCHEMA
())
AND TABLE_NAME = tableName
AND INDEX_NAME = CONCAT( 'IX_', indexName ));
IF
_tableCount = 1
AND _indexCount = 0 THEN
SET @_sqlText = CONCAT( ' CREATE UNIQUE INDEX `IX_', indexName, '` ON `', tableName, '`(', columnName, ');' );
PREPARE stmt1
FROM
@_sqlText;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END IF;
END;;
DELIMITER;
DELIMITER;;
CREATE PROCEDURE `DropColumnIfExists` (
tableName VARCHAR ( 100 ),
columnName VARCHAR ( 100 )) BEGIN
DECLARE
_count INT;
SET _count = ( SELECT COUNT( 1 ) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ( SELECT SCHEMA ()) AND TABLE_NAME = tableName AND COLUMN_NAME = columnName );
IF
_count = 1 THEN
SET @_sqlText = CONCAT( ' ALTER TABLE ', tableName, ' DROP COLUMN ', columnName, ' ;' );
PREPARE stmt1
FROM
@_sqlText;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END IF;
END;;
DELIMITER;
DELIMITER;;
CREATE PROCEDURE `DropIndexIfExists` (
tableName VARCHAR ( 100 ),
columnName VARCHAR ( 100 )) BEGIN
DECLARE
_count INT;
SET _count = (
SELECT
count( 1 )
FROM
information_schema.statistics
WHERE
TABLE_SCHEMA = (
SELECT SCHEMA
())
AND TABLE_NAME = tableName
AND INDEX_NAME = concat( 'IX_', columnName ));
IF
_count > 0 THEN
SET @_sqlText = CONCAT( ' DROP INDEX `IX_', columnName, '` ON `', tableName, '`; ' );
PREPARE stmt1
FROM
@_sqlText;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END IF;
END;;
DELIMITER;