.jpg)
在 mysql 中，update join 語句中包含 order by 會(huì)導(dǎo)致 mysql 崩潰嗎？
問題
在使用 update join 語句時(shí)，加入 order by 會(huì)導(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 '流水號(hào) | 與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ù)約推送時(shí)間（yyyymmddhhmmss）', `push_type` char(1) not null comment '推送種類(1:推送配置項(xiàng)/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 '配置項(xiàng)id', `push_begin_time` char(17) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '00000000000000000' comment '推送開始時(shí)間（yyyymmddhhmmssfff）', `push_end_time` char(17) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '00000000000000000' comment '推送結(jié)束時(shí)間（yyyymmddhhmmssfff）', `push_duration_s` int not null default '0' comment '推送耗時(shí)', `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)建時(shí)間', `update_by` varchar(32) character set utf8mb4 collate utf8mb4_0900_ai_ci default null comment '更新人', `update_time` datetime default null comment '更新時(shí)間', 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ù)約推送時(shí)間(yyyymmddhhmmss)', `push_begin_time` char(17) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '00000000000000000' comment '推送開始時(shí)間(yyyymmddhhmmssfff)', `push_end_time` char(17) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '00000000000000000' comment '推送結(jié)束時(shí)間(yyyymmddhhmmssfff)', `push_duration_s` int not null default '0' comment '推送耗時(shí)', `effective_time` char(17) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '00000000000000000' comment '實(shí)際生效時(shí)間(yyyymmddhhmmssfff)', `invalid_time` char(17) character set utf8mb4 collate utf8mb4_0900_ai_ci not null default '00000000000000000' comment '實(shí)際失效時(shí)間(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)建時(shí)間', `update_by` varchar(32) character set utf8mb4 collate utf8mb4_0900_ai_ci default null comment '更新人', `update_time` datetime default null comment '更新時(shí)間', 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)';
答案
在 mysql 中，update join 語句中不允許包含 order by，否則會(huì)報(bào)錯(cuò) incorrect usage of update and order by。
原因
update join 語句的含義是更新加入另一個(gè)表中滿足條件的行。當(dāng)使用 order by 時(shí)，mysql 無法確定哪個(gè)行應(yīng)該被更新。
解決方案
要避免此錯(cuò)誤，可以將 order by 移到子查詢或使用笛卡爾乘積：
update cfg_dev_network_push_status set push_retry_times = push_retry_times + 1,
? 版權(quán)聲明
文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載。
THE END
.png)
