Variant 1: Spell Retention
Prepare spells as normal, standard charts. However, when a spell is cast it is not automatically wiped from the magic-user’s mind. The magic-user gets a save vs. spells (penalized by spell level) to retain it. At low levels this means that about 30% of the time spells will not be expended the first time they are cast. For powerful magic-users, it makes low level spells closer to at-will powers (but not actually unlimited). Thus, the resource management aspect of the class is not destroyed.
What’s the downside? If the magic-user rolls a 1 on the saving throw, that is considered a spell fumble and they lose the spell and must roll on a magical mishaps table (or suffer some other campaign-specific penalty). Fumbles can only occur when casting spells of the highest two or three levels that can be prepared. That is, by the time a magic-user can cast fourth level spells they have completely mastered first level spells and can no longer fumble their spell retention save. The highest level spells, however, always carry some level of risk.
Variant 2: Improv Casting
Prepare spells reliably as per the traditional rules. Any unprepared spell that the magic-user is familiar with (i.e., has in their spell book) may be cast but requires an action and a successful save vs. spells (penalty equal to spell level as above). Failure and the action is wasted, fumble and bad stuff happens. Further, a fumble occurs on any die roll equal to or less than the spell level. Thus, casting an unprepared third level spell would fumble on a roll of 1, 2, or 3. Improv fumbles should always at least prevent the magic-user from trying the same spell again until they can return to their spell books and puzzle out what went wrong.
“I saw this one thing this one time and it kinda went like this…”
Any spell that the magic-user has witnessed may also be attempted (for example, if the magic-user has seen another magic-user cast fireball). This uses the same rules as casting unprepared spells, but the save penalty and fumble threat range are doubled.
Both of these variants increase the magic-user’s power or versatility, but also expose them to fumbles. Many people prefer magic to include an element of danger, so that may be a feature rather than a bug. I like this mechanic because it is thematically coherent, is simple (no bookkeeping), and uses saving throws (which are like my favorite thing ever).
This does probably make magic-users more powerful, so if that is a problem you should control spell acquisition carefully (for example, no free new spells on level up). If one was so inclined, one could use both variants together, as they cover different aspects of casting, but that would result in a larger divergence from the traditional game.
I gather that there is a mechanic in the Dungeon Crawl Classics RPG which has a separate fumble table for each spell, so that might provide some inspiration, though a fumble table per spell seems like overkill to me.