MySQL 检查mysql用户是否存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3049929/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Checking if mysql user exists
提问by sergiogx
How can I check if a user exists?
如何检查用户是否存在？
Im doing an installer for a mysql database, and I need to check if a user exits, if not create user, if yes delete user and create it again.
我正在为 mysql 数据库做一个安装程序，我需要检查用户是否退出，如果没有创建用户，如果是，则删除用户并重新创建它。
this so i can execute the script without worries.
这样我就可以毫无顾虑地执行脚本。
thanks.
谢谢。
采纳答案by Matt
If you're deleting the MySQL user anyways, then there's really no need to check if it exists first. MySQL won't throw any errors if there's nothing to delete:
如果您无论如何要删除 MySQL 用户，那么实际上没有必要先检查它是否存在。如果没有要删除的内容，MySQL 不会抛出任何错误：
DELETE FROM mysql.user WHERE User = 'username';
回答by Lauri Lehtinen
MySQL stores user data in a table called userin a database named mysql(by default). The following query will return 1if a user with the specified username exists, 0otherwise.
MySQL 将用户数据存储在user名为mysql（默认情况下）的数据库中调用的表中。1如果存在具有指定用户名的用户，则以下查询将返回，0否则将返回。
SELECT EXISTS(SELECT 1 FROM mysql.user WHERE user = 'username')
回答by TheMadCat
As newer versions of MySQL allow this option:
由于较新版本的 MySQL 允许此选项：
DROP USER IF EXISTS 'username'@'host';
回答by stivlo
MySQL lacks DROP USER IF EXISTS construct.
MySQL 缺少 DROP USER IF EXISTS 构造。
A good workaround is to grant a harmless privilege to the user before dropping it. This will create the user if it doesn't exist, so that it can be dropped safely, like so:
一个好的解决方法是在删除之前向用户授予无害的特权。如果用户不存在，这将创建用户，以便可以安全地删除它，如下所示：
GRANT USAGE ON *.* TO 'username'@'localhost';
DROP USER 'username'@'localhost';
FLUSH PRIVILEGES;
USAGE actually means no privilege.
USAGE 实际上意味着没有特权。
回答by Havok
This is how I was able to do it:
这就是我能够做到的：
#!/usr/bin/env bash
set -o errexit
set -o nounset
# Create credentials file
echo -e "[client]\nuser=root\npassword=${MYSQL_ROOT_PASSWORD}" > ~/.my.cnf
# Create standard user and grant permissions
if ! echo "SELECT COUNT(*) FROM mysql.user WHERE user = 'myuser';" | mysql | grep 1 &> /dev/null; then
echo "Creating database user ..."
echo "CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;" | mysql
else
echo "Database user already created. Continue ..."
fi
Change myuser, mydatabaseand mypasswordaccordingly.
改变myuser，mydatabase并mypassword相应地。
回答by Paul Tobias
MySQL 5.7 already includes DROP USER IF EXISTS, but for older versions I use pt-show-grants --dropfrom percona-toolkit and feed back the DROP USERpart to mysql:
MySQL 5.7 已经包含DROP USER IF EXISTS，但对于旧版本，我使用pt-show-grants --droppercona-toolkit 并将DROP USER部分反馈给 mysql：
pt-show-grants --drop --only=$username | grep '^DROP USER' | mysql -v
If there are multiple username-hostname pairs this removes all of them.
如果有多个用户名-主机名对，这将删除所有这些。
回答by fbastien
When in need to check if a user exists without deletingit (for instance when just skipping some instructions instead of executing them in any case), one can use this (where $USERis the user to check):
当需要检查用户是否存在而不删除它时（例如，当只是跳过一些指令而不是在任何情况下执行它们时），可以使用这个（$USER用户在哪里检查）：
if [ $(echo "SELECT COUNT(*) FROM mysql.user WHERE user = '$USER'" | mysql | tail -n1) -gt 0 ]
then
echo "User exists"
else
echo "User doesn't exist"
fi
NB:
注意：
mysqlcommand requires extra args and/or configuration for authentication)tail -n1is used for removing the query result header
mysql命令需要额外的参数和/或配置来进行身份验证）tail -n1用于去除查询结果头
