用 SQL 对 MySQL 做全库搜索

已知某个关键词，但是不知道这个关键词在哪个数据表中。怎么办？set @@pattern = 'abcdefg';select CONCAT('select * from `', tbl.TABLE_SCHEMA, '`.`', tbl.table_name, '` where `', col.COLUMN_NAME, '` like "%', @pattern, '%"', ' limit 20;

maray

3672人浏览 · 2021-08-29 23:13:31

maray · 2021-08-29 23:13:31 发布

已知某个关键词，但是不知道这个关键词在哪个数据表中。怎么办？


set @@pattern = 'abcdefg';

select CONCAT('select * from `', tbl.TABLE_SCHEMA, '`.`', tbl.table_name, '` where `', col.COLUMN_NAME, '` like "%', @pattern, '%"', ' limit 20;') query
from information_schema.TABLES tbl inner join information_schema.COLUMNS col
ON tbl.TABLE_CATALOG = col.TABLE_CATALOG AND tbl.TABLE_SCHEMA = col.TABLE_SCHEMA AND tbl.TABLE_NAME = col.TABLE_NAME
WHERE tbl.TABLE_SCHEMA = 'test'
limit 100;

它会输出全部检索数据表的 SQL，例如：

+----------------------------------------------------------------------------------------------------------+
| query                                                                                                    |
+----------------------------------------------------------------------------------------------------------+
| select * from `test`.`base_browser` where `id` like "%15692641%" limit 20;                              |
| select * from `test`.`base_browser` where `browser` like "%15692641%" limit 20;                         |
| select * from `test`.`base_browser` where `browser_version` like "%15692641%" limit 20;                 |
| select * from `test`.`base_crawler_types` where `id` like "%15692641%" limit 20;                        |
| select * from `test`.`base_crawler_types` where `name` like "%15692641%" limit 20;                      |
| select * from `test`.`base_crawlers` where `id` like "%15692641%" limit 20;                             |
| select * from `test`.`base_crawlers` where `user_agent` like "%15692641%" limit 20;                     |
| select * from `test`.`base_crawlers` where `name` like "%15692641%" limit 20;                           |
| select * from `test`.`base_crawlers` where `org_name` like "%15692641%" limit 20;                       |
| select * from `test`.`base_device_brand` where `id` like "%15692641%" limit 20;                         |
| select * from `test`.`base_device_brand` where `name` like "%15692641%" limit 20;                       |
| select * from `test`.`base_device_lang` where `id` like "%15692641%" limit 20;                          |
| select * from `test`.`base_device_lang` where `name` like "%15692641%" limit 20;                        |
| select * from `test`.`base_device_model` where `id` like "%15692641%" limit 20;                         |
| select * from `test`.`base_device_model` where `model` like "%15692641%" limit 20;                      |
| select * from `test`.`base_device_model` where `name` like "%15692641%" limit 20;                       |
| select * from `test`.`base_device_model` where `model_dop_info` like "%15692641%" limit 20;             |
| select * from `test`.`base_device_technical_data_1` where `id` like "%15692641%" limit 20;              |
| select * from `test`.`base_device_technical_data_1` where `display_size` like "%15692641%" limit 20;    |
| select * from `test`.`base_device_technical_data_2` where `id` like "%15692641%" limit 20;              |
| select * from `test`.`base_device_technical_data_2` where `resolution` like "%15692641%" limit 20;      |
| select * from `test`.`base_device_technical_data_3` where `id` like "%15692641%" limit 20;              |
| select * from `test`.`base_device_technical_data_3` where `pointing_method` like "%15692641%" limit 20; |
| select * from `test`.`base_device_technical_data_4` where `id` like "%15692641%" limit 20;              |
+----------------------------------------------------------------------------------------------------------+