将字符串转换为具有架构的表 [英] To convert a string into table with schema

问题描述

为您提供了一个具有两种类型分隔符的字符串.提供了表的架构.如何以有效的方式将分隔字符串转换为临时表?例如，

You are provided with a string having delimiters of two types. The schema of the table is provided. How to convert the delimited string into a temporary table in an efficient manner? For example,

Andy~22~US|Jane~24~Australia|Davis~30~UK|Nancy~32~Germany

我是 MySql 的新手.所以任何帮助将不胜感激?

I am new to MySql. So any help will be greatly appreciated?

推荐答案

我会这样写 也许 Name 有足够的方法，但它必须工作！

I would write it like this maybe there is sufficient way with Name , but it has to work!

Create table MIX (
`id` int not null,
`x` text not null)
ENGINE = InnoDB;
insert into mix value (1,'Andy~22~US|Jane~24~Australia|Davis~30~UK|Nancy~32~Germany');
create table unmix select
numbers.n as id,
SUBSTRING_INDEX(SUBSTRING_INDEX(x, '~', 2*numbers.n),'~', -1) as Age,
SUBSTRING_INDEX(SUBSTRING_INDEX(x, '|', 1*numbers.n),'~', -1) as Country,
substring_index(SUBSTRING_INDEX(SUBSTRING_INDEX(x,'~', 2*numbers.n),'|',-1),'~',+1) as Name
from
(select 1 n union all
 select 2 union all 
 select 3 union all select 4 ) 
 numbers INNER JOIN mix
on CHAR_LENGTH(mix.x)
 -CHAR_LENGTH(REPLACE(mix.x, ' ', ''))>=numbers.n-4;

这篇关于将字符串转换为具有架构的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！