Mysql – Is it possible to bulk insert using CTE in MySQL

cteMySQL

I created this small sample fiddle to play with the model: https://www.db-fiddle.com/f/b37ASVuSxna9wEoWn9hZDw/0

I have a table called folder, which represents a folder hierarchy with a parent_id column pointing to the parent folder. The root folder in a tree has NULL as its parent_id.

I have another table that specifies access restrictions to folders for users and user groups, it is called folder_access. It has a foreign key relation to the folder table, and then it has columns for user_id, user_group_id and a flag called access (which is an int column, but it is used as a bitwise flag that signifies access like read, write, delete (not important in relation to this question)).

I am implementing an API that gets a list of folder ids, a list of either user ids or user group ids and a single access value. The API should then insert or update rows in the folder_access table like this:

  1. Insert a row for each combination of folder id and user id (or user group id)
  2. Also insert a row for each descendant folder in the hierarchy
  3. If a row already exists for the given combination of folder id, user id/user group id, then update the access value for that row

I implemented a version without the second requirement above:

public async Task SaveFolderAccess(long areaId, List<long> folderIds, List<long> userIds, List<int> userGroupIds, Access access)
{
    var values = new List<string>();
    foreach (var folderId in folderIds)
    {
        foreach (var userId in userIds)
        {
            values.Add($"({areaId},{folderId},{userId},NULL,{(int)access})");
        }

        foreach (var userGroupId in userGroupIds)
        {
            values.Add($"({areaId},{folderId},NULL,{userGroupId},{(int)access})");
        }
    }

    await dbSession.ExecuteSqlRawAsync(
        @$"INSERT INTO folder_access (area_id, folder_id, user_id, user_group_id, access)
            VALUES {string.Join(",", values)}
        ON DUPLICATE KEY UPDATE access = {(int)access}");
}

I know I can create a common table expression to find all the descendant folder ids for a particular folder, like this (replacing {folderId} with the actual value):

WITH RECURSIVE CTE (id) AS
(
SELECT f.id
    FROM folder AS f
    WHERE id = {folderId}
UNION ALL
SELECT f.id
    FROM CTE JOIN folder AS f
    ON f.parent_id = CTE.id
)
SELECT id FROM CTE

But is it possible to combine this into one big command that leverages both the CTE and the "ON DUPLICATE KEY UPDATE"?

Best Answer

The task is not defined completely, but nevertheless:

-- Parameters 
SET @folderId:=2;
SET @user_ids:='11,14';
SET @access_mask:=2;

-- Insert/update access masks for single users
INSERT INTO FOLDER_ACCESS (FOLDER_ID, USER_ID, ACCESS)
WITH RECURSIVE 
folder_ids AS ( SELECT f.id
                FROM FOLDER AS f
                WHERE id = @folderId
              UNION ALL
                SELECT f.id
                FROM folder_ids 
                JOIN FOLDER AS f ON f.parent_id = folder_ids.id ),
user_ids AS ( SELECT SUBSTRING_INDEX(@user_ids, ',', 1) user_id,
                     CONCAT(SUBSTRING(@user_ids FROM 1 + LOCATE(',', @user_ids)), ',') slack
            UNION ALL
              SELECT SUBSTRING_INDEX(slack, ',', 1),
                     SUBSTRING(slack FROM 2 + LENGTH(SUBSTRING_INDEX(slack, ',', 1))) 
              FROM user_ids
              WHERE slack )
SELECT folder_ids.id, user_ids.user_id, @access_mask
FROM folder_ids, user_ids
ON DUPLICATE KEY 
UPDATE ACCESS = @access_mask;

fiddle

The query for groups access masks update is absolutely similar.

If a row already exists for the given combination of folder id, user id/user group id, then update the access value for that row

"Update" is too vague term.

I think that the flag of access mask change type must exist. For example, @type, with the values:

  1. unconditional replace
  2. set bits
  3. clear bits

And update clause will be

UPDATE ACCESS = CASE @type WHEN 1 THEN @access_mask
                           WHEN 2 THEN ACCESS | @access_mask
                           WHEN 3 THEN ACCESS & ~@access_mask
                           ELSE ACCESS
                           END
Related Question