摘要:在本教程中,您将学习如何使用 MySQL DROP USER
语句从数据库中删除一个或多个用户帐户。
MySQL DROP USER
语句简介
要从 MySQL 服务器中删除用户帐户,请使用DROP USER
语句,如下所示:
DROP USER account_name;
Code language: SQL (Structured Query Language) (sql)
在此语法中,您可以在DROP USER
关键字后指定要删除的用户帐户的名称。
如果要一次删除多个用户帐户,请在DROP USER
子句中指定以逗号分隔的用户帐户列表:
DROP USER account_name [,account_name2]...
Code language: SQL (Structured Query Language) (sql)
如果删除不存在的用户帐户,MySQL 将发出错误。
在 MySQL 5.7.8+ 中,您可以使用IF EXISTS
子句有条件地删除存在的用户:
DROP USER [IF EXISTS] account_name [,account_name2]...;
Code language: SQL (Structured Query Language) (sql)
除了删除用户帐户之外, DROP USER
语句还会从所有授权表中删除用户的所有权限。
MySQL DROP USER
示例
让我们举一些用户流失的例子。
A) 使用 MySQL DROP USER
语句删除用户示例
首先,使用root
帐户连接到 MySQL 服务器:
mysql -u root -p
Code language: SQL (Structured Query Language) (sql)
输入root
用户的密码并按Enter
:
Enter password: ********
Code language: SQL (Structured Query Language) (sql)
其次,创建四个帐户用户accounts api@localhost
、 remote
、 dbadmin@localhost
和alice@localhost
:
mysql> create user api@localhost, remote, dbadmin@localhost, alice@localhost identified by 'Secure1Pass!';
Code language: SQL (Structured Query Language) (sql)
第三,显示来自 MySQL 服务器的用户:
mysql> select user, host from mysql.user;
Code language: SQL (Structured Query Language) (sql)
这是当前用户列表:
+------------------+-----------+
| user | host |
+------------------+-----------+
| remote | % |
| alice | localhost |
| api | localhost |
| bob | localhost |
| dbadmin | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
9 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
第四,使用DROP USER
语句删除用户dbadmin@localhost
:
mysql> drop user dbadmin@localhost
Code language: SQL (Structured Query Language) (sql)
五、再次显示所有用户:
mysql> select user, host from mysql.user;
Code language: SQL (Structured Query Language) (sql)
+------------------+-----------+
| user | host |
+------------------+-----------+
| remote | % |
| alice | localhost |
| api | localhost |
| bob | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
8 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
用户帐户dbadmin@localhost
已成功删除。
B) 使用 MySQL DROP USER
一次删除多个用户帐户
首先,使用以下语句删除两个用户帐户api@localhost
和remote
:
mysql> drop user api@localhost, remote;
Code language: SQL (Structured Query Language) (sql)
其次,显示当前数据库中的用户:
mysql> select user, host from mysql.user;
Code language: SQL (Structured Query Language) (sql)
这是输出:
+------------------+-----------+
| user | host |
+------------------+-----------+
| alice | localhost |
| bob | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
C) 使用 MySQL DROP USER
删除已连接的用户
首先,创建一个名为people
的新数据库:
mysql> create database people;
Code language: SQL (Structured Query Language) (sql)
二、选择people
数据库:
mysql> use people;
Code language: SQL (Structured Query Language) (sql)
第三,在people
数据库中创建一个新表persons
:
mysql> create table persons(
-> id int auto_increment,
-> firstName varchar(100) not null,
-> lastName varchar(100) not null,
-> primary key(id));
Code language: SQL (Structured Query Language) (sql)
第四,将people
数据库的所有权限授予帐户用户alice
:
mysql> grant all privileges on people.* to alice@localhost;
Code language: SQL (Structured Query Language) (sql)
第五,启动另一个会话并使用用户alice@localhost
连接到数据库:
mysql -u alice -p
Code language: SQL (Structured Query Language) (sql)
输入用户帐户alice
的密码,然后按Enter
键:
Enter password: ************
Code language: SQL (Structured Query Language) (sql)
六、选择people
数据库:
mysql> use people;
Code language: SQL (Structured Query Language) (sql)
第七,向persons
表中插入一行:
mysql> insert into persons(firstname, lastname) values('John','Doe');
Code language: SQL (Structured Query Language) (sql)
假设您要删除用户alice@localhost
。
但是,用户帐户alice@localhost
仍然连接到 MySQL 服务器。如果您删除当前连接的用户,该用户可以正常操作,直到下次登录。在这种情况下,您应该首先通知用户。
如果您不能这样做,您可以先终止用户会话,然后再删除用户帐户。
第八,使用根会话中的SHOW PROCESSLIST
语句查找连接的 id:
+----+-----------------+-----------------+--------+---------+-------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+--------+---------+-------+------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 31803 | Waiting on empty queue | NULL |
| 20 | root | localhost:63851 | NULL | Query | 0 | starting | show processlist |
| 21 | alice | localhost:64060 | people | Sleep | 14 | | NULL |
+----+-----------------+-----------------+--------+---------+-------+------------------------+------------------+
Code language: PHP (php)
如您所见,用户帐户alice@localhost
的连接 ID 为21
。
第九,使用KILL
语句终止进程21:
KILL 21;
Code language: SQL (Structured Query Language) (sql)
如果用户帐户alice
发出任何查询,则会收到一条错误消息:
ERROR 2013 (HY000): Lost connection to MySQL server during query
Code language: SQL (Structured Query Language) (sql)
最后,执行DROP USER
语句以删除用户帐户alice@localhost
。
DROP USER alice@localhost;
Code language: SQL (Structured Query Language) (sql)
在本教程中,您学习了如何使用 MySQL DROP USER
语句删除一个或多个用户帐户。