php - MySQL - 如何规范化包含分隔符分隔的 ID 的列

Question

我正在尝试规范化一个表，之前的开发人员设计该表有一个包含竖线分隔 ID 的列，这些 ID 链接到同一表中的其他行。

客户表

id    |    aliases (VARCHAR)
----------------------------
1     |    |4|58|76
2     |    
3     |
4     |    |1|58|76
...   |    
58    |    |1|4|76
...   |
76    |    |1|4|58

所以客户1、4、58、76都是彼此的“别名”。客户 2 和 3 没有别名，因此该字段包含一个空字符串。

我想取消整个“别名”系统，并规范化数据，这样我就可以将其他客户全部映射到一条记录中。所以我希望客户 1、4、58 和 76 的相关表数据都映射到客户 1。

我想我会填充一个新表，然后我可以加入其他表并对其他表执行更新。

加入表格

id  |  customer_id  |  alias_id
-------------------------------
1   |  1            |  4
2   |  1            |  58
3   |  1            |  76

如何从第一个表中获取数据，并将其转换为上述格式？如果这将成为纯 SQL 中的绝对噩梦，我将只编写一个 PHP 脚本来尝试完成这项工作并插入数据。

Answer 1

当我开始回答这个问题时，我认为它会快速而简单，因为我曾在 SQL Server 中做过一次非常相似的事情，但在翻译中证明了这个概念在这个完整的解决方案中迅速发展。

您的问题中没有明确说明您是否有条件声明主 ID 与别名 ID。例如，此解决方案将允许 1 的别名是 4，而 4 的别名是 1，这与简化示例问题中提供的数据一致。

为了设置这个例子的数据，我使用了这个结构:

CREATE TABLE notnormal_customers (
  id INT NOT NULL PRIMARY KEY,
  aliases VARCHAR(10)
);

INSERT INTO notnormal_customers (id,aliases)
VALUES
(1,'|4|58|76'),
(2,''),
(3,''),
(4,'|1|58|76'),
(58,'|1|4|76'),
(76,'|1|4|58');

首先，为了表示一个客户到多个别名的一对多关系，我创建了这个表:

CREATE TABLE customer_aliases (
    primary_id INT NOT NULL,
    alias_id INT NOT NULL,
    FOREIGN KEY (primary_id) REFERENCES notnormal_customers(id),
    FOREIGN KEY (alias_id)   REFERENCES notnormal_customers(id),
    /* clustered primary key prevents duplicates */
    PRIMARY KEY (primary_id,alias_id)
)

最重要的是，我们将使用 custom SPLIT_STR function :

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

然后我们将创建一个存储过程来完成所有工作。代码带有对源引用的注释。

DELIMITER $$
CREATE PROCEDURE normalize_customers()
BEGIN

  DECLARE cust_id INT DEFAULT 0;
  DECLARE al_id INT UNSIGNED DEFAULT 0;
  DECLARE alias_str VARCHAR(10) DEFAULT '';
  /* set the value of the string delimiter */
  DECLARE string_delim CHAR(1) DEFAULT '|';
  DECLARE count_aliases INT DEFAULT 0;
  DECLARE i INT DEFAULT 1;

  /*
    use cursor to iterate through all customer records
    http://burnignorance.com/mysql-tips/how-to-loop-through-a-result-set-in-mysql-strored-procedure/
  */
  DECLARE done INT DEFAULT 0;
  DECLARE cur CURSOR FOR
      SELECT `id`, `aliases`
      FROM `notnormal_customers`;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;
  read_loop: LOOP

    /*
      Fetch one record from CURSOR and set to customer id and alias string.
      If not found then `done` will be set to 1 by continue handler.
    */
    FETCH cur INTO cust_id, alias_str;
    IF done THEN
        /* If done set to 1 then exit the loop, else continue. */
        LEAVE read_loop;
    END IF;

    /* skip to next record if no aliases */
    IF alias_str = '' THEN
      ITERATE read_loop;
    END IF;

    /*
      get number of aliases
      https://pisceansheart.wordpress.com/2008/04/15/count-occurrence-of-character-in-a-string-using-mysql/
    */
    SET count_aliases = LENGTH(alias_str) - LENGTH(REPLACE(alias_str, string_delim, ''));

    /* strip off the first pipe to make it compatible with our SPLIT_STR function */
    SET alias_str = SUBSTR(alias_str, 2);

    /*
      iterate and get each alias from custom split string function
      https://stackoverflow.com/questions/18304857/split-delimited-string-value-into-rows
    */
    WHILE i <= count_aliases DO

      /* get the next alias id */
      SET al_id = CAST(SPLIT_STR(alias_str, string_delim, i) AS UNSIGNED);
      /* REPLACE existing values instead of insert to prevent errors on primary key */
      REPLACE INTO customer_aliases (primary_id,alias_id) VALUES (cust_id,al_id);
      SET i = i+1;

    END WHILE;
    SET i = 1;

  END LOOP;
  CLOSE cur;

END$$
DELIMITER ;

最后你可以通过调用简单地运行它:

CALL normalize_customers();

然后就可以在console中查看数据了:

mysql> select * from customer_aliases;
+------------+----------+
| primary_id | alias_id |
+------------+----------+
|          4 |        1 |
|         58 |        1 |
|         76 |        1 |
|          1 |        4 |
|         58 |        4 |
|         76 |        4 |
|          1 |       58 |
|          4 |       58 |
|         76 |       58 |
|          1 |       76 |
|          4 |       76 |
|         58 |       76 |
+------------+----------+
12 rows in set (0.00 sec)