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

您的问题中不明确的一个警告是,您是否有条件声明主要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,pos -1)) + 1),'');

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

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
    
  

  
 
 
   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
      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,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();

然后你可以在控制台中检查数据：

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)