有关MySql编写可重复执行的sql(主要用于部署升级等)

在系统部署时候经常有sql提交，然而像ddl，dml文件重复执行则会报错，此篇文章是在网上搜罗了一些的常见情况的解决方法。

一、操作表的语句

对表进行操作，需要编写存储过程来进行判断后再进行操作。在information_schema表中数据进行判断，使用了select database()方法查询当前使用数据库(因为information_schema查询出来是所有数据库的信息)

操作	SQL语句
修改表名	alter table t_book rename to bbb;
添加列	alter table 表名 add column 列名 varchar(30);
删除列	alter table 表名 drop column 列名;
修改列名	alter table bbb change nnnnn hh int;
修改列属性	alter table t_book modify name varchar(22);

存储过程

删除列

--删除列
drop PROCEDURE if EXISTS add_col_homework;
create procedure add_col_homework() BEGIN 
IF EXISTS (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA in (select database()) AND table_name='mytable' AND COLUMN_NAME='mycolumn')
THEN 
   ALTER TABLE `mytable`  DROP COLUMN `mycolumn`;
END IF;  
END;
call add_col_homework(); 
drop PROCEDURE if EXISTS add_col_homework;

修改列

-----修改列属性-
drop PROCEDURE if EXISTS add_col_homework;
create procedure add_col_homework() BEGIN 
IF not EXISTS (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA in (select database()) AND table_name='mytable' AND COLUMN_NAME='mycolumn' AND COLUMN_TYPE='varchar(20)' and COLUMN_COMMENT='原有注释')
THEN 
   ALTER TABLE `mytable`  MODIFY `mycolumn` varchar(20) not null comment '修改注释';
END IF;  
END;
call add_col_homework(); 
drop PROCEDURE if EXISTS add_col_homework;

增加列

--添加列
drop PROCEDURE if EXISTS add_col_homework;
create procedure add_col_homework() BEGIN 
IF not EXISTS (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA in (select database()) AND table_name='mytable' AND COLUMN_NAME='mycolumn')
THEN 
   ALTER TABLE `mytable`  add COLUMN `mycolumn` varchar(20) not null comment '注释';
END IF;  
END;
call add_col_homework(); 
drop PROCEDURE if EXISTS add_col_homework;

二、dml中insert语句

①在insert中加入where条件根据唯一性判断是否存在

    例如，

INSERT INTO `table_name` (`ID`, `NAME`) select 1101, 'test' from dual 
     where not exists (select 1 from table_name where id = 1);

②使用特定insert语句(ignore、replace、on duplicate key update)

    insert ignore语句

insert ignore into `table_name` (`id`,`name`) values (1,'aa');

    用主键primary或者唯一索引unique区分了记录的唯一性，当有重复记录就会忽略,执行后返回数字0

    replace into语句

REPLACE INTO `table_name` (`ID`,`NAME`) VALUES (1101,'test');

    类似Insert语句，但是如果旧记录与新记录有相同的值，则在新记录被插入之前，旧记录被删除。

    旧记录与新记录有相同的值的判断标准就是：表有一个PRIMARY KEY或UNIQUE索引，否则，使用一个REPLACE语句没有意义

    REPLACE语句会返回一个数，来指示受影响的行的数目。该数是被删除和被插入的行数的和，该数为1（添加）或更大（替换）

    on duplicate key update语句

    在INSERT INTO…..后面加上 ON DUPLICATE KEY UPDATE方法来实现。如果指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE。

转载于:https://my.oschina.net/u/1587199/blog/805303