MySQL 判断和删除目录和表外键-mysql教程-PHP中文网

MySQL 判断和删除目录和表外键

php中文网

发布： 2016-06-07 16:27:17

原创

1231人浏览过

MySQL 判断和删除索引和表外键 ---- Procedure Of Delete Index--DROP PROCEDURE IF EXISTS DEL_IDX;CREATE PROCEDURE DEL_IDX(IN P_TABLE_NAME varchar(200), IN P_IDX_NAME VARCHAR(200))BEGINDECLARE ST VARCHAR(250); SET @ST=CONCAT(' DROP INDEX ', P_I

MySQL 判断和删除索引和表外键

--
-- Procedure Of Delete Index
--
DROP PROCEDURE IF EXISTS DEL_IDX;
CREATE PROCEDURE DEL_IDX(IN P_TABLE_NAME varchar(200), IN P_IDX_NAME VARCHAR(200))
BEGIN
DECLARE ST VARCHAR(250);
  SET @ST=CONCAT(' DROP INDEX ', P_IDX_NAME, ' ON ', P_TABLE_NAME);
  SELECT count(*) INTO @CNT FROM information_schema.statistics WHERE table_name=P_TABLE_NAME and index_name=P_IDX_NAME;
  IF @CNT >0 THEN
    PREPARE stmt FROM @ST;
    EXECUTE stmt;
  END IF;
END;
CALL DEL_IDX('T_NAME, 'IDX_NAME');
DROP PROCEDURE IF EXISTS DEL_IDX;

--
-- Procedure Of Delete FK
--
DROP PROCEDURE IF EXISTS DEL_FK;
CREATE PROCEDURE DEL_FK(IN P_TABLE_NAME varchar(200), IN F_K_NAME VARCHAR(200))
BEGIN
DECLARE ST VARCHAR(250);
  SET @ST=CONCAT(' ALTER TABLE ', P_TABLE_NAME, ' DROP FOREIGN KEY ', F_K_NAME);
  SELECT COUNT(*) INTO @CNT FROM information_schema.key_column_usage WHERE table_name=P_TABLE_NAME and constraint_name=F_K_NAME;
  IF @CNT >0 THEN
    PREPARE stmt FROM @ST;
    EXECUTE stmt;
  END IF;
END;
CALL DEL_FK('T_NAM, 'FK_NAME);
DROP PROCEDURE IF EXISTS DEL_FK;

登录后复制

启昌企业网站管理系统1.3

注意：请将此程序放在网站根目录下运行。若没有IIS，请直接运行根目录下的测试.exe 进行本地测试。基本功能表基本设置：后台可修改联系方式,网站信息。管理员：可新增管理员。自定义导航：新增修改导航菜单、菜单排序等。单页管理：单页面新增关键词和描述等。新闻增加：新闻可设置标题、新闻分类、添加内容等。新闻管理：可分类查看新闻、修改新闻、删除新闻等。产品管理：产品增加二级分类，产品略缩图、产品