4

可重复执行的 MySQL 常用存储过程

 2 years ago
source link: https://www.sulinehk.com/post/repeatable-mysql-common-stored-procedures/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

可重复执行的 MySQL 常用存储过程

2021-04-16

因为公司内部的 SQL 提交系统规定，在一个环境内可能会多次执行同一条 SQL，所以需要保证 SQL 的可重复执行性。

这样就可以把常用的一些 SQL 写成存储过程，并使其可重复执行，会比较方便。

MySQL Logo

CREATE PROCEDURE `CreateColumnIfNotExists` (
	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
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

创建 Unique 索引

CREATE PROCEDURE `CreateUniqueIndexIfNotExists` (
	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
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
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 = 1 THEN
		SET @_sqlText = CONCAT(' DROP INDEX `IX_', columnName, '` ON `', tableName, '`; ');
		PREPARE stmt1 FROM @_sqlText;
		EXECUTE stmt1;
		DEALLOCATE PREPARE stmt1;
	END IF;
END

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK