在MySQL数据库的每个表的字段中搜索文本

我想从一个MySQL数据库的所有表的所有字段搜索一个给定的字符串，可能使用语法为:

SELECT * FROM * WHERE * LIKE '%stuff%'

有可能做这样的事情吗?

当前回答

这个解决方案 a)只有MySQL，不需要其他语言，并且 b)返回SQL结果，准备处理!

#Search multiple database tables and/or columns
#Version 0.1 - JK 2014-01
#USAGE: 1. set the search term @search, 2. set the scope by adapting the WHERE clause of the `information_schema`.`columns` query
#NOTE: This is a usage example and might be advanced by setting the scope through a variable, putting it all in a function, and so on...

#define the search term here (using rules for the LIKE command, e.g % as a wildcard)
SET @search = '%needle%';

#settings
SET SESSION group_concat_max_len := @@max_allowed_packet;

#ini variable
SET @sql = NULL;

#query for prepared statement
SELECT
    GROUP_CONCAT("SELECT '",`TABLE_NAME`,"' AS `table`, '",`COLUMN_NAME`,"' AS `column`, `",`COLUMN_NAME`,"` AS `value` FROM `",TABLE_NAME,"` WHERE `",COLUMN_NAME,"` LIKE '",@search,"'" SEPARATOR "\nUNION\n") AS col
INTO @sql
FROM `information_schema`.`columns`
WHERE TABLE_NAME IN
(
    SELECT TABLE_NAME FROM `information_schema`.`columns`
    WHERE
        TABLE_SCHEMA IN ("my_database")
        && TABLE_NAME IN ("my_table1", "my_table2") || TABLE_NAME LIKE "my_prefix_%"
);

#prepare and execute the statement
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

2014-01-29 17:59:29

其他回答

这个解决方案 a)只有MySQL，不需要其他语言，并且 b)返回SQL结果，准备处理!

#Search multiple database tables and/or columns
#Version 0.1 - JK 2014-01
#USAGE: 1. set the search term @search, 2. set the scope by adapting the WHERE clause of the `information_schema`.`columns` query
#NOTE: This is a usage example and might be advanced by setting the scope through a variable, putting it all in a function, and so on...

#define the search term here (using rules for the LIKE command, e.g % as a wildcard)
SET @search = '%needle%';

#settings
SET SESSION group_concat_max_len := @@max_allowed_packet;

#ini variable
SET @sql = NULL;

#query for prepared statement
SELECT
    GROUP_CONCAT("SELECT '",`TABLE_NAME`,"' AS `table`, '",`COLUMN_NAME`,"' AS `column`, `",`COLUMN_NAME`,"` AS `value` FROM `",TABLE_NAME,"` WHERE `",COLUMN_NAME,"` LIKE '",@search,"'" SEPARATOR "\nUNION\n") AS col
INTO @sql
FROM `information_schema`.`columns`
WHERE TABLE_NAME IN
(
    SELECT TABLE_NAME FROM `information_schema`.`columns`
    WHERE
        TABLE_SCHEMA IN ("my_database")
        && TABLE_NAME IN ("my_table1", "my_table2") || TABLE_NAME LIKE "my_prefix_%"
);

#prepare and execute the statement
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

2014-01-29 17:59:29

虽然这个问题已经很老了，但如果你使用的是mysql workbench 6.3，你可以这样做。(很可能它也适用于其他版本)

右键点击你的模式和“搜索表数据”，输入你的值，然后点击“开始搜索”。这是它。

2016-08-21 21:38:03

您可以查看information_schema模式。它包含所有表和表中所有字段的列表。然后，您可以使用从该表中获得的信息运行查询。

涉及的表包括SCHEMATA、tables和COLUMNS。有一些外键，这样您就可以在模式中准确地构建表的创建方式。

2009-03-12 17:00:19

HeidiSQL是一个实用可靠的工具，专为使用流行的MySQL服务器的web开发人员设计。

在HeidiSQL中，您可以按shift + ctrl + f，您可以在服务器上的所有表中找到文本。这个选项非常有用。

2016-02-13 06:36:55

MySQL工作台

这里有一些说明。