将列表传递到MySQL存储过程并检查是否存在所有值 [英] Passing a list into MySQL stored procedure and check ALL values are present

问题描述

我有一个查询，检查用户是否具有权限列表中存在的所有权限.

I have a query, where I check the user has ALL the permissions present in a list of perms.

所以，是这样的...

So, it's something like this...

SELECT DISTINCT account_id
FROM   pp_acl_user_roles ur, pp_acl_role_permissions rp
JOIN pp_acl_permissions p ON rp.permission_id=p.id
WHERE (
  ur.account_id = '1'
  #check for permission ids OR keys depending on what has been passed in.
  AND ( p.id IN ('read_accounts', 'write_accounts') OR p.key IN ('read_accounts', 'write_accounts') )
  AND ur.role_id = rp.role_id
)
#ensure we have ALL the permissions we asked for, not just some -makes IN() an AND not an OR.
GROUP BY ur.account_id
HAVING COUNT(DISTINCT rp.permission_id) = 2

它检查权限是id的列表还是keys的列表，因此可以用其中任何一个调用它，因此此行.

It checks for either a list of ids or a list of keys for the permissions, so it could be called with either, so this line.

p.id IN ('read_accounts', 'write_accounts') OR p.key IN ('read_accounts', 'write_accounts')

可能是

p.id IN (1, 2) OR p.key IN (1, 2)

取决于调用方式.

最后的HAVING确保我们匹配了所需的所有项目.

The HAVING at the end ensures we matched all the items we asked for.

这很好，但是我想将其移至存储过程，但是遇到了问题.

This is fine, but I want to move it to a stored procedure, and I'm hitting a issue.

我必须将IN更改为FIND_IN_SET，以便可以将字符串列表传递到过程中，但是问题是，我无法动态计算许多项目在列表，所以我无法检查它们是否都存在.

I had to change IN for FIND_IN_SET so I could pass a string list into the procedure, but the problem is, I have no way to dynamically calculate how many items are in the list, so I can't check they are all present.

到目前为止，我已经知道了...

Iv'e got this so far...

CREATE PROCEDURE has_permission( IN account_id BIGINT, IN permissions TEXT )
BEGIN
  SELECT DISTINCT account_id
  FROM   pp_acl_user_roles ur, pp_acl_role_permissions rp
  JOIN pp_acl_permissions p ON rp.permission_id=p.id
  WHERE (
    ur.account_id = account_id
    #check for permission ids OR keys depending on what has been passed in.
    AND ( FIND_IN_SET(p.id, permissions) OR FIND_IN_SET(p.key, permissions) )
    AND ur.role_id = rp.role_id
  )
  #ensure we have ALL the permissions we asked for, not just some -makes IN() an AND not an OR.
  GROUP BY ur.account_id;
  HAVING COUNT(DISTINCT rp.permission_id) = ????????????
END //                      
DELIMITER ; 

但是无法计算传入的权限的长度.

But there is no way to calculate the length of the permissions passed in.

我敢肯定有一种方法可以根据字符串中的项目将其加入到perms表中，并确保我们在这两个表中都有匹配项，但是我无法解决.

I'm sure there is a way to maybe JOIN to the perms table based on the items in the string and ensure we have matches in both tables, but I cant work it out.

任何指针都值得赞赏...

Any pointers much appreciated...

推荐答案

这是我到目前为止所获得的...

This is what I've got so far...

不太理想，但是您可以使用此方法计算出字符串中项目的长度.

Not ideal, but you can work out the length of the items in the string with this..

(SELECT LENGTH(permissions) - LENGTH( REPLACE(permissions, ',', '') ) + 1)

基本上，它计算字符串中的所有逗号，并将其用作传入的烫发总数.

It basically counts all the commas in the string and uses that as the total number of perms passed in.

CREATE PROCEDURE has_permission( IN account_id BIGINT, IN permissions TEXT)
BEGIN
  SELECT DISTINCT account_id
  FROM   pp_acl_user_roles ur, pp_acl_role_permissions rp
  JOIN pp_acl_permissions p ON rp.permission_id=p.id
  WHERE (
    ur.account_id = account_id
    #check for permission ids OR keys depending on what has been passed in.
    AND ( FIND_IN_SET(p.id, permissions) OR FIND_IN_SET(p.key, permissions) )
    AND ur.role_id = rp.role_id

  )
  #ensure we have ALL the permissions we asked for, not just some -makes IN() an AND not an OR.
  GROUP BY ur.account_id
  HAVING COUNT(DISTINCT rp.permission_id) = (SELECT LENGTH(permissions) - LENGTH( REPLACE(permissions, ',', '') ) + 1);

这篇关于将列表传递到MySQL存储过程并检查是否存在所有值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！