• mysql在增加列前进行判断该列是否存在


    通过存储过程判断字段是否存在,不存在则增加:

    DROP PROCEDURE IF EXISTS pro_AddColumn;
    CREATE PROCEDURE pro_AddColumn() BEGIN
    IF NOT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name='component' AND COLUMN_NAME='PRINT_CHECK_STATUS') THEN
    ALTER TABLE component ADD PRINT_CHECK_STATUS int(10) default 0;
    END IF;
    IF NOT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name='component' AND COLUMN_NAME='PRINT_CHECK_TIME') THEN
    ALTER TABLE component ADD PRINT_CHECK_TIME datetime NULL;
    END IF;
    IF NOT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=podcloud AND table_name='component' AND COLUMN_NAME='PRINT_CHECK_BACK_REASON') THEN
    ALTER TABLE component ADD PRINT_CHECK_BACK_REASON varchar(500) default null;
    END IF;
    END;
    CALL pro_AddColumn;
    DROP PROCEDURE pro_AddColumn;

    通过存储过程判断索引是否存在,不存在则增加:

     DROP PROCEDURE IF EXISTS pro_AddIndex;  
     DELIMITER;
     CREATE PROCEDURE pro_AddIndex() BEGIN IF NOT EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema=CurrentDatabase AND table_name = 'rtc_phototype' AND index_name = 'index_name') THEN  
         ALTER TABLE `rtc_Phototype` ADD INDEX index_name ( `imgtype` );
      END IF;  
     END;
    DELIMITER; CALL pro_AddIndex();
    Drop procedure pro_AddIndex; 

    插入语句判断是否存在,不存在则插入:

    insert into permission(id,name,navigation_id,parentid) select '130','印前审查',null,'1' from DUAL WHERE NOT EXISTS(SELECT * FROM permission WHERE id='130');
    insert into navigation(id,name,parent,path,seq_num,sub_sys,url) select '39','参数配置',11,'/3/11',1,3,null from DUAL WHERE NOT EXISTS(SELECT * FROM navigation WHERE id='39');
  • 相关阅读:
    Tensorflow 学习
    几种常见损失函数
    两人比赛先选后选谁获胜系列的动态规划问题
    LeetCode 全解(bug free 训练)
    局部敏感哈希LSH
    Annoy解析
    MCMC例子
    TinyBERT简单note
    ALBERT简单note
    求根号2, 网易的一道面试题
  • 原文地址:https://www.cnblogs.com/loong-hon/p/9957353.html
Copyright © 2020-2023  润新知