ERROR 1172 (42000): Result consisted of more than one row?

你是用SELECT INTO OR 子查询？只能返回单一记录。
贴你的记录及要求结果、SQL语句出来

不是游标的问题是你的SQL语句中出现了应该是一个值的地方，你返回了一个集。比如 update tblXX set cola=(select id from tlbYY where id<10)又或者select * from tblXX where id =(select id from tblYY where id>10);

你的SQL语句是什么？表结构是什么？记录是什么样？需要的结果是什么样。这样比较容易查找你的问题，及给出解决方案。

写一个存储过程，作用是去重
例如表一user
         id   name
    表二stu
         id   class
因为表一中name可能重复，所以要去重，保留最小的那个id。同时要将表二中的相应的id改变成最小的那个id
drop procedure if exists user_pro;
create procedure user_pro ()
begin
  declare xid varchar(10);
     select min(id) into xid from user
     group by name having count(name)>1;     update stu
     set id=xid
     where id in (select id from user where name in (select name from use where id=xid));     delete from user
     where name in (select name from user group by name having count(name)>1) and id!=xid;
end

因为表一中name可能重复，所以要去重，保留最小的那个id:
delete a from b1 a left join
(select name,min(id) as mi from b1 group by name) b
on a.name=b.name and a.id=b.mi where b.name is null

表一中id是不重复的，name重复

比较简单的方法是delete u,s from user u inner join stu s on u.id=s.id inner join (
select id from user a where exists (select id from user where name=a.name and id>a.id)
) b on u.id=b.id

你的存储过程中select min(id) into xid from user group by name having count(name)>1;这一句返回的是一个记录集，不是一个值。所以错误。只能通过游标来处理。
或者
select group_concat(min_id) into xid from (sellect name ,min(id) as min_id from user group by name) c
delete from stu where not FIND_IN_SET(id,xid);
delete from user where not FIND_IN_SET(id,xid);

第二张表stu不是删除信息，是修改信息

表一 user   （含有两列，id以及名字）
id     name
1      A
2      B
3      C
4      A
5      C
6      D
7      B
8      E
表二 stu   （含有两列，类别以及id）
class   id
1        2
2        1
3        3
4        7
5        1
6        8
7        4
8        4
9        5
10       2
在表一中，要求去重，即将name重复的记录删除，保留id最小
在表二中，将表一中去重的记录的id换成相应的最小id
例如，表一中name为A重复，A对应的id有1、4，所以在表一中删除4，而在表二中将id为4的记录修改为1
要求的结果如下：
表一 user
id   name
1     A
2     B
3     C
6     D
8     E
表二   stu
class    id
1        2
2        1
3        3
4        2
5        1
6        8
7        1
8        1
9        3
10       2

update b2 inner join
(select name,max(id) as ma,min(id) as mi from b1 group by name having count(*)>=2) c
on b2.id=c.ma
set b2.id=c.miselect name,min(id) from b1 group by name

在表一中，要求去重，即将name重复的记录删除，保留id最小mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
|  4 | A    |
|  5 | C    |
|  6 | D    |
|  7 | B    |
|  8 | E    |
+----+------+
8 rows in set (0.00 sec)mysql> delete u from user u,user u1 where u.name=u1.name and u.id>u1.id;
Query OK, 3 rows affected (0.08 sec)mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
|  6 | D    |
|  8 | E    |
+----+------+
5 rows in set (0.00 sec)mysql>

先做这一步在表二中，将表一中去重的记录的id换成相应的最小id
然后再  在表一中，要求去重，即将name重复的记录删除，保留id最小mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
|  4 | A    |
|  5 | C    |
|  6 | D    |
|  7 | B    |
|  8 | E    |
+----+------+
8 rows in set (0.00 sec)mysql> select * from stu;
+-------+------+
| class | id   |
+-------+------+
|     1 |    2 |
|     2 |    1 |
|     3 |    3 |
|     4 |    7 |
|     5 |    1 |
|     6 |    8 |
|     7 |    4 |
|     8 |    4 |
|     9 |    5 |
|    10 |    2 |
+-------+------+
10 rows in set (0.00 sec)mysql> update stu,(
    ->  select u1.id,min(u2.id) as m_id
    ->  from user u1,user u2 where u1.name=u2.name
    ->  group by u1.id) u
    -> set stu.id=u.m_id
    -> where stu.id=u.id;
Query OK, 4 rows affected (0.06 sec)
Rows matched: 10  Changed: 4  Warnings: 0mysql> select * from stu;
+-------+------+
| class | id   |
+-------+------+
|     1 |    2 |
|     2 |    1 |
|     3 |    3 |
|     4 |    2 |
|     5 |    1 |
|     6 |    8 |
|     7 |    1 |
|     8 |    1 |
|     9 |    3 |
|    10 |    2 |
+-------+------+
10 rows in set (0.00 sec)
mysql> delete u from user u,user u1 where u.name=u1.name and u.id>u1.id;
Query OK, 3 rows affected (0.05 sec)mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
|  6 | D    |
|  8 | E    |
+----+------+
5 rows in set (0.00 sec)mysql>

