MySQL字段数据解析 [英] MySQL Field Data Parsing
本文介绍了MySQL字段数据解析的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
在这里开始，我被要求创建一个为了充实我将在这里尝试做的新帖子，但恐怕它比较罗word.我创建了一个测试表，其中包含需要解析的数据的良好横截面.在此处中使用此查询，并在其中添加了一些其他字段所需的，这是现在的查询.
SELECT ID, PartNo,
GROUP_CONCAT(
REPLACE(t.part, '-', CONCAT(' ', LEFT(t.part, 2)))
ORDER BY t.ModelList
SEPARATOR ' '
) Models
FROM (
SELECT t.ID, t.PartNo, t.Models, p.ModelList,
REPLACE(REPLACE(
SUBSTRING_INDEX(t.Models, ';', p.ModelList),
SUBSTRING_INDEX(t.Models, ';', p.ModelList - 1),
''
), ';', '') part
FROM parts_listing t CROSS JOIN (
SELECT 1 ModelList UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) p
WHERE REPLACE(REPLACE(Models, '-', ''), ';', '') REGEXP '^[0-9]*$'
) t
WHERE t.part <> ''
GROUP BY t.ID, t.PartNo, t.Models
ORDER BY t.PartNo, t.Models
对于最基本的部分，模型"数据如下所示:
2206-13-26-33; 2302-06-13-32-33
. . .这是我现在正在研究的部分. 模型"列还包含所有23RD等，也需要以某种方式进行解析，但现在我仅关注上面显示的数据，并且需要将其解析为类似以下内容:
2206 2213 2226 2233 2302 2306 2313 2332 2333
但是，上面的查询似乎在重复第一位，因此显示为:
2206 2213 2226 2233 2202 2206 2213 2232 2233
将结果分成一个块，但是如果每个结果都在自己的行上也是可以的，因为最终它将被转储到另一个表中，届时将是这样.
它也给出的结果太少.实际表包含15,000多行，但这仅获取一小部分，在下面的数据示例中，它应获取约20行，但实际上仅获取6行，尽管小提琴查询只能获取555行. >
可以使用以下方法创建横截面数据样本:
DROP TABLE IF EXISTS `parts_listing`;
CREATE TABLE IF NOT EXISTS `parts_listing` (
`ID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`GroupNumber` varchar(20) DEFAULT NULL,
`PartNo` varchar(30) DEFAULT NULL,
`Models` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
INSERT INTO `parts_listing` (`ID`, `GroupNumber`, `PartNo`, `Models`) VALUES
(1, '0.000', '391906', '2201-02-11-20-22-32-40; 2301'),
(2, '0.000', '391907', '2206-26-33'),
(3, '0.000', '399391', '2213'),
(4, '0.000', '415209', '2302-06-32-33'),
(5, '0.000', '415245', '2313'),
(6, '0.000', '437273', 'ALL 24TH; 25TH; 26TH'),
(7, '0.000', '436803', '2631 (BODY 2678); 5431 (BODY 5478)'),
(8, '0.000', '448611', 'ALL 54TH'),
(9, '0.0015', '372174', 'ALL 22ND; 23RD'),
(10, '0.0015', '399324', '2201-02-11-20-22-32-40; 2301'),
(11, '0.0015', '399324', '2206-13-26-33; 2302-06-13-32-33'),
(12, '0.0015', '372146', 'ALL 22ND; 23RD'),
(13, '0.0015', 'G179359', '2201-02-11-20-22-32-40; 2301'),
(14, '0.0015', 'G179559', '2206-13-26-33; 2302-06-13-32-33'),
(15, '0.0015', '422024', '2201-02-11-20-22-32-40; 2301'),
(16, '0.0015', '422024', '2206-13-26-33; 2302-06-13-32-33'),
(17, '0.0016', '378664', 'ALL 22ND; 23RD'),
(18, '0.0016', 'G178532', 'ALL 22ND; 23RD'),
(19, '0.0016', 'G123197', 'ALL 22ND; 23RD'),
(20, '30.793', '448421', 'ALL 54TH'),
(21, '30.7932', '422762', '2462-65-92-95-98; 2562-65-92-95; 2662-65-92-95'),
(22, '30.7932', '448902', '5462-65-67-82-85-92-95-97'),
(23, '30.7938', '399941', 'ALL 22ND; 23RD'),
(24, '30.7938', '425809', 'ALL 24TH; 25TH; 26TH'),
(25, '30.7938', '447799', 'ALL 54TH'),
(26, '30.79383', '399927', 'ALL 22ND; 23RD; 24TH; 25TH; 26TH'),
(27, '30.79396', 'PA437550', '2562-65-77-79; 2677-79'),
(28, '30.79398', '422470', '2452-72; 2552-72'),
(29, '30.79398', '422471', '2452-72; 2552-72'),
(30, '30.79398', '422553', 'ALL 24TH; 25TH'),
(31, '30.8007', '425517', '2413-52-62-65-67-72-92-95-98; 2552-62-65-72-77-92-95; 2650-51-52-62-65-72-77-92-95-97; 5450-51-52-62-65-67-72-77-82-85-92-95-97'),
(32, '30.8007', '425920', 'RHD, 2462-65-92-95; 2562-65-92-95; 2662-65-92-95-97; 5462-65-82-85-92-95-97'),
(33, '30.8044', 'G129662', '2293');
解决方案
我认为您需要一种更复杂的方法来验证Models列值是否正确，因为分号后有空格.此外，如果需要处理包含十个以上部分的Models列值，则需要更长的数字序列.我为此使用 INFORMATION_SCHEMA COLUMNS 表.您也可以为此创建一个辅助表.
SELECT
CONCAT(
IF(N.n = 0, '', LEFT(t.part, 2)),
SUBSTRING_INDEX(
SUBSTRING_INDEX(t.part, '-', N.n + 1), '-', -1
)
) AS part, t.isHD, t.ID, t.GroupNumber, t.PartNo, t.Models
FROM Numbers AS N CROSS JOIN (
SELECT
SUBSTRING_INDEX(
SUBSTRING_INDEX(t.part, ' ', N.n + 1), ' ', -1
) AS part, t.isHD, t.ID, t.GroupNumber, t.PartNo, t.Models
FROM Numbers AS N CROSS JOIN (
SELECT
SUBSTRING_INDEX(
SUBSTRING_INDEX(t.part, ';', N.n + 1), ';', -1
) AS part, t.isHD, t.ID, t.GroupNumber, t.PartNo, t.Models
FROM Numbers AS N CROSS JOIN (
SELECT
REPLACE(
SUBSTRING_INDEX(
SUBSTRING_INDEX(t.part, 'LHD', N.n + 1), 'LHD', -1
), ',', ''
) AS part, IF(N.n > 0, 1, t.isHD) AS isHD,
t.ID, t.GroupNumber, t.PartNo, t.Models
FROM Numbers AS N CROSS JOIN (
SELECT
SUBSTRING_INDEX(
SUBSTRING_INDEX(p.Models, 'RHD', N.n + 1), 'RHD', -1
) AS part, IF(N.n > 0, 2, 0) AS isHD,
p.ID, p.GroupNumber, p.PartNo, p.Models
FROM Numbers AS N CROSS JOIN parts_listing AS p
WHERE p.Models REGEXP '[[:<:]]([LR]HD,? +)?[0-9]+(-[0-9]+)*[[:>:]]' AND
N.n <= (LENGTH(p.Models) - LENGTH(REPLACE(p.Models, 'RHD', ''))) / 3
) AS t WHERE N.n <= (LENGTH(t.part) - LENGTH(REPLACE(t.part, 'LHD', ''))) / 3
) AS t WHERE N.n <= LENGTH(t.part) - LENGTH(REPLACE(t.part, ';', ''))
) AS t WHERE N.n <= LENGTH(t.part) - LENGTH(REPLACE(t.part, ' ', ''))
) AS t WHERE t.part REGEXP '^[0-9]+(-[0-9]+)*$' AND
N.n <= LENGTH(t.part) - LENGTH(REPLACE(t.part, '-', ''));
部分输出(29行):
+------+------+----+-------------+---------+--------------------------------------+
| part | isHD | ID | GroupNumber | PartNo | Models |
+------+------+----+-------------+---------+--------------------------------------+
| 2201 | 0 | 1 | 0.000 | 391906 | 2201-02-11-20-22-32-40; 2301 |
| 2202 | 0 | 1 | 0.000 | 391906 | 2201-02-11-20-22-32-40; 2301 |
| 2211 | 0 | 1 | 0.000 | 391906 | 2201-02-11-20-22-32-40; 2301 |
| 2220 | 0 | 1 | 0.000 | 391906 | 2201-02-11-20-22-32-40; 2301 |
| 2222 | 0 | 1 | 0.000 | 391906 | 2201-02-11-20-22-32-40; 2301 |
| 2232 | 0 | 1 | 0.000 | 391906 | 2201-02-11-20-22-32-40; 2301 |
| 2240 | 0 | 1 | 0.000 | 391906 | 2201-02-11-20-22-32-40; 2301 |
| 2301 | 0 | 1 | 0.000 | 391906 | 2201-02-11-20-22-32-40; 2301 |
| 2213 | 1 | 3 | 0.000 | 399391 | LHD 2213 |
| 2313 | 2 | 5 | 0.000 | 415245 | RHD, 2313 |
| 2631 | 0 | 7 | 0.000 | 436803 | 2631 (BODY 2678); 5431 (BODY 5478) |
| 5431 | 0 | 7 | 0.000 | 436803 | 2631 (BODY 2678); 5431 (BODY 5478) |
| 2206 | 1 | 14 | 0.0015 | G179559 | LHD, 2206-13-26-33; 2302-06-13-32-33 |
| 2213 | 1 | 14 | 0.0015 | G179559 | LHD, 2206-13-26-33; 2302-06-13-32-33 |
| 2226 | 1 | 14 | 0.0015 | G179559 | LHD, 2206-13-26-33; 2302-06-13-32-33 |
| 2233 | 1 | 14 | 0.0015 | G179559 | LHD, 2206-13-26-33; 2302-06-13-32-33 |
| 2302 | 1 | 14 | 0.0015 | G179559 | LHD, 2206-13-26-33; 2302-06-13-32-33 |
| 2306 | 1 | 14 | 0.0015 | G179559 | LHD, 2206-13-26-33; 2302-06-13-32-33 |
| 2313 | 1 | 14 | 0.0015 | G179559 | LHD, 2206-13-26-33; 2302-06-13-32-33 |
| 2332 | 1 | 14 | 0.0015 | G179559 | LHD, 2206-13-26-33; 2302-06-13-32-33 |
| 2333 | 1 | 14 | 0.0015 | G179559 | LHD, 2206-13-26-33; 2302-06-13-32-33 |
| 2201 | 0 | 15 | 0.0015 | 422024 | 2201-02-11-20-22-32-40; RHD, 2301 |
| 2202 | 0 | 15 | 0.0015 | 422024 | 2201-02-11-20-22-32-40; RHD, 2301 |
| 2211 | 0 | 15 | 0.0015 | 422024 | 2201-02-11-20-22-32-40; RHD, 2301 |
| 2220 | 0 | 15 | 0.0015 | 422024 | 2201-02-11-20-22-32-40; RHD, 2301 |
| 2222 | 0 | 15 | 0.0015 | 422024 | 2201-02-11-20-22-32-40; RHD, 2301 |
| 2232 | 0 | 15 | 0.0015 | 422024 | 2201-02-11-20-22-32-40; RHD, 2301 |
| 2240 | 0 | 15 | 0.0015 | 422024 | 2201-02-11-20-22-32-40; RHD, 2301 |
| 2301 | 2 | 15 | 0.0015 | 422024 | 2201-02-11-20-22-32-40; RHD, 2301 |
+------+------+----+-------------+---------+--------------------------------------+
Started here, I was asked to create a new post in order to flesh it out which I'll try to do here but I'm afraid it's rather wordy. I created a test table with what seems to be a good cross-section of the data that needs to be parsed. Using this query from here to which I added some additional fields that are needed, this is the query now.
SELECT ID, PartNo,
GROUP_CONCAT(
REPLACE(t.part, '-', CONCAT(' ', LEFT(t.part, 2)))
ORDER BY t.ModelList
SEPARATOR ' '
) Models
FROM (
SELECT t.ID, t.PartNo, t.Models, p.ModelList,
REPLACE(REPLACE(
SUBSTRING_INDEX(t.Models, ';', p.ModelList),
SUBSTRING_INDEX(t.Models, ';', p.ModelList - 1),
''
), ';', '') part
FROM parts_listing t CROSS JOIN (
SELECT 1 ModelList UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) p
WHERE REPLACE(REPLACE(Models, '-', ''), ';', '') REGEXP '^[0-9]*$'
) t
WHERE t.part <> ''
GROUP BY t.ID, t.PartNo, t.Models
ORDER BY t.PartNo, t.Models
For the most basic parts, the Models data looks like this:
2206-13-26-33; 2302-06-13-32-33
. . . and this is the part that I am working on now. The Models column also contains ALL 23RD etc that will also need to be parsed somehow but for now I am concerned only with the data as shown above and I need to parse it to something like:
2206 2213 2226 2233 2302 2306 2313 2332 2333
The query above, however, seems to be repeating the first bit so it comes out as:
2206 2213 2226 2233 2202 2206 2213 2232 2233
It gives the result in one chunk but it would also be okay if each result was on it's own line as ultimately it will be dumped into another table and that is the way it will be by then.
It also gives too few results. The actual table contains over 15,000 rows but this is fetching only a small fraction and in the data sample below, it should fetch about 20 rows but actually fetches only 6 although the fiddle, which has a very limited data set, does indeed give the proper results. Running the the fiddle query against my own data fetches only 555 rows.
A cross-section sample of data can be created with this:
DROP TABLE IF EXISTS `parts_listing`;
CREATE TABLE IF NOT EXISTS `parts_listing` (
`ID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`GroupNumber` varchar(20) DEFAULT NULL,
`PartNo` varchar(30) DEFAULT NULL,
`Models` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
INSERT INTO `parts_listing` (`ID`, `GroupNumber`, `PartNo`, `Models`) VALUES
(1, '0.000', '391906', '2201-02-11-20-22-32-40; 2301'),
(2, '0.000', '391907', '2206-26-33'),
(3, '0.000', '399391', '2213'),
(4, '0.000', '415209', '2302-06-32-33'),
(5, '0.000', '415245', '2313'),
(6, '0.000', '437273', 'ALL 24TH; 25TH; 26TH'),
(7, '0.000', '436803', '2631 (BODY 2678); 5431 (BODY 5478)'),
(8, '0.000', '448611', 'ALL 54TH'),
(9, '0.0015', '372174', 'ALL 22ND; 23RD'),
(10, '0.0015', '399324', '2201-02-11-20-22-32-40; 2301'),
(11, '0.0015', '399324', '2206-13-26-33; 2302-06-13-32-33'),
(12, '0.0015', '372146', 'ALL 22ND; 23RD'),
(13, '0.0015', 'G179359', '2201-02-11-20-22-32-40; 2301'),
(14, '0.0015', 'G179559', '2206-13-26-33; 2302-06-13-32-33'),
(15, '0.0015', '422024', '2201-02-11-20-22-32-40; 2301'),
(16, '0.0015', '422024', '2206-13-26-33; 2302-06-13-32-33'),
(17, '0.0016', '378664', 'ALL 22ND; 23RD'),
(18, '0.0016', 'G178532', 'ALL 22ND; 23RD'),
(19, '0.0016', 'G123197', 'ALL 22ND; 23RD'),
(20, '30.793', '448421', 'ALL 54TH'),
(21, '30.7932', '422762', '2462-65-92-95-98; 2562-65-92-95; 2662-65-92-95'),
(22, '30.7932', '448902', '5462-65-67-82-85-92-95-97'),
(23, '30.7938', '399941', 'ALL 22ND; 23RD'),
(24, '30.7938', '425809', 'ALL 24TH; 25TH; 26TH'),
(25, '30.7938', '447799', 'ALL 54TH'),
(26, '30.79383', '399927', 'ALL 22ND; 23RD; 24TH; 25TH; 26TH'),
(27, '30.79396', 'PA437550', '2562-65-77-79; 2677-79'),
(28, '30.79398', '422470', '2452-72; 2552-72'),
(29, '30.79398', '422471', '2452-72; 2552-72'),
(30, '30.79398', '422553', 'ALL 24TH; 25TH'),
(31, '30.8007', '425517', '2413-52-62-65-67-72-92-95-98; 2552-62-65-72-77-92-95; 2650-51-52-62-65-72-77-92-95-97; 5450-51-52-62-65-67-72-77-82-85-92-95-97'),
(32, '30.8007', '425920', 'RHD, 2462-65-92-95; 2562-65-92-95; 2662-65-92-95-97; 5462-65-82-85-92-95-97'),
(33, '30.8044', 'G129662', '2293');
解决方案
I think you need a more sophisticated way to verify that the Models column values are correct due to spaces after semicolons. In addition, if you need to process the Models column values that contain more than ten parts, you will need a longer sequence of numbers. I use the INFORMATION_SCHEMA COLUMNS table for this. You can also create an auxiliary table for this.
SELECT
CONCAT(
IF(N.n = 0, '', LEFT(t.part, 2)),
SUBSTRING_INDEX(
SUBSTRING_INDEX(t.part, '-', N.n + 1), '-', -1
)
) AS part, t.isHD, t.ID, t.GroupNumber, t.PartNo, t.Models
FROM Numbers AS N CROSS JOIN (
SELECT
SUBSTRING_INDEX(
SUBSTRING_INDEX(t.part, ' ', N.n + 1), ' ', -1
) AS part, t.isHD, t.ID, t.GroupNumber, t.PartNo, t.Models
FROM Numbers AS N CROSS JOIN (
SELECT
SUBSTRING_INDEX(
SUBSTRING_INDEX(t.part, ';', N.n + 1), ';', -1
) AS part, t.isHD, t.ID, t.GroupNumber, t.PartNo, t.Models
FROM Numbers AS N CROSS JOIN (
SELECT
REPLACE(
SUBSTRING_INDEX(
SUBSTRING_INDEX(t.part, 'LHD', N.n + 1), 'LHD', -1
), ',', ''
) AS part, IF(N.n > 0, 1, t.isHD) AS isHD,
t.ID, t.GroupNumber, t.PartNo, t.Models
FROM Numbers AS N CROSS JOIN (
SELECT
SUBSTRING_INDEX(
SUBSTRING_INDEX(p.Models, 'RHD', N.n + 1), 'RHD', -1
) AS part, IF(N.n > 0, 2, 0) AS isHD,
p.ID, p.GroupNumber, p.PartNo, p.Models
FROM Numbers AS N CROSS JOIN parts_listing AS p
WHERE p.Models REGEXP '[[:<:]]([LR]HD,? +)?[0-9]+(-[0-9]+)*[[:>:]]' AND
N.n <= (LENGTH(p.Models) - LENGTH(REPLACE(p.Models, 'RHD', ''))) / 3
) AS t WHERE N.n <= (LENGTH(t.part) - LENGTH(REPLACE(t.part, 'LHD', ''))) / 3
) AS t WHERE N.n <= LENGTH(t.part) - LENGTH(REPLACE(t.part, ';', ''))
) AS t WHERE N.n <= LENGTH(t.part) - LENGTH(REPLACE(t.part, ' ', ''))
) AS t WHERE t.part REGEXP '^[0-9]+(-[0-9]+)*$' AND
N.n <= LENGTH(t.part) - LENGTH(REPLACE(t.part, '-', ''));
Partial output (29 rows):
+------+------+----+-------------+---------+--------------------------------------+
| part | isHD | ID | GroupNumber | PartNo | Models |
+------+------+----+-------------+---------+--------------------------------------+
| 2201 | 0 | 1 | 0.000 | 391906 | 2201-02-11-20-22-32-40; 2301 |
| 2202 | 0 | 1 | 0.000 | 391906 | 2201-02-11-20-22-32-40; 2301 |
| 2211 | 0 | 1 | 0.000 | 391906 | 2201-02-11-20-22-32-40; 2301 |
| 2220 | 0 | 1 | 0.000 | 391906 | 2201-02-11-20-22-32-40; 2301 |
| 2222 | 0 | 1 | 0.000 | 391906 | 2201-02-11-20-22-32-40; 2301 |
| 2232 | 0 | 1 | 0.000 | 391906 | 2201-02-11-20-22-32-40; 2301 |
| 2240 | 0 | 1 | 0.000 | 391906 | 2201-02-11-20-22-32-40; 2301 |
| 2301 | 0 | 1 | 0.000 | 391906 | 2201-02-11-20-22-32-40; 2301 |
| 2213 | 1 | 3 | 0.000 | 399391 | LHD 2213 |
| 2313 | 2 | 5 | 0.000 | 415245 | RHD, 2313 |
| 2631 | 0 | 7 | 0.000 | 436803 | 2631 (BODY 2678); 5431 (BODY 5478) |
| 5431 | 0 | 7 | 0.000 | 436803 | 2631 (BODY 2678); 5431 (BODY 5478) |
| 2206 | 1 | 14 | 0.0015 | G179559 | LHD, 2206-13-26-33; 2302-06-13-32-33 |
| 2213 | 1 | 14 | 0.0015 | G179559 | LHD, 2206-13-26-33; 2302-06-13-32-33 |
| 2226 | 1 | 14 | 0.0015 | G179559 | LHD, 2206-13-26-33; 2302-06-13-32-33 |
| 2233 | 1 | 14 | 0.0015 | G179559 | LHD, 2206-13-26-33; 2302-06-13-32-33 |
| 2302 | 1 | 14 | 0.0015 | G179559 | LHD, 2206-13-26-33; 2302-06-13-32-33 |
| 2306 | 1 | 14 | 0.0015 | G179559 | LHD, 2206-13-26-33; 2302-06-13-32-33 |
| 2313 | 1 | 14 | 0.0015 | G179559 | LHD, 2206-13-26-33; 2302-06-13-32-33 |
| 2332 | 1 | 14 | 0.0015 | G179559 | LHD, 2206-13-26-33; 2302-06-13-32-33 |
| 2333 | 1 | 14 | 0.0015 | G179559 | LHD, 2206-13-26-33; 2302-06-13-32-33 |
| 2201 | 0 | 15 | 0.0015 | 422024 | 2201-02-11-20-22-32-40; RHD, 2301 |
| 2202 | 0 | 15 | 0.0015 | 422024 | 2201-02-11-20-22-32-40; RHD, 2301 |
| 2211 | 0 | 15 | 0.0015 | 422024 | 2201-02-11-20-22-32-40; RHD, 2301 |
| 2220 | 0 | 15 | 0.0015 | 422024 | 2201-02-11-20-22-32-40; RHD, 2301 |
| 2222 | 0 | 15 | 0.0015 | 422024 | 2201-02-11-20-22-32-40; RHD, 2301 |
| 2232 | 0 | 15 | 0.0015 | 422024 | 2201-02-11-20-22-32-40; RHD, 2301 |
| 2240 | 0 | 15 | 0.0015 | 422024 | 2201-02-11-20-22-32-40; RHD, 2301 |
| 2301 | 2 | 15 | 0.0015 | 422024 | 2201-02-11-20-22-32-40; RHD, 2301 |
+------+------+----+-------------+---------+--------------------------------------+
Demo on DB Fiddle.
这篇关于MySQL字段数据解析的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
