/* 文件说明
* 用于数据库表结构版本更新,注意以下几点:
* 1.新增表不能删除旧的表,否则数据丢失,CREATE TABLE IF NOT EXISTS
* 2.新增字段、修改字段、删除字段,新增索引,删除索引,调用存储过程实现,否则第二次运行报错,存储过程名字Pro_Temp_ColumnWork, Pro_Temp_Index
* 3.sql语句放在删除存储过程语句之前(END 删除存储过程),否则调用不到存储过程
* 4.sql语句需要添加日期注释,方便追踪异常
* 5.本地navicat运行本文件内容测试通过再上传
*================================================================*/
SET NAMES utf8;
use `paperless-meeting-cms`;
DELIMITER $$;
DROP PROCEDURE IF EXISTS Pro_Temp_ColumnWork;
/* Mysql版本修改表结构
* TableName:表名称
* ColumnName:字段名称
* SqlStr:字段参数
* CType:操作类型,1表示新增列,2表示修改列类型,3表示删除列
*举例说明
-- 新增列
-- CALL Pro_Temp_ColumnWork ('表名','字段名','字段参数 ', 1);
-- CALL Pro_Temp_ColumnWork ('mytest','passwd','varchar(50) NOT NULL AFTER `name`', 1);
-- 修改列
-- CALL Pro_Temp_ColumnWork ('表名','字段名','字段参数 ', 2);
-- CALL Pro_Temp_ColumnWork ('mytest','passwd','varchar(60) NOT NULL AFTER `name`', 2);
-- 删除列
-- CALL Pro_Temp_ColumnWork ('表名','字段名','', 3);
-- CALL Pro_Temp_ColumnWork ('mytest','passwd','', 3);
*================================================================*/
CREATE PROCEDURE Pro_Temp_ColumnWork(TableName VARCHAR(50),ColumnName VARCHAR(50),SqlStr VARCHAR(4000),CType INT)
BEGIN
DECLARE Rows1 INT;
SET Rows1=0;
SELECT COUNT(*) INTO Rows1 FROM INFORMATION_SCHEMA.Columns
WHERE table_schema= DATABASE() AND table_name=TableName AND column_name=ColumnName;
-- 新增列
IF (CType=1 AND Rows1<=0) THEN
SET SqlStr := CONCAT('ALTER TABLE ',TableName,' ADD COLUMN ',ColumnName,' ',SqlStr);
-- 修改列类型
ELSEIF (CType=2 AND Rows1>0) THEN
SET SqlStr := CONCAT('ALTER TABLE ',TableName,' MODIFY ',ColumnName,' ',SqlStr);
-- 删除列
ELSEIF (CType=3 AND Rows1>0) THEN
SET SqlStr := CONCAT('ALTER TABLE ',TableName,' DROP COLUMN ',ColumnName);
ELSE SET SqlStr :='';
END IF;
-- 执行命令
IF (SqlStr<>'') THEN
SET @SQL1 = SqlStr;
PREPARE stmt1 FROM @SQL1;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END IF;
END
-- 标记符结束
$$;
DELIMITER;
DELIMITER $$;
DROP PROCEDURE IF EXISTS Pro_Temp_Index;
/* Mysql版本修改索引
* TableName:表名称
* IndexName:索引名称
* SqlStr:添加索引语句
* CType: CType:操作类型,1表示新增索引,3表示删除索引
*举例说明
-- 新增索引
-- CALL Pro_Temp_Index ('表名','索引名','索引添加语句', 1);
-- CALL Pro_Temp_Index ('mytest','idx_name','ADD INDEX `idx_name` (`name`)', 1);
-- 删除索引
-- CALL Pro_Temp_Index ('表名','索引名','', 3);
-- CALL Pro_Temp_Index ('mytest','idx_name','', 3);
*================================================================*/
CREATE PROCEDURE Pro_Temp_Index(TableName VARCHAR(50),IndexName VARCHAR(50),SqlStr VARCHAR(4000),CType INT)
BEGIN
DECLARE Rows1 INT;
DECLARE t_sql VARCHAR(1000);
SET Rows1=0;
SET @t_sql=concat('SELECT COUNT(*) INTO @Rows1 FROM information_schema.statistics WHERE TABLE_NAME="',TableName,'" AND INDEX_NAME="',IndexName,'"');
PREPARE stmt FROM @t_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 新增索引
IF (CType=1 AND @Rows1<=0) THEN
SET SqlStr := CONCAT('ALTER TABLE ',TableName,' ',SqlStr);
-- 删除索引
ELSEIF (CType=3 AND @Rows1>0) THEN
SET SqlStr := CONCAT('ALTER TABLE ',TableName,' DROP INDEX ',IndexName);
ELSE SET SqlStr :='';
END IF;
-- 执行命令
IF (SqlStr<>'') THEN
SET @SQL1 = SqlStr;
PREPARE stmt1 FROM @SQL1;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END IF;
END
-- 标记符结束
$$;
DELIMITER;
-- [2019-05-08]
CALL Pro_Temp_ColumnWork ('user','weight',"int(4) NOT NULL DEFAULT 0 COMMENT '用户排序权重,数值越大排名越前'", 1);
-- END 删除存储过程
DROP PROCEDURE Pro_Temp_ColumnWork;
DROP PROCEDURE Pro_Temp_Index;
版本迭代数据库结构更新sql
最新推荐文章于 2021-02-18 07:41:35 发布