mysql - MYSQL 中的 CONCAT 字符串和 INT 并将大 mysql 表导出到小 csv 文件

Question

我是 MYSQL 编程新手。我想使用 MYSQL 循环脚本将一个非常大的表选择到多个 csv 文件中。我的脚本如下:

BEGIN
        SET @t_lines=0;
        SET @t_count=0;
        SET @t_filepath='/home/ab/path/table_name_02212013_';
        WHILE t_lines<=5000000
        SET @t_filename=CONCAT(@t_filepath,CAST(@t_count as CHAR));
        select * into outfile @t_filename fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' from table_name limit @t_lines,@t_lines+300000;
        SET @t_lines= @t_lines+300000;
        SET @t_count= @t_count+1;
        END WHILE;
        COMMIT;
END

我在行中遇到语法错误:

SET @t_filename=CONCAT(@t_filepath,CAST(@t_count as CHAR));

WHILE ... DO 引起的语法错误。感谢您的快速回复。这里我在“limit”之后的“@t_lines,@t_lines+300000”处仍然有语法错误。我发现，似乎“限制”不允许“@t_lines+300000”给出行范围。它可以通过提供一个新变量来修复:

   BEGIN
            DECLARE t_lines INT DEFAULT 0;
            DECLARE t_count INT DEFAULT 0;
            DECLARE t_endlines INT DEFAULT 300000;
            DECLARE t_linerange INT DEFAULT 300000;
            SET @t_filepath='/home/ab/path/table_name_02212013_';
            WHILE t_lines<=5000000 DO
            SET @t_filename=CONCAT(@t_filepath,CAST(@t_count as CHAR));
            select * into outfile '@t_filename' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' from table_name limit t_lines,t_endlines;
            SET t_lines= t_lines+t_linerange;
            SET t_endlines= t_endlines+t_linerange
            SET t_count= t_count+1;
            END WHILE;
            COMMIT;
    END

非常感谢

Answer 1

syntax for a WHILE loop in MySQL需要一个DO；

WHILE t_lines<=5000000 DO
...
END WHILE;