如何替换MySQL字符串中特定字符的所有其他实例? [英] How to replace every other instance of a particular character in a MySQL string?
问题描述
如何通过查询替换mysql列中的值,例如,列为options
及其类型为varchar(255)
How to replace value in mysql column by query like, Column is options
and its of type 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.
So instead of PHP, I want to do this in MySQL.
推荐答案
您应考虑将数据存储在规范化架构中.在您的情况下,表格应如下所示:
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.
那么如何将给定的数据转换为新的架构?您将需要一个包含序列号的帮助程序表.由于您的列是varchar(255)
,因此您只能在其中存储128个值(+ 127个定界符).但是,我们只创建1000个数字.您可以使用具有足够行的任何表.但是,由于任何MySQL服务器都有information_schema.columns
表,因此我将使用它.
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.
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.
要从定界字符串中提取值,可以使用substring_index()
函数. i
位置的值将是
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(t.options, '|', i ), '|', -1)
在您的字符串中,有一个键序列及其后的值.键的位置是一个奇数.因此,如果键的位置为i
,则对应值的位置将为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
要获取字符串中定界符的数量并限制我们的联接,我们可以使用
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
现在运行select * from normalized_table
,您将获得以下信息:
Now run select * from normalized_table
and you will get this:
| 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 |
或您想要的格式
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;
然后放下normalized_table
.
但是那样您将无法使用简单的查询,例如
But then you won't be able to use simple queries like
select *
from normalized_table
where k = 'A'
请参阅rextester.com上的演示
See demo at rextester.com
这篇关于如何替换MySQL字符串中特定字符的所有其他实例?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!