订阅规则 SQL 筛选器语法Subscription Rule SQL Filter Syntax

• 02/01/2021
• • r
  • v
  • D
  • b
  • O

SQL 筛选器是服务总线主题订阅的某个可用筛选器类型。A SQL filter is one of the available filter types for Service Bus topic subscriptions. 它是一个依赖于 SQL-92 标准子集的文本表达式。It's a text expression that leans on a subset of the SQL-92 standard. 筛选表达式与 Azure 资源管理器模板中服务总线 Rule 的“sqlFilter”属性的 sqlExpression 元素一起使用，或者与 Azure CLI az servicebus topic subscription rule create 命令的 --filter-sql-expression 参数以及几个允许管理订阅规则的 SDK 函数一起使用。Filter expressions are used with the sqlExpression element of the 'sqlFilter' property of a Service Bus Rule in an Azure Resource Manager template, or the Azure CLI az servicebus topic subscription rule create command's --filter-sql-expression argument, and several SDK functions that allow managing subscription rules.

服务总线高级版还通过 JMS 2.0 API 支持 JMS SQL 消息选择器语法。Service Bus Premium also supports the JMS SQL message selector syntax through the JMS 2.0 API.

<predicate ::=  
      { NOT <predicate> }  
      | <predicate> AND <predicate>  
      | <predicate> OR <predicate>  
      | <expression> { = | <> | != | > | >= | < | <= } <expression>  
      | <property> IS [NOT] NULL  
      | <expression> [NOT] IN ( <expression> [, ...n] )  
      | <expression> [NOT] LIKE <pattern> [ESCAPE <escape_char>]  
      | EXISTS ( <property> )  
      | ( <predicate> )  

<expression> ::=  
      <constant>   
      | <function>  
      | <property>  
      | <expression> { + | - | * | / | % } <expression>  
      | { + | - } <expression>  
      | ( <expression> )  

<property> :=   
       [<scope> .] <property_name>  

参数Arguments

• <scope> 是一个可选字符串，指示 <property_name> 的范围。<scope> is an optional string indicating the scope of the <property_name>. 有效值为 sys or user进行求值的基于 SQL 语言的筛选器表达式。Valid values are sys or user. sys 值指示系统范围，其中 <property_name> 是 BrokeredMessage 类的公共属性名称。The sys value indicates system scope where <property_name> is a public property name of the BrokeredMessage class. user 指示用户范围，其中 <property_name> 是 BrokeredMessage 类字典的项。user indicates user scope where <property_name> is a key of the BrokeredMessage class dictionary. user 范围是默认范围（如果 <scope> 未指定）。user scope is the default scope if <scope> isn't specified.

备注Remarks

尝试访问不存在的系统属性属于错误，而尝试访问不存在的用户属性不属于错误。An attempt to access a non-existent system property is an error, while an attempt to access a non-existent user property isn't an error. 相反，不存在的用户属性在内部作为未知值进行求值。Instead, a non-existent user property is internally evaluated as an unknown value. 运算符求值期间会对未知值进行特殊处理。An unknown value is treated specially during operator evaluation.

property_nameproperty_name

<property_name> ::=  
     <identifier>  
     | <delimited_identifier>  

<identifier> ::=  
     <regular_identifier> | <quoted_identifier> | <delimited_identifier>  

参数Arguments

<regular_identifier> 是字符串，由以下正则表达式表示：<regular_identifier> is a string represented by the following regular expression:

[[:IsLetter:]][_[:IsLetter:][:IsDigit:]]*  

此语法是指任何以字母开头且后跟一个或多个下划线/字母/数字的字符串。This grammar means any string that starts with a letter and is followed by one or more underscore/letter/digit.

[:IsLetter:] 是指分类为 Unicode 字母的任何 Unicode 字符。[:IsLetter:] means any Unicode character that is categorized as a Unicode letter. System.Char.IsLetter(c) 返回 true（如果 c 为 Unicode 字母）。System.Char.IsLetter(c) returns true if c is a Unicode letter.

[:IsDigit:] 是指分类为十进制数字的任何 Unicode 字符。[:IsDigit:] means any Unicode character that is categorized as a decimal digit. System.Char.IsDigit(c) 返回 true（如果 c 为 Unicode 数字）。System.Char.IsDigit(c) returns true if c is a Unicode digit.

