iT邦幫忙

6

【MySQL】 全資料庫表格搜尋符合字串的欄位

答應幫忙寫一個MySQL版本的全資料庫搜尋某字串
請問如何在 MySQL 資料庫中找到某字串?


情境:

當有一天想要查詢資料庫所有表格中,是不是有符合某資料的欄位
並且把它們都查詢出來。


主要邏輯:

  1. 使用INFORMATION_SCHEMA.COLUMNS撈取所有的表格、欄位資料
  2. 內層使用GROUP_CONCAT組合出where {欄位名稱A} = {搜尋的值} or {欄位名稱B} = {搜尋的值} or {欄位名稱C} = {搜尋的值} .....的條件
  3. 外層使用GROUP_CONCAT加上union all來拼接一次性查詢結果
  4. 注意因為GROUP_CONCAT會有長度限制,需要用@@local.group_concat_max_len來調整大小。可以參考
    mysql - SQL Stored Procedure - variable too short - Stack Overflow
  5. 假如不需要查詢系統表格要加上where TABLE_SCHEMA not in ('information_schema','performance_schema','mysql','sys' )
  6. 使用動態拼接字串SQL並Execute。
  7. 這版特色是可以只返回一個結果集搜尋全表格欄位。
  8. 假如要搜尋整數改成 set @strSearch := N'132';就可以。

Script:

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; 

DDL測試資料:

CREATE TABLE Table0 (`name` varchar(32), `age` int, `city` varchar(6))   
ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;                                                                   
INSERT INTO Table0(`name`, `age`, `city`)VALUES(N'ITMan0', 31, 'Taipei'),(N'ITMan1', 32, 'Taipei');
CREATE TABLE Table1 (`name` varchar(32), `age` int, `city` varchar(6))
ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;   
INSERT INTO Table1(`name`, `age`, `city`)VALUES(N'ITMan1', 31, 'Taipei'),(N'ITMan2', 32, 'Taipei');
CREATE TABLE Table2 (`name` varchar(32), `age` int, `city` varchar(6))
ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;   
INSERT INTO Table2(`name`, `age`, `city`)VALUES(N'ITMan2', 31, '高雄'),(N'ITMan3', 32, 'Taipei');
CREATE TABLE Table3 (`name` varchar(32), `age` int, `city` varchar(6))
ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;   
INSERT INTO Table3(`name`, `age`, `city`)VALUES(N'ITMan3', 31, '台北'),(N'ITMan4', 32, 'Taipei');
CREATE TABLE Table4 (`name` varchar(32), `age` int, `city` nvarchar(6))
ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;   
INSERT INTO Table4(`name`, `age`, `city`)VALUES(N'ITMan3', 31, '台北'),(N'ITMan5', 0, 'Taipei');

得到結果:


線上測試連結


假如想要sql-server版本可以看 @純真的人 大大的文章,強烈推薦!
MSSQL如何跨庫多表查詢某個關鍵字在某個欄位

假如想要oracle版本可以看我之前寫的文章,這版本需要C#配合,之後會再優化成單純SP版本。小程式-Oracle&C#全表格查詢搜尋單一值


1
純真的人
iT邦研究生 2 級 ‧ 2018-07-06 18:41:20

哇~好厲害~~~/images/emoticon/emoticon34.gif

暐翰 iT邦大師 9 級‧ 2018-07-06 18:43:36 檢舉

純真的人 大大
我是看到大大更新的文章
才有靈感來寫這一個版本的 /images/emoticon/emoticon12.gif

真心覺得你們兩個就可以處理所有版上SQL的問題了XD

0
terryliu
iT邦新手 5 級 ‧ 2018-07-09 14:19:48

暐翰大大:
請問一下,上述的 Script 要在什麼樣的環境下執行?
我目前是 CentOS 7 的主機,用 Xshell SSH 登入進去
先進入 MySQL Console 將您給的Script貼上,這樣正確嗎?
(我有將第二行的set @strSearch := N'ITMan'; 改成 set @strSearch := N'聖誕卡';)

還是我要將 Script 用什麼方式打包成執行檔之類的?請您再賜教一下,非常感謝!

看更多先前的回應...收起先前的回應...
純真的人 iT邦研究生 2 級‧ 2018-07-09 20:23:04 檢舉

我是用mysql資料庫工具下指令的@@..
MySQL Workbench 6.3 CE

你可以去下載適合你的OS工具~
https://dev.mysql.com/downloads/workbench/?utm_source=tuicool

