Mysql cursor cannot find data problem and solution

A few days ago, I encountered a problem of mysql The cursor could not find data. This problem troubled me for two days, and now it is finally solved. Let me share it with you.

This is a table creation statement, insert two rows of data

drop table如果存在testinfo;
创建表testinfo
（
name varchar（10）not null，
年龄int
）;
truncate table testinfo;
插入testinfo（）值（'你'，15）;
插入testinfo（）值（'me'，20）;

Query the table data of

mysql> select name，age from testinfo; 
+ ------ + ------ + 
| 名称| 年龄| 
+ ------ + ------ + 
| 你| 15 | 
| 我| 20 | 
+ ------ + ------ + 
这是使用游标的存储过程 
drop procedure if exists test_proc;
分隔符//
创建过程test_proc（）
开始
DECLARE done int default false;
DECLARE一个char（10）;
DECLARE b int;
DECLARE cur1游标用于选择名称，age from testinfo;
DECLARE继续处理SQLSTATE'02000'set done = true;
打开cur1;
read_loop：loop
        FETCh cur1进入a，b;
        如果这样做了
                离开read_loop
        万一;
        选择@ a，@ b;
        插入testinfo值（@ a，@ b）;
端环;
关闭cur1;
结束
//
分隔符
调用test_proc（）;

The execution results are as follows

mysql> \。/opt/mysql/test_proc.sql 
查询行，受影响的0行，1个警告（0.00秒） 
查询行，受影响（0.02秒）0行 
+ ------ + ------ + 
| @a | @b | 
+ ------ + ------ + 
| NULL | NULL | 
+ ------ + ------ + 
1行集（0.00秒）

There is an error in the above: 1048 (23000): Column 'name' cannot be empty

I don't know where the error is, why can't I get
the data? It’s been two days, and now I finally know. It should be changed like this, as follows:

Insert the testinfo value (@ a, @ b); change it to insert the testinfo value (a, b); That’s it.
Declare the variables a, b, and then pass The cursor assigns a value to it, but it does not assign a value to @ a, @ b...

So if you encounter a problem, it can be easily solved if you look carefully.

【Related recommendations】

1. Free mysql online video tutorial

2. MySQL latest manual tutorial

3. Those things about database design

The above is the detailed content of Mysql cursor cannot find data problem and solution. For more information, please follow other related articles on the PHP Chinese website!