<regular_identifier> 不能是保留关键字。A <regular_identifier> can't be a reserved keyword.

<delimited_identifier> 是用左/右方括号 ([]) 括起来的任何字符串。<delimited_identifier> is any string that is enclosed with left/right square brackets ([]). 右方括号以两个右方括号表示。A right square bracket is represented as two right square brackets. 下面是 <delimited_identifier>的示例：The following are examples of <delimited_identifier>:

[Property With Space]  
[HR-EmployeeID]  

<quoted_identifier> 是指使用双引号引起来的任何字符串。<quoted_identifier> is any string that is enclosed with double quotation marks. 标识符中的双引号以两个双引号表示。A double quotation mark in identifier is represented as two double quotation marks. 不建议使用带引号的标识符，因为很容易将其与字符串常量混淆。It isn't recommended to use quoted identifiers because it can easily be confused with a string constant. 如果可能，请使用分隔标识符。Use a delimited identifier if possible. 下面是 <quoted_identifier> 的示例：Here's an example of <quoted_identifier>:

"Contoso & Northwind"  

patternpattern

<pattern> ::=  
      <expression>  

备注Remarks

<pattern> 必须是作为字符串进行求值的表达式。<pattern> must be an expression that is evaluated as a string. 它用作 LIKE 运算符的模式。It's used as a pattern for the LIKE operator. 它可以包含以下通配符：It can contain the following wildcard characters:

• %：包含零个或多个字符的任意字符串。%: Any string of zero or more characters.

• _：任何单个字符。_: Any single character.

escape_charescape_char

<escape_char> ::=  
      <expression>  

备注Remarks

<escape_char> 必须是作为长度为 1 的字符串进行求值的表达式。<escape_char> must be an expression that is evaluated as a string of length 1. 它用作 LIKE 运算符的转义符。It's used as an escape character for the LIKE operator.

例如，property LIKE 'ABC\%' ESCAPE '\' 匹配 ABC%，而不匹配以 ABC 开头的字符串。For example, property LIKE 'ABC\%' ESCAPE '\' matches ABC% rather than a string that starts with ABC.

constantconstant

<constant> ::=  
      <integer_constant> | <decimal_constant> | <approximate_number_constant> | <boolean_constant> | NULL  

参数Arguments

• <integer_constant> 是指不使用引号引起来且不包含小数点的数字字符串。<integer_constant> is a string of numbers that aren't enclosed in quotation marks and don't contain decimal points. 这些值作为 System.Int64 在内部存储，并具有相同的作用域。The values are stored as System.Int64 internally, and follow the same range.

  下面是长常量的示例：Here are examples of long constants:

  1894  
  2  
  

• <decimal_constant> 是指不使用引号引起来但包含小数点的数字字符串。<decimal_constant> is a string of numbers that aren't enclosed in quotation marks, and contain a decimal point. 这些值作为 System.Double 在内部存储，并具有相同的作用域/精度。The values are stored as System.Double internally, and follow the same range/precision.

  在未来版本中，此数字可能以其他数据类型存储，目的是支持确切的数字语义，因此不应依赖于 <decimal_constant> 的基础数据类型为 System.Double 这一事实。In a future version, this number might be stored in a different data type to support exact number semantics, so you shouldn't rely on the fact the underlying data type is System.Double for <decimal_constant>.

  下面是十进制常量的示例：The following are examples of decimal constants:

  1894.1204  
  2.0  
  

• <approximate_number_constant> 是指使用科学记数法书写的数字。<approximate_number_constant> is a number written in scientific notation. 这些值作为 System.Double 在内部存储，并具有相同的作用域/精度。The values are stored as System.Double internally, and follow the same range/precision. 下面是近似数常量的示例：The following are examples of approximate number constants:

  101.5E5  
  0.5E-2  
  

boolean_constantboolean_constant

<boolean_constant> :=  
      TRUE | FALSE  

备注Remarks

布尔常量以关键字 TRUE 或 FALSE 表示。Boolean constants are represented by the keywords TRUE or FALSE. 这些值作为 System.Boolean存储。The values are stored as System.Boolean.

string_constantstring_constant

<string_constant>  

备注Remarks

字符串常量使用单引号引起来，并包含任何有效的 Unicode 字符。String constants are enclosed in single quotation marks and include any valid Unicode characters. 字符串常量中嵌入的单引号以两个单引号表示。A single quotation mark embedded in a string constant is represented as two single quotation marks.

