Mysql循环选择列并在循环中执行Insert查询

I'm trying to loop over selected slugs and execute little complicated INSERT INTO SELECT query. slugs[iteration] usage is not a correct mysql syntax. But I have to access fetched slugs one by one inside the query. How Could I achieve that ?

我试图遍历选定的slugs并执行一些复杂的INSERT INTO SELECT查询。 slugs [iteration]用法不是正确的mysql语法。但我必须在查询中逐个访问获取的slu。我怎么能实现这一目标?

DELIMITER $$
CREATE PROCEDURE create_sitemap_from_slugs()
BEGIN
    SELECT `slug`  INTO slugs FROM slug_table;
    SELECT COUNT(*)  INTO count FROM slug_table;

    SET iteration = 0;

    START TRANSACTION;

        WHILE iteration < count DO

            INSERT INTO line_combinations
                SELECT REPLACE(`line`, '{a}', slugs[iteration]) AS `line`
                FROM line_combinations
                WHERE `line` LIKE CONCAT('%/', '{a}', '%');

            SET iteration = iteration + 1;

        END WHILE;

    COMMIT;


END
$$
DELIMITER ;

Btw, I don't want to use any external programming language to make this, this procedure will be working for billions of rows. I read Loops in SQL is not a good way due to performance concerns.

顺便说一句,我不想使用任何外部编程语言来实现这一点,这个程序将用于数十亿行。由于性能问题,我读SQL中的循环并不是一个好方法。

If you suggest another way I would accept this also.

如果你建议另一种方式我也会接受这个。

I asked another detailed question but couldn't get an answer. if you would like to check that also : https://stackoverflow.com/questions/35320494/fetch-placeholders-from-table-and-place-into-generated-line-combination-pattern

我问了另一个详细的问题,但无法得到答案。如果你想检查一下:https://stackoverflow.com/questions/35320494/fetch-placeholders-from-table-and-place-into-generated-line-combination-pattern

1 个解决方案

#1

So for each line with {a} you need to insert COUNT(*) from slug_table times values filled with slug value.

因此对于具有{a}的每一行,您需要从slug_table中插入填充了slug值的COUNT(*)。

It seems you can do that just in one INSERT from SELECT

看来你只能在SELECT的一个INSERT中做到这一点

INSERT INTO line_combinations
            (SELECT REPLACE(lc.line, '{a}', st.slug) AS `line`
            FROM line_combinations lc, slug_table st
            WHERE lc.line LIKE CONCAT('%/', '{a}', '%');

UPDATE:

You can create a temp table line_combinations2 and insert all the records

您可以创建临时表line_combinations2并插入所有记录

FROM line_combinations 
WHERE line LIKE CONCAT('%/', '{a}', '%')

into the temp table. Then just use the temp table in the INSERT instead of original one

进入临时表。然后只使用INSERT中的临时表而不是原始表

1 个解决方案

#1

更多相关文章

随机推荐