blocks|key|386896|text|如果需要从集合中删除的值不能多次出现，则可以使用以下命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|386897|UPDATE+yourtable
SET
++categories+=
++++TRIM(BOTH+','+FROM+REPLACE(CONCAT(',',+categories,+','),+',2,',+','))
WHERE
++FIND_IN_SET('2',+categories)|code-block|syntax|javascript|386898|看看它是如何工作的，here。如果该值可以多次出现，则会删除该值的所有实例：|offset|length|386899|UPDATE+yourtable
SET
++categories+=
++++TRIM(BOTH+','+FROM
++++++REPLACE(
++++++++REPLACE(CONCAT(',',REPLACE(col,+',',+',,'),+','),',2,',+''),+',,',+',')
++++)
WHERE
++FIND_IN_SET('2',+categories)|386900|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!2/e5cd9/1^0|0|0|A|4|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@$I|X|J|Y|1|Z]]|A|$]]|$1|K|3|L|5|D|7|10|8|@]|9|@]|A|$E|F]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

If the value you need to remove from the set can't be present more than once, you could use this:

<pre><code>UPDATE yourtable
SET
 categories =
 TRIM(BOTH ',' FROM REPLACE(CONCAT(',', categories, ','), ',2,', ','))
WHERE
 FIND_IN_SET('2', categories)
</code></pre>

see it working <a href="http://sqlfiddle.com/#!2/e5cd9/1" rel="noreferrer">here</a>. If the value can be present more than once, this will remove all occourences of it:

<pre><code>UPDATE yourtable
SET
 categories =
 TRIM(BOTH ',' FROM
 REPLACE(
 REPLACE(CONCAT(',',REPLACE(col, ',', ',,'), ','),',2,', ''), ',,', ',')
 )
WHERE
 FIND_IN_SET('2', categories)
</code></pre>

blocks|key|168877|text|update+TABLE
set+COLUMN+=+COLUMN+&+~NUM
where+COLUMN+&+NUM|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|168878|摘自：http://dev.mysql.com/doc/refman/5.0/en/set.html的评论部分|unstyled|offset|length|168879|不过要注意，NUM不是值'2‘，而是它的内部索引。因此，如果您定义了类似"set+('1，'2'，'3'，'4'，'5')“的字段，则这些值的对应索引为(1,2,4,8,16)。|168880|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/set.html^0|0|3|1B|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|T|8|@]|9|@$G|U|H|V|1|W]]|A|$]]|$1|I|3|J|5|F|7|X|8|@]|9|@]|A|$]]|$1|K|3|-4|5|F|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

<pre><code>update TABLE
set COLUMN = COLUMN &amp; ~NUM
where COLUMN &amp; NUM
</code></pre>

Taken from comments section of: <a href="http://dev.mysql.com/doc/refman/5.0/en/set.html" rel="noreferrer">http://dev.mysql.com/doc/refman/5.0/en/set.html</a>

Beware though, NUM is not the value '2' but its internal index. So if you defined the field like "set ('1,'2','3','4','5')" then the corresponding indexes of these values are (1,2,4,8,16).

blocks|key|387018|text|这是另一种方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|387019|UPDATE+table
SET+categories+=+CONCAT_WS(',',
++++IF(FIND_IN_SET('1',+categories),+'1',+NULL),
++++--+Note+that+'2'+is+missing+here!
++++IF(FIND_IN_SET('3',+categories),+'3',+NULL),
++++IF(FIND_IN_SET('4',+categories),+'4',+NULL),
++++IF(FIND_IN_SET('5',+categories),+'5',+NULL)
);|code-block|syntax|javascript|387020|CONCAT_WS将其所有参数(除参数1外)与第一个参数(在本例中为',')连接在一起，如果它们不是NULL的话。我们查找SET字段的每个可能值(如果该字段包含该值)，但跳过要删除的值(在本例中为2)。如果是，则返回该值，否则返回NULL。这将使用','连接集合的所有值，跳过我们想要删除的值，并为SET字段重新构造一个新值。|offset|length|style|CODE|387021|当然，这只有在您知道categories的所有可能值的情况下才有效，但由于这是一个SET字段，所以您知道这一点。|387022|entityMap^0|0|0|0|9|Y|3|1E|4|1P|3|2Q|1|37|4|3G|3|45|3|0|A|A|15|3|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]|$I|V|J|W|K|L]|$I|X|J|Y|K|L]|$I|Z|J|10|K|L]|$I|11|J|12|K|L]|$I|13|J|14|K|L]|$I|15|J|16|K|L]|$I|17|J|18|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|19|8|@$I|1A|J|1B|K|L]|$I|1C|J|1D|K|L]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]]|P|$]]

Here is another way of doing this:

<pre><code>UPDATE table
SET categories = CONCAT_WS(',',
 IF(FIND_IN_SET('1', categories), '1', NULL),
 -- Note that '2' is missing here!
 IF(FIND_IN_SET('3', categories), '3', NULL),
 IF(FIND_IN_SET('4', categories), '4', NULL),
 IF(FIND_IN_SET('5', categories), '5', NULL)
);
</code></pre>