functionfunction

<function> :=  
      newid() |  
      property(name) | p(name)  

备注Remarks

newid() 函数返回 System.Guid.NewGuid() 方法生成的 System.Guid。The newid() function returns a System.Guid generated by the System.Guid.NewGuid() method.

property(name) 函数返回 name 所引用的属性的值。The property(name) function returns the value of the property referenced by name. name 值可以是返回字符串值的任何有效表达式。The name value can be any valid expression that returns a string value.

注意事项Considerations

请注意以下 SqlFilter 语义：Consider the following SqlFilter semantics:

• 属性名称区分大小写。Property names are case-insensitive.

• 运算符尽可能遵循 C# 隐式转换语义。Operators follow C# implicit conversion semantics whenever possible.

• 系统属性是值在 BrokeredMessage 实例中公开的公共属性。System properties are public properties exposed in BrokeredMessage instances.

  请注意以下 IS [NOT] NULL 语义：Consider the following IS [NOT] NULL semantics:

  • property IS NULL 作为 true 求值（如果属性不存在，或者属性的值为 null）。property IS NULL is evaluated as true if either the property doesn't exist or the property's value is null.

属性求值语义Property evaluation semantics

• 尝试对不存在的系统属性求值会引发 FilterException 异常。An attempt to evaluate a non-existent system property throws a FilterException exception.

• 不存在的属性在内部作为未知进行求值。A property that doesn't exist is internally evaluated as unknown.

  算术运算符中的未知求值：Unknown evaluation in arithmetic operators:

• 对于二元运算符，如果操作数的左侧或右侧的求值结果为未知，则结果为未知 。For binary operators, if either the left or right side of operands is evaluated as unknown, then the result is unknown.

• 对于一元运算符，如果操作数的求值结果为 未知，则结果为 未知。For unary operators, if an operand is evaluated as unknown, then the result is unknown.

  二进制比较运算符中的未知求值：Unknown evaluation in binary comparison operators:

• 如果操作数的左侧或右侧的求值结果为未知，则结果为未知 。If either the left or right side of operands is evaluated as unknown, then the result is unknown.

  [NOT] LIKE中的未知求值：Unknown evaluation in [NOT] LIKE:

• 如果任何操作数的求值结果为“未知”，则结果为“未知”。If any operand is evaluated as unknown, then the result is unknown.

  [NOT] IN中的未知求值：Unknown evaluation in [NOT] IN:

• 如果左侧操作数的求值结果为“未知”，则结果为“未知”。If the left operand is evaluated as unknown, then the result is unknown.

  AND 运算符中的未知求值：Unknown evaluation in AND operator:

+---+---+---+---+  
|AND| T | F | U |  
+---+---+---+---+  
| T | T | F | U |  
+---+---+---+---+  
| F | F | F | F |  
+---+---+---+---+  
| U | U | F | U |  
+---+---+---+---+  

OR 运算符中的未知求值：Unknown evaluation in OR operator:

+---+---+---+---+  
|OR | T | F | U |  
+---+---+---+---+  
| T | T | T | T |  
+---+---+---+---+  
| F | T | F | U |  
+---+---+---+---+  
| U | T | U | U |  
+---+---+---+---+  

运算符绑定语义Operator binding semantics

• 在进行数据类型提升和隐式转换时，比较运算符（例如 >、>=、<、<=、!= 和 =）与 C# 运算符绑定遵循相同的语义。Comparison operators such as >, >=, <, <=, !=, and = follow the same semantics as the C# operator binding in data type promotions and implicit conversions.

• 在进行数据类型提升和隐式转换时，算术运算符（例如 +、-、*、/ 和 %）与 C# 运算符绑定遵循相同的语义。Arithmetic operators such as +, -, *, /, and % follow the same semantics as the C# operator binding in data type promotions and implicit conversions.

示例Examples

按系统属性筛选Filter on system properties

若要在筛选器中引用系统属性，请使用以下格式：sys.<system-property-name>。To refer to a system property in a filter, use the following format: sys.<system-property-name>.

