我在mysql中使用一组筛选条件来做select,可以得到多条记录
但是改写成delete以后,用同样的,一模一样的筛选条件,却只能删除一条记录。每次我重跑该delete语句的时候,都可以删除一条记录,但是也一直是只能删除一条记录。请问这是什么原因?where语句比较复杂。里面总共有11句“(字段名='' or 字段名 is null)”,以and相连。还有三句 “id not in (子查询)”也是and关系。当我删除掉一些条件以后,就可以delete多条了。请问是因为条件太多了么?各位高人,麻烦帮忙,谢谢啦。
但是改写成delete以后,用同样的,一模一样的筛选条件,却只能删除一条记录。每次我重跑该delete语句的时候,都可以删除一条记录,但是也一直是只能删除一条记录。请问这是什么原因?where语句比较复杂。里面总共有11句“(字段名='' or 字段名 is null)”,以and相连。还有三句 “id not in (子查询)”也是and关系。当我删除掉一些条件以后,就可以delete多条了。请问是因为条件太多了么?各位高人,麻烦帮忙,谢谢啦。
where given_name = 'สมาชิà¸à¹ƒà¸«à¸¡à¹ˆ'
and (mbl_nbr='' or mbl_nbr is null)
and (email=''or email is null)
and (home_addr_city = ''or home_addr_city is null )
and (home_addr_strt = ''or home_addr_strt is null )
and (home_tel_nbr = '' or home_tel_nbr is null)
and (work_addr_strt is null or work_addr_strt = '')
and (other_tel_nbr = '' or other_tel_nbr is null)
and (home_addr_pcode='' or home_addr_pcode is null)
and (birth_date='' or birth_date is null or birth_date = '0000-00-00')
and (nric='' or nric is null)
and (family_name = ''or family_name is null)
and member_id not in
(
select distinct member_id
from customer_abc._t_sales_detail
)
and member_id not in
(select distinct memberId
from customer_abc.event_2009_target)
and member_id not in
(
select distinct memberId
from customer_abc.event_2008_target
)
and member_id not in
(
select distinct memberId
from customer_abc.event_2007_target
)
;
where given_name = 'à?aà??à?2à??à?′à??à1?à??à??à1?'
and (mbl_nbr='' or mbl_nbr is null)
and (email=''or email is null)
and (home_addr_city = ''or home_addr_city is null )
and (home_addr_strt = ''or home_addr_strt is null )
and (home_tel_nbr = '' or home_tel_nbr is null)
and (work_addr_strt is null or work_addr_strt = '')
and (other_tel_nbr = '' or other_tel_nbr is null)
and (home_addr_pcode='' or home_addr_pcode is null)
and (birth_date='' or birth_date is null or birth_date = '0000-00-00')
and (nric='' or nric is null)
and (family_name = ''or family_name is null)
and member_id not in
( select distinct member_id
from customer_abc._t_sales_detail)
and member_id not in
( select distinct memberId
from customer_abc.event_2009_target)
and member_id not in
( select distinct memberId
from customer_abc.event_2008_target)
and member_id not in
( select distinct memberId
from customer_abc.event_2007_target)
;
1.名字是个特定的字符串的,原文是中文,我用的mySQL yog的客户端,显示是乱码,我就用乱码来query了。
2.相关的一些会员属性的字段内容为空,但不是所有的属性,只有我写在query里面的那些。
3.不存在在_t_sales_detail ,event_2009_target,event_2008_target,event_2007_target 四张表里面。当我把第一句delete from customer_abc._t_member 改成select count(*) from customer_abc._t_member 的时候,有8千多条记录,但是当我删除的时候,就只能一次删除一条。不知道为什么。
有可能是MySQL服务器的设置问题么?
mysql> select count(*) from tx;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.06 sec)mysql> delete from tx where id>3;
Query OK, 0 rows affected (0.06 sec)mysql>
发现当我屏蔽掉and (home_addr_strt = ''or home_addr_strt is null )这一句的时候,就会全部删除。有没有可能跟这个字段有关?
5340(1 row(s)returned)
(594 ms taken)2. delete from...跑完以后,得到:(1 row(s)affected)
(0 ms taken)3. 再跑select count(*)...跑完以后,得到:count(*)
5339(1 row(s)returned)
(546 ms taken)4. 再跑delete from...跑完以后,得到:(1 row(s)affected)
(0 ms taken)以此类推。
会不会是因为有内容超过长度被截断了,截得又不对,造成这样的问题?
customer_abc._t_member check status OKanylize table customer_abc._t_member EXTENDED 的查询结果:Table Op Msg_type Msg_text
customer_abc._t_member analyze status Table is already up to date看起来没什么
customer_abc._t_member check status OK
anylize table customer_abc._t_member 的查询结果:Table Op Msg_type Msg_text
customer_abc._t_member analyze status Table is already up to date刚刚copy错了
2. 复制表,将home_addr_strt这个字段从vchar(255)改到vchar(500),问题仍然存在
3. 新建一个一样的表结构,将home_addr_strt字段从vchar(255)改到vchar(500),然后写一个select * 的insert 语句,将数据插入,问题仍然存在
4. 新建一个一样的表结构,将home_addr_strt字段从vchar(255)改到vchar(500),然后写一个select * from 原表 where given_name <> 'สมาชิà¸à¹ƒà¸«à¸¡à¹ˆ' 的insert,再写一个select * from 原表 where given_name = 'สมาชิà¸à¹ƒà¸«à¸¡à¹ˆ' 的insert,其实还是把所有的人插入,此时测试,问题仍然存在
5. 新建一个一样的表结构,将home_addr_strt字段从vchar(255)改到vchar(500),然后先插入select * from 原表 where given_name = 'สมาชิà¸à¹ƒà¸«à¸¡à¹ˆ' 的insert,再写一个select * from 原表 where given_name <> 'สมาชิà¸à¹ƒà¸«à¸¡à¹ˆ' 的insert,其实就是把第四点的两个语句换了一下顺序,此时测试,问题不再存在
其他方式暂时不能重现该问题。由此推测,问题应该是存在于home_addr_strt字段的长度与内容本身的某些不协调。而且不协调的记录应该是存在在内容过长的记录。
因为given_name = 'สมาชิà¸à¹ƒà¸«à¸¡à¹ˆ' 的人home_addr_strt的长度很短或者没有。这个是当前总结的一些状态。
不过 not in太多了,如果后面的sub-select搞出很多行数据,会影响效率的。
初步可以判断,应该是跟given_name = 'สมาชิà¸à¹ƒà¸«à¸¡à¹ˆ' 有关。
你不妨做一个试验,造一张一样的表,把given_name的值换成普通的ascii串,然后改一下sql中的given_name的值,看看delete是否如你所愿。我怀疑是select和delete对条件的判断可能不一样。
BTW, 能顺便报一下你的mysql具体的版本号吗? 如:mysql> select @@version
-> ;
+-----------------+
| @@version |
+-----------------+
| 5.1.31-1ubuntu2 |
+-----------------+
1 row in set (0.00 sec)