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

问题描述

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

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

我不知道如何轻松搜索多个字段；详情如下:

表是客户".它有大约 30 个字段和 800 个条目，太多了，无法在浏览器中一次全部显示.我想搜索一个名字(即玛丽")，但它可能在 shipping_name 字段 或 billing_name 字段 或 email字段等

我想在所有字段中搜索包含字符串Mary"的任何条目.这是我认为应该有效但无效的方法:

SELECT * FROM `clients` IN 'Mary'

解决方案

试试这个:

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

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

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

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

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

只搜索所有字段:我相信没有办法让 SQL 查询在不明确声明要搜索的字段的情况下搜索所有字段.原因是存在关系理论数据库和操作关系数据的严格规则(例如关系代数或 codd 代数；这些是 SQL 的来源)，并且理论不允许诸如只搜索所有字段"之类的事情.当然，实际行为取决于供应商的具体实现.但在一般情况下，这是不可能的.为了确保，请检查 SELECT 运算符语法(WHERE 部分，准确地说).

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:

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.

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'

解决方案

Try something like this:

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

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

Edit: 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%")

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屋！