Selfhosted

Use a dedicated DNS-service on your local network which has the local IPs in it's DB. Use that DNS-service as your first/primary DNS on your local network (settings).

Yes, this is called split horizon dns, basically, you have to control the DNS servers.

First, make a forwarding/resolver/cache, tell it to forward all requests for your_domain to your internal authoritative DNS server, it will return your internal IPs, externally though, when that domain is looked up, it will return the public ips of that host.

Depending on your wifi/router you could add a static DNS record for your domain while on the hi home network. The cached DNS will only matter while connected to the home wifi network. While on 5g you'll pick up the public DNS record. If it does some how cache the local DNS while public then shorten the ttl in the local DNS record

This is the perfect opportunity to set up a pihole. Its primary purpose is to block ads network wide but since it is essentially a DNS with a block list you can also set custom dns-entries.

Very possible and done all the time.

At home you run a local DNS server, plenty of options out there especially as a self-hoster: bind, power dns, microsoft DNS if you're into windows. you can also combine this with something like pihole to block ads and junk at the dns level.

You create the dns zone on your dns server, internal devices use that dns server. You create the same zone on a public DNS provider like cloudflare or whoever (or host your own if you feel like it, on your vps), with public IPs.

Any of your devices coming/going from your home should be using DHCP to obtain an IP. At home your DHCP settings would hand out the local DNS server, anywhere else you go you'll be using other DNS servers that will resolve the public IP. It should all be pretty seamless and transparent once set up.

Not exactly the solution you're describing but Tailscale or ZeroTier could be the solution to your problem.

They're P2P VPNs which don't require static IPs or even open ports.

I set up Tailscale to forward my home subnet via my NAS. That allows transparent access of the NAS itself and any other device on the home network no matter where I am (as long as there's internet).

There's some great advice here on how to accomplish this, but a note of caution: If you're doing this split DNS on a device that you expect to be able to walk out the door with and continue working properly.. Some apps will cache DNS lookups even beyond the configured TTL. Meaning that a running app that thinks your server has a particular IP might stop working as soon as you walk outside of WiFi range and that IP is no longer reachable. And it might stay not working for quite a while. Ask me how I know this. :D

for PCs you can just add a hosts file entry, but that's probably not possible/too much work on phones. split horizon DNS is what you want.