MySQL可重复执行的ALTER SQL语句写法
一、增删表语句
1、以创建rsc_version表为例：
--如果不存在则创建
CREATE TABLE if not exists nsy_scm.`rsc_version` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`product_id` INT(11) NOT NULL DEFAULT '0',
`process_id` INT(11) NOT NULL DEFAULT '0',
`module_id` INT(11) NOT NULL DEFAULT '0',
`module_name` VARCHAR(32) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
`version` VARCHAR(16) NOT NULL DEFAULT '0' COLLATE 'utf8_general_ci',
`updatetime` TIMESTAMP NOT NULL DEFAULT '1970-01-01 10:00:00',
`createtime` TIMESTAMP NOT NULL DEFAULT '1970-01-01 10:00:00',
`memo` VARCHAR(128) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_module_id` (`module_id`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
2、以删除LF_VERSION表为例：
DROP TABLE IF EXISTS nsy_scm.`rsc_version`;
二、增删表触发器
1、以创建触发器TIG_TRANS_LOG为例：
DELIMITER ;;
DROP TRIGGER IF EXISTS `TIG_TRANS_LOG`;
CREATE TRIGGER `TIG_TRANS_LOG` BEFORE INSERT ON `TRANS_LOG` FOR EACH ROW BEGIN
SET @P_CURTIME = NOW();
IF DATE_FORMAT(NEW.CREATETIME, %Y-%m-%d) = 0000-00-00 THEN
SET NEW.CREATETIME = @P_CURTIME;
END IF;
END;;
DELIMITER ;
2、以删除触发器TIG_TRANS_LOG为例：
DROP TRIGGER IF EXISTS `TIG_TRANS_LOG`;
三、增删表索引
1、以创建索引idx_module_id为例：
-- 判断索引是否存在，不存在则创建索引
DROP PROCEDURE IF EXISTS add_index;
DELIMITER ;;
CREATE PROCEDURE add_index()
BEGIN
DECLARE target_database VARCHAR(100);
DECLARE target_table_name VARCHAR(100);
DECLARE target_column_name VARCHAR(100);
DECLARE target_index_name VARCHAR(100);
SET target_table_name = 'rsc_version';
SET target_column_name = 'module_id';
SET target_index_name = 'idx_module_id';
SET target_database= 'nsy_scm';
IF
NOT EXISTS (
SELECT *
FROM information_schema.statistics
WHERE table_schema =
target_database AND table_name = target_table_name AND index_name =
target_index_name) THEN SET @statement = CONCAT("ALTER TABLE ", target_database,".",target_table_name, " ADD INDEX ",
target_index_name, "(", target_column_name, " );");
PREPARE STMT
FROM @statement; EXECUTE STMT; END IF; END;;
DELIMITER ;
CALL add_index();
2、以删除索引idx_module_id为例：
-- 判断索引是否存在，存在则删除索引DROP PROCEDURE IF EXISTS del_index;
DELIMITER ;;
CREATE PROCEDURE del_index()
BEGIN
DECLARE target_database VARCHAR(100);
DECLARE target_table_name VARCHAR(100);
DECLARE target_column_name VARCHAR(100);
DECLARE target_index_name VARCHAR(100);
SET target_table_name = 'rsc_version';
SET target_column_name = 'module_id';
SET target_index_name = 'idx_module_id';
SET target_database= 'nsy_scm';
IF EXISTS (
SELECT *
FROM information_schema.statistics
WHERE table_schema =
target_database AND table_name = target_table_name AND index_name =
target_index_name) THEN SET @statement = CONCAT("DROP INDEX ", target_index_name, " ON ",target_database,'.',target_table_name); PREPARE STMT
FROM @statement; EXECUTE STMT; END IF; END;;
DELIMITER ; CALL del_index();
四、增删表主键
1、以增加表主键id为例：
-- 判断表的主键是否存在，不存在则创建主键
DROP PROCEDURE IF EXISTS add_primary;
DELIMITER ;;
CREATE PROCEDURE add_primary()
BEGIN
DECLARE target_database VARCHAR(100);
DECLARE target_table_name VARCHAR(100);
DECLARE target_column_name VARCHAR(100);
set target_table_name = 'my_test';
set target_column_name = 'id';
set target_database= 'nsy_scm'
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` WHERE
table_schema = target_database AND table_name=target_table_name AND
constraint_name=PRIMARY) THEN
set @statement = CONCAT("ALTER TABLE ", target_database,'.',target_table_name, " ADD PRIMARY KEY(",
target_column_name, ")");
PREPARE STMT FROM @statement;
EXECUTE STMT;
END IF;
END;;
DELIMITER ;
CALL add_primary();
2、以删除表主键ID为例：
-- 判断表的主键是否存在，存在则删除
DROP PROCEDURE IF EXISTS del_primary;
DELIMITER ;;
CREATE PROCEDURE del_primary()
BEGIN
DECLARE target_database VARCHAR(100);
DECLARE target_table_name VARCHAR(100);
set target_table_name = my_test;
set target_database= 'nsy_scm'
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` WHERE table_schema =
target_database AND table_name=target_table_name AND constraint_name=PRIMARY) THEN
set @statement = CONCAT("ALTER TABLE ", target_database,'.',target_table_name, " DROP PRIMARY KEY");
PREPARE STMT FROM @statement;
EXECUTE STMT;
END IF;
END;;
DELIMITER ;
CALL del_primary();
五、增加修改表字段
1、以增加表字段age为例：
-- 判断表字段是否存在，不存在则添加
DROP PROCEDURE IF EXISTS add_column;
DELIMITER ;;
CREATE PROCEDURE add_column(IN COLTYPE varchar(200))
BEGIN
DECLARE target_database VARCHAR(100);
DECLARE target_table_name VARCHAR(100);
DECLARE target_column_name VARCHAR(100);
set target_table_name = 'my_test';
set target_column_name = 'age';
set target_database= 'nsy_scm'
IF NOT EXISTS (select * from information_schema.columns where table_name =
target_table_name and column_name = target_column_name) THEN
set @statement = CONCAT("ALTER TABLE ", target_database,'.',target_table_name, " add ",
target_column_name,COLTYPE);
PREPARE STMT FROM @statement;
EXECUTE STMT;
END IF;
END;;
DELIMITER ;
CALL add_column( int(6));
2、以修改表字段age为例：
-- 判断表的字段是否存在，存在则修改
DROP PROCEDURE IF EXISTS modify_column;
DELIMITER ;;
CREATE PROCEDURE modify_column(IN COLTYPE varchar(200))
BEGIN
DECLARE target_database VARCHAR(100);
DECLARE target_table_name VARCHAR(100);
DECLARE target_column_name VARCHAR(100);
set target_table_name = 'my_test';
set target_column_name = 'age';
set target_database= 'nsy_scm'
IF EXISTS (select * from information_schema.columns where table_name =
target_table_name and column_name = target_column_name) THEN
set @statement = CONCAT("ALTER TABLE ", target_database,'.',target_table_name, " MODIFY ",
target_column_name,COLTYPE);
PREPARE STMT FROM @statement;
EXECUTE STMT;
END IF;
END;;
DELIMITER ;
CALL modify_column( int(100));
