如何替换MySQL字符串中特定字符的所有其他实例？

php string replace mysql sql

2022-08-31 01:09:24

如何通过查询替换mysql列中的值，例如，列是及其类型optionsvarchar(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);
?>

https://eval.in/841007

因此，我想在MySQL中执行此操作，而不是PHP。

答案 1

应考虑将数据存储在规范化架构中。在您的情况下，该表应如下所示：

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

此架构更灵活，您就会明白原因。

那么如何将给定的数据转换为新的架构呢？您将需要一个包含序列号的帮助程序表。由于您的列是，因此您只能在其中存储128个值（+ 127个分隔符）。但是，让我们只创建 1000 个数字。您可以使用具有足够行的任何表。但是由于任何MySQL服务器都有该表，因此我将使用它。varchar(255)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)

在字符串中，您有一系列键，后跟其值。键的位置是一个奇数。因此，如果键的位置是，则相应值的位置将为ii+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'

在 rextester.com 观看演示

答案 2

不使用存储过程，我将分2步完成：

在第二次出现管道字符时插入逗号：

update options set options = insert(options, locate('|', options, locate('|', options) + 1), 1, ',');

插入剩余的逗号 - 执行查询 N 次：

update options set options = insert(options, locate('|', options, locate('|', options, length(options) - locate(',', reverse(options)) + 1) + 1), 1, ',');

其中 N =

select max(round(((length(options) - length(replace(options, '|', ''))) - 1 ) / 2) - 1) from options;

（或者不要费心计数并继续执行查询，只要它不告诉您“受影响的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

（我稍后会提供解释）