mysql - 使用准备和执行

Question

我有一个包含培训记录的表。每条记录都有一个带有 Agency 值的字段。我还有另一张仅显示机构值(value)的表格。我想将每个机构的记录导出到 CSV 文件中。有超过 200 万条记录，因此我不想导出整个表并手动执行。

我创建了一个存储过程，它使用游标从 Agency_codes 表中提取一个值，并在 WHERE 子句和部分 INTO OUTFILE< 的 select 语句中使用该值/strong> 名称。

该过程有效，但仅适用于 Agency_codes 表中的前两个值。在第三个 (ACB) 值上，它给出错误“where 子句”中的未知列“ACB” 我很困惑为什么它与前两个值一起工作，然后在第三个值上停止。

程序如下:

DELIMITER $$
DROP PROCEDURE IF EXISTS  export_csv $$
CREATE PROCEDURE export_csv()
BEGIN
DECLARE agency_name VARCHAR(255);

DECLARE exit_loop BOOLEAN;         

DECLARE agency_cursor CURSOR FOR
SELECT agency FROM agency_codes;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;

OPEN agency_cursor;

agency_loop: LOOP
FETCH  agency_cursor INTO agency_name;

SET @sql_text = Concat("(select 'class_code','course_code','course_name','course_type','username','grade_type','score','letter_grade','is_passed','completion_date','completion_status','registration_date','registration_entry_status','registration_type','comment','first_name','last_name','class_name','agency') Union (select class_code,course_code,course_name,course_type,username,grade_type,score,letter_grade,is_passed,completion_date,completion_status,registration_date,registration_entry_status,registration_type,comment,first_name,last_name,class_name,agency from hrdis_oru where hrdis_oru.agency =", agency_name," into outfile 'C:/HDD/",agency_name,".csv' fields enclosed by '\"' terminated by ',' escaped by '\"' lines terminated by '\r\n')");

prepare s1 from @sql_text;
execute s1;
deallocate prepare s1; 


IF exit_loop THEN
     CLOSE agency_cursor;
     LEAVE agency_loop;
 END IF;
END LOOP agency_loop;
END $$
DELIMITER ;

我的 Agency 表中的前几个值是:

• 17
• 303
• ACB
• 精算师
• agr
• 目标

任何帮助都会很棒。谢谢。

Answer 1

如果您考虑每次迭代中 @sql_text 将保留哪些值，问题应该立即显而易见。为了清晰起见添加一些空格:

(
  select 'class_code','course_code','course_name','course_type','username',
         'grade_type','score','letter_grade','is_passed','completion_date',
         'completion_status','registration_date','registration_entry_status',
         'registration_type','comment','first_name','last_name','class_name',
         'agency'
) Union (
  select class_code,course_code,course_name,course_type,username,
         grade_type,score,letter_grade,is_passed,completion_date,
         completion_status,registration_date,registration_entry_status,
         registration_type,comment,first_name,last_name,class_name,
         agency
  from   hrdis_oru
  where  hrdis_oru.agency =ACB
  into   outfile 'C:/HDD/ACB.csv'
  fields enclosed   by '\"'
         terminated by ','
         escaped    by '\"'
  lines  terminated by '\r\n'
)

特别注意，其中 htdis_oru.agency =ACB。

由于 ACB 尚未被引用，MySQL 将其解析为模式对象标识符，并在找不到任何此类命名的对象时发出提示(纯数字机构名称并非如此，因为它们被解析为整数，随后得到 cast to strings during expression evaluation )。

为了使 MySQL 正确地将非数字值解析为字符串文字，必须将它们加引号:

... where hrdis_oru.agency ='", agency_name, "' ...
                            ^                 ^

当然，如果 agency_name 包含 ' 字符串引号字符，这会带来一个问题 - 任何此类出现当然必须是 escaped 。 MySQL 方便地提供了 QUOTE()函数正是为了这个目的:

... where hrdis_oru.agency =", QUOTE(agency_name), " ...

但是，为了防范可能的 SQL 注入(inject)攻击，您确实应该参数化您准备好的语句:

FETCH agency_cursor INTO @agency_name;

(您不再需要DECLARE Agency_name)；那么:

... where hrdis_oru.agency = ? into outfile CONCAT('C:/HDD/', ?, '.csv') ...

随后:

PREPARE s1 FROM @sql_text;
EXECUTE s1 USING @agency_name, @agency_name;
DEALLOCATE PREPARE s1;

请注意，您现在还可以在进入循环之前PREPARE语句，并在循环内简单地EXECUTE它(使用适当的值) -这应该会带来轻微的性能提升。请记住退出循环后DEALLOCATE。

最后一点:您应该在 FETCH 命令之后立即检查 exit_loop，否则您最终将尝试执行 当没有更多机构时，最后一次选择 ... INTO OUTFILE 语句。

<小时/>

还值得注意的是，在这种情况下，您根本不需要使用准备好的语句。您可以简单地执行以下操作:

CREATE PROCEDURE export_csv() BEGIN
  DECLARE agency_name VARCHAR(255);
  DECLARE agency_cursor CURSOR FOR SELECT agency FROM agency_codes;
  DECLARE EXIT HANDLER FOR NOT FOUND CLOSE agency_cursor;

  OPEN agency_cursor;
  LOOP
    FETCH agency_cursor INTO agency_name;

      SELECT 'class_code','course_code','course_name','course_type','username',
             'grade_type','score','letter_grade','is_passed','completion_date',
             'completion_status','registration_date','registration_entry_status',
             'registration_type','comment','first_name','last_name','class_name'

    UNION ALL

      SELECT class_code,course_code,course_name,course_type,username,
             grade_type,score,letter_grade,is_passed,completion_date,
             completion_status,registration_date,registration_entry_status,
             registration_type,comment,first_name,last_name,class_name
      FROM   hrdis_oru
      WHERE  agency = agency_name

    INTO   OUTFILE CONCAT('C:/HDD/', agency_name, '.csv')
    FIELDS ENCLOSED   BY '"'
           TERMINATED BY ','
           ESCAPED    BY '"'
    LINES  TERMINATED BY '\r\n';

  END LOOP;
END