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

Question

如何通过查询替换 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);
?>

https://eval.in/841007

所以我想在 MySQL 中而不是 PHP 中执行此操作。

Answer 1

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

| 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)

在您的字符串中，您有一系列键，后跟它的值。键的位置是奇数。所以如果key的位置是i，那么对应的value的位置就是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'

见 demo at rextester.com