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

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

posted @   晒太阳的兔子很忙  阅读(93)  评论(0编辑  收藏  举报
编辑推荐:
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列:向量存储与相似性搜索在 .NET 中的实现
· 基于Microsoft.Extensions.AI核心库实现RAG应用
阅读排行:
· 10年+ .NET Coder 心语 ── 封装的思维:从隐藏、稳定开始理解其本质意义
· 地球OL攻略 —— 某应届生求职总结
· 提示词工程——AI应用必不可少的技术
· Open-Sora 2.0 重磅开源!
· 周边上新:园子的第一款马克杯温暖上架
历史上的今天:
2020-06-01 MongoDB批量操作隐含的特性
点击右上角即可分享
微信分享提示