在 mysql 中，update join 語句中包含 order by 會導(dǎo)致 mysql 崩潰嗎？
問題
在使用 update join 語句時，加入 order by 會導(dǎo)致 mysql 崩潰。原因不明，求助大神。
建表語句
-- `lebon-infra-dev`.cfg_dev_network_push_status definition create table `cfg_dev_network_push_status` ( `id` bigint not null auto_increment comment '流水號 | 與id為同一字段，自增數(shù)列', `task_id` varchar(16) not null comment '任務(wù)id', `push_status` char(1) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '0' comment '推送狀態(tài)(0:待推送/1:已推送/2:已取消)', `push_plan_time` char(14) character set utf8mb4 collate utf8mb4_0900_ai_ci not null comment '預(yù)約推送時間（yyyymmddhhmmss）', `push_type` char(1) not null comment '推送種類(1:推送配置項/2:推送優(yōu)先度/3:推送重啟命令)', `dev_id` varchar(32) not null comment '設(shè)備id', `config_group_id` varchar(16) not null comment '配置組id', `config_item_id` varchar(16) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '' comment '配置項id', `push_begin_time` char(17) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '00000000000000000' comment '推送開始時間（yyyymmddhhmmssfff）', `push_end_time` char(17) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '00000000000000000' comment '推送結(jié)束時間（yyyymmddhhmmssfff）', `push_duration_s` int not null default '0' comment '推送耗時', `push_result` char(1) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '' comment '推送結(jié)果0:成功/1:失敗', `fail_reason` varchar(128) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '' comment '失敗原因', `push_retry_times` int not null default '0' comment '重試次數(shù)', `remark` varchar(256) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '' comment '備注', `del_flag` char(1) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '0' comment '刪除狀態(tài)', `version` int not null default '0' comment '版本鎖', `create_by` varchar(32) character set utf8mb4 collate utf8mb4_0900_ai_ci not null comment '創(chuàng)建人', `create_time` datetime not null comment '創(chuàng)建時間', `update_by` varchar(32) character set utf8mb4 collate utf8mb4_0900_ai_ci default null comment '更新人', `update_time` datetime default null comment '更新時間', primary key (`id`), unique key `cfg_dev_network_push_status_un` (`id`) using btree ) engine=innodb auto_increment=149 default charset=utf8mb4 collate=utf8mb4_0900_ai_ci comment='配置-終端網(wǎng)絡(luò)配置推送狀態(tài)'; -- `lebon-infra-dev`.cfg_dev_network_bind_status definition create table `cfg_dev_network_bind_status` ( `id` bigint not null auto_increment comment '任務(wù)id | 與id為同一字段，自增數(shù)列', `dev_id` varchar(32) not null comment '設(shè)備id', `config_group_id` varchar(16) not null comment '配置組id', `config_group_name` varchar(32) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '' comment '配置組名', `status` char(1) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '0' comment '狀態(tài)', `push_plan_time` char(14) character set utf8mb4 collate utf8mb4_0900_ai_ci not null comment '預(yù)約推送時間(yyyymmddhhmmss)', `push_begin_time` char(17) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '00000000000000000' comment '推送開始時間(yyyymmddhhmmssfff)', `push_end_time` char(17) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '00000000000000000' comment '推送結(jié)束時間(yyyymmddhhmmssfff)', `push_duration_s` int not null default '0' comment '推送耗時', `effective_time` char(17) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '00000000000000000' comment '實際生效時間(yyyymmddhhmmssfff)', `invalid_time` char(17) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '00000000000000000' comment '實際失效時間(yyyymmddhhmmssfff)', `fail_reason` varchar(128) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '' comment '失敗原因', `cancel_reason` varchar(128) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '' comment '取消原因', `operator` varchar(32) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '' comment '操作者', `remark` varchar(256) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '' comment '備注', `version` int not null default '0' comment '版本鎖', `del_flag` char(1) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '0' comment '刪除狀態(tài)', `create_by` varchar(32) character set utf8mb4 collate utf8mb4_0900_ai_ci not null comment '創(chuàng)建人', `create_time` datetime not null comment '創(chuàng)建時間', `update_by` varchar(32) character set utf8mb4 collate utf8mb4_0900_ai_ci default null comment '更新人', `update_time` datetime default null comment '更新時間', primary key (`id`) using btree, unique key `cfg_dev_network_bind_status_un` (`id`) using btree ) engine=innodb auto_increment=1070 default charset=utf8mb4 collate=utf8mb4_0900_ai_ci comment='配置-終端網(wǎng)絡(luò)配置綁定狀態(tài)';
登錄后復(fù)制
答案
在 mysql 中，update join 語句中不允許包含 order by，否則會報錯 incorrect usage of update and order by。
原因
update join 語句的含義是更新加入另一個表中滿足條件的行。當(dāng)使用 order by 時，mysql 無法確定哪個行應(yīng)該被更新。
解決方案
要避免此錯誤，可以將 order by 移到子查詢或使用笛卡爾乘積：
update cfg_dev_network_push_status set push_retry_times = push_retry_times + 1,
登錄后復(fù)制
.png)
推廣.jpg)