find_in_set 和 find_in_set 意外结果 [英] find_in_set and find_in_set unexpected result

问题描述

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 ;

要使用的完整数据示例:

Example complete data to work with:

+-------------+-------------+
| 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 参数是已知用户，而已知主机是那些应该在这个数据库上有用户记录的.

I have marked the result I would want to show from running the procedure, basically the two IN parameters are known users, and known hosts those that should be have a user record on this database.

像这样调用函数

# 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"访问权限，他们可以创建/重新配置自己的访问凭据.这样做的问题是，如果其中一台服务器遭到入侵，就会有机会将新用户添加到基本上所有数据库的凭据.开放且免费.

Explanation (off this topic but I think I should clarify) The reason I want this procedure to work, I have a master server with multiple remote slaves, the slaves need to have access to the masters database which means they also have to have "root" access, they can create/reconfigure their own access credentials. The problem with this is if one of those servers were ever compromised it would leave open the chance to have a new user added with credentials to basically all of the database. Wide open and free to take.

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

I could lock the slaves out after initial configuration and manually open up the door, run an update and then lock it again which would be pretty laborious for the application and make the application virtually useless.

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

The idea I'm going with right now is to run this procedure via cron run script and check for unknown users/hosts and lock that slave server out of the database until I accept or reject the user from the main application.

推荐答案

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 中.

which means that you want to exclude the rows for which:
host is included in KnownHosts or user is included in KnownUsers.

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

So for your sample data, the row:

unknownuser | 192.168.1.5

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

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

will not be returned, because host = '192.168.1.5' and it is included in KnownHosts (= '192.168.1.5').

Maybe change the logical operator to OR, if this is the logic that you want to apply:

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

这篇关于find_in_set 和 find_in_set 意外结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！