在mySQL的整个表中搜索字符串 [英] Search a whole table in mySQL for a string

问题描述

我正在尝试在mySQL的整个表中搜索字符串.

I'm trying to search a whole table in mySQL for a string.

我想搜索表的所有字段和所有主菜，返回包含指定文本的每个完整条目.

I want to search all fields and all entrees of a table, returning each full entry that contains the specified text.

我不知道如何轻松地搜索多个字段；这是详细信息:

I can't figure out how to search multiple fields easily; here are the details:

该表是客户端".它具有大约30个字段和800个条目，太多了以至于无法在浏览器中一次全部显示.我想搜索一个名称(例如"Mary")，但是它可以在shipping_name field或billing_name field或email字段中，等等.

The table is "clients". It has about 30 fields and 800 entries, too much to show all at once in a browser. I would like to search for a name (i.e. "Mary"), but it could be in the shipping_name field or the billing_name field, or the email field, etc.

我想在所有字段中搜索包含字符串"Mary"的所有条目.我认为这应该可行，但不可行:

I would like to search all fields for any entries that contain the string "Mary". This is what I think should work but doesn't:

SELECT * FROM `clients` IN 'Mary'

推荐答案

尝试如下操作:

SELECT * FROM clients WHERE CONCAT(field1, '', field2, '', fieldn) LIKE "%Mary%"

您可能希望查看SQL文档，以获取有关字符串运算符和正则表达式的其他信息.

You may want to see SQL docs for additional information on string operators and regular expressions.

NULL字段可能存在一些问题，因此以防万一，您可能想使用IFNULL(field_i, '')而不是仅仅使用field_i

There may be some issues with NULL fields, so just in case you may want to use IFNULL(field_i, '') instead of just field_i

区分大小写:您可以使用不区分大小写的排序规则或类似的方法:

Case sensitivity: You can use case insensitive collation or something like this:

... WHERE LOWER(CONCAT(...)) LIKE LOWER("%Mary%")

仅搜索所有字段:我相信没有一种方法可以使SQL查询在没有显式声明要搜索的字段的情况下搜索所有字段.原因是存在关系理论数据库和用于处理关系数据的严格规则(诸如关系代数或codd代数；它们是SQL的来源)，理论上也不允许仅搜索所有字段"之类的事情.当然，实际行为取决于供应商的具体实现.但是在通常情况下是不可能的.为了确保这一点，请检查SELECT运算符语法(准确地说是WHERE部分).

Just search all field: I believe there is no way to make an SQL-query that will search through all field without explicitly declaring field to search in. The reason is there is a theory of relational databases and strict rules for manipulating relational data (something like relational algebra or codd algebra; these are what SQL is from), and theory doesn't allow things such as "just search all fields". Of course actual behaviour depends on vendor's concrete realisation. But in common case it is not possible. To make sure, check SELECT operator syntax (WHERE section, to be precise).

这篇关于在mySQL的整个表中搜索字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！