snippetsqlMinor
How do I search to see if a MySQL user exists on the system?
Viewed 0 times
thesearchexistsusersystemseemysqlhow
Problem
I'm not finding a user by
update: for clarification, as I'm thinking maybe I've not been 100% clear. by wildcard I meant I wanted to be able to search for only part of the users name, or for all users who's name matches pattern. On the system I needed this on there were 5k+ users, and although that's not performance loss huge, I don't want to look through them one at a time either.
show grants for 'username'@'localhost'' I'm thinking that perhaps the username is slightly different, so I want to do a wildcard search of all the database users. How can I do this?update: for clarification, as I'm thinking maybe I've not been 100% clear. by wildcard I meant I wanted to be able to search for only part of the users name, or for all users who's name matches pattern. On the system I needed this on there were 5k+ users, and although that's not performance loss huge, I don't want to look through them one at a time either.
Solution
Thanks to @matthewh's answer I managed to compose a query that would work when searching through a large number of users:
Note: `` is whatever component of the username you need to search for, you may need a % on both sides if you don't know the start of the username, also <> are merely to note the variable, don't include them in an actual query, unless the username includes .
use mysql;
select User, Host from user where User like 'user%';Note: `` is whatever component of the username you need to search for, you may need a % on both sides if you don't know the start of the username, also <> are merely to note the variable, don't include them in an actual query, unless the username includes .
Code Snippets
use mysql;
select User, Host from user where User like 'user%';Context
StackExchange Database Administrators Q#682, answer score: 9
Revisions (0)
No revisions yet.