字段、约束和索引在存储过程中的判断
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 ;
阅读是一种修养,分享是一种美德。
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 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批量操作隐含的特性