选择从 Y 行开始的 X 行 [英] Select X lines starting at line Y

问题描述

我正在尝试从 mysql 的 LONGTEXT 中选择特定范围的行.

例如，假设我有一个名为 log_data 的 LONGTEXT 列，其中包含以下文本:

INFO 这是第一行INFO 这是第二行INFO 这是第三行INFO 这是第四行INFO 这是第五行

我希望能够选择第 2-4 行(但这些值可能会改变，有时我可能想从 45 行长条目中的第 15-26 行等中进行选择).

类似的东西

select LINES(log_data, 2, 4) as log_data from logs where id = 7;

应该导致

INFO 这是第二行INFO 这是第三行INFO 这是第四行

<块引用>

注意:行总是用 \n 分隔，从不以 \r\n 分隔.

我知道我可以使用 SUBSTRING_INDEX 选择第 0 行到 X 行，但据我所知，这并不能让我选择起始行.

解决方案

如果我们在 Substring_Index() 函数，从右边开始计数向左，并为我们获取分隔符右侧的子字符串.

所以，通过一些数学运算，为了得到第二 (2) 到第四 (4) 行:

SELECTSUBSTRING_INDEX(SUBSTRING_INDEX(log_data, '\n', 4),'\n',-(4 - 2 + 1))

同样，对于第 15 - 26 行，它将是:

SELECTSUBSTRING_INDEX(SUBSTRING_INDEX(log_data, '\n', 26),'\n',-(26 - 15 + 1))

p 行到 q 行的一般公式是:

SELECTSUBSTRING_INDEX(SUBSTRING_INDEX(log_data, '\n', q),'\n',-(q - p + 1))

I'm trying to select a specific range of lines from a LONGTEXT in mysql.

For example, say I have a LONGTEXT column named log_data with the following text in it:

INFO this is the first line
INFO this is the second line
INFO this is the third line
INFO this is the fourth line
INFO this is the fifth line

I want to be able to select line 2-4 inclusive (but these values may change, some times i might want to select from line 15-26 in a 45 line long entry, etc).

Something like

select LINES(log_data, 2, 4) as log_data from logs where id = 7;

Should result in

INFO this is the second line
INFO this is the third line
INFO this is the fourth line

Note: Lines are always delimited with \n and never \r\n.

I know i can select lines 0 to X using SUBSTRING_INDEX, but that doesn't let me select the starting line as far as I know.

解决方案

If we provide negative count in the Substring_Index() function, it will count from the right to left, and fetch us the substring to the right of the delimiter.

So, with some maths, in order to get second (2) to fourth (4) line:

SELECT 
  SUBSTRING_INDEX(
      SUBSTRING_INDEX(log_data, '\n', 4), 
      '\n',
      -(4 - 2 + 1)
      )

Similarly, for lines 15 - 26 it will be:

SELECT 
  SUBSTRING_INDEX(
      SUBSTRING_INDEX(log_data, '\n', 26), 
      '\n',
      -(26 - 15 + 1)
      )

General formula for a p line to q line would be:

SELECT 
  SUBSTRING_INDEX(
      SUBSTRING_INDEX(log_data, '\n', q), 
      '\n',
      -(q - p + 1)
      )

这篇关于选择从 Y 行开始的 X 行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！