mysql 可以重复执行的表结构修改存储过程

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;