将mysql值拆分为未知数量的部分 [英] Splitting mysql value into unknown number of parts

问题描述

已为我提供了一个mySQL数据库，以将其重组为OpenCart安装.

I have been given a mySQL database to restructure into an OpenCart installation.

我收集了大部分数据，但在旧站点中，产品类别"已全部放在一个列中

I've pulled most of the data across but in the old site "product categories" have been all put in a single column

|228|243|228|239|228| 或者 |88| 或者 |88|243|

所以我不知道任何特定记录中会有多少.

So I have no idea how many would be in any particular record.

我不想使用php函数来提取数据，因为我正在使用SQL查询将数据手动提取到新数据库中.

I don't want to use a php function to extract this, as I am manually extracting the data with SQL queries into the new database.

更为复杂的是，我必须在products_to_categories中为该列中的每个值创建新行-我不在乎多步骤过程，我不希望通过单个查询来完成此操作-我希望避免重新输入所有数据.

An added complication is I have to create a new line in the products_to_categories for each value in the column - I don't mind a multiple step process, I'm not expecting to do this with a single query - I am looking to avoid re-entering all the data.

我知道这类似于 MySQL拆分字符串但我不认为这是重复项，因为它不能完全回答我的问题，因为列中可能有任意数量的值-不只是2.

I know this is similar to MySQL Split String but I don't feel it's a duplicate as that does not answer my question fully as there may be any number of values in the column - not just 2.

我尝试了common_schema，但是以目前的技能水平，我发现很难获得想要的结果，但是将来我肯定会使用它.记录下来，这是我最接近的解决方案-可以您在MySQL查询中拆分/分解一个字段?

I tried common_schema, but at my current level of skill I found it difficult to get the result I was seeking, but I will certainly use it in future. For the record this is closest to my my solution - Can you split/explode a field in a MySQL query?

推荐答案

我建议的一种选择是使用 和 split_token() ，这会有所帮助.

One option that I recommend is to use common_schema and specifically functions get_num_tokens() and split_token(), this will help.

以下是您可以适应您的解决方案的简单使用示例:

Here a simple example of the use that you can adapt for your solution:

/* CODE FOR DEMONSTRATION PURPOSES */

/* Need to install common_schema - code.google.com/p/common-schema/ */ 

/* Procedure structure for procedure `explode1` */     

/*!50003 DROP PROCEDURE IF EXISTS  `explode1` */;

DELIMITER $$

CREATE PROCEDURE `explode1`(str varchar(65500), delim VARCHAR(255))
BEGIN
    DECLARE _iteration, _num_tokens INT UNSIGNED DEFAULT 0;
    DROP TEMPORARY TABLE IF EXISTS `temp_explode`;
    CREATE TEMPORARY TABLE `temp_explode` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `word` VARCHAR(200), PRIMARY KEY (`id`));
    SET _num_tokens := (SELECT `common_schema`.`get_num_tokens`(str, delim));
    WHILE _iteration < _num_tokens DO
        SET _iteration := _iteration + 1;
        INSERT INTO `temp_explode` (`word`) SELECT `common_schema`.`split_token`(str, delim, _iteration);
    END WHILE;
    SELECT `id`, `word` FROM `temp_explode`;
    DROP TEMPORARY TABLE IF EXISTS `temp_explode`;
END $$

DELIMITER ;

/* TEST */
CALL `explode1`('Lorem Ipsum is simply dummy text of the printing and typesetting', CHAR(32));

这篇关于将mysql值拆分为未知数量的部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！