mariadb Regexp 有时在存储过程中返回空

2022-01-15 00:00:00 regex sql mariadb mysql stored-procedures

需要帮助，我这几天遇到了麻烦.

need help, i got trouble for few day.

问题是在存储过程上运行子查询的正则表达式，有时返回空有时正确.

the problem is regexp from subquery run on stored procedure, sometime return empty sometime correct.

    REGEXP
        (
            select group_concat(_data_grupkasbank_auth.datahirarki separator '|') from _data_grupkasbank_auth where _data_grupkasbank_auth.uid='1'
        )

如果我用字符串值替换子查询，它总是正确的:

if i replace subquery with string value, its always correct :

REGEXP
    (
        '/Sangatta/K3PC/|/Yayasan Balikpapan/Masjid/'
    )

我的 dbfiddle 运行完美，但不能在直接 myserver windows 10、mariadb 10.3.23 上运行

here my dbfiddle run work perfectly, but not on direct myserver windows 10, mariadb 10.3.23

https://www.db-fiddle.com/f/pRWvdP3KUwv7rbTN7H7PZC/0

捕获:结果测试

子查询

直接字符串

(来自评论)

select  group_concat(_data_grupkasbank_view.kode_grupkasbank separator ',' )
    from  _data_grupkasbank_view
    where  _data_grupkasbank_view.hirarki_grupkasbank REGEXP 
      ( SELECT  group_concat(_data_grupkasbank_auth.datahirarki separator '|')
            from  _data_grupkasbank_auth
            where  _data_grupkasbank_auth.uid='1' 
      )
    INTO  v_tempsql

推荐答案

我不知道，但是这个工作，我用这个来分配 SET v_temp2=v_temp1;

i dont know, but this work, i use this to assign SET v_temp2=v_temp1;

    -- //////////        
    
    DECLARE v_temp1 TEXT;
    DECLARE v_temp2 TEXT;
    
    select
    group_concat(yourfield separator '|')
    INTO
    v_temp1
    from
    table;
    
    SET v_temp2 = v_temp1;
    
    select 
    field1, field2
    from table
    where
    field1 regexp v_temp2;
    
    -- ///////////

相关文章