在 mysql 数据库中搜索字符串的最快方法 [英] Fastest way to search a string in a mysql database

问题描述

在 mysql 数据库中搜索字符串而不知道我可能会在哪个表或列中找到它时，我通常求助于

When searching a string in an mysql database without knowing in what table or column I might find it, I usually resort to

 mysqldump --extended-insert=FALSE --complete-insert=TRUE dbname|grep SomeString

这会转储大量不需要的数据，忽略为该确切目的发明的indeces，并使结果本地化变得相当困难.有没有更方便、更高效的方式?(除了安装第 3 方软件.)

That dumps a lot of unneeded data, ignores indeces invented for that exact purpose, and makes it rather hard to localize the results. Is there a more convenient and more performant way? (Except installing 3rd party software.)

关于重复问题:正如评论指出的那样，我不限于 SQL 查询，但会接受任何解决方案 - 可能是 bash 脚本、我可能没有见过的某些 CLI 函数，或者按照建议对 DB 文件进行 grepping.

About duplicate questions: As a comment pointed out, I am not restricted to SQL queries, but will accept any solution - may it be a bash script, some CLI function I might have not seen, or grepping the DB file as suggested.

推荐答案

我发现了一个 find 命令，它列出了包含特定字符串的文件:

I found a find command that lists the file which contains a specific string:

find [path of data folder of database]/*.ibd -type f -exec grep -i '[search string]' {} +

找到时的输出:

Binary file [path]/[table].ibd matches

未找到时不输出任何内容.

Doesn't outputs anything when not found.

意思:

find     : linux find command
path     : the path pointing to the database folder under mysql/data 
           directory which contains the table's ibd files
-type f  : only search for files
-exec    : execute this while listing

    grep -i  : case insensitive text search
    string   : string to search for

对于其他人，请阅读:

https://serverfault.com/a/343177

这里的用法相同:

http://www.valleyprogramming.com/blog/linux-find-grep-commands-exec-combine-search

这篇关于在 mysql 数据库中搜索字符串的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！