今天去做一个数据清洗的任务,在执行数据变更的时候遇到了一个奇怪的问题.
类似于这种SQL (隐去公司业务相关性)
我是犬类爱好者,所以假如我有一个记载各种狗的id和名称的表
table lovelydog
| dogId | name | description | intelligenceRank |
|---|---|---|---|
| 1 | Labrador Retriever(拉布拉多) | cheerful,gentle friendly,intelligent | 7 |
| 2 | German Shepherd (德牧羊) | courageous,intelligent,loyal,watchful | 3 |
| 3 | Golden Retriever (金毛) | Intelligent,Kind,Friendly,Confident | 4 |
| 4 | Bulldog (沙皮) | Frendly,docile,Willful,Gregarious | 77 |
| 5 | Beagle(比格) | Gentle,Intelligent,Even Tempered,Determined | 72 |
delete from `lovelydog` where intelligenceRank
in (select max(intelligenceRank)from `lovelydog` )
非常简单,我从表中剔除最笨的一种狗狗.
但是执行数据变更的时候报出 MYSQL error “You can’t specify target table X for update in FROM clause”.
为什么流程不和我们想象的一样顺化,先从表中找出智力的最小值,然后把智力为该值的狗狗从表中delete.
google了一番发现了如下几个没有思考过的问题.
Recursive Query 查询递归
Mysql 引擎不允许我们在做DML(delete/insert/update)的时候在subquery里面去reference当前table. 这一点在mysql 的手册UPDATE下面最后一行有指出.”You cannot update a table and select from the same table in a subquery.”
为什么呢?
因为这里有一个query cycle.
在stackoverflow上有如下解释
Think about the semantics. Either MySQL has to keep a copy of the table before the update started, or the inner query might use data that has already been updated by the query as it’s in progress. Neither of these side-effects is necessarily desirable, so the safest bet is to force you to specify what will happen using an extra table. – siride
This is because your update could be cyclical… what if updating that record causes something to happen which made the WHERE condition FALSE? You know that isn’t the case, but the engine doesn’t. There also could be opposing locks on the table in the operation.
所以这是一个reasonable design decision.
引擎在两难的困境不知道怎么做,所以把这个顺序决定权交在了user手中.
解决:创造新的视图
这个问题的解决方式是一个简单的变通,我们在subquery中创建一个新的视图,从而force engine来首先进行subquery.
delete from `lovelydog` where intelligenceRank
in (select * from (select max(intelligenceRank)from `lovelydog` )AS X )