<code>CONCAT_WS</code> concatenates all its arguments (except argument 1) with the first argument (in this case <code>','</code>), if they are not <code>NULL</code>.
We look for each possible value of the <code>SET</code> field if the field contains it but skip the one we want to remove (<code>2</code> in this case). If yes, we return that value, otherwise <code>NULL</code>.
This concatenates all values of the set with <code>','</code>, skipping the one we want to delete, reconstructing a new value for the <code>SET</code> field.

This of course only works, if you know all possible values of <code>categories</code>, but since this is a <code>SET</code> field, you know that.

blocks|key|387058|text|我喜欢克里斯·吉伦(https://blog.krisgielen.be/archives/255)的这种方式：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|387059|UPDATE+table+SET+categories+=
++TRIM(BOTH+','+FROM+REPLACE(CONCAT(',',+categories,+','),
++CONCAT(',',+'2',+','),+','))
WHERE+...|code-block|syntax|javascript|387060|要向集合添加值，请执行以下操作：|387061|UPDATE+table+SET+categories+=+
++CONCAT_WS(',',+categories,+'2')
WHERE+...|387062|entityMap|0|LINK|mutability|MUTABLE|url|https://blog.krisgielen.be/archives/255^0|A|13|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|C|$]]|$1|K|3|L|5|F|7|10|8|@]|9|@]|C|$G|H]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|T]]]]

I like this way by Kris Gielen (<a href="https://blog.krisgielen.be/archives/255" rel="nofollow noreferrer">https://blog.krisgielen.be/archives/255</a>):

<pre><code>UPDATE table SET categories =
 TRIM(BOTH ',' FROM REPLACE(CONCAT(',', categories, ','),
 CONCAT(',', '2', ','), ','))
WHERE ...
</code></pre>

To add a value to the set:

<pre><code>UPDATE table SET categories = 
 CONCAT_WS(',', categories, '2')
WHERE ...
</code></pre>

blocks|key|387103|text|在这里，@palindrom和@JohnWoo提供了最好的答案。事实上，@palindrom提供的答案非常好，应该是公认的答案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|387104|但是，在@JohnWoo提供答案的情况下，这样大的case语句是绝对不必要的，并且可以用更简短、更整洁的代码来完成。我已经在下面说明了这一点-|387105|UPDATE+my_table
++++->+SET+categories+=+CASE
++++->+WHEN+categories+LIKE+"2"
++++->+THEN+REPLACE(categories,"2","")
++++->+WHEN+categories+LIKE+"2%25"
++++->+THEN+REPLACE(categories,"2,","")
++++->+WHEN+categories+LIKE+"%252%25"
++++->+THEN+REPLACE(categories,",2","")
++++->+ELSE+categories
++++->+END;|code-block|syntax|javascript|387106|在这里，研究结合两个update语句的post是很有帮助的。我在这里也提供了一个答案。|offset|length|387107|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/4641970/how-do-i-combine-two-update-statements-in-one-mysql-query/69350126#69350126^0|0|0|0|J|4|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@$K|Y|L|Z|1|10]]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

Here, the best answers have been provided by @palindrom and @JohnWoo. In fact the answer provided by @palindrom is exceptionally good and should be the accepted answer.
But, in case of answer provided by @JohnWoo, such a large case statement is absolutely unnecessary and can be done in a much more short and neat coding. I have illustrated this below -
<pre><code>UPDATE my_table
 -&gt; SET categories = CASE
 -&gt; WHEN categories LIKE &quot;2&quot;
 -&gt; THEN REPLACE(categories,&quot;2&quot;,&quot;&quot;)
 -&gt; WHEN categories LIKE &quot;2%&quot;
 -&gt; THEN REPLACE(categories,&quot;2,&quot;,&quot;&quot;)
 -&gt; WHEN categories LIKE &quot;%2%&quot;
 -&gt; THEN REPLACE(categories,&quot;,2&quot;,&quot;&quot;)
 -&gt; ELSE categories
 -&gt; END;
</code></pre>
Here, studying this <a href="https://stackoverflow.com/questions/4641970/how-do-i-combine-two-update-statements-in-one-mysql-query/69350126#69350126">post</a> on combining two update statements is helpful. I have also provided an answer here.

Which is the best way to update a mysql SET field, to remove a specific value from the field. 

Eg. field categories with values: 1,2,3,4,5?
I want to remove '2' from the list:

<pre><code>UPDATE table 
SET categories = REPLACE(categories, ',2,', ',') 
WHERE field LIKE '%,2,%';
</code></pre>

But what if '2' is the first or the last value from the list?

<pre><code>UPDATE table 
SET categories = REPLACE(categories, '2,', '') 
WHERE field LIKE '2,%';

UPDATE table 
SET categories = REPLACE(categories, ',2', '') 
WHERE field LIKE ',2%';
</code></pre>

How could I handle all 3 cases with one single query?!

The best way to remove value from SET field?

这是更新mysql集合字段的最佳方法，也是从字段中删除特定值的最佳方式。例如：字段类别的值:1、2、3、4、5？我想从列表中删除“2”：UPDATE table SET categories = REPLACE(categories, ',2,', ',') WHERE field LIKE '%,2,%';但是如果'...

问从SET字段中删除值的最好方法是什么？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从SET字段中删除值的最好方法是什么？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从SET字段中删除值的最好方法是什么？
EN