将VARCHAR字段拆分为2行MySQL

I have a database full of emails. It contains the to, from, subject, body, etc as columns. I am trying to output essentially the same format, but for the application I will be using the output file for, the body text cannot be longer than about 300 characters. I don't want to just grab the first 300 characters, because I need all the text. What I want to do is chunk the text up such that if an email body is say, 900 characters, to return 3 separate rows, each containing a third of the message, and have the rows all contain the to, from, subject, etc information.

我有一个充满电子邮件的数据库。它包含to,from,subject,body等列。我试图输出基本相同的格式,但对于我将使用输出文件的应用程序,正文文本不能超过约300个字符。我不想只抓住前300个字符,因为我需要所有的文字。我想要做的是将文本分块,如果一个电子邮件正文,900字符,返回3个单独的行,每行包含三分之一的消息,并且所有行都包含to,from,subject等信息。

i.e.) I have this:

即)我有这个:

sender1      recipient1       subject1       body1(600 characters)
sender2      recipient2       subject2       body2(150 characters)

I want this:

我要这个:

sender1     recipient1        subject1       body1(first 300 characters)
sender1     recipient1        subject1       body1(next 300 characters)
sender2     recipient2        subject2       body2(150 characters)

Thanks!

1 个解决方案

#1

Here's an example of an approach:

以下是一种方法示例:

SELECT p.i, SUBSTR(e.body,p.i*n.len+1,n.len) AS bodypart
  FROM ( SELECT 'abcdefghijklmnopqrstuvwxyz' AS body
       ) e
 CROSS
  JOIN ( SELECT 10 AS len
       ) n
  JOIN ( SELECT 0 AS i UNION ALL SELECT 1 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
    ON p.i <= LENGTH(e.body) DIV n.len

That query splits out up to ten (10) parts up to n.len characters in length.

该查询最多可拆分十(10)个部分,最长可达n.len个字符。

To get parts up to 300 characters in length, change the literal 10 to 300, or whatever max size you want. To get more parts returned, you would modify the inline view aliased as p to return a set of integers 0 thru the maximum number of parts you need. To get this to run against your table, you would replace the inline view aliased as e with your table containing the body column, and return the other columns you want.

要获得长度不超过300个字符的部分,请将文字10更改为300,或更改所需的最大大小。要获得更多返回的零件,您可以将别名的内联视图修改为p,以返回一组整数0到所需的最大零件数。要使此操作针对您的表运行,您将使用包含body列的表替换别名为e的内联视图,并返回所需的其他列。

Something like this:

像这样的东西:

SELECT e.sender
     , e.recipient
     , e.subject
     , SUBSTR(e.body,p.i*n.len+1,n.len) AS bodypart
     , p.i AS bodypart_number
  FROM myemailtable e
 CROSS
  JOIN ( SELECT 300 AS len
       ) n
  JOIN ( SELECT 0 AS i UNION ALL SELECT 1 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
    ON p.i <= LENGTH(e.body) DIV n.len

---

Update

That query has a corner case, when LENGTH(e.body) is an even multiple of n.len, it will return an "extra" empty piece. One ugly-ish fix is to change the predicate in the ON clause:

该查询有一个极端情况,当LENGTH(e.body)是n.len的偶数倍时,它将返回一个“额外”的空片。一个丑陋的解决方法是更改​​ON子句中的谓词:

    ON p.i < LENGTH(e.body) DIV n.len 
    OR (p.i = LENGTH(e.body) DIV n.len AND LENGTH(e.body) MOD n.len > 0)

Also note that if you store the result from the bodypart expression in a VARCHAR column, any trailing space(s) will be removed from each part.

另请注意,如果将bodypart表达式的结果存储在VARCHAR列中,则将从每个部分中删除任何尾随空格。