There's a mod available to remove that artificial 500 mA limit Nintendo putted in place. It's one IC per USB port which cuts the power when > 500 mA gets drawn and the mod is to simply bridge the 5 V rail over these ICs.
It's better to use 2.5" HDDs as they require less power (with a 3.5" one a Y cable might still not deliver enough power under some load scenarios).
Not sure what brands you buy but I have 15 years old HDDs running just finen (some of them 24/7). My oldest still running HDDs are way older than my SSDs. There's just one brand I trust through: Wester Digital. Had bad experiences with others.
Also so far I was able to recover the data of all except one failing HDD. That one was helium filled and the helium leaked out. Sadly I can't say the same about flash based media.
Yes you can bridge the power pins, but you are still limited to the same total power, if you want to use 2 USB HDDs as well as USB peripherals, it could be not enough, so it's not an ideal solution (and many people understandably might not be willing to hardware mod their Wii U when other solutions exist)
You are mistaken. There are no 3.5" drives that are USB powered. 3.5" drives always have a separate power supply, they run on 12V and not the 5V that USB provides and draw far too much power for USB.
2.5" drives on the other hand are always USB powered, some of them might support an external power supply but I've never seen one that actually came with it in the box.
So if you don't mind the extra bulk, a 3.5" drive is the best way to ensure you will not encounter any power related issues using external HDDs on the WIi U. Some people still encounter issues using 2.5" drives even with a Y-cable.
I can't 100% vouch for external SSDs not needing a Y-cable, I've only tested it with 1 drive and I'm guessing that a m.2 SSD in a USB enclosure might draw more power since m.2 is not designed to be connected through USB and is not made with USB power limits in mind. But at the least, they don't see the power spikes that HDDs do because they don't need to spin up, and in theory an SSD will just slow down its read/write speed if it doesn't have enough power (which would not be an issue for Wii/Wii U usage)
More testing of different SSDs by other people is needed to confirm for sure whether all of them work without a Y-cable or only some/most of them.
I have 10 year old HDDs running fine. I also have several drives that failed around the 1 year mark or developed small amounts of bad sectors leading to corrupted data and flakey operation. They're really inconsistent, and you don't know if a HDD is going to last until it makes it past the 1 year mark with no signs of failure in SMART info. After that, a drive is usually good for at least 5 years.
2.5" drives seem to fail a lot more often than 3.5" for me though. I've had nothing but trouble with them for the past decade, but all my 3.5" drives have lasted more than 5 years.
I don't have any 15 year old drives still in operation though. Drives from that time were so small that I've long since replaced them with something bigger. They still work last I checked, but they haven't been in use for a long time.