mysql之设置字段分隔符_php – MySQL – 如何规范化包含分隔符分隔ID的列

当我开始回答这个问题时,我认为这将是快速而简单的,因为我在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)

)

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

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)