grant to multiple db using one command

问题:

I have many mariaDB databases that has same prefix. Such as

apple1
apple2
apple3
....
apple5000
apple5001
banana1
banana2
...
banana100

And I want create new user USER who can SELECT databases has apple prefix. So I grant SELECT to new user USER using multiple command below to.

GRANT SELECT ON `apple1` TO 'USER'@'%';
GRANT SELECT ON `apple2` TO 'USER'@'%';
GRANT SELECT ON `apple3` TO 'USER'@'%';
...
GRANT SELECT ON `apple5001` TO 'USER'@'%';

Is there any solution grant to multiple databases has specify prefix using one command like wildcard(%) of LIKE statement?

回答1:

GRANT SELECT ON `apple%`.* TO 'USER'@'192.168.0.227';

回答2:

The real problem is having thousands of databases. You are likely to be slowing things down by having so many. (This is an OS problem, since MySQL instantiates each database via a directory.)

Write a Stored Procedure to query against information_schema to discover all the databases (using LIKE) and generate (using SELECT ... CONCAT) the desired GRANT statements. Then manually copy that output into the mysql commandline tool to execute them.