主页 / dba / 问题 / 291361
Accepted
emma
Asked: 2021-05-12 05:06:10 +0800 CST

将带有 #-delimiters 的 varchar 字段转换为多行

  • 772

我正在使用一个数据库，其中一些数据（表示下拉菜单的选定选项的整数值）仅在一个表列中保存为字符串（varchar）。在products_table（假设）中，有一product_id列映射到一selected-options列。在后者中，数据保存为#3#9#15#其中 3、9 和 15 是用户已选择的下拉菜单选项的 ID。还有另一个表（比方说option_table），其中 ID 为 1 到 15 的每个选项都被赋予了更多属性（如 alabel和 a weight）。

出于统计原因，我想创建一个 MySQL 视图，其中包含列selected option和number of products（已选择此选项的产品数量）。要到达那里，我需要来自 的单个值#3#9#15#，也就是说，在途中某处爆炸字符串。

现在我读到不鼓励这种形式的保存数据，这就是为什么 MySQL 中没有内置的字符串分解/拆分功能。但我无法更改数据格式，这是在一个巨大的软件内部确定的。我没有做出这个设计选择，但我必须以某种方式使用它。

使用内置函数substring_index()是没有选择的，因为保存在此字段中的选定选项的数量从 0 到 15 不等。此外，最大数量不是固定的，因为可能会不时创建新选项。

我尝试编写一个自己的过程，该过程将从中获取一个数字#3#9#15#（例如，通过反复修剪 # 分隔符）并用它创建一个新行。SELECT该过程应该使用语句访问该值，使用创建一个新视图CREATE OR REPLACE VIEW，然后插入一个新行，其中仅包含第一个选项 (3) 选项，例如INSERT INTO `viewname`(`product_id`,`option`) VALUES(@productID, SUBSTR(@options,2,1));where 。@options == #3#9#15#我试着在一开始只为第一个选项编写程序，但在这个阶段它已经失败了。

我的其他想法包括尝试将字符串@options转换为 SET 数据类型，以便我可以使用find_in_set()函数。我也阅读了这个问题和这篇文章，但发现它们很难理解。

只有 MySQL 的基本知识，我有什么方法可以创建这个视图吗？我将非常感谢任何提示我应该继续研究的方向。

编辑：我有服务器版本 10.3.28 - MariaDB 服务器。您需要更多有关这方面的信息吗？

Edit2：由于 phpMyAdmin 中的（持续）错误，我无法使用 SQLWITH语句（请参见此处）。

Edit3 : 原来，软件使用的分隔符实际上并不是一个#标志，而只是由 phpMyAdmin 显示。相反，它是 ASCII 控制字符0001或"^A".

mysql string-splitting

2 个回答

  1. 这是一种方法：

    SELECT zz.`idx`
  FROM (SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX('#3#9#15#', '#', num.`id`), '#', -1) AS SIGNED) as `idx`
          FROM (SELECT (h*10+t+1) as `id`
                  FROM (SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
                       (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b) num
                 WHERE num.`id` > 0) zz
 WHERE zz.`idx` > 0
 ORDER BY zz.`idx`;

    您最多可以将 100 个#分隔的 ID 传递到字符串中，并将您需要的内容拆分为UNSIGNED整数。

    • 1
  2. Best Answer

    强烈基于@matigo 的回答，我想出以下 SQL 调用将包含在 phpMyAdmin 的CREATE VIEW viewname AS .... 它从一个表列中获取一个值（以及在另一个表中赋予该值的 ID），在指定分隔符的每个实例（此处为：）处拆分该值，CHAR(1)并将这些拆分值与前面提到的 ID 一起输出。

    有关设置的信息：我们有两个从中获取信息的表，一个称为split_table_name此处（它包含split_column_name应在分隔符处拆分值的列）和另一个称为id_table_name（包含产品的唯一 ID；包含此 ID insplit_table_name作为外键列称为id_table_id)。

    请注意，该软件使用的分隔符实际上并不是一个#符号（正如我在我的问题中所假设的那样），而是仅由 phpMyAdmin 显示。它实际上是 ASCII 控制字符 0001。所以我需要将它转换为一个字符，CHAR(1)这样我才能在SUBSTRING_INDEX函数中使用它。

      SELECT CAST(
      COALESCE(
NULLIF(
SUBSTRING_INDEX ((SUBSTRING_INDEX (split_column_alias, CHAR(1), num.id)), CHAR(1), -1), ''),
          '0')  AS SIGNED)
     as one_split, content_alias.id_column_alias as `id_column_alias` FROM
(SELECT `split_table_alias`.`split_column_name` AS `split_column_alias`, `id_table_alias`.`id` AS `id_column_alias`
FROM `database_name`.`split_table_name` `split_table_alias`
JOIN `database_name`.`id_table_name` `id_table_alias`
ON `id_table_alias`.`id` = `split_table_alias`.`id_table_id`
        ) content_alias
INNER JOIN
(SELECT (h*10+t+1) as id FROM (
      SELECT 0 h  UNION
      SELECT 1    UNION
      SELECT 2    UNION
      SELECT 3    UNION
      SELECT 4    UNION
      SELECT 5    UNION
      SELECT 6    UNION
      SELECT 7    UNION
      SELECT 8    UNION
      SELECT 9) a,
    (
      SELECT 0 t  UNION
      SELECT 1    UNION
      SELECT 2    UNION
      SELECT 3    UNION
      SELECT 4    UNION
      SELECT 5    UNION
      SELECT 6    UNION
      SELECT 7    UNION
      SELECT 8    UNION
      SELECT 9) b ) num
WHERE num.id > 0
) zz
WHERE zz.one_split > 0
ORDER BY zz.one_split;
    • 0

相关问题