您应该考虑将数据存储在规范化的模式中。在您的情况下,表应该如下:
| 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 |
所以,为什么使用这种格式是更好的选择?除了其他许多原因外,一个原因是你可以轻松地将其转换为旧架构,并且保留HTML标记。
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上查看演示(点击此处)
A
是从哪里来的?10
又是从哪里来的?它们是否在不同的列中?你已经使用了什么查询? - Milan Chheda