如何替换MySQL字符串中特定字符的所有其他实例？
如何替换MySQL字符串中特定字符的所有其他实例？
提问于 2017-08-03 16:00:22
如何用查询替换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);
?>所以我不想用PHP，而是用MySQL。
回答 1
Stack Overflow用户
发布于 2017-08-08 08:15:37
您应该考虑将数据存储在规范化的模式中。在您的示例中，该表应如下所示：
| 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'页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/45478226
复制相关文章
相似问题