I've often used comic villains, but never used ONLY comic villains. The incestuous hillbilly gungan darksider in a Star Wars game, the hilariously arrogant nobleman rival in Savage Tide (and his creepily servile minion). A couple of change-of-pace one shot villains in a superhero game (but comic relief is basically genre-standard there, after all). They've generally been very very well received (better than my actual villainish villains, often). You can really go hammy with your portrayal of them (goofy voices and all) even if that's not your normal GMing style, and it'll fit in really well and can be really memorable. And if you're running a campaign where the main antagonists are distant, humourless, or inhumanly horrifying, then a comic relief guy is a good way of actually bantering with the PCs, needling their emotional weaknesses/hypocrisies/divisions/etc. Sauron is a great bad guy, but Gollum makes it personal.
I've never tried to have a comedy relief villain as the actual main antagonist though. A main antagonist - unless in an entirely comic campaign - has to be either hated or feared, and comic reliefs are generally neither (though they're frequently despised). They work perfectly well as sidekicks to the main bad guy, or sidequest bad guys as a change of pace, or long-running rival-types who never quite get to full 'I try to cut his head off!' enemy status. Or as additional encumbrances when the PCs are trying to accomplish some other arduous task - a dangerous journey, or some difficult politicking or whatever.
Things to watch out for - the longer your comic relief villain lasts, the more effective he is. If the PCs kill him in their first encounter he'll just be gone, even if he got a couple of quips off before biting the big one. Think of a reason they can't kill him. Give him a reason for wanting to talk to them or interact with them too, because that's where the comedy is.