For websites I've always restricted username to use Apostrophe' and " and some times even space . If a website necessitate special character then I prefer to create an additional DB field ~DisplayName.
It's easier to forbid the use of Apostrophe, otherwise you will have to escape also your search query to match what has been recorded in the DB.
even though it's okay to forbid characters in user names for convenience, if you forbid them in order to not have to escape them, you're basically building unintentional backdoors. every user input that goes to DB or shells must be escaped. otherwise you may miss something or an update introduces a new meaning for a character and you get hacked.
Nope, don't place any character restrictions on any field unless it makes sense from an application perspective. Then write tests to ensure the dangerous inputs don't cause issues.
The only limitations I do is place an upper bound on number of bytes so I don't get DOSed with an abnormally long field. There are zero length characters, so check based on number of bytes, not UTF codepoints. I set this high enough to not impede users, but low enough to prevent problems (e.g. 256 bytes for username and password) and a separate length limitation for UX purposes (say, 30 characters for user display names).
If you're limiting characters for security purposes, you're doing security wrong. Only limit it for application purposes.
That's great if every external library and application also expects usernames that can contain every character, but mostly they won't. If you're very good about testing, you might catch every instance where they don't, but that doesn't mean you can necessarily fix them / get them fixed.
Plus, there's just displaying them. If you have sane limits on usernames, you can have sane user management tools that do things that list users vs. storage used, or whatever. If a username might contain backspace characters or who knows what else, all those tools have to be made more complicated too.
In addition, there's user support. If you allow usernames to contain zalgo text, you're going to have far more users needing support because they're having trouble entering their username. You're also going to give your support people nightmares trying to help those users out.
"Ok, I see you entered your username as "s̶u̵g̶a̴r̴_̵i̷n̴_̵y̶o̸u̷r̸-̴t̴e̷a̴" not "s̶u̵g̵a̶r̷_̶i̴n̸_̸y̵o̴u̵r̷_̸t̵e̵a̷", could you try re-entering that username and see if that fixes the problem?"
If your libraries aren't using proper SQL practices (or any other DB), you should get better libraries. Something like a username should be treated as just bytes by your backend services, so there should never be an issue.
If your frontend wants to limit usernames, that's fine. But that's not a security issue, but a display issue.