前言

MySQL 应用使用的 SQL 的 Where 条件存在 FIND_IN_SET，无法在 YashanDB 直接执行，需要改写。本文探讨不兼容的原因，并给出改写手段。

问题

SQL 示例

MySQL 执行成功

<table cellspacing="0"><tbody><tr><td><p><span>mysql&gt; select c1 from t1 where find_in_set(c2,'</span><span>a,b</span><span>,c');</span></p><p><span>+------+</span></p><p><span>| c1 |</span></p><p><span>+------+</span></p><p><span>| 1 |</span></p><p><span>+------+</span></p><p><span>1 row in set (0.00 sec)</span></p></td></tr></tbody></table>

YashanDB 报错

<table cellspacing="0"><tbody><tr><td><p><span>SQL&gt; select c1 from t1 where find_in_set(c2,'</span><span>a,b</span><span>,c');</span></p><p><span>[</span><span>1:25]YAS</span><span>-04336 boolean expression expected</span></p></td></tr></tbody></table>

原因

MySQL 的 Boolean 类型实际上是 tinyint 类型的同义词，0 值为 false，非 0 值为 true。这意味着在 MySQL 的数值类型可以条件运算

参考： https://dev.mysql.com/doc/refman/8.4/en/numeric-type-syntax.html

<table cellspacing="0"><tbody><tr><td><p><span>mysql&gt; select find_in_set('b','</span><span>a,b</span><span>,c') from dual;</span></p><p><span>+--------------------------+</span></p><p><span>| find_in_set('b','</span><span>a,b</span><span>,c') |</span></p><p><span>+--------------------------+</span></p><p><span>| 2 |</span></p><p><span>+--------------------------+</span></p><p><span>1 row in set (0.00 sec)</span></p><p><span>&nbsp;</span></p><p><span>mysql&gt; select find_in_set('d','</span><span>a,b</span><span>,c') from dual;</span></p><p><span>+--------------------------+</span></p><p><span>| find_in_set('d','</span><span>a,b</span><span>,c') |</span></p><p><span>+--------------------------+</span></p><p><span>| 0 |</span></p><p><span>+--------------------------+</span></p><p><span>1 row in set (0.00 sec)</span></p><p><span>&nbsp;</span></p><p><span>mysql&gt; select 1 from dual where 2;</span></p><p><span>+---+</span></p><p><span>| 1 |</span></p><p><span>+---+</span></p><p><span>| 1 |</span></p><p><span>+---+</span></p><p><span>1 row in set (0.00 sec)</span></p><p><span>&nbsp;</span></p><p><span>mysql&gt; select 1 from dual where 0;</span></p><p><span>Empty set (0.00 sec)</span></p></td></tr></tbody></table>

但是，YashanDB 的 tinyint 类型不能参与条件运算

<table cellspacing="0"><tbody><tr><td><p><span>SQL&gt; select find_in_set('b','</span><span>a,b</span><span>,c') from dual;</span></p><p><span>FIND_IN_</span><span>SET(</span><span>'B','A,B</span></p><p><span>--------------------</span></p><p><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2</span></p><p><span>&nbsp;</span></p><p><span>1 row fetched.</span></p><p><span>SQL&gt; select find_in_set('d','</span><span>a,b</span><span>,c') from dual;</span></p><p><span>FIND_IN_</span><span>SET(</span><span>'D','A,B</span></p><p><span>--------------------</span></p><p><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0</span></p><p><span>&nbsp;</span></p><p><span>1 row fetched.</span></p><p><span>SQL&gt; select 1 from dual where 2;</span></p><p><span>[</span><span>1:26]YAS</span><span>-04336 boolean expression expected</span></p><p><span>SQL&gt; select 1 from dual where 0;</span></p><p><span>[</span><span>1:26]YAS</span><span>-04336 boolean expression expected</span></p></td></tr></tbody></table>

改写方案

按照 find_in_set 的逻辑，可以对所有参与 where 条件的 find_in_set 进行文本替换，例如示例中的替换规则就是  where find_in_set( 替换  where 0 < find_in_set(

<table cellspacing="0"><tbody><tr><td><p><span>SQL&gt; select c1 from t1 where 0 &lt; find_in_set(c2,'</span><span>a,b</span><span>,c');</span></p><p><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;C1</span></p><p><span>------------</span></p><p><span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1</span></p><p><span>1 row fetched.</span></p></td></tr></tbody></table>

备注：一般而言，单个项目的源代码由于开发人员的习惯，对 find_in_set 的写法基本类似，所以往往可以用少数文本替换规则，就能对所有不兼容的 find_in_set 进行改写