MySQL-在所有資料中找到特定資料所在的資料表

2019 年 6 月 19 日 
SET @@local.group_concat_max_len=100000000;
set @Search := N'高雄';#輸入要搜尋的字串
## 判斷變數類型
set @Condition := (select 
case when BINARY (@Search * 1) = BINARY @Search then
      '"bigint","decimal","double","float","int","smallint","tinyint"'
else '"char","varchar","nvarchar"'
end );
set @sql := (
select GROUP_CONCAT(DISTINCT `sql` SEPARATOR ' union all ') from (
  SELECT 
      concat(
        N'select \'',TABLE_NAME,'\' as `表格名稱` , concat( '
        
        ,GROUP_CONCAT(DISTINCT concat(' case when count(case when `',COLUMN_NAME, '` = N\'',@Search,'\' then 1 end) > 0 then  '
                                      ,'"',COLUMN_NAME,'  " else "" end'
                                     )
                    ORDER BY COLUMN_TYPE asc 
                    SEPARATOR ' , ')        
        ,' ) as `符合欄位` from ',TABLE_NAME,'  where '
        ,GROUP_CONCAT(DISTINCT concat('`',COLUMN_NAME, '` = N\'',@Search,'\'')
                    ORDER BY COLUMN_TYPE asc 
                    SEPARATOR ' or ') 
      )  as `sql`
  FROM INFORMATION_SCHEMA.COLUMNS
  where TABLE_SCHEMA not in  ('information_schema','performance_schema','mysql','sys' )
  and  @Condition like concat('%"',DATA_TYPE,'"%')
  group by TABLE_NAME) T
);

SET @sql = CONCAT("select *
                    from (
                   "
                   ,@sql
                   ,") T
                    where `符合欄位` <> ''
                    ");         

PREPARE stmt1 FROM @sql; 
EXECUTE stmt1; 
DEALLOCATE PREPARE stmt1;