字段、约束和索引在存储过程中的判断

1.查询命令

use 数据库名称;

查询列命令：select * from information_schema.columns where table_schema = database() and table_name ='数据表名称';

查询约束命令：select * from information_schema.table_constraints where table_schema = database() and table_name ='数据表名称';

查询索引命令：select * from information_schema.statistics where table_schema = database() and table_name ='数据表名称';

 

2.存储过程添加索引

delimiter $$

drop procedure if exists create_index_procedure $$

create procedure create_index_proc(tableName varchar(64), indexName varchar(64), columnName varchar(64))

begin

 if not exists (select * from information_schema.statistics where table_schema = database() and table_name = tableName and index_name = indexName) then

  set @statement = concat("create index ", indexName, " on ", tableName, "(", columnName, ")");

     prepare pre_stmt from @statement;

     execute pre_stmt;

 end if;

end$$

delimiter ;

 

3.存储过程添加字段

delimiter $$

drop procedure if exists add_column_proc $$

create procedure add_column_proc(tableName varchar(64), columnName varchar(64), property varchar(64))

begin

 if not exists (select * from information_schema.columns WHERE table_schema = database() and table_name = tableName and column_name = columnName) then

  set @statement = concat("alter table ", tableName, " add column ", columnName, " ", property);

     prepare pre_stmt from @statement;

     execute pre_stmt;

end if;

end$$

delimiter ;

 

4.存储过程添加约束

delimiter $$

drop procedure if exists add_constraint_proc $$

create procedure add_constraint_proc(tableName varchar(64), constraintName varchar(64), property varchar(64))

begin

 if not exists (select * from information_schema.table_constraints where table_schema = database() and table_name = tableName and constraint_name = constraintName) then

  set @statement = concat("alter table ", tableName, " and constraint ", constraintName, " ", property );

     prepare pre_stmt from @statement;

     execute pre_stmt;

end if;

end$$

delimiter ;

 

https://mp.weixin.qq.com/s/JC0XQV3xHU7G4tbcflKvfA