【YashanDB知识库】MySQL的FIND_IN_SET如何在YashanDB改写

本文内容来自YashanDB官网，原文内容请见 https://www.yashandb.com/newsinfo/7323369.html?templateId=171...

前言

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

问题

SQL示例

MySQL执行成功

<p><span>mysql> 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>

YashanDB报错

<p><span>SQL> 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>

原因 MySQL的Boolean类型实际上是tinyint类型的同义词，0值为false，非0值为true。这意味着在MySQL的数值类型可以条件运算 参考：https://dev.mysql.com/doc/refman/8.4/en/numeric-type-syntax.html

<p><span>mysql> 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> </span></p><p><span>mysql> 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> </span></p><p><span>mysql> 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> </span></p><p><span>mysql> select 1 from dual where 0;</span></p><p><span>Empty set (0.00 sec)</span></p>

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

<p><span>SQL> 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>                   2</span></p><p><span> </span></p><p><span>1 row fetched.</span></p><p><span>SQL> 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>                   0</span></p><p><span> </span></p><p><span>1 row fetched.</span></p><p><span>SQL> 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> select 1 from dual where 0;</span></p><p><span>[</span><span>1:26]YAS</span><span>-04336 boolean expression expected</span></p>

改写方案 按照find\_in\_set的逻辑，可以对所有参与where条件的find\_in\_set进行文本替换，例如示例中的替换规则就是 where find\_in\_set( 替换 where 0 < find\_in\_set(

<p><span>SQL> select c1 from t1 where 0 < find_in_set(c2,'</span><span>a,b</span><span>,c');</span></p><p><span>          C1</span></p><p><span>------------</span></p><p><span>           1</span></p><p><span>1 row fetched.</span></p>

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