循环查询，直到找到结果

Loop a query until result is found

提问人： 提问时间：4/30/2022 更新时间：4/30/2022 访问量：200

问：

我在创建简单的“while 循环”时遇到问题。

如何循环选择查询并删除字符串的最后一个字符，直到找到结果？

例如：

myfield
_______
1stone
1stick
1stove
1stones
2stick
2stove
2stone
2sticks
3stoves
3stone
3stick
3stove


[loop1] SELECT myfield FROM table WHERE myfield LIKE  '1story%'  -> 0 results
[loop2] SELECT myfield FROM table WHERE myfield LIKE  '1stor%'   -> 0 results
[loop3] SELECT myfield FROM table WHERE myfield LIKE  '1sto%'    -> 3 results

需要导致

1stone
1stones
1stove

或

[loop1] SELECT myfield FROM table WHERE myfield LIKE '2somevalue%'  -> 0 results 
[loop2] SELECT myfield FROM table WHERE myfield LIKE '2somevalu%'   -> 0 results 
[loop3] SELECT myfield FROM table WHERE myfield LIKE '2someval%'   -> 0 results 
[loop4] SELECT myfield FROM table WHERE myfield LIKE '2someva%'   -> 0 results 
[loop5] SELECT myfield FROM table WHERE myfield LIKE '2somev%'   -> 0 results 
[loop6] SELECT myfield FROM table WHERE myfield LIKE '2some%'   -> 0 results 
[loop7] SELECT myfield FROM table WHERE myfield LIKE '2som%'   -> 0 results 
[loop8] SELECT myfield FROM table WHERE myfield LIKE '2so%'  -> 0 results 
[loop9] SELECT myfield FROM table WHERE myfield LIKE '2s%'   -> 4 results

需要导致

2stick
2sticks
2stone
2stove

我一直在尝试这个，但到目前为止没有运气

SET @counter = 0,  @results = 0, @value = 'somevalue'

WHILE ( @counter <= LENGTH(@value) AND @results = 0)
BEGIN
    SET @qry = "SELECT COUNT(myfield) FROM table WHERE myfield LIKE '" + LEFT(@value, LENGTH(@value)-@Counter) + "%'  " 
    SET @results = (SELECT COUNT(myfield) FROM table WHERE myfield LIKE LEFT(@value, LENGTH(@value)-@Counter) + '%') 
    SET @counter  = @Counter  + 1 
END 


exec @qry
MySQL SQL 字符串 循环 匹配

评论

0赞 nbk 4/30/2022
在 SQL 中，您使用的是哪些 RDM，到目前为止您尝试了什么？
0赞 4/30/2022
我正在使用 MySQL，我想只在 SQL 中完成它，而不是使用 php 或 vb 等其他脚本来解决
0赞 nbk 4/30/2022
您需要一个程序，Length 获取字符数，LEFT 获取左侧部分，其余的是一个 while 循环，所以试试吧
0赞 4/30/2022
我一直在尝试各种各样的事情，所以我想我一定是完全走错了方向。

答：

0赞 nbk 4/30/2022 #1

正如我所说，你需要一个程序

这使用动态 sql，因为代码 else 容易受到 sql 注入的影响

CREATE TABLE tab
    (`myfield` varchar(7))
;
    
INSERT INTO tab
    (`myfield`)
VALUES
    ('1stone'),
    ('1stick'),
    ('1stove'),
    ('1stones'),
    ('2stick'),
    ('2stove'),
    ('2stone'),
    ('2sticks'),
    ('3stoves'),
    ('3stone'),
    ('3stick'),
    ('3stove')
;

CREATE PROCEDURE procedure_name(_searchtext varchar(100))
BEGIN
   DECLARE _mylen int DEFAULT 0;
   SET _mylen := LENGTH(_searchtext);
   myloop: WHILE _myLen > 0 DO
       
       SET @sql = 'SELECT COUNT(*) INTO @i FROM tab WHERE myfield LIKE  ?';
        set @text := (SELECT CONCAT(LEFT(_searchtext,_mylen), '%'));
        PREPARE stmt FROM @sql;
        EXECUTE stmt USING @text;
        DEALLOCATE PREPARE stmt;
       IF @i > 0 then
           SET @sql = 'SELECT myfield FROM tab WHERE myfield LIKE  ?';
        PREPARE stmt FROM @sql;
        EXECUTE stmt USING @text;
        DEALLOCATE PREPARE stmt;           
           LEAVE myloop;
       end if;
       SET _myLen := _myLen -1;
   END WHILE;
   IF  @i = 0 then
       SELECT 'No entries' AS result;
   END IF;
END;

CALL procedure_name('1story')

| myfield |
| :------ |
| 1stone  |
| 1stove  |
| 1stones |

✓

CALL procedure_name('abc')

| result     |
| :--------- |
| No entries |

✓

db<>fiddle 在这里

评论

0赞 4/30/2022
非常感谢，这非常有效。但是为什么我的代码不起作用呢？
0赞 nbk 4/30/2022
MySQL没有exec

上一个：是否有更好的查询来获得最匹配的结果

下一个：REGEXP 单词匹配