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