mysql - 优化 "SQL Where Clause"的表设计

Question

我正在为 SQL 的Where子句进行数据库设计。我的应用程序是一个工作流引擎，允许最终用户指定 SQL 的 where 子句。目前我的应用程序支持创建 where 子句，如下所示:

<input type="text" name="clause-name" placeholder="condition name here" />
<br/>
<br/>
<input type="radio" name="where">Complex</input>
<br/>
<div style="display:inline-block">
  <select>
    <option value="1">ch4 &ge; 1.5</option>
    <option value="2">locality = chino</option>
    <option value="3">postal_code like [0-9]{5}</option>
    <option value="4">date &le; 2015-12-31</option>
    <option value="5">chino and ch4</option>
    <option value="6">postalcode and date</option>
    <option value="7">chino or postalcode</option>
  </select>

  <select>
    <option>AND</option>
    <option>OR</option>
  </select>

  <select>
    <option value="1">ch4 &ge; 1.5</option>
    <option value="2">locality = chino</option>
    <option value="3">postal_code like [0-9]{5}</option>
    <option value="4">date &le; 2015-12-31</option>
    <option value="5">chino and ch4</option>
    <option value="6">postalcode and date</option>
    <option value="7">chino or postalcode</option>
  </select>
</div>
<br/>
<input type="radio" name="where">Simple</input>
<br/>
<div style="display:inline-block">
  <select>
    <option>ch4</option>
    <option>locality</option>
    <option>postal_code</option>
    <option>date</option>
  </select>

  <select>
    <option>&le;</option>
    <option>&lt;</option>
    <option>&ge;</option>
    <option>&gt;</option>
    <option>&ne;</option>
    <option>=</option>
    <option>like</option>
  </select>

  <input type="text" placeholder="value here" />
</div>
<br/>
<br/>
<button>Create Condition</button>

在其他页面上，我有一个选择下拉列表，您可以在其中选择创建的子句。问题是我为这个场景设计的数据库表。目前情况如下:

WhereTable(id, name, feature, op, value, condition1, jointype, condition2)

这样id是主键且 condition1, condition2是 self 的外键。条件 (ch4 >= 1.5 and locality = chino) or (postal_code like [0-9]{5} and date <= 2015-12-31) 的示例表如下:

table, th, td{
  border: 1px solid black;
  }

<table>
  <tr>
    <th>id</th>
    <th>name</th>
    <th>feature</th>
    <th>op</th>
    <th>value</th>
    <th>condition1</th>
    <th>jointype</th>
    <th>condition2</th>
   </tr>
  <tr>
    <td>1</td>
    <td>ch4&ge;1.5</td>
    <td>ch4</td>
    <td>&gt;=</td>
    <td>1.5</td>
    <td>null</td>
    <td>null</td>
    <td>null</td>
   </tr>
  <tr>
    <td>2</td>
    <td>locality=chino</td>
    <td>locality</td>
    <td>=</td>
    <td>chino</td>
    <td>null</td>
    <td>null</td>
    <td>null</td>
   </tr>
  <tr>
    <td>3</td>
    <td>postal_code like [0-9]{5}</td>
    <td>postal_code</td>
    <td>like</td>
    <td>[0-9]{5}</td>
    <td>null</td>
    <td>null</td>
    <td>null</td>
   </tr>
  <tr>
    <td>4</td>
    <td>date&le;2015-12-31</td>
    <td>date</td>
    <td>&lt;=</td>
    <td>2015-12-31</td>
    <td>null</td>
    <td>null</td>
    <td>null</td>
   </tr>
  <tr>
    <td>5</td>
    <td>chino and ch4</td>
    <td>null</td>
    <td>null</td>
    <td>null</td>
    <td>1</td>
    <td>and</td>
    <td>2</td>
   </tr>
  <tr>
    <td>6</td>
    <td>postalcode and date</td>
    <td>null</td>
    <td>null</td>
    <td>null</td>
    <td>3</td>
    <td>and</td>
    <td>4</td>
   </tr>
  <tr>
    <td>7</td>
    <td>chino or postalcode</td>
    <td>null</td>
    <td>null</td>
    <td>null</td>
    <td>5</td>
    <td>or</td>
    <td>6</td>
   </tr>
 </table>

有什么方法可以优化这个表设计以减少空值吗？我使用 MySQL 作为我的数据库服务器。

Answer 1

啊，现在我想我明白了。

用面向对象的术语来说，你有一个像这样的继承结构

Formula
   int id
   String name

BasicFormula extends Formula
   String variable
   Operator operator  // something like <=, contains, ...
   String value

CompoundFormula extends Formula
   Formula leftFormula
   Operator operator  // and, or
   Formula rightFormula

现在，要将这样的构造映射到数据库，有不同的策略。使用每类层次结构表是完全合法的，这就是您所建议的，只需拥有一个包含两个特化属性的表即可。详细描述见https://docs.jboss.org/hibernate/orm/3.5/reference/en/html/inheritance.html 。在许多情况下，只有一张表是最简单的解决方案。当然，任何查询数据库的人都必须知道如何解释该方案，但这也适用于其他策略。