• 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');
  • 相关阅读:
    单元测试之block
    (转)CBCentralManager Class 的相关分析
    (转)iOS蓝牙调用的一般流程
    facebook pop 学习笔记
    GitHub 上值得关注学习的 iOS 开源项目
    关于block以及__bridge的一些笔记
    你的iOS静态库该减肥了
    iOS--消息推送后方法回调情况(简)
    UINavgation日常小bug-有兴趣的朋友可以看看
    UIDynamic(一)
  • 原文地址:https://www.cnblogs.com/loong-hon/p/9957353.html
Copyright © 2020-2023  润新知