sys.Label LIKE '%bus%'`
sys.messageid = 'xxxx'
sys.correlationid like 'abc-%'

按消息属性筛选Filter on message properties

下面是在筛选器中使用消息属性的示例。Here are the examples of using message properties in a filter. 可以使用 user.property-name 或仅 property-name 来访问消息属性。You can access message properties using user.property-name or just property-name.

MessageProperty = 'A'
SuperHero like 'SuperMan%'

按带有特殊字符的消息属性筛选Filter on message properties with special characters

如果消息属性名称包含特殊字符，请使用双引号 (") 将属性名称括起来。If the message property name has special characters, use double quotes (") to enclose the property name. 例如，如果属性名称为 "http://schemas.microsoft.com/xrm/2011/Claims/EntityLogicalName"，则在筛选器中使用以下语法。For example if the property name is "http://schemas.microsoft.com/xrm/2011/Claims/EntityLogicalName", use the following syntax in the filter.

"http://schemas.microsoft.com/xrm/2011/Claims/EntityLogicalName" = 'account'

按带有数字值的消息属性筛选Filter on message properties with numeric values

下面的示例演示如何在筛选器中使用带有数字值的属性。The following examples show how you can use properties with numeric values in filters.

MessageProperty = 1
MessageProperty > 1
MessageProperty > 2.08
MessageProperty = 1 AND MessageProperty2 = 3
MessageProperty = 1 OR MessageProperty2 = 3

基于参数的筛选器Parameter-based filters

下面是使用基于参数的筛选器的几个示例。Here are a few examples of using parameter-based filters. 在这些示例中，DataTimeMp 是 DateTime 类型的消息属性，@dtParam 是作为 DateTime 对象传递给筛选器的参数。In these examples, DataTimeMp is a message property of type DateTime and @dtParam is a parameter passed to the filter as a DateTime object.

DateTimeMp < @dtParam
DateTimeMp > @dtParam

(DateTimeMp2-DateTimeMp1) <= @timespan //@timespan is a parameter of type TimeSpan
DateTimeMp2-DateTimeMp1 <= @timespan

使用 IN 和 NOT INUsing IN and NOT IN

StoreId IN('Store1', 'Store2', 'Store3')"

sys.To IN ('Store5','Store6','Store7') OR StoreId = 'Store8'

sys.To NOT IN ('Store1','Store2','Store3','Store4','Store5','Store6','Store7','Store8') OR StoreId NOT IN ('Store1','Store2','Store3','Store4','Store5','Store6','Store7','Store8')

有关 C# 示例，请参阅 GitHub 上的主题筛选器示例。For a C# sample, see Topic Filters sample on GitHub.

为 SQL 筛选器设置规则操作Set rule action for a SQL filter

// instantiate the ManagementClient
this.mgmtClient = new ManagementClient(connectionString);

// create the SQL filter
var sqlFilter = new SqlFilter("source = @stringParam");

// assign value for the parameter
sqlFilter.Parameters.Add("@stringParam", "orders");

// instantiate the Rule = Filter + Action
var filterActionRule = new RuleDescription
{
    Name = "filterActionRule",
    Filter = sqlFilter,
    Action = new SqlRuleAction("SET source='routedOrders'")
};

// create the rule on Service Bus
await this.mgmtClient.CreateRuleAsync(topicName, subscriptionName, filterActionRule);

使用 CorrelationID 的关联筛选器Correlation filter using CorrelationID

new CorrelationFilter("Contoso");

它将筛选 CorrelationID 设置为 Contoso 的消息。It filters messages with CorrelationID set to Contoso.

使用系统属性和用户属性的关联筛选器Correlation filter using system and user properties

var filter = new CorrelationFilter();
filter.Label = "Important";
filter.ReplyTo = "johndoe@contoso.com";
filter.Properties["color"] = "Red";

它等效于 sys.ReplyTo = 'johndoe@contoso.com' AND sys.Label = 'Important' AND color = 'Red'It's equivalent to: sys.ReplyTo = 'johndoe@contoso.com' AND sys.Label = 'Important' AND color = 'Red'

后续步骤Next steps

• SQLFilter 类 (.NET Framework)SQLFilter class (.NET Framework)
• SQLFilter 类 (.NET Framework)SQLFilter class (.NET Standard)
• SqlFilter 类 (Java)SqlFilter class (Java)
• SqlRuleFilter (JavaScript)SqlRuleFilter (JavaScript)
• az servicebus topic subscription ruleaz servicebus topic subscription rule
• New-AzServiceBusRuleNew-AzServiceBusRule