不需要游标update b2 inner join
(select name,max(id) as ma,min(id) as mi from b1 group by name having count(*)>=2) c
on b2.id=c.ma
set b2.id=c.midelete a from b1a a inner join b1a b
on a.name=b.name and a.id>b.id

晕，数据量比较大
又是上个问题The total number of locks exceeds the lock table size

SHOW ENGINE INNODB STATUS\G看看LOCK的情况。

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
090514 16:04:24 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 55 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 58653, signal count 58602
Mutex spin waits 0, rounds 168148, OS waits 6887
RW-shared spins 103231, OS waits 51610; RW-excl spins 89, OS waits 86
------------
TRANSACTIONS
------------
Trx id counter 0 4096580
Purge done for trx's n:o < 0 4096009 undo n:o < 0 0
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 2096, OS thread id 2747169680
MySQL thread id 111, query id 1279 localhost root
show engine innodb status
---TRANSACTION 0 4096570, not started, process no 2096, OS thread id 2701028240
MySQL thread id 98, query id 1129 localhost guoling
---TRANSACTION 0 0, not started, process no 2096, OS thread id 2746968976
MySQL thread id 59, query id 711 172.31.252.169 root
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
62776 OS file reads, 7 OS file writes, 7 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 7 862427748
Log flushed up to   7 862427748
Last checkpoint at  7 862427748
0 pending log writes, 0 pending chkp writes
10 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 19546924; in additional pool allocated 1048576
Dictionary memory allocated 483888
Buffer pool size   512
Free buffers       381
Database pages     130
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 863650, created 0, written 1
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 2096, id 2711718800, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 156489156
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================1 row in set (0.00 sec)

我用的存储过程
create procedure stu_pro ()
begin
（上述代码）end是否有影响
这么写对么？

你先用select 看一下到底有多少符合条件的记录要被删除。
然后看看这个系统统参数。max_write_lock_count 的值如果很大，看看 innodb_buffer_pool_size 的设置是多少。调大一些。

max_write_lock_count 是4294967295
我将innodb_buffer_pool_size 设置为800M
这些还不够么？还有那个LIMIT不是只能用在select中么？
在修改和删除中好用么？

limit 在update 和 delete 中均可使用。不过这样一次就无法update / delete全部记录，就需要你在存储过程中对row_count()进行判断循环。

REPEAT
....
delete from yourTble order by id limit 1000;
UNTIL row_count()=0 END REPEAT;

MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html

我搜索过了，但是没发现这中讲过row_count()

http://dev.mysql.com/doc/refman/5.1/zh/functions.html打开这个MySQL官方网页，然后ctrl + F搜索 ROW_COUNT实在有些无语了。

这里面就这些，没有详细的讲解了ROW_COUNT()
ROW_COUNT()返回被前面语句升级的、插入的或删除的行数。这个行数和 mysql 客户端显示的行数及 mysql_affected_rows() C API 函数返回的值相同。
mysql> INSERT INTO t VALUES(1),(2),(3);
问询完成, 表中有3 行 (0.00秒)
记录: 3 重复: 0 警告: 0

mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| 3 |
+-------------+
表中有1行 (0.00 秒)

mysql> DELETE FROM t WHERE i IN(1,2);
问询完成, 找到2 行 (0.00 秒)

mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| 2 |
+-------------+
表中有1行 (0.00 秒)

我是不是得提前对row_count()赋值啊？

create procedure M()
begin
repeat
update b2 inner join
(select name,max(id) as ma,min(id) as mi from b1 group by name having count(*)>=2) c
on b2.id=c.ma
set b2.id=c.mi
order bu b2.id limit 100;
until row_count()=0 end repeat;delete a from b1 a inner join b1 b
on a.name=b.name and a.id>b.id
end
这个存储过程错在哪里？

create procedure M()
begin
repeat
update b2 inner join
(select name,max(id) as ma,min(id) as mi from b1 group by name having count(*)>=2) c
on b2.id=c.ma
set b2.id=c.mi
order bu b2.id limit 100;
until row_count()=0 end repeat; delete a from b1 a inner join b1 b
on a.name=b.name and a.id>b.id
end
这个存储过程错在哪里？

报错信息是什么？建议看一下。create procedure M()
begin
repeat
update b2 inner join
(select name,max(id) as ma,min(id) as mi from b1 group by name having count(*)>=2) c
on b2.id=c.ma
set b2.id=c.mi
order bu b2.id limit 100;
until row_count()=0
end repeat;

delete a from b1 a inner join b1 b
on a.name=b.name and a.id>b.id
end

