UPDATE - Apple support (on chat) was unable to fix this and they moved me from Music to OS/MacBook support, as I do not have the problem on my synced iPhone, only on the MacBook.
To cut a long story short, I seem to have fixed it by following the steps below. I don't promise it will work for you, and I always backup everything (multiple times), because the thought of losing my collection is a source of distress.
I went to Music app Settings > Files > Music Media folder location
I made a note of the location listed there.
Then I went to make a duplicate of the folder at that location. (For me it is a very big folder, as I have hundreds of CDs uploaded there. I will keep the copy on an external backup drive too, as I don't want to have to upload everything again.)
Then I relocated the original folder within another folder on the MacBook, and I pointed to the new folder from Settings > Files > Music Media folder location.
The system then prompted me (sorry, I don't have the exact working, as I did not take a snapshot) if I wanted to preserve the organisation that I had previously had.
To this I clicked YES (as I assumed this would keep the tracks in their albums/Artist organisation etc).
It then went through every song title and updated the links. It did this at a very fast pace, though it still took a few minutes to get through the thousands of tracks. But you can actually see the titles fly past, so you don't interrupt the process before it is complete.
And now Apple Music is playing everything as expected. This includes my uploaded CD tracks, as well as some Apple Music that had somehow also fallen victim. (I did not realise this when I made my initial post.)
I think renaming, or moving the Music Media folder simply forced it to rebuild its directory, and so far it seems to be working just as it should.
As I said, I would backup everything before doing this, and keep a separate copy of your Music Media folder for protection, but this worked for me.
I hope it works for you too.