The middle pickup can be anything. Traditionally it's the same as the neck pickup, but of course placing it in the middle position gives less output and a slightly brighter sound. The current fashion seems to be to put a humbucker-sized P-90 (e.g. SD Phat Cat) in the middle, with regular humbuckers at the bridge and neck. If you want the pickups to all be balanced then yes, the middle pickup does need to be a little hotter than the neck and a little lower output than the bridge, although since the middle humbucker is often used as a dedicated rhtyhm pickup or to blend in with the other two, it's more common for the middle pickup to have the same output as the neck or slightly lower.
What it gives you depends on how you wire it. Just by itself, an actual pickup in the middle does sound very different to the more common neck+bridge combined "middle" position; it tends to be slightly clearer and respond to picking dynamics better, although of course this does depend on what pickups you're using and your playing style. It's quite popular in various types of alterntive rock and metal since it can sound darker than a bridge pickup but doesn't get muddy and boomy when downtuning like the neck pickup can.
Often a 3-humbucker LP or similar guitar is wired so the toggle switch does the usual selections and one of the volume/tone pots is used to roll in the middle pickup. This can be useful to add a little warmth to the bridge pickup without having to use a tone control (which might dampen response) or to brighten up the neck pickup a little. Of course it can also be used fully on and combined with one (or all) of the other pickups, just as a Strat's layout works. The difference in tone between one humbucker on its own and two parallel isn't quite as drastic as it is with single coils though. Of course coil splits could be installed to give you those more typical Strat combined pickup sounds.
So basically, it'll sound right in between the bridge pickup's tone and the neck pickup's tone, but how you make use of that is up to you. There are lots of options and none of them are wrong.
For what it's worth, I also have a 3-humbucker super-Strat with a 5-way switch and two coil splits (bridge and middle; the neck is automatically split when selecting the neck&middle position). I can get a lot of tones out of it thanks to the more verstaile switching system though in practice I usually leave the middle humbucker split. In fact I can't remember the last time I used it as a full humbucker. It's not that a middle humbucker isn't usful, it's just that I find a middle single coil is more useful. I think that is probably why so many people have started using P-90s/Phat Cats in the middle of 3-pickup LPs instead of humbuckers.
... I spend far too much time thinking about these things.
