MYSQL逗号分隔列表，可以添加和删除值吗? [英] MYSQL Comma Delimited list, possible to add and remove values?

问题描述

我有一个逗号分隔的列表，它存储在 mysql 表的 varchar 字段中.

I have a comma delimited list that im storing in a varchar field in a mysql table.

是否可以直接使用 sql 查询从列表中添加和删除值?还是我必须把表中的数据取出来，在PHP中操作，然后将其替换回mysql?

Is it possible to add and remove values from the list directly using sql queries? Or do I have to take the data out of the table, manipulate in PHP and replace it back into mysql?

推荐答案

在 mysql 中的 InnoDB 和 MyIsam 引擎中没有办法做到这一点.可能在其他引擎中(检查 CSV 引擎).
您可以在存储过程中执行此操作，但不建议这样做.
解决这样的问题你应该做的是重构你的代码并规范化你的数据库 =>
原表

There is no way to do it in InnoDB and MyIsam engines in mysql. Might be in other engines (check CSV engine).
You can do it in a stored procedure, but, not recommended.
What you should do to solve such an issue is to refactor your code and normalize your DB =>
original table

T1: id | data                    | some_other_data
     1 |  gg,jj,ss,ee,tt,hh      |   abanibi

成为:

T1: id | some_other_data
     1 |   abanibi

T2: id   |  t1_id    |   data_piece
     1   |    1      |      gg 
     2   |    1      |      jj 
     3   |    1      |      ss 
     4   |    1      |      ee
     5   |    1      |      tt
     6   |    1      |      hh 

如果data_piece是系统中重复使用较多的常量值，则还需要添加查找表.

and if data_piece is a constant value in the system which is reused a lot, you need to add there a lookup table too.

我知道它看起来工作量更大，但它会为您节省像现在这样需要更多时间来解决的问题.

I know it looks more work, but then it will save you issues like you have now, which take much more time to solve.

这篇关于MYSQL逗号分隔列表，可以添加和删除值吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！