如何在MySQL中提取多行文本字段每行的最后一个单词？

核心思路 #

要处理字段内换行分隔的多行文本，需先将多行拆分为独立行，再提取每行的最后一个单词（即VIN），最后按VIN排序输出。

具体实现方案 #

1. 使用递归CTE拆分多行（MySQL 8.0+支持） #

递归CTE可以高效拆分换行分隔的内容，再提取每行的VIN：

WITH RECURSIVE split_lines AS (
    -- 初始化：拆分第一条记录的第一行
    SELECT 
        id,
        SUBSTRING_INDEX(MakesModels, '\n', 1) AS line,
        SUBSTRING(MakesModels, LENGTH(SUBSTRING_INDEX(MakesModels, '\n', 1)) + 2) AS remaining
    FROM quotes
    WHERE MakesModels IS NOT NULL AND MakesModels != ''
    
    UNION ALL
    
    -- 递归拆分剩余行
    SELECT 
        id,
        SUBSTRING_INDEX(remaining, '\n', 1) AS line,
        SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, '\n', 1)) + 2) AS remaining
    FROM split_lines
    WHERE remaining IS NOT NULL AND remaining != ''
)
SELECT 
    SUBSTRING_INDEX(TRIM(line), ' ', -1) AS vin,
    line AS full_vehicle_info
FROM split_lines
ORDER BY vin ASC;

注：如果字段内的换行符是\r\n，请把代码中的+2改为+3（\r\n占2个字符，加1是跳过换行符本身）。

2. 兼容低版本MySQL的方案（无CTE支持） #

如果你的MySQL版本低于8.0，可以借助数字辅助表来拆分多行：

• 先创建一个数字表（覆盖字段内最多行数，比如1到100）：

CREATE TABLE numbers (n INT PRIMARY KEY);
INSERT INTO numbers VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),...,(100);

• 拆分并提取VIN：

SELECT 
    SUBSTRING_INDEX(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(q.MakesModels, '\n', n.n), '\n', -1)), ' ', -1) AS vin,
    SUBSTRING_INDEX(SUBSTRING_INDEX(q.MakesModels, '\n', n.n), '\n', -1) AS full_vehicle_info
FROM quotes q
JOIN numbers n ON n.n <= LENGTH(q.MakesModels) - LENGTH(REPLACE(q.MakesModels, '\n', '')) + 1
WHERE q.MakesModels IS NOT NULL AND q.MakesModels != ''
ORDER BY vin ASC;

示例输出 #

针对你提供的示例数据，执行上述代码后会得到如下结果：

vin                  full_vehicle_info
1G6DJ8E37C0181444    2013 Porsche 1G6DJ8E37C0181444
1GYFK26259R997864    2013 Isuzu Box Truck 1GYFK26259R997864
KNALN4D79E5857681    1984 Chevrolet Impala 2-Door Hardtop KNALN4D79E5857681
SAJWA0FAXAH271577    2021 Nissan Leaf Sedan SAJWA0FAXAH271577
WAUDF48H97A796136    2007 Mercury WAUDF48H97A796136
WAUSF78E38A883031    2007 Honda Accord Sedan WAUSF78E38A883031
WVGAV7AX1FW055522    2003 Pontiac Grand Prix Hardtop WVGAV7AX1FW055522

内容的提问来源于stack exchange，提问作者user5175034