本文介绍了从SET字段中删除值的最佳方法是什么?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这是更新mysql SET字段,从字段中删除特定值的最佳方法.
Which is the best way to update a mysql SET field, to remove a specific value from the field.
例如.值分别为1,2,3,4,5的字段类别? 我想从列表中删除"2":
Eg. field categories with values: 1,2,3,4,5? I want to remove '2' from the list:
UPDATE table SET categories = REPLACE(categories, ',2,', ',') WHERE field LIKE '%,2,%';但是如果'2'是列表中的第一个或最后一个值怎么办?
But what if '2' is the first or the last value from the list?
UPDATE table SET categories = REPLACE(categories, '2,', '') WHERE field LIKE '2,%'; UPDATE table SET categories = REPLACE(categories, ',2', '') WHERE field LIKE ',2%';如何用一个查询处理所有3种情况?!
How could I handle all 3 cases with one single query?!
推荐答案如果需要从集合中删除的值不能多次出现,则可以使用以下方法:
If the value you need to remove from the set can't be present more than once, you could use this:
UPDATE yourtable SET categories = TRIM(BOTH ',' FROM REPLACE(CONCAT(',', categories, ','), ',2,', ',')) WHERE FIND_IN_SET('2', categories)在此处查看它是否正常运行.如果该值可以多次出现,则将删除所有出现的值:
see it working here. If the value can be present more than once, this will remove all occourences of it:
UPDATE yourtable SET categories = TRIM(BOTH ',' FROM REPLACE( REPLACE(CONCAT(',',REPLACE(col, ',', ',,'), ','),',2,', ''), ',,', ',') ) WHERE FIND_IN_SET('2', categories)更多推荐
从SET字段中删除值的最佳方法是什么?
发布评论