布尔表达式的数据模型
您是否知道一种在数据库中组织布尔表达式同时允许表达式无限嵌套的方法?
示例:
a = 1 AND (b = 1 OR b = 2)
表达式作为一个整体不应存储为 varchar 以保持数据完整性。
Do you know a way to organize boolean expressions in a database while allowing infinite nesting of the expressions?
Example:
a = 1 AND (b = 1 OR b = 2)
The expression as a whole shouldn't be stored as varchar to preserve data integrity.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(8)
选项 1 是使用嵌套表(具有 id/parent_id 结构的树),就像 Gamecat 建议的那样。 这样做的成本相对较高,并且需要重复发出 SQL 查询来构建单个嵌套表达式的等效项。
选项 2 是使用序列化对象并将其存储到 varchar 列中。 例如,JSON 就是一个不错的选择。 它对空格不敏感,可以用多种语言创建和解析,并且保留数据完整性。
一旦将表达式字符串解析为内存中的树对象,就可以将其序列化并存储。 如果不需要在数据库级别操作表达式,我想我会走这条路。
Option 1 would be to use a nested table (a tree with id / parent_id structure), like Gamecat suggested. This is relatively expensive to do, and requires issuing SQL queries repetitively to build the equivalent of a single nested expression.
Option 2 would be to use a serialized object and store it into a varchar column. For example, JSON would be a good choice. It is not white-space sensitive, can be created and parsed in a vast number of languages, and it retains data integrity.
As soon as you have parsed your expression string into a tree object in memory, you can serialize it and store it. If there was no need to manipulate the expression on the database level, I guess I would go that route.
表达式是一个树状结构。 因此,您需要一种在表格中呈现树的方法。
例如,您可以使用以下字段:
在这种情况下,您有以下类型:
但我认为有更好的方式来组织表达。 我曾经制作了一个简单的表达式求值器,它接受一个字符串并生成一个数字结果。
An expression is a treelike structure. So you need a way to present the tree in a table.
You can for example use the fields:
In this case, you have the following types:
But I think there are better ways to organise expression. I once made a simple expression evaluator that accepts a string and produces a numeric result.
我会将表达式以波兰形式存储在 varchar/text 列中。 波兰形式的表达式(操作数在操作数之前,没有括号)更容易使用递归函数(或者当然是堆栈)进行解析。
a = 1 AND (b = 1 OR b = 2)
波兰语形式如下所示:
AND = a 1 OR = b 1 = b 2
I would store the expression in polish form, in a varchar/text column. An expression in polish form (operand before operands, no brackets) is much easier to parse with a recursive function (or a stack of course).
a = 1 AND (b = 1 OR b = 2)
in polish form shows like this:
AND = a 1 OR = b 1 = b 2
这种类型的表达式最常表示为树(层次结构),这在 SQL 中查询是非常烦人的。
我们假设
a
和b
暂时是数字,并且文字 ('1', '2') 与变量不同。这种结构非常灵活,但是查询它来检索复杂的表达式将很“有趣”(读作“具有挑战性”)。
而且您仍然必须首先解析结构并在重构后评估表达式。
This type of expression is most usually expressed as a tree (a hierarchy), which are notoriously annoying to query in SQL.
We'll assume that
a
andb
are numeric for the moment and that literals ('1', '2') are distinguished from variables.This structure is very flexible, but querying it to retrieve a complex expression is going to be "fun" (read that "challenging").
And you still have to parse the structure to begin with and evaluate the expression after it has been reconstructed.
这将很难用关系来表示,因为从本质上讲,它既是分层的又是多态的(树的叶子可以是变量也可以是常量)。
This is going to be difficult to represent relationally, because by its nature it is both hierarchical and polymorphic (the leaves of your tree can be either variables or constants).
布尔函数建模的传统方法是使用二元决策图,特别是降阶二元决策图。 您可能可以为您的 DBMS 找到一个为该概念提供良好支持的扩展。
更新:
或者,如果您不需要查询布尔逻辑,则可以使用 BDD 库并将 BDD 序列化为
BLOB
或等效形式。 它优于使用varchar
字段,因为 BDD 库将确保数据有效。The traditional way to model Boolean functions is to use Binary Decision Diagrams, especially Reduced Order Binary Decision Diagrams. It's possible you can find an extension for your DBMS that provides good support for the concept.
UPDATE:
Alternately, if you don't need to query on the Boolean logic, you could use a BDD library and just serialize the BDD into a
BLOB
or equivalent. It beats using avarchar
field because the BDD library would ensure the data was valid.好的答案,但是如果表达式组中有超过 2 个表达式怎么办?
我建议这样:
value1
、value2
和operation
对最终比较进行建模,如“b = 3
”(在我的casevalue1 = 'b'
、value2 = '3'
和operation = 'EQUALS'
)groupeType
可以是“AND
”或“OR
”ConditionGroup
可以具有ConditionGroup
的子列表 或最终条件
,但不是两者。ConditionGroup
,递归地挖掘其subConditionGroups
直到找到最终条件,然后返回该值并应用正确的条件
。事实上这就是我要尝试的。
Ok withe the answers but what if there are more than 2 expressions in an expression group ?
I propose this :
value1
,value2
andoperation
model a final comparison like 'b = 3
' (in my casevalue1 = 'b'
,value2 = '3'
andoperation = 'EQUALS'
)groupeType
can be 'AND
' or 'OR
'ConditionGroup
can have either a sub-list ofConditionGroup
s OR a finalCondition
but not both.ConditionGroup
, recursively dig into itssubConditionGroups
until you find a final condition, then return the value and apply the propercondition
.Actually that is what I'm about to try.
添加到@Gamechat答案
我认为它应该像这样
ID
TypeExpression(和,或等等...)
FirstChildID --这可以是叶节点或指向同一个表中另一行的指针
SecondChildID --这可以是叶节点或指向同一表中另一行的指针
isFirstChildLeaf
isSecondChildLeaf
Adding to @Gamechat answer
I think it should be like this
ID
TypeExpression (and, or etc...)
FirstChildID --This can be a leaf node or a pointer to another row in same table
SecondChildID --This can be a leaf node or a pointer to another row in same table
isFirstChildLeaf
isSecondChildLeaf