如果不包含值，则 MySQL 在行中更新字段 [英] MySQL update field in row if it doesn't contain value

问题描述

我有一个包含逗号分隔列表(以 VARCHAR 形式)的表，如果该列表中的用户 ID 不存在，我想更新该列表.

I have a table that has a comma-separated list (in the form of a VARCHAR) that I want to update if a user id in that list does not exist.

我正在从 Node.js 应用程序运行该语句.我不完全确定我将如何通过 SQL 语句做到这一点.这是我所拥有的:

I'm running the statement from a Node.js app. I'm not entirely sure how I would do it via the SQL statement. Here's what I have:

'UPDATE `tablename` SET `t` = "' + memberId + '" WHERE rowId = ' + id

这将替换整个记录.如果它不存在，我只想将 memberId 添加到 t.

That will replace the entire record. I only want to ADD the memberId to t if it does not exist.

t 看起来像这样:'1652,47,245,783,2,3,55,738,2306';

所以如果我想加 15，它应该是 '1652,47,245,783,2,3,55,738,2306,15';

So if I wanted to add 15 to that, it should be '1652,47,245,783,2,3,55,738,2306,15';

我想在 SQL 语句中执行此操作，而不是在 JavaScript (Node.js) 中拉取字段数据然后将其添加到字符串中，然后更新记录.

I want to do this in the SQL statement, instead of pulling the field data and then adding it to the string in JavaScript (Node.js) and then updating the record.

推荐答案

使用 FIND_IN_SET 分析您的字符串值并确定该 ID 是否存在于该逗号分隔列表中.

Use FIND_IN_SET to analyze your string value and determine if the ID is present in that comma-delimited list.

在此处查看 FIND_IN_SET 的完整文档:

See full docs for FIND_IN_SET here:

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set

伪查询将是:

UPDATE `tablename` SET `t` = CONCAT(t, ',15) 
WHERE rowId = ${rowId} AND FIND_IN_SET('15', t) = 0

当然，根据需要插入 ${rowId} 以构建查询.

Of course, interpolate ${rowId} as needed to build your query.

这篇关于如果不包含值，则 MySQL 在行中更新字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！