mysql - 如何使用 MySQL/MariaDB 查询数据库所有表的所有列?
问题描述
我想在给定数据库中所有表的所有列中查找字符串值。
我设法编写了这个查询来获取 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')
但我还没有设法查询上面找到的所有列。
我想用 LIKE 运算符搜索一个字符串,比如“mystring”。例如
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'
解决方案
使用程序,但我认为这不是一个好主意..
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');
推荐阅读
- php - 解析错误:第 7 行 C:\xampp\htdocs\script\includes\application_top.php 中的语法错误、意外的 ','、期望 ')'
- javascript - 如何在 Angular 2 项目中使用脚本标签
- javascript - 无法使用 react-redux 更新状态
- bash - bash循环覆盖下一个文件
- jsf - 用于 p:selectOneMenu 转换器的应用范围 bean
- java - 在应用中间安装apk - Android Studio
- android - 如何显示所有聊天 Mesibo Android SDK
- git - git checkout 文件夹到底是做什么的?
- asp.net - 在 Redis 中存储临时块上传的文件
- python - python中张量切片中的值总和