将DROP USER和DROP DATABASE与SELECT .. WHERE查询组合在一起？

I'd like to make a very simple thing, replicate the functionality of mysql's interactive mysql_secure_installation script. My question is that is there a simple, built-in way in MySQL to combine the output of a SELECT query with the input of a DROP user or DROP database script?

我想做一个非常简单的事情,复制mysql的交互式mysql_secure_installation脚本的功能。我的问题是MySQL中是否有一种简单的内置方法将SELECT查询的输出与DROP用户或DROP数据库脚本的输入结合起来?

For example, if I'd like to drop all users with empty passwords. How could I do that with DROP USER statement? I know an obvious solution would be to run everything for example from a Python script,

例如,如果我想删除所有空密码的用户。我怎么能用DROP USER语句呢?我知道一个明显的解决方案是从Python脚本运行所有内容,

run a query with mysql -Bse "select..."

用mysql运行查询-Bse“select ...”

parse the output with some program

用一些程序解析输出

construct the drop query

构造drop查询

run it.

Is there an easy way to do it in a simple SQL query? I've seen some example here, but I wouldn't call it simple: https://*.com/a/12097567/518169

有一种简单的方法可以在简单的SQL查询中执行此操作吗?我在这里看到了一些例子,但我不会简单地称之为:https://*.com/a/12097567/518169

Would you recommend making a combined query, or just to parse the output using for example Python or bash scripts/sed?

您是否建议进行组合查询,或者仅使用Python或bash脚本/ sed来解析输出?

1 个解决方案

#1

You can build SQL dynamically, for example:

您可以动态构建SQL,例如:

SELECT CONCAT('DROP USER ', GROUP_CONCAT(QUOTE(User), '@', QUOTE(Host)))
INTO   @sql
FROM   mysql.user
WHERE  PASSWORD = ''

Then prepare a statement from it:

然后准备一份声明:

PREPARE stmt FROM @sql

Then execute that statement:

然后执行该语句:

EXECUTE stmt

Finally, deallocate the prepared statement:

最后,取消分配准备好的声明:

DEALLOCATE PREPARE stmt

#1