如何使用MySQL / MariaDB查询数据库所有表的所有列?

Question

我想在给定数据库的所有表的所有列中查找一个字符串值。

我设法编写了此查询以获取mariadb数据库中的所有列和表名称：

SELECT TABLE_NAME , COLUMN_NAME
FROM information_schema.`COLUMNS`
WHERE TABLE_NAME IN
(SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'my_database')

但是我还没有设法查询上面找到的所有列。

我想搜索一个字符串，比如"mystring"，带有 LIKE 运算符。 例如

SELECT *
FROM TABLE_NAME.COLUMN_NAME
WHERE COLUMN_NAME LIKE '%mystring%'

版：

正如一些人建议我查看这篇文章，但我仍然无法在所有表格中搜索它。

我尝试了以下方法：

SET @Pattern = '%mystring%';
SELECT @q := concat ('SELECT * FROM table1 WHERE concat (', group_concat(column_name), ', "") LIKE "', @Pattern, '"')
FROM information_schema.columns c
WHERE table_name = 'table1';
PREPARE st FROM @q;
EXECUTE st;

但是我不知道如何合并两个片段以将table1更改为SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'my_database'

Answer 1

使用过程，但我认为这不是一个好主意。

DROP PROCEDURE IF EXISTS query_all;
CREATE PROCEDURE query_all(in _key VARCHAR(256))
BEGIN
DROP TABLE IF EXISTS result;
CREATE TEMPORARY TABLE IF NOT EXISTS result (result VARCHAR(4000) NOT NULL);
BEGIN
DECLARE s INT DEFAULT 0;
DECLARE t VARCHAR (256);
DECLARE c VARCHAR (256);
DECLARE _CURSOR CURSOR FOR SELECT
TABLE_NAME,
COLUMN_NAME
FROM
information_schema.`COLUMNS`
WHERE
TABLE_NAME IN (
SELECT
TABLE_NAME
FROM
information_schema. TABLES
WHERE
TABLE_SCHEMA = 'my_database'
);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
open _CURSOR;
fetch _CURSOR into t,c;
while s<>1 do
SET @sqlstr = concat("INSERT INTO result (`result`)SELECT " ,c," from ",t," where ",c," like '%",_key,"%'"); 
PREPARE p_sqlstr FROM @sqlstr;
EXECUTE p_sqlstr;
DEALLOCATE PREPARE p_sqlstr;
fetch _CURSOR into t,c;
END WHILE;
CLOSE _CURSOR;
SELECT * FROM result;
END;
END;

CALL query_all('mystring');

我的数据库是mysql5.5.62，我更改为5.5.60-MariaDB，以下过程可能适合您。

DROP PROCEDURE IF EXISTS query_all;
CREATE PROCEDURE query_all(in _key VARCHAR(256))
BEGIN
DROP TABLE IF EXISTS result;
CREATE TEMPORARY TABLE IF NOT EXISTS result (result VARCHAR(4000) NOT NULL);
BEGIN
DECLARE s INT DEFAULT 0;
DECLARE t VARCHAR (256);
DECLARE c VARCHAR (256);
DECLARE _CURSOR CURSOR FOR SELECT
TABLE_NAME,
COLUMN_NAME
FROM
information_schema.`COLUMNS`
WHERE
TABLE_SCHEMA = 'my_database';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
open _CURSOR;
fetch _CURSOR into t,c;
while s<>1 do
SET @sqlstr = concat("INSERT INTO result(`result`)SELECT " ,c," from my_database.`",t,"` where ",c," like '%",_key,"%'"); 
PREPARE p_sqlstr FROM @sqlstr;
EXECUTE p_sqlstr;
DEALLOCATE PREPARE p_sqlstr;
fetch _CURSOR into t,c;
END WHILE;
CLOSE _CURSOR;
SELECT * FROM result;
END;
END;

CALL query_all('3');