拼写错误
错误是：
ERROR 1064 (42000):you have an error in your SQL syntax;check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6
ERROR 1064 (42000):you have an error in your SQL syntax;check the manual that corresponds to your MySQL server version for the right syntax to use near 'until cow_count()=0 end repeat' at line 1
ERROR 1064 (42000):you have an error in your SQL syntax;check the manual that corresponds to your MySQL server version for the right syntax to use near 'end' at line 3

错误是：
ERROR 1064 (42000):you have an error in your SQL syntax;check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6
ERROR 1064 (42000):you have an error in your SQL syntax;check the manual that corresponds to your MySQL server version for the right syntax to use near 'until cow_count()=0 end repeat' at line 1
ERROR 1064 (42000):you have an error in your SQL syntax;check the manual that corresponds to your MySQL server version for the right syntax to use near 'end' at line 3

order bu b2.id limit 100; 改成order by b2.id limit 100;
先把语法错误处理了再试试。

这是我程序的原版代码，之前怕大家看着晕，就把列名改简单了点
drop procedure if exists MsgContent_pro;
create procedure MsgContent_pro ()
begin
repeat
  update tbContent inner join (select msgContent,max(msgID) as ma,min(msgID) as mi from tbMsgContent group by msgContent having count(msgContent)>=2) c on tbContent.msgID=c.ma
  set tbContent.msgID=c.mi
  order by tbContent.msgID limit 100;
     until row_count()=0 end repeat;
  delete a from tbMsgContent a inner join tbMsgContent b
    on a.msgContent=b.msgContent and a.msgID>b.msgID
end
错误是
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'until row_count()=0 end repeat' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'end' at line 3
mysql>

没有问题啊。只是把你的语句重新格式化了一下。另外delete那句加了个分号。mysql> drop procedure if exists MsgContent_pro;
Query OK, 0 rows affected, 1 warning (0.48 sec)mysql>
mysql> delimiter //
mysql>
mysql> create procedure MsgContent_pro ()
    -> begin
    ->  repeat
    ->          update tbContent inner join
    ->                  (select msgContent,max(msgID) as ma,min(msgID) as mi
    ->                          from tbMsgContent
    ->                          group by msgContent
    ->                          having count(msgContent)>=2
    ->                  ) c
    ->                  on tbContent.msgID=c.ma
    ->          set tbContent.msgID=c.mi
    ->          order by tbContent.msgID limit 100;
    ->  until row_count()=0 end repeat;
    ->  delete a from tbMsgContent a inner join tbMsgContent b
    ->          on a.msgContent=b.msgContent and a.msgID>b.msgID;
    -> end;
    ->
    -> //
Query OK, 0 rows affected (0.08 sec)mysql>
mysql> delimiter ;
mysql>

Query OK, 0 rows affected, 1 warning (0.00 sec)ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'until row_count()=0 end repeat' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'end' at line 3
mysql> Aborted

发错了
mysql> source /home/guoling/桌面/xiangmu/procedure.sql
Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> call MsgContent_pro//
ERROR 1221 (HY000): Incorrect usage of UPDATE and ORDER BY
mysql>

汗，太多了也太大了
这个只是其中的一小部分
思想和我举得例子是一样的
调试是成功了
只是运行出现问题
这个问题是说update和order by的使用不正确吧？

即我怎么测试呢？自己随便建个表？为什么你不能提供你的建表的语句呢？只需要create table 语句啊，又不需要你的数据。

这两个表的够么？还要其他的么？
create table tbMsgContent( msgID bigint not null, msgContent tinytext not null, primary key (msgID));
create table tbContent( reID bigint not null, msgTypeID integer not null, isRequest boolean not null, msgID bigint not null, textID bigint, fileID bigint);

mysql> delimiter //
mysql>
mysql> create procedure MsgContent_pro ()
    -> begin
    ->  repeat
    -> update tbContent
    ->  set msgID=(     select mi from (select msgContent,max(msgID) as ma,min(msgID) as mi
    ->          from tbMsgContent
    ->          group by msgContent
    ->          having count(msgContent)>=2
    ->          ) c where ma=tbContent.msgID)
    ->  where msgID in (select ma from (select msgContent,max(msgID) as ma,min(msgID) as mi
    ->          from tbMsgContent
    ->          group by msgContent
    ->          having count(msgContent)>=2
    ->          ) c )
    ->  order by tbContent.msgID
    ->  limit 100;
    ->  until row_count()=0 end repeat;
    ->  delete a from tbMsgContent a inner join tbMsgContent b
    ->          on a.msgContent=b.msgContent and a.msgID>b.msgID;
    -> end;
    ->
    -> //
Query OK, 0 rows affected (0.01 sec)mysql>
mysql> delimiter ;
mysql> call MsgContent_pro();
Query OK, 0 rows affected (0.06 sec)mysql>

mysql> call MsgContent_pro//
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
mysql>

调试易

ERROR 1172 (42000): Result consisted of more than one row?

解决方案 »