mysql 查询指定库中的所有表的所有字符串字段的内容是否包括某些字符

 前置语句

查询指定库中的所以表和所有字段：

SELECT
    a.TABLE_NAME '表名',
    ORDINAL_POSITION '顺序',
    COLUMN_NAME '字段',
    DATA_TYPE '类型',
    CHARACTER_OCTET_LENGTH '字节长',

IF(COLUMN_KEY = 'PRI', "√", "")'主键',

IF(
    EXTRA = 'auto_increment',
    "√",
    ""
)'自增长',

IF(IS_NULLABLE = 'YES', "√", "")'空',
 CHARACTER_SET_NAME '编码',
 COLUMN_DEFAULT '默认值',
 COLUMN_COMMENT '说明'
FROM
    information_schema. COLUMNS a join information_schema.TABLES b
on a.TABLE_NAME=b.TABLE_NAME
WHERE b.Table_SCHEMA='database_name'

 

DROP PROCEDURE IF EXISTS `search_alltbl_allcol_ofkeyword`;
DELIMITER ;;
CREATE DEFINER=`skip-grants user`@`skip-grants host` PROCEDURE `search_alltbl_allcol_ofkeyword`()
BEGIN
        declare sqlStr varchar(200) default '';  
    declare TABLENAME,COLUMNNAME,DATATYPE varchar(20);  

    declare done int default false;  
    declare cur cursor for select a.TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM
            information_schema. COLUMNS a join information_schema.TABLES b
            on a.TABLE_NAME=b.TABLE_NAME
            WHERE b.Table_SCHEMA='getgoodwebsite';  
    declare continue HANDLER for not found set done = true;        
    open cur;  
    repeat  
    fetch cur into TABLENAME,COLUMNNAME,DATATYPE;  
    if not done then  
                if DATATYPE='varchar' THEN
                        #set @sqlStr='select * from '+TABLENAME+' where '+COLUMNNAME+' = ?';  
                        select DATATYPE;
                        #PREPARE stmt FROM @sqlStr;
                        #EXECUTE stmt USING @keyWord; 
                        #deallocate prepare stmt; 
                end if;
    end if;  
    until done end repeat;  
    close cur;  
END
;;
DELIMITER ;

 存储过程：

DROP PROCEDURE IF EXISTS `search_alltbl_allcol_ofkeyword`;
DELIMITER ;;
CREATE DEFINER=`skip-grants user`@`skip-grants host` PROCEDURE `search_alltbl_allcol_ofkeyword`()
BEGIN
        declare sqlStr varchar(200) default '';  
    declare TABLENAME,COLUMNNAME,DATATYPE varchar(50);  
        
    declare done int default false;  
    declare cur cursor for select a.TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM
            information_schema. COLUMNS a join information_schema.TABLES b
            on a.TABLE_NAME=b.TABLE_NAME
            WHERE b.Table_SCHEMA='数据库名';  
    declare continue HANDLER for not found set done = true;        
    open cur;  
    repeat  
    fetch cur into TABLENAME,COLUMNNAME,DATATYPE;  
    if not done then  
                if DATATYPE='varchar' or DATATYPE='text'  or DATATYPE='mediumtext' or DATATYPE='longtext'  or DATATYPE='char' THEN
                        set @keyWord:='敏感词汇';
                        set sqlStr=concat('select count(*) into @recordcount  from `',TABLENAME,'` where `',COLUMNNAME,'` like ''%',@keyWord,'%''');  
                        #select sqlStr;
                        set @sqlcounts := sqlStr;  
                        prepare stmt FROM @sqlcounts;
                        execute stmt; 

                        deallocate prepare stmt; 
                        
                        if @recordcount>0 THEN
                            SELECT concat(@sqlcounts);
                        end IF;
                end if;
    end if;  
    until done end repeat;  
    close cur;    
END
;;
DELIMITER ;

 

问题1、

报错：The user specified as a definer ('skip-grants user'@'skip-grants host') does not exist

grant all privileges on *.* to 'skip-grants user'@'skip-grants host' identified by "."