mysql - find_in_set 和 find_in_set 意外结果

Question

USE mysql;
DROP PROCEDURE IF EXISTS ShowUsers;
DELIMITER $

CREATE PROCEDURE `ShowUsers`(IN KnownUsers varchar(500), IN KnownHosts varchar(500))
BEGIN
  SELECT
    user,host
  FROM
    user
  WHERE 
    NOT FIND_IN_SET(host, KnownHosts)
  AND
    NOT FIND_IN_SET(user, KnownUsers)
  ORDER BY user, host ASC;
END $
DELIMITER ;

要使用的示例完整数据:

+-------------+-------------+
| user        | host        |
+-------------+-------------+
| knownuser1  | 192.168.1.5 |
| knownuser2  | 192.168.1.5 |
| unknownuser | 192.168.1.5 | # I want this result to show
| someuser1   | 192.168.1.6 |
| someuser2   | 192.168.1.6 |
| someuser3   | 192.168.1.6 |
| root        | localhost   |
+-------------+-------------+

我已经标记了我想通过运行程序显示的结果，基本上这两个 IN 参数是已知用户，已知主机是那些应该在该数据库上有用户记录的主机。

这样调用函数

# users and hostnames(ips) to match for exclusion from results.
SET @Usernames = 'knownuser1,knownuser2';
SET @Hostnames = '192.168.1.5';

CALL ShowUsers(@Usernames, @Hostnames);

预期结果:

+-------------+-------------+
| user        | host        |
+-------------+-------------+
| unknownuser | 192.168.1.5 | # I want this result to show
| someuser1   | 192.168.1.6 |
| someuser2   | 192.168.1.6 |
| someuser3   | 192.168.1.6 |
| root        | localhost   |
+-------------+-------------+

实际结果:

+-------------+-------------+
| user        | host        |
+-------------+-------------+
| someuser1   | 192.168.1.6 |
| someuser2   | 192.168.1.6 |
| someuser3   | 192.168.1.6 |
| root        | localhost   |
+-------------+-------------+

解释(脱离这个主题，但我想我应该澄清一下)我想让这个过程起作用的原因是，我有一个主服务器和多个远程从属服务器，从属服务器需要访问主数据库，这意味着他们也必须具有“root”访问权限，他们可以创建/重新配置自己的访问凭证。这样做的问题是，如果其中一台服务器遭到破坏，它将有机会向基本上所有数据库添加凭据的新用户。敞开心扉，自由取用。

我可以在初始配置后将 slaves 锁在外面，然后手动打开门，运行更新，然后再次锁定它，这对应用程序来说非常费力，并且使应用程序几乎无用。

我现在的想法是通过 cron 运行脚本运行此过程并检查未知用户/主机并将该从服务器锁定在数据库之外，直到我接受或拒绝来自主应用程序的用户。

Answer 1

WHERE 子句中的条件是:

NOT FIND_IN_SET(host, KnownHosts) AND NOT FIND_IN_SET(user, KnownUsers)

相当于:

NOT (FIND_IN_SET(host, KnownHosts) OR FIND_IN_SET(user, KnownUsers))

这意味着您要排除以下行:
host 包含在 KnownHosts 中，或者 user 包含在 KnownUsers 中。

因此对于您的示例数据，行:

unknownuser | 192.168.1.5

不会返回，因为 host = '192.168.1.5' 并且它包含在 KnownHosts (= '192.168 .1.5').

也许将逻辑运算符更改为 OR，如果这是您要应用的逻辑:

NOT FIND_IN_SET(host, KnownHosts) OR NOT FIND_IN_SET(user, KnownUsers)