Grant permissions to a set of databases matching a pattern in MysQL 5.0

Question

I'm lead to understand that the following grants all proveleges to all databases that name begin with 'xian_', but mysql complains about a syntax error near ''xian_...

GRANT ALL PRIVILEGES ON 'xian_%.*' TO xian@'192.168.1.%';

What is the correct syntax? Am I right in thinking that the _ needs escaping to \_ too as it is also a wildcard?

Answer 1

Use ` instead of ' in the database name, and escape the _

GRANT ALL PRIVILEGES ON `xian\_%`.* TO xian@'192.168.1.%';