mysql - 如何将字符串拆分为 4 个字符的子字符串，然后用连字符连接它们？

Question

我需要编写一个执行以下操作的 mysql 查询:

我在数据库表中有序列号列表，查询应该只更新序列号长度超过 15 的序列号行，然后每个序列号被分成 4 位子字符串，用 a 分隔连字符。示例:

“10028641232912356”应该变成“1002-8641-1232-9123-56”

我从这个开始，它只在前 4 个之后插入一个连字符:

SELECT serial_no, CHAR_LENGTH(TRIM(serial_no)) 作为“长度”，
CONCAT_WS('-',SUBSTRING(TRIM(serial_no),1,4),
SUBSTRING(TRIM(serial_no),5,4)) 作为结果
从位置
serial_no 不为空且 CHAR_LENGTH(TRIM(serial_no))>=15 ;

这只是 select 语句，起初我只是想获取(如在 select 中)序列号的新格式，然后我会更新它，但由于我不知道我需要的每个序列号的确切长度弄清楚这部分:

CONCAT_WS('-',SUBSTRING(TRIM(serial_no),1,4)

这只能使用 mysql 函数完成

感谢任何帮助

Answer 1

简介

因为我们已经知道您的字符串可以有任意长度；您的问题归结为如何在 MySQL 中将任意长度的字符串拆分为一定长度的 block 。不幸的是，MySQL 中存在一个严重的问题，阻止我们以“ native ”方式执行此操作。这个问题是——MySQL 不支持序列。这意味着您不能使用某种内部迭代器(例如构造)来遍历您的字符串。

但总有办法

构建顺序

我们可以使用一个技巧来做到这一点。技巧的第一部分:使用 CROSS JOIN 生成所需的行集。如果您不知道它是如何工作的，那么我会提醒您。它将产生 Cartesian product两个行集。技巧的第二部分:使用众所周知的公式。

N = d₁x10¹ + d₂x10² + ...

实际上，您可以使用任何基数来做到这一点，而不仅仅是 10。对于此演示，我将使用 10。用法:

SELECT 
  n1.i+10*n2.i AS num
FROM 
  (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) as n1 
  CROSS 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) as n2

所以我们对数字 0..9 使用 UNION ALL 来生成乘法部分(我们的 d_i)。通过上面的查询，您将获得连续的 0..99 数字。这是因为我们只使用直到 2 的基本权力，并且 10²=100。你可以查看fiddle以确保它能正常工作。

遍历字符串

现在有了这些“伪生成器”，我们可以通过字符串模拟迭代。为此，有 MySQL variables那将是我们的迭代器。当然分离弦片是SUBSTR()的作品.所以基本的 skel 是:

SELECT 
  SUBSTR(@str, @i:=@i+@len, @len) as chunk 
FROM 
  (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) as n1 
  CROSS 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) as n2 
  CROSS JOIN 
  (SELECT @str:='ABCD1234EFGH5678IJKL90', @len:=4, @i:=1-@len) as init 
LIMIT 6;

(上面示例的 fiddle 是 here )。我们只是遍历序列，然后使用迭代器创建正确的偏移量。现在剩下要做的就是收集我们的字符串并插入连字符。希望有GROUP_CONCAT()在 MySQL 中:

SELECT 
  GROUP_CONCAT(chunk SEPARATOR '-') AS string 
FROM 
  (SELECT 
    SUBSTR(@str, @i:=@i+@len, @len) as chunk 
  FROM 
    (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) as n1 
    CROSS 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) as n2 
    CROSS JOIN 
    (SELECT @str:='ABCD1234EFGH5678IJKL90', @len:=4, @i:=1-@len) as init
  ) AS data 
WHERE chunk!='';

(再次，fiddle 在这里)。

整张 table

现在它是一个示例，您想从表格中选择它。会更复杂:

SELECT 
  serial_no, 
  GROUP_CONCAT(chunk SEPARATOR '-') AS serial 
FROM 
  (SELECT 
    SUBSTR(
      IF(@str=serial_no, @str, serial_no), 
      @i:=IF(@str=serial_no, @i+@len, 1), 
      @len
    ) AS chunk, 
    @str:=serial_no AS serial_no 
  FROM 
    (SELECT 
      serial_no 
    FROM 
      pos 
      CROSS 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) as n1 
      CROSS 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) as n2 
    ORDER BY 
      serial_no) AS data 
  CROSS JOIN 
    (SELECT @len:=4, @i:=1-@len) AS init) AS seq 
WHERE 
  chunk!='' 
GROUP BY 
  serial_no;

Fiddle 可用 here .生成行集还有另一个技巧。我们现在将使用 CROSS JOIN 而不是初始化字符串。所以我们应该传递 serial_no 的当前 pos 值。为了确保所有行都将正确迭代，我们必须对它们进行排序(这是通过内部 ORDER BY 完成的)。

限制

如您所知，序列限制为 99；因此，将我们的 @len 定义为 4 后，我们最多只能拆分 4000 长度的字符串。此外，这将在任何情况下使用整个序列。即使您的字符串短得多(很可能是这样)。因此可能会产生性能影响。

值得付出努力吗？

我的观点是:大部分情况下，没有。也许，用一次可能没问题。但它不会被重用，也不会可读。因此，使用 DBMS 函数执行此类字符串操作意义不大，因为我们有此类应用程序。它应该用于此目的，使用它您实际上可以创建可重用/可扩展/或/任何代码。

另一种方法可能是创建存储过程，我们可以在其中执行所需的操作(因此，按给定的长度拆分字符串并将其与给定的定界符连接起来)。但老实说，这只是一种“隐藏问题”的尝试。因为即使它可以重复使用，它仍然会有同样的弱点；性能影响。如果我们要为 DBMS 创建代码，则更是如此。再说一次，我们为什么不将该代码放在应用程序中呢？在 99% 的情况下，DBMS 是数据存储的地方，而应用程序是代码(例如逻辑)的地方。混合这两者几乎总是以糟糕的结果告终。