暐翰 iT邦大師 9 級‧ 2018-07-09 21:55:24 檢舉

不用打包成執行檔
這是給DBA查資料用的script
純真的人 大大說的一樣,使用有查詢功能的GUI管理程式就可以

暐翰 大大,我發生了一個困難:

搜尋英文字串時是OK的;但搜尋中文字就出現 Error:
例如:
第二行
set @strSearch := N'Video'; ==>這樣 OK
set @strSearch := N'影音光碟'; ==>這樣不行

錯誤如下:

[SQL]

PREPARE stmt1 FROM @sql;
[Err] 1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

看起來是 Collation 錯誤,是否可再賜教?(感謝!感謝!)

也謝謝 純真的人 大大,我目前是用 Navicate 測試,等一下就來試試 MySQL WorkBench!謝謝!

暐翰 iT邦大師 9 級‧ 2018-07-11 13:08:17 檢舉

terryliu

表格CHARACTER、COLLATE是UTF-8嗎?
有中文資料的表格記得要使用UTF-8
舉例:

CREATE TABLE Table0 (`name` varchar(32), `age` int, `city` varchar(6))
ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

DB Fiddle - SQL Database Playground

暐翰 大大,我進一步測試,結果好像不如預期,有一些不合條件的也進來了:

請看圖片

我先用 Console 執行您的指令,建了四個 Table 在原有的資料庫中,
再用 Query 的功能執行您的 Script,前面是如您的範例沒錯;但後面也出現了一些不合條件的答案...

如果您有空,是否可以幫忙解惑?

純真的人 iT邦研究生 2 級‧ 2018-07-11 22:53:46 檢舉

你可以下正常sql去檢查欄位是否有符合~

select term_id
from wppe_aws_index
where term_id = 'ITMan3'

若沒有出現的話~就要改善SQL有判斷錯誤的問題~

暐翰 iT邦大師 9 級‧ 2018-07-12 13:16:01 檢舉

terryliu
對,麻煩使用純真大大的語法查一下資料

暐翰 iT邦大師 9 級‧ 2018-07-13 08:50:16 檢舉

找到問題在"數字跟字串的比較",可以參考最近的文章
【MySQL】 (20 = "20XXX" ) 結果會是True - iT 邦幫忙::一起幫忙解決難題,拯救 IT 人的一天

舉例:
當0跟N"ITMAN3"比較是結果會是True!
因為MySQL檢查ITMAN3檢查字串不是數字型態
所以比較的時候會變成 0=0

線上測試例子


我更新在下面留言

謝謝 純真的人 大大
我測試了 MySQL WorkBench 了,發現它不支援比較舊的 MySQL,
我的環境中有 MySQL 5.0 的及 MySQL 5.6 的,5.0 的不支援...
如圖

暐翰 iT邦大師 9 級‧ 2018-07-13 10:14:08 檢舉

terryliu 麻煩更新一下Script,有問題再跟我說。

純真的人 iT邦研究生 2 級‧ 2018-07-13 13:30:58 檢舉

terryliu
我查了MySQL WorkBench簡介~
的確要MySQL5.1以上~
http://kidspeak.pixnet.net/blog/post/33086634-mysql-workbench-%E7%B0%A1%E4%BB%8B

0
暐翰
iT邦大師 9 級 ‧ 2018-07-13 10:01:36

【解決BUG】

暐翰 大大,我進一步測試,結果好像不如預期,有一些不合條件的也進來了:

請看圖片

我先用 Console 執行您的指令,建了四個 Table 在原有的資料庫中,
再用 Query 的功能執行您的 Script,前面是如您的範例沒錯;但後面也出現了一些不合條件的答案...

如果您有空,是否可以幫忙解惑?

原因:

找到問題在"數字跟字串的比較",可以參考最近的文章
【MySQL】 (20 = "20XXX" ) 結果會是True - iT 邦幫忙::一起幫忙解決難題,拯救 IT 人的一天

舉例:
當0跟N"ITMAN3"比較是結果會是True!
因為MySQL檢查ITMAN3檢查字串不是數字型態
所以比較的時候會變成 0=0

線上測試例子


解決方式:
判斷變數是字串還是整數

set @Condition := (select 
case when BINARY (@Search * 1) = BINARY @Search then
      '"bigint","decimal","double","float","int","smallint","tinyint"'
else '"char","varchar","nvarchar"'
end );

全部Script:

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; 

線上測試連結

看更多先前的回應...收起先前的回應...

To 暐翰大大:

奇怪,出現錯誤訊息:
[SQL]
PREPARE stmt1 FROM @sql;
[Err] 1146 - Table 'WPnWoo_Bak.Table0' doesn't exist

我原本是在 WPnWoo_Bak 資料庫中,用您的指令加了四個 Table
剛剛把四個 Table 刪了,就不能執行了

暐翰 iT邦大師 9 級‧ 2018-07-13 12:27:44 檢舉

原因是INFORMATION_SCHEMA.COLUMNS是統計表
會有時間延遲,所以當你刪除後統計表還是存在
才導致查詢到不存在的表

我下午再改一版
來判斷表格是否存在。

To 暐翰大大:
謝謝您!我有想到是因為類似「快取」之類的狀況,但找不出在哪裡,謝謝您!

另外,因為我原本是用已經有的 WordPress 備份資料庫中來測試,尋找英文字串沒問題;但找中文的字串則不行,會出現:

PREPARE stmt1 FROM @sql;
[Err] 1267 - Illegal mix of collations (latin1swedishci,IMPLICIT) and (utf8generalci,COERCIBLE) for operation '='

找了一下網路文章,說是 Character Set 錯誤,用指令看了,如下:
mariadb> show VARIABLES LIKE 'character_set_%';

+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set

好像是 character_set_server 錯了,請問在哪裡修正呢?

謝謝賜教!

自問自答一下:

我用的是 Navicate,在連接 DB 時,編碼不要用 Auto,要指定它使用 UTF8,就可以正常搜尋中文字了...

但我想,把 MariaDB 的預設 Encoding 改 UTF8 可能比較好!
目前還在找資料...

大概是這樣修改:

  1. 修改 /etc/my.cnf.d/client.cnf

在 [client] 區段中加入:
default-character-set=UTF8

  1. 修改 /etc/my.cnf.d/server.cnf
    在 [mysqld] 區段中加入:
    character-set-server=UTF8

  2. 重啟 MariaDB 並進入 MariaDB Console 檢查:
    mysql -u root -p
    輸入 root 密碼
    下指令: Show Variables Like 'Character%';
    看是否都是 UTF-8 編碼,如果 OK 就表示改好了...

但這應該是不溯及既往,新增的才會用 UTF-8 編碼(猜測)

暐翰 iT邦大師 9 級‧ 2018-07-16 19:32:26 檢舉
0
terryliu
iT邦新手 5 級 ‧ 2018-07-17 17:59:49

To 暐翰大大:

請問上面提到的「INFORMATION_SCHEMA.COLUMNS」統計表有時間延遲的問題,是否可以協助排除?
(判斷表格是否存在...)

看更多先前的回應...收起先前的回應...
暐翰 iT邦大師 9 級‧ 2018-07-17 23:35:24 檢舉

目前我沒辦法模擬出你說的情況
統計表刪除是即時更新
想尋問你刪除表格後有下commit嗎

測試DEMO

暐翰大大:

我的情形如下↓↓
資料庫有:
information_schema
mysql
performance_schema
TestQuery
WPnWoo_Bak

TestQuery 中,只有用您的範例建出的 Table0 到 Table4
WPnWoo_Bak 中,有用您的範例建出的 Table0 到 Table4 及其他 114 個資料表(WordPress建立的)

當 TestQuery 及 WPnWoo 都有 Table0 到 Table4 時,您的 Query指令 Script 可以正常動作;但只要我在這兩個 DB 中,刪掉任一邊的 Table0 到 Table4 其中一個,Script 就不 work 了... (一定要兩邊都刪,例如 Table0 兩邊都刪才行)

暐翰 iT邦大師 9 級‧ 2018-07-19 18:45:32 檢舉

目前查MySQL沒有對應的檢查方式

謝謝暐翰大大,至少目前已經是可以用了!
我想辦法把畫面錄影下來試試!

暐翰 iT邦大師 9 級‧ 2018-07-21 19:19:23 檢舉

/images/emoticon/emoticon12.gif 有心了!

暐翰大大:

今天想要模擬出問題的狀況並錄下畫面;但意外發現語法有不支援 5.0 的現象,請問這有解嗎?

Error Message 是:

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' as 表格名稱 , concat( '

,GROUP_CONCAT(DISTINCT conca' at line 5

好像是第五行的指令不支援舊版...

暐翰 iT邦大師 9 級‧ 2018-07-24 09:10:00 檢舉

GROUP_CONCAT() v4.1 就支持才對

我要留言

立即登入留言