如何在Lucee 5.x中向MariaDB新增自动补全列表的新标签？

Let's break down the best ways to solve this problem—whether you prefer handling the comparison in the database (most efficient for large datasets) or directly in Lucee (simpler for smaller ones). I'll also clear up why your previous NOT IN attempt might have gone wrong.

Approach 1: Database-Side Comparison (Recommended for Large Tag Lists) #

This method lets MariaDB do the heavy lifting, which is faster when you have lots of existing tags. The goal is to find exactly which user-provided tags aren't already in your table.

Step 1: Prep the User's Tag List #

First, convert the user's space-separated string into a Lucee array:

var userTagsList = "green blue purple white";
var userTagsArray = listToArray(userTagsList, " ");
// Optional: Remove duplicates if the user might submit them
userTagsArray = arrayUnique(userTagsArray);

Step 2: Write SQL to Find Missing Tags #

Your earlier NOT IN query was probably backwards—you were asking for existing tags not in the user's list, instead of user tags not in the database. Here's a reliable way using a LEFT JOIN with a temporary set of user tags:

We'll build the query dynamically to handle any length of user tags:

var placeholders = [];
var valuePairs = [];

// Build placeholders for each tag in the user's list
for (var i = 1; i <= arrayLen(userTagsArray); i++) {
    arrayAppend(placeholders, { "tag#i": userTagsArray[i] });
    arrayAppend(valuePairs, "( :tag#i# )");
}

var missingTagsQuery = queryExecute(
    "SELECT ut.tag 
     FROM (VALUES #arrayToList(valuePairs, ", ")#) AS ut(tag)
     LEFT JOIN existing_tags et ON et.tag = ut.tag
     WHERE et.tag IS NULL",
    placeholders,
    { datasource: "YourDatasourceName" }
);

This query creates a temporary table of the user's tags, joins it to your existing tags table, and picks out the tags that have no match (i.e., missing from the database).

Step 3: Insert the Missing Tags #

Once you have the list of missing tags, insert them into the database. For efficiency, use batch mode:

if (missingTagsQuery.recordCount > 0) {
    var insertParams = [];
    // Loop through the query results (using a for-in loop for queries)
    for (var tagRow in missingTagsQuery) {
        arrayAppend(insertParams, { tag: tagRow.tag });
    }
    // Batch insert all missing tags at once
    queryExecute(
        "INSERT INTO existing_tags (tag) VALUES (:tag)",
        insertParams,
        { datasource: "YourDatasourceName", batchMode: true }
    );
}

Approach 2: Lucee-Side Comparison (Simple for Small Datasets) #

If your existing tags table is small, doing the check directly in Lucee is straightforward.

Step 1: Fetch Existing Tags into an Array #

First, get all existing tags from the database and convert them to an array:

var existingTagsQuery = queryExecute(
    "SELECT tag FROM existing_tags",
    {},
    { datasource: "YourDatasourceName" }
);
var existingTagsArray = queryColumnToArray(existingTagsQuery, "tag");
// Optional: Make comparison case-insensitive
existingTagsArray = arrayMap(existingTagsArray, function(tag) { return lcase(tag); });

Step 2: Filter Missing Tags from User's List #

Loop through the user's tags and check which aren't in the existing array. A for-in loop for arrays is perfect here:

var userTagsArray = listToArray(userTagsList, " ");
var missingTags = [];

for (var tag in userTagsArray) {
    var lowerTag = lcase(tag); // Match case-insensitive if needed
    if (!arrayFind(existingTagsArray, lowerTag)) {
        arrayAppend(missingTags, tag);
    }
}
// Optional: Remove duplicates
missingTags = arrayUnique(missingTags);

Step 3: Insert the Missing Tags #

Same as before—use batch insert to add all missing tags at once:

if (arrayLen(missingTags) > 0) {
    var insertParams = arrayMap(missingTags, function(tag) { return { tag: tag }; });
    queryExecute(
        "INSERT INTO existing_tags (tag) VALUES (:tag)",
        insertParams,
        { datasource: "YourDatasourceName", batchMode: true }
    );
}

Which Loop Should You Use? #

• for-in Loop (Array): Cleanest and most readable for iterating over the user's tag array or the missing tags array.
• for-in Loop (Query): Ideal for looping through query results (like the missing tags from the database-side query).
• Index-based for Loop: Only necessary if you need the position of each item in the array—otherwise, for-in is better.

Why Your Previous NOT IN Attempt Failed #

Chances are you wrote something like:

SELECT tag FROM existing_tags WHERE tag NOT IN ('green', 'blue', 'purple', 'white')

This returns existing tags that aren't in the user's list—the opposite of what you want. You need to select the user's tags that aren't present in the database, which the LEFT JOIN method above handles correctly.

Edge Cases to Consider #

• Case Sensitivity: MariaDB uses case-insensitive collation by default (e.g., utf8mb4_general_ci). If you want case-sensitive checks, use a case-sensitive collation for your tag column or adjust comparisons in code.
• Duplicate Tags: Always deduplicate the user's list first with arrayUnique() to avoid inserting duplicates.
• Performance: For large datasets, stick to the database-side approach—databases are optimized for set operations, so it'll be faster than looping in Lucee.

内容的提问来源于stack exchange，提问作者HPWD