MySQL更新视图v_licence_data报目标表不可更新错误如何解决

问题根因 #

MySQL多表关联视图的更新有额外的隐含限制，你大概率忽略了以下两个要求：

• 仅使用MERGE算法的视图支持更新，如果你创建视图时指定了ALGORITHM = TEMPTABLE，或者MySQL自动判定用临时表生成视图结果，视图就不可更新
• 更新操作仅能修改单个基表的列，且视图中的每一行必须和要更新的基表行是1:1映射的，不能出现一行视图行对应多行基表行的情况

解决方法 #

方案1（推荐，无需修改视图）：直接更新基表

绕过视图更新的限制，直接通过licence_id关联更新users表的对应字段即可，语句如下：

UPDATE users 
SET email = 'test'
WHERE id = (
    SELECT user_id FROM licences 
    WHERE licence_id = '6787C1-07B5F5-CDC649-28D156-A8706B-V3'
);

方案2：调整视图定义支持更新

如果你必须通过视图执行UPDATE操作，先修改视图创建语句，显式指定使用MERGE算法：

CREATE OR REPLACE ALGORITHM = MERGE VIEW v_licence_data AS
SELECT licences.user_id AS user_id,
       licences.licence_id AS licence_id,
       licences.licence_created AS licence_created,
       licences.licence_expiry AS licence_expiry,
       licences.licence_key AS licence_key,
       licences.machine_id AS machine_id,
       users.email AS email,
       users.first_name AS first_name,
       users.last_name AS last_name,
       users.date_created AS date_created,
       machines.machine_fingerprint AS machine_fingerprint,
       licences.id AS id
FROM licences
LEFT JOIN users ON licences.user_id = users.id
LEFT JOIN machines ON licences.machine_id = machines.id;

修改完成后即可正常执行你原来的UPDATE语句。

内容的提问来源于stack exchange，提问作者Sam