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

How to replace value in mysql column by query like, Column is options and its of type varchar(255)

如何用类似查询的方式替换mysql列中的值，列是选项，其类型为varchar(255)

From

从

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

To

来

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

I am doing it by php like this.

我是用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

https://eval.in/841007

So instead of PHP, I want to do this in MySQL.

用MySQL代替PHP。

5 个解决方案

#1

You should consider to store your data in a normalized schema. In your case the table should look like:

您应该考虑将数据存储在规范化模式中。在您的情况下，该表应如下:

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

This schema is more flexible and you will see why.

这个模式更加灵活，您将看到原因。

So how to convert the given data into the new schema? You will need a helper table containing sequence numbers. Since your column is varchar(255) you can only store 128 values (+ 127 delimiters) in it. But let's just create 1000 numbers. You can use any table with enough rows. But since any MySQL server has the information_schema.columns table, I will use it.

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

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;

We will use this numbers as position of the values in your string by joining the two tables.

通过连接这两个表，我们将把这些数字用作字符串中值的位置。

To extract a value from a delimited string you can use the substring_index() function. The value at position i will be

要从带分隔符的字符串中提取值，可以使用substring_index()函数。在我的位置上

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

In your string you have a sequence of keys followed by its values. The position of a key is an odd number. So if the position of the key is i, the position of the corresponding value will be i+1

在你的字符串中，你有一系列的键，然后是它的值。钥匙的位置是一个奇数。如果键的位置是i，对应值的位置就是i+1

To get the number of the delimiters in the string and limit our join we can use

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

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

The query to store the data in a normalized form would be:

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

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

Now run select * from normalized_table and you will get this:

现在，从normalized_table中运行select *，您将得到以下内容:

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

So why is this format a better choice? Besides many other reasons, one is that you can easily convert it to your old schema with

那么，为什么这种格式是更好的选择呢?除了许多其他原因之外，一个原因是您可以很容易地将它转换为您的旧模式

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 |

or to your desired format

或者你想要的格式

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 |

If you don't care about normalization and just want this task to be done, you can update your table with

如果您不关心规范化，只希望完成此任务，那么可以使用以下方法更新表

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;

And drop the normalized_table.

放normalized_table。

But then you won't be able to use simple queries like

但是这样你就不能使用简单的查询了

select *
from normalized_table
where k = 'A'

See demo at rextester.com

看到演示rextester.com

#2

Not using stored procedures, I would do it in 2 steps:

不使用存储过程，我将分两步完成:

1. Insert the comma at the second occurrence of the pipe character:

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

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

2. Insert the remaining commas - execute the query N times:

   插入剩余的逗号——执行查询N次:

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

   where N =

   其中N =

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

   (or don't bother with counting and continue to execute the query as long as it doesn't tell you "0 rows affected")

   (或者不用计数，只要查询不告诉您“受影响的0行”，就继续执行查询)

Checked with this set of data:

核对了这组数据:

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

results in:

结果:

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

---

(I'll provide an explanation later)

(稍后我会解释)

#3

Demo

演示

Rextester demo

Rextester演示

Explanation

解释

This could be solved relatively easily if only MySQL had a regular expression replacement function but unfortunately it doesn't. So I wrote one - see this blog post. The "advanced version" is needed here to allows it to perform a recursive replace in the found match for the replacement. Then the following relatively simple SQL can be used:

如果MySQL有一个正则表达式替换函数，这个问题可以相对容易地解决，但不幸的是它没有。所以我写了一篇——看这篇博文。这里需要“高级版本”，以便在找到的匹配项中执行递归替换。然后可以使用以下比较简单的SQL:

SQL (function code omitted for brevity)

SQL(简洁性省略函数代码)

SELECT id,
       options AS `before`,
       reg_replace(options,
                   '\\|.*\\|', -- 2 pipe symbols with any text in between
                   '\\|$',     -- Replace the second pipe symbol
                   ',',        -- Replace with a comma
                   FALSE,      -- Non-greedy matching
                   2,          -- Min match length = 2 (2 pipe symbols)
                   0,          -- No max match length
                   0,          -- Min sub-match length = 1 (1 pipe symbol)
                   0           -- Max sub-match length = 1 (1 pipe symbol)
                   ) AS `after`
FROM tbl;

#4

Hum, I think you are trying to do something like this

嗯，我想你是想做这样的事

SELECT GROUP_CONCAT(CONCAT(options,",") SEPARATOR "|") FROM Table.name;

I explain briefly, I take for each row the result and I concatenate "," and I concatenate all the row with the separator "|". You will have to change the Table.name with the name of your table

我简单地解释一下，我为每一行取结果，并将“”、“”和“|”分隔符连接在一起。您必须使用表的名称更改表的名称

If you want to concatenate one more value like A,B,C (you did not explain from where the ABC value are coming from so let's say ValueWhereABCisComingFrom):

如果您想再连接一个值，比如A、B、C(您没有解释ABC值从哪里来，所以我们假设ValueWhereABCisComingFrom):

SELECT GROUP_CONCAT(CONCAT(ValueWhereABCisComingFrom,"|",options) SEPARATOR ",") FROM Table.name;

if my table is like this :

如果我的桌子是这样的:

id | ValueWhereABCisComingFrom | options
0  | A    | 10
1  | B    | 20
2  | C    | 30

You wil have something like that :

你会有这样的东西:

A|10,B|20,C|30

EDIT 1

编辑1

There is no way to do that in that case. There is no function like preg_replace in mysql. All you can do is to replace all the "|" like

在那种情况下没有办法那样做。mysql中没有类似preg_replace的函数。你所能做的就是替换掉所有的|。

SELECT  Replace(options, '|', ',') AS P
FROM `docs`;

In MariaDB, there is such a function so you could maybe try to pass from one base to an other. But with MYSQL only, no way :/

在MariaDB中，有这样一个函数，你可以尝试从一个碱基传递到另一个碱基。但是只有MYSQL，没有办法:/

#5

You can do by creating a function

你可以创建一个函数

CREATE FUNCTION doiterate(str TEXT, i INT, next INT, isp TINYINT(1))
  RETURNS TEXT
  BEGIN
    myloop: LOOP
      IF next = 0 THEN
        LEAVE myloop;
      END IF;
      IF isp = TRUE THEN
        set str = insert(str, i, 1, ',');
        set isp = FALSE;
        set i = next;
        set next = locate('|', str, i + 1);
        ITERATE myloop;
      ELSE
        set isp = TRUE;
        set i = next;
        set next = locate('|', str, i + 1);
        ITERATE myloop;
      END IF;
      LEAVE myloop;
    END LOOP;
    return str;
  END;

and calling it that way :

这样称呼它:

SELECT t.`column`,
  @loc := locate('|', t.`column`) as position,
  @next := locate('|', t.`column`, @loc +1) as next,
  @isp := 0 is_pipe,
  @r := doiterate(t.column, @loc, @next, @isp) as returnstring
from test t;

I assume you'll be smart enough to

我想你会很聪明的

• change the tablename & column name
• 更改表名和列名
• insert this into an update request
• 将其插入到更新请求中

You can change the @isp := to 1 if I got the wrong pipe/coma change (i assumed second pipe should be changed to a coma)

如果我得到错误的管道/彗差更改，您可以将@isp:=更改为1(我假设第二个管道应该更改为彗差)