MariaDB/MySQL UPDATE 语句具有多个连接，包括范围连接

我有桌子

登录历史

create table login_history
(
    id         int auto_increment primary key,
    ip         int unsigned,
    created    datetime(6)  not null,
    uid    int unsigned not null,
);

IP 到位置表

create table ip2location
(
    ip_from      int unsigned not null primary key,
    ip_to        int unsigned null,
    country_code char(2)      null,
)

帐户表

create table account
(
    uid               int unsigned not null primary key,
);

一些订单

create table order
(
    id             int auto_increment primary key,
    item_id        varchar(20)       not null,
    price          int               not null,
    timestamp      datetime(6)       not null,
    country_code   char(2)           null,
    uid            int unsigned      null
)

所有表都有适合这个问题的索引.

update order

left join account using(uid)
left join (
    select uid, 
           LAST_VALUE(ip) over (PARTITION BY uid) as `ip_int`
    from login_history
    where created >= '{{ current_date }}'
    and created < '{{ next_date }}'
    group by user_id
    ) as lh
on account.uid = lh.uid
left join ip2location as ip on
    (ip.ip_from < login_history.ip_int and ip.ip_to >= login_history.ip_int)
    or
    (ip.ip_from = lh.ip_int)
set
    order.country_id = ip.country_id
where order.country_id is null;

left join ip2location as ip on
        (ip.ip_from <= login_history.ip_int and ip.ip_to >= login_history.ip_int)
        or
        (ip.ip_from = lh.ip_int)

select uid, 
               LAST_VALUE(ip) over (PARTITION BY uid) as `ip_int`
        from login_history
        where created >= '{{ current_date }}'
        and created < '{{ next_date }}'
        group by user_id

update orders o
set country = (
    select il.country_code
    from login_history lh
    inner join ip2location il on lh.ip >= il.ip_from and lh.ip_to < il.ip_to
    where lh.created <= o.timestamp and lh.uid = o.uid
    order by lh.created desc limit 1
) 
where o.country_id is null