你当前正在访问 Microsoft Azure Global Edition 技术文档网站。 如果需要访问由世纪互联运营的 Microsoft Azure 中国技术文档网站，请访问 https://docs.azure.cn。
Applies to: ✅Microsoft Fabric✅Azure Data Explorer✅Azure Monitor✅Microsoft Sentinel
在多个表和列中搜索文本模式。
Note
If you know the specific tables and columns you want to search, it's more performant to use the union and where operators. 在搜索大量的表和列时，search 运算符可能很慢。
Syntax
[T|] search [kind=CaseSensitivity ] [in(TableSources)] SearchPredicate
Learn more about syntax conventions.
Parameters
| Name | 类型 | Required | Description |
|---|---|---|---|
| T | string |
The tabular data source to be searched over, such as a table name, a union operator, or the results of a tabular query. Can't be specified together with TableSources. | |
| CaseSensitivity | string |
一个标志，它控制所有 string 标量运算符（例如 has）在区分大小写方面的行为。 有效值为 default、case_insensitive、case_sensitive。 选项 default 和 case_insensitive 同义，因为默认行为是不区分大小写。 |
|
| TableSources | string |
要参与搜索的“带通配符的”表名的逗号分隔列表。 The list has the same syntax as the list of the union operator. Can't be specified together with tabular data source (T). | |
| SearchPredicate | string |
✔️ | 要对输入中的每个记录计算的布尔表达式。 如果它返回 true，则输出记录。 请参阅搜索谓词语法。 |
Note
If both tabular data source (T) and TableSources are omitted, the search is carried over all unrestricted tables and views of the database in scope.
搜索谓词语法
The SearchPredicate allows you to search for specific terms in all columns of a table. 应用于搜索词的运算符取决于术语中通配符星号（*）的存在和位置，如下表所示。
| Literal | Operator |
|---|---|
billg |
has |
*billg |
hassuffix |
billg* |
hasprefix |
*billg* |
contains |
bi*lg |
matches regex |
还可以将搜索限制为特定的列，查找完全匹配项而不是字词匹配项，或者按正则表达式进行搜索。 下表显示了其中每个案例的语法。
| Syntax | Explanation |
|---|---|
ColumnName:StringLiteral |
此语法可用于将搜索限制为特定的列。 默认行为是搜索所有列。 |
ColumnName==StringLiteral |
此语法可用于根据字符串值搜索列的完全匹配项。 默认行为是查找字词匹配项。 |
Columnmatches regexStringLiteral |
This syntax indicates regular expression matching, in which StringLiteral is the regex pattern. |
使用布尔表达式合并条件并创建更复杂的搜索。 例如，"error" and x==123 将搜索在任何列中具有字词 error 且在 123 列中具有值 x 的记录。
搜索谓词语法示例
| # | Syntax | 含义（等效的 where） |
Comments |
|---|---|---|---|
| 1 | search "err" |
where * has "err" |
|
| 2 | search in (T1,T2,A*) "err" |
并集 T1、T2、A * |其中 * 有 "err" |
|
| 3 | search col:"err" |
where col has "err" |
|
| 4 | search col=="err" |
where col=="err" |
|
| 5 | search "err*" |
where * hasprefix "err" |
|
| 6 | search "*err" |
where * hassuffix "err" |
|
| 7 | search "*err*" |
where * contains "err" |
|
| 8 | search "Lab*PC" |
where * matches regex @"\bLab.*PC\b" |
|
| 9 | search * |
where 0==0 |
|
| 10 | search col matches regex "..." |
where col matches regex "..." |
|
| 11 | search kind=case_sensitive |
所有字符串比较都区分大小写 | |
| 12 | search "abc" and ("def" or "hij") |
where * has "abc" and (* has "def" or * has hij") |
|
| 13 | search "err" or (A>a and A<b) |
where * has "err" or (A>a and A<b) |
Remarks
Unlike the find operator, the search operator doesn't support the following syntax:
-
withsource=：输出始终包含一$table个名为类型的string列，其值为从中检索每个记录的表名（如果源不是表，而是复合表达式，则为某些系统生成的名称）。 -
project=，project-smart：search运算符不支持自定义输出列的这些选项。 相反，它会自动为输出选择一组相关的列，这相当于运算符中project-smart选项检索find的列集。
Examples
The examples in this article use publicly available tables in the help cluster, such as the
StormEventstable in the Samples database.
The examples in this article use publicly available tables, such as the
Weathertable in the Weather analytics sample gallery. 可能需要修改示例查询中的表名称以匹配工作区中的表。
下面的示例演示如何执行全局术语搜索。 Search for the term Green in all the tables of the ContosoSales database.
The output finds records with the term Green as a last name or a color in the Customers, Products, and SalesTable tables.
search "Green"
Output
| $table | CityName | ContinentName | CustomerKey | 教育 | FirstName | Gender | LastName |
|---|---|---|---|---|---|---|---|
| Customers | Ballard | 北美 | 16549 | Partial College | Mason | M | Green |
| Customers | Bellingham | 北美 | 2070 | High School | Adam | M | Green |
| Customers | Bellingham | 北美 | 10658 | Bachelors | Sara | F | Green |
| Customers | Beverly Hills | 北美 | 806 | Graduate Degree | Richard | M | Green |
| Customers | Beverly Hills | 北美 | 7674 | Graduate Degree | James | M | Green |
| Customers | Burbank | 北美 | 5241 | Graduate Degree | Madeline | F | Green |
以下示例演示如何执行条件全局术语搜索。 Search for records that contain the term Green and one of either terms Deluxe or Proseware in the ContosoSales database.
search "Green" and ("Deluxe" or "Proseware")
Output
| $table | ProductName | Manufacturer | ColorName | ClassName | ProductCategoryName |
|---|---|---|---|---|---|
| Products | Contoso 8GB 时钟和无线电 MP3 播放器 X850 绿色 | Contoso, Ltd | Green | Deluxe | Audio |
| Products | Proseware Scan Jet Digital Flat Bed Scanner M300 Green | Proseware, Inc. | Green | Regular | Computers |
| Products | Proseware All-In-One Photo Printer M200 Green | Proseware, Inc. | Green | Regular | Computers |
| Products | Proseware Ink Jet Wireless All-In-One Printer M400 Green | Proseware, Inc. | Green | Regular | Computers |
| Products | Proseware Ink Jet 即时 PDF Sheet-Fed 扫描仪 M300 绿色 | Proseware, Inc. | Green | Regular | Computers |
| Products | Proseware Desk Jet 全能打印机、扫描仪、复制器 M350 绿色 | Proseware, Inc. | Green | Regular | Computers |
| Products | Proseware 双工扫描仪 M200 绿色 | Proseware, Inc. | Green | Regular | Computers |
以下示例演示如何搜索特定表中的术语。
Search for the term Green only in the Customers table.
search in (Products) "Green"
Output
| $table | ProductName | Manufacturer | ColorName |
|---|---|---|---|
| Products | Contoso 4G MP3 Player E400 Green | Contoso, Ltd | Green |
| Products | Contoso 8GB Super-Slim MP3/Video Player M800 Green | Contoso, Ltd | Green |
| Products | Contoso 16GB Mp5 Player M1600 Green | Contoso, Ltd | Green |
| Products | Contoso 8GB 时钟和无线电 MP3 播放器 X850 绿色 | Contoso, Ltd | Green |
| Products | NT 无线蓝牙立体声耳机 M402 绿色 | Northwind Traders | Green |
| Products | NT 无线发射器和蓝牙耳机 M150 绿色 | Northwind Traders | Green |
以下示例演示如何搜索区分大小写的术语。 Search for records that match the case-sensitive term in the ContosoSales database.
search kind=case_sensitive "blue"
Output
| $table | ProductName | Manufacturer | ColorName | ClassName |
|---|---|---|---|---|
| Products | Contoso 16GB 新一代 MP5 播放器 M1650 蓝色 | Contoso, Ltd | blue | Regular |
| Products | Contoso Bright Light battery E20 blue | Contoso, Ltd | blue | Economy |
| Products | Litware 120mm Blue LED Case Fan E901 blue | Litware, Inc. | blue | Economy |
| NewSales | Litware 120mm Blue LED Case Fan E901 blue | Litware, Inc. | blue | Economy |
| NewSales | Litware 120mm Blue LED Case Fan E901 blue | Litware, Inc. | blue | Economy |
| NewSales | Litware 120mm Blue LED Case Fan E901 blue | Litware, Inc. | blue | Economy |
| NewSales | Litware 120mm Blue LED Case Fan E901 blue | Litware, Inc. | blue | Economy |
以下示例演示如何在特定列中搜索字词。 Search for the terms Aaron and Hughes, in the "FirstName" and "LastName" columns respectively, in the ContosoSales database.
search FirstName:"Aaron" or LastName:"Hughes"
Output
| $table | CustomerKey | 教育 | FirstName | Gender | LastName |
|---|---|---|---|---|---|
| Customers | 18285 | High School | Riley | F | Hughes |
| Customers | 802 | Graduate Degree | Aaron | M | Sharma |
| Customers | 986 | Bachelors | Melanie | F | Hughes |
| Customers | 12669 | High School | Jessica | F | Hughes |
| Customers | 13436 | Graduate Degree | Mariah | F | Hughes |
| Customers | 10152 | Graduate Degree | Aaron | M | Campbell |
以下示例演示如何使用时间戳搜索字词。 Search for the term Hughes in the ContosoSales database, if the term appears in a record with a date greater than the given date in 'datetime'.
search "Hughes" and DateKey > datetime('2009-01-01')
Output
| $table | DateKey | SalesAmount_real |
|---|---|---|
| SalesTable | 2021-12-13T00:00:00Z | 446.4715 |
| SalesTable | 2021-12-13T00:00:00Z | 120.555 |
| SalesTable | 2021-12-13T00:00:00Z | 48.4405 |
| SalesTable | 2021-12-13T00:00:00Z | 39.6435 |
| SalesTable | 2021-12-13T00:00:00Z | 56.9905 |
Performance Tips
| # | Tip | Prefer | Over |
|---|---|---|---|
| 1 | 最好使用单个 search 运算符而非多个连续的 search 运算符 |
search "billg" and ("steveb" or "satyan") |
搜索"billg" | 搜索 "steveb"或"satyan" |
| 2 | 最好在 search 运算符内进行筛选 |
search "billg" and "steveb" |
搜索 * | 其中 * 有 "billg"；* 有 "steveb" |