mysql:如何输出csv -限制x行数？

Question

我有一些类似的疑问

(SELECT 'title', 'created_at', 'modified_at')
UNION
(select title, created_at, modified_at from table1 order by created_at
INTO OUTFILE 'result.csv'
CHARACTER SET utf8
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n');

结果超过10000行。

有办法把这个文件分割成多个文件吗？我想限制行数。

我知道我可以这样做：

select * from table1 limit 0, 1000 into outfile result1.csv
select * from table1 limit 1000, 1000 into outfile result2.csv
select * from table1 limit 2000, 1000 into outfile result3.csv
...

由于有超过10000行，我想知道是否可以使用某种循环。

任何建议和帮助都将不胜感激！

：：编辑：：

我编写了重复过程，但它给了我一个语法错误。

DELIMITER ;;
CREATE PROCEDURE `reapeatloop`()
BEGIN
declare x int;
declare filename varchar(255);
declare lowlimit int;
SET x = 0;
REPEAT
  set lowlimit = x*1000;
  (select * from table1 limit lowlimit, 1000
   INTO OUTFILE concat('post',x,'.csv')
   CHARACTER SET utf8
   FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"' LINES 
   TERMINATED BY '\r\n');
   SET x = x + 1;
UNTIL x > 19 END REPEAT;
END;;
DELIMITER ;

在这个查询中，我使用局部变量来定义csv和限值，这会导致语法错误。有人能看出这个查询有什么问题吗？一旦我将这个变量更改为某个数字或字符串，它就会成功地创建过程。

Answer 1

你在手术过程中有一些错误。特别是检查选择进入OUTFILE。小心FROM的位置。在您的例子中，请尝试如下：

DROP PROCEDURE reapeatloop;

DELIMITER $$
CREATE PROCEDURE `reapeatloop`()
BEGIN
declare x int;
declare filename varchar(255);
declare lowlimit int;
SET x = 0;
REPEAT
  set lowlimit = x*1000;  
  SET @fullOutputPath := CONCAT('"D:/post',x,'.csv"');
  set @q1 := concat("select * INTO OUTFILE ", @fullOutputPath, 
    " CHARACTER SET utf8 FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\"' LINES TERMINATED BY '\r\n'
   from table1 limit ", lowlimit, ",1000");
    
    select @q1;
    
    prepare s1 from @q1;
    execute s1;deallocate prepare s1;
    
    SET x = x + 1;
    
UNTIL x > 19 END REPEAT;
END$$

DELIMITER ;