mysql中游标中的记录数量不迭代？ [英] mysql number of records in cursor without iterating?

问题描述

  select id，fullname from user where fullname like concat（lastname ，''，firstname，'（'，middlename，'％'）; 
  

0，然后

  select id，fullname from user where fullname like concat（lastname，''，firstname，'％'）; 
 
 ....根据结果很少有查询，
  

尝试编写过程mysql过程，因为我使用的是mysql游标，

  DECLARE user_cnt CURSOR FOR select id，fullname from users其中fullname像concat（lastname，''，firstname，'（'，middlename，'％'）; 
  

现在我想检查user_cursor中的记录数，以便我可以检查条件如果查询返回0条记录然后

如何查找记录数

解决方案

在游标之前进行计数：

  select count（*）
 into @user_cnt 
 from users 
其中fullname like concat（
 lastname，
，
 firstname，
'（'，
 middlename，
'％'
）; 
 

编辑：
如果您想在打开游标后进行操作，请尝试： / p>



 打开your_cursor; 
选择FOUND_ROWS（）into user_cnt; 
  




I am trying to write mysql procedure for below logic,

select id, fullname from users where fullname like concat(lastname, ' ', firstname, ' (' , middlename, '%');

if above query returns 0 records then

    select id, fullname from users where fullname like concat(lastname, ' ', firstname, '%');

.... few more queries depending upon result,

I am trying to write procedure mysql procedure , in that, I am using mysql cursor,

DECLARE user_cnt CURSOR FOR select id, fullname from users where fullname like concat(lastname, ' ', firstname, ' (' , middlename, '%'); 

now I want to check number of records in user_cursor so that I can check the condition "if query returns 0 records then"

How can I find the number of records in mysql cursor without iterating on it ?

解决方案

Do a count before the cursor:

select count(*)
into @user_cnt
from users
where fullname like concat (
    lastname,
    ' ',
    firstname,
    ' (',
    middlename,
    '%'
    );

EDIT: If you want to do it after OPENING the cursor, try doing:

OPEN your_cursor;
select FOUND_ROWS() into user_cnt ;

这篇关于mysql中游标中的记录数量不迭代？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！