I've never really found a satisfactory method of dividing spells by type. D&D's method is pretty cool, like having Abjuration, but Necromancy and Illusion breaking the naming formula isn't too nice, plus Transmutation is overloaded and Enchantment and Illusion seriously shouldn't be separate categories.
The Elder Scrolls method is, in my opinion, a bit underrated. It gets you everything you need, all the spell effects most adventurers would care about with a nice 'miscellaneous' category in Mysticism. Still breaks the naming convention but at least it tries.
Roll 1d12 for whatever category spell you fail at casting. If you were actually playing an Elder Scrolls system, most of the time the spell would just fizzle, but this is for when you fail AND you have a low luck character.
 Water is drawn towards you, creeps up and down your frame.
 One piece of leather on you becomes the color and hardness of bronze. Useless for metallurgy.
 The weight on one piece of your equipment is shifted to a tip or base.
 The ground beneath your feet turns to mud.
 One nearby lock is jammed for a short period of time.
 Creates a huge amount of heat or cold in the area, dissipates normally.
 You feel as though you are carrying 50% more gear then you really are.
 Water becomes as dense as air for you, but you cannot breathe it.
 Nearby wooden objects turns into brittle porous stone.
 One piece of metal armor you are wearing become a weaker metal, or reverse for an enemy.
 You NEED to breathe water for 1d6x10 minutes.
 Your weapon becomes so lightweight is barely deals any damage, falls like a leaf.
 Conjure a tiny biting horsefly, which follows you around until it dies in 1-2 months.
 You smell of the brimstone of oblivion or rotting flesh.
 Daedric chastity device appears on your groin. Locked, without its spiky key.
 Daedric weapon appears in your hand, unable to be dispelled or dropped.
 Scamp appears in your bedroom tonight, which wrecks things and you if you step off the bed.
 You grow horns. If you already had horns, the old ones fall out.
 Nearby animal mutates into a daedric style beast, aggressive to all but the summoner.
 Nearby tentacles appear from various holes and sewer great, seeking knowledge.
 Zombie gas cloud appears around you, make a save or be infected by a disease.
 Nearby corpse reanimates, does menial task and will attack anyone if interrupted.
 Rations are replaced with foul food, meat becomes rancid and plant matter becomes aggressive.
 Daedric helmet appears on your head, too heavy and causing you to be mostly blind.
 Flame and Frost mix to create steam, which rises uselessly. Affects all Fire/Frost spells.
 Tiny wisps of fire blow back towards you, which might light you on fire.
 One of your stats is randomly damaged by -1d6 points.
 You take twice the damage from a random element. Lasts until the next source of that damage.
 Lightning shocks you, causing you to be stunned for one round and makes your hair fritz up.
 Frost sticks your feet to the ground, making you unable to move until it melts or is chipped away.
 Next piece of wall or masonry you touch with your hand is reduced to gravel.
 Hole burns into your coinpurse or into a potion flask.
 You take +1 damage from normal weapons.
 Next person you touch besides yourself takes 2d6 lightning damage. You can't control it.
 Normal fire is drawn towards you like a magnet, trying to burn you.
 Any piece of clothing you wear degrades at 5x the normal rate.
 You laugh hysterically at inappropriate times.
 You are under the effect of a calm spell, and cannot take violent action unless attacked first.
 You are under the effect of a rally spell, and cannot willingly retreat.
 You are silenced for 1d8 turns.
 Very light chameleon effect on your body, fading in and out, making you slightly transparent.
 Your entire body glows lightly, making it very hard to sneak.
 Paralyzed for 1d4 rounds.
 Creature or object you next use in combat is actually an illusion, and deals no damage.
 You are charmed and find it hard to resist any polite request during this effect.
 Random door or window nearby is hidden from you, must be found like a secret door.
 You are feared and must flee from any threats or dangers, regardless of your power.
 Ghost figments are created in the area, like disembodied voices and apparitions.
 Other creatures detect you as though you were under a detect life spell.
 The next time you use any teleport spell or service, you are whisked inside the sewer of a city.
 You've soul trapped yourself, but your soul cannot be captured without at least a black soul gem.
 The next spell you cast is reflected back to you in full.
 Random objects float around you and follow you like a loyal dog.
 Next person who hits you with a melee attacks heals ½ of the damage dealt.
 You sweat magicka, lose magicka equal to stamina lost.
 Your detection spells are too sensitive, causing everything to glow so bright you can't see.
 Every 5 rounds a wave of dispel washes over you, stopping your enchantments.
 You teleport to the least convenient place you could be within 100 ft.
 Random objects of similar size and shape swap places, like a brick and a tome.
 Nearby spells bend to hit you instead of their intended targets.
 Any minor injuries you have heal over into large ugly scars and scabs.
 One random attribute of yours is doubled, while all the rest are halved.
 You are poisoned with a minor poison, dealing 1d4 damage per turn.
 You are infected with a common disease.
 Constantly out of breath and tired.
 You need triple rations then normal, you whine for food constantly.
 You resist heat but are weaker to cold. You are always shivering.
 Susceptibility to disease, get -2 on saves vs disease.
 Graduate the next disease you catch by one step, common to blight and blight to incurable.
 Your body becomes extremely delicious to vampires and the undead, who can sense you.
 Your maximum health, stamina, and magicka swap randomly.
 Nearby enemy has one of their stats fortified by +1d6 points.