mysqlSQL问题汇总（持续更新）

1、异常处理：

问题：项目中需要将4张表合并，其中遇到了主键冲突导致insert into无法进行。

解决：跳过冲突，继续插入。添加异常处理语句：

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;

2、联表删除数据时提示：

 ERROR 1093 (HY000): You can't specify target table 'bai' for update in FROM clause

两张表联结查询后更新字段：

表：member_own 

结构：

+----------+----------+---------+------------+
| idx | id | owncash | newbuycash |
+----------+----------+---------+------------+
| 10000000 | 10000000 | 0 | 100 |
| 10000001 | 10000001 | 0 | 100 |
| 10000002 | 10000002 | 0 | 2 |
| 10000003 | 10000003 | 0 | 11 |
| 10000004 | 10000004 | 0 | 5540 |
| 10000005 | 10000005 | 0 | 5580 |
| 10000006 | 10000006 | 0 | 5580 |
| 10000007 | 10000007 | 0 | 5680 |
| 10000008 | 10000008 | 0 | 5820 |
| 10000009 | 10000009 | 0 | 5720 |
+----------+----------+---------+------------+

表：pay_bill 

结构：

+----+----------+----------+-------+--------------+--------+----------+------------------------+------------------------------+---------------------+---------------------+---------------------+-------------+-----------+
| id | user_idx | user_id | fee | content | status | pay_type | bill_no | pay_no | create_time | update_time | pay_time | refund_time | coin_type |
+----+----------+----------+-------+--------------+--------+----------+------------------------+------------------------------+---------------------+---------------------+---------------------+-------------+-----------+
| 1 | 20000001 | 20000001 | 0.01 | ddd | 2 | 1 | D201807191548032761864 | 2018071921001004900554822419 | 2018-07-18 16:10:50 | 2018-07-21 15:53:09 | 2018-08-15 09:47:57 | NULL | CNY |
| 2 | 20000006 | 20000006 | 10.00 | 充值金币 | 1 | 1 | L201807211733583555977 | NULL | 2018-07-21 18:10:47 | NULL | NULL | NULL | NULL |
| 3 | 20000005 | 20000005 | 0.01 | 充值金币 | 1 | 1 | L201807231425465305145 | NULL | 2018-07-25 21:03:55 | NULL | NULL | NULL | NULL |
| 4 | 20000005 | 20000005 | 0.01 | 升级vip | 2 | 1 | L201807231425515978886 | 2018072521001004900579392043 | 2018-07-25 21:18:17 | 2018-07-25 21:29:23 | 2018-08-15 09:47:57 | NULL | CNY |
| 5 | 20000005 | 20000005 | 0.01 | 充值金币 | 2 | 1 | L201807231425053005631 | 2018072521001004900579812474 | 2018-07-25 21:34:09 | 2018-07-25 21:34:21 | 2018-08-15 09:47:57 | NULL | CNY |
| 6 | 20000006 | 20000006 | 10.00 | 充值金币 | 1 | 1 | L201807260944051527041 | NULL | 2018-07-26 09:44:18 | NULL | NULL | NULL | NULL |
| 7 | 20000006 | 20000006 | 21.00 | 升级vip | 1 | 1 | L201807260945045520061 | NULL | 2018-07-26 09:45:16 | NULL | NULL | NULL | NULL |
| 8 | 20000006 | 20000006 | 21.00 | 升级vip | 1 | 1 | L201807260950139344449 | NULL | 2018-07-26 09:50:26 | NULL | NULL | NULL | NULL |
| 9 | 20000006 | 20000006 | 10.00 | 充值金币 | 1 | 1 | L201807260954551633911 | NULL | 2018-07-26 09:55:07 | NULL | NULL | NULL | NULL |
| 10 | 20000012 | 20000012 | 10.00 | 充值金币 | 1 | 1 | L201807261000361676932 | NULL | 2018-07-26 10:01:06 | NULL | NULL | NULL | NULL |
+----+----------+----------+-------+--------------+--------+----------+------------------------+------------------------------+---------------------+---------------------+---------------------+-------------+-----------+

语句：update member_own set owncash = 0 where idx in ( select idx from member_own where owncash>0 and idx not in( select a.idx from member_own a,pay_bill b where a.idx = b.user_idx and b.`status` = '2'));

修改后：

update member_own set owncash = 0 where idx in ( select idx from (select idx from member_own where owncash>0 and idx not in( select idx from (select idx from member_own a,pay_bill b where a.idx = b.user_idx and b.`status` = '2') c)) d);

两个子查询的地方外面再包一层中间表，也就是再select一次。

因为在查询时更新了数据，在更新时查询了数据。

解决，在里面封装一层（注意后面要加别名，不然会报错）：

delete from get where id in (select id from (select id from get where id = '2222222' group by id having count(id)>1) as a);

3、查询时报错：

Column 'id' in order clause is ambiguous;

这是因为没有指定明确的列名导致。

4、重复数据去重，只保留一条数据：

delete from ii where id in (select id from (select id from ii group by id having count(id) > 1) a) and name not in (select * from (select min(name) from ii group by name having count(name) > 1 ) b);

通过两列来确定去重数据，id和name列。

5、隐式转换导致索引失效。

表中的字段为varchar类型，查询语句的条件为数值类型，没有加引号，所以发生了隐式转换。导致全表扫描，索引失效。

解决方法：（1）：查询条件上加引号。（2）修改字段类型，改为数值类型。