Mysql update 索引

执行mysql update,或者delete的时候会遇到：

You can't specify target table for update in FROM clause

相关的原因自不必说：下面有stackoverflow中的帖子：

https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause

我采取的是将id ,放到一个临时表中；

(2):https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause

   添加select:

    

UPDATE tbl SET col = (
  SELECT ... FROM (SELECT.... FROM) AS x);

    这里面讲述了，mysql 5.7以上添加select 还是不好用，

However, beware that from MySQL 5.7.6 and onward, the optimiser may optimise out the subquery, and still give you the error. Luckily, the optimizer_switch variable can be used to switch off this behaviour; although I couldn't recommend doing this as anything more than a short term fix, or for small one-off tasks.

SET optimizer_switch = 'derived_merge=off';

Thanks to Peter V. Mørch for this advice in the comments.

Example technique was from Baron Schwartz, originally published at Nabble, paraphrased and extended here.

 

(3);执行带索引：

    1\  update t set t.sex = 'm' where t.id in (select id from d);   //explain 感觉很慢；

   正确做法：

    update t,d set t.sex = 'm'where t.id = d.id; //soso..