如何替换MySQL字符串中特定字符的每个其他实例?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (69)

如何通过查询替换MySQL列中的值options及其类型varchar(255)

id   options
1    A|10|B|20|C|30
2    A|Positive|B|Negative

id   options
1    A|10,B|20,C|30
2    A|Positive,B|Negative

我是这样用php做的。

<?php
    $str =  "A|10|B|20|C|30";
    $arr = explode("|",$str);
    $newArr = array();
    for($i=0;$i<count($arr);$i+=2){
      if($arr[$i] && $arr[$i+1]){
        $newArr[] = $arr[$i]."|".$arr[$i+1];
      }
    }
    echo "Before:".$str."\n";
    echo "After :".implode(",",$newArr);
?>

所以,我想用MySQL代替PHP。

提问于
用户回答回答于

如果不使用存储过程,我将分两步完成:

  1. 在管道字符的第二次出现处插入逗号: 更新选项集选项=插入(选项,定位(‘’,选项,定位(‘,选项)+1),1,’,‘;
  2. 插入其余逗号-执行查询N次: 更新选项集选项=插入(选项,定位(‘,选项,定位(’,选项,长度(选项)-定位(‘,’,反向(选项))+1)+1),1,‘,’); 其中N= 从选项中选择max(圆形((长度(选项)-长度(替换(选项,‘,’‘))-1)/2)-1; (或者,只要查询没有告诉你“受影响的0行”,就不要费心计数,继续执行查询)

使用这组数据进行检查:

id   options
1    A|10|B|20|C|30
2    A|Positive|B|Negative
3    A|10|B|20|C|30|D|40|E|50|F|60
4    A|Positive|B|Negative|C|Neutral|D|Dunno

成果如下:

id   options
1    A|10,B|20,C|30
2    A|Positive,B|Negative
3    A|10,B|20,C|30,D|40,E|50,F|60
4    A|Positive,B|Negative,C|Neutral,D|Dunno
用户回答回答于

应该考虑将数据存储在规范化架构中。在示例中,表应该如下所示:

| id | k |        v |
|----|---|----------|
|  1 | A |       10 |
|  1 | B |       20 |
|  1 | C |       30 |
|  2 | A | Positive |
|  2 | B | Negative |

这个模式更灵活,你将看到其中的原因。

那么如何将给定的数据转换为新的模式呢?你将需要一个包含序列号的助手表。因为你的专栏是varchar(255)只能在其中存储128个值(+127个分隔符)。但让我们创建1000个数字。你可以使用任何有足够行的表。但是,因为任何MySQL服务器都有information_schema.columns表,我用它。

drop table if exists helper_sequence;
create table helper_sequence (i int auto_increment primary key)
    select null as i
    from information_schema.columns c1
    join information_schema.columns c2
    limit 1000;

通过连接两个表,我们将使用这个数字作为字符串中值的位置。

若要从分隔字符串中提取值,可以使用substring_index()功能。位置值i将是

substring_index(substring_index(t.options, '|', i  ), '|', -1)

在字符串中,有一个键序列,后面跟着它的值。键的位置是奇数。所以如果钥匙的位置是i,相应值的位置为i+1

要获取字符串中分隔符的数目并限制我们的联接,我们可以使用

char_length(t.options) - char_length(replace(t.options, '|', ''))

以规范化形式存储数据的查询如下:

create table normalized_table
    select t.id
        , substring_index(substring_index(t.options, '|', i  ), '|', -1) as k
        , substring_index(substring_index(t.options, '|', i+1), '|', -1) as v
    from old_table t
    join helper_sequence s
      on s.i <= char_length(t.options) - char_length(replace(t.options, '|', ''))
    where s.i % 2 = 1

现在运行select * from normalized_table你会得到这个:

| id | k |        v |
|----|---|----------|
|  1 | A |       10 |
|  1 | B |       20 |
|  1 | C |       30 |
|  2 | A | Positive |
|  2 | B | Negative |

那么,为什么这种格式是更好的选择呢?除了许多其他原因之外,一个原因是你可以轻松地将其转换为旧模式。

select id, group_concat(concat(k, '|', v) order by k separator '|') as options
from normalized_table
group by id;

| id |               options |
|----|-----------------------|
|  1 |        A|10|B|20|C|30 |
|  2 | A|Positive|B|Negative |

或者以你想要的格式

select id, group_concat(concat(k, '|', v) order by k separator ',') as options
from normalized_table
group by id;

| id |               options |
|----|-----------------------|
|  1 |        A|10,B|20,C|30 |
|  2 | A|Positive,B|Negative |

如果不关心规范化,只希望完成此任务,则可以用

update old_table o
join (
    select id, group_concat(concat(k, '|', v) order by k separator ',') as options
    from normalized_table
    group by id
) n using (id)
set o.options = n.options;

然后放下normalized_table...

但是这样您就不能使用简单的查询,例如

select *
from normalized_table
where k = 'A'

扫码关注云+社区