mysql 查找数据库里面字段的id是否存在 可以限定表和字段

DELIMITER $$


USE `awebprice`$$


DROP PROCEDURE IF EXISTS `p_search_value`$$


CREATE DEFINER=`dbn_admin`@`%` PROCEDURE `p_search_value`(
IN p_value VARCHAR(255)
,IN p_tablelike VARCHAR(255) 
,IN p_columnlike VARCHAR(255) 
)
BEGIN
DECLARE var_table VARCHAR(255);
DECLARE var_column VARCHAR(255);
DECLARE var_sql VARCHAR(1000);
DECLARE var_done INT DEFAULT FALSE;
DECLARE cur_Search CURSOR FOR 
SELECT table_name,column_name,
CONCAT('set @aa =(select 1  from `',table_name,'` where `',column_name,'`=',


(CASE WHEN data_type IN ('int','bigint') THEN p_value ELSE CONCAT('''',p_value,'''') END)
,' limit 1)') AS sq
FROM information_schema.`COLUMNS`
WHERE table_schema=DATABASE() AND data_type IN ('varchar','int','bigint')
AND table_name LIKE CONCAT('%',IFNULL(p_tablelike,'_'),'%')
AND column_name LIKE CONCAT('%',IFNULL(p_columnlike,'_'),'%')
;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done = TRUE;
DROP TEMPORARY TABLE IF EXISTS tmp_to_save_result;
CREATE TEMPORARY TABLE tmp_to_save_result(tb VARCHAR(255),col VARCHAR(255));
 OPEN cur_Search;
 read_loop:LOOP
   FETCH cur_Search INTO var_table,var_column,var_sql;
 
    
   IF var_done THEN
     LEAVE read_loop;
   END IF;
   


   SET @p_sql=var_sql;
   PREPARE stmt FROM @p_sql;
   EXECUTE stmt; 
  
   IF(FOUND_ROWS()=1) THEN
INSERT INTO tmp_to_save_result VALUE(var_table,var_column);
   END IF;
   DEALLOCATE PREPARE stmt;  
   END LOOP;
 CLOSE cur_Search;
SELECT tb,col FROM tmp_to_save_result;
    END$$


DELIMITER ;
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符 “速评一下”
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页