MySQL存储过程 - 插入多行

Question

我的应用程序通过存储过程将每个HTTP请求的详细信息记录在多个MySQL表中，并返回唯一的请求ID给应用程序。

CALL http_req('ip', 'url', 'method', 'timestamp', @error, @request_id);

现在我还想将所有的HTTP请求头记录在一个表中，每个头都在单独的一行中：

CREATE TABLE `http_header` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`request_id` INT(10) UNSIGNED NOT NULL,
`name` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
`value` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
PRIMARY KEY (`id`))

问题在于每个客户端的标头数量和类型都不同。我还没有找到一种方法将所有标头详细信息传递给我的存储过程，然后将它们插入上述表格中。

目前，我必须在从应用程序调用存储过程后生成并执行第二个插入查询来保存标题：

INSERT INTO http_header (request_id, name, value)
    VALUES (20153, 'cache-control', 'max-age=0'),
           (20153, 'accept', 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8'),
           (20153, 'accept-encoding', 'gzip,deflate,sdch');

能否在存储过程内保存第二个查询并插入标头？比如将所有标头作为单个字符串传递，并在存储过程内解析它？

- 1nsane

Answer 1

是的，这是可能的。MySQL支持足够的流程控制结构（REPEAT，IF）和字符串处理（LOCATE()，SUBSTRING()），以允许在数据库中拆分标题字符串。以下是一个非常简单的示例：

CREATE PROCEDURE http_req(IN ip CHAR(12), IN url VARCHAR(512), IN method CHAR(8), IN ts DATETIME, IN headers TEXT, OUT err INT, OUT request_id INT)
BEGIN
 DECLARE loc INT;
 DECLARE hloc INT;
 DECLARE hdr TEXT DEFAULT NULL;
 DECLARE hval TEXT DEFAULT NULL;
 DECLARE s TEXT DEFAULT NULL;

 START TRANSACTION;

 INSERT INTO http_requests VALUES (NULL,INET_ATON(ip), url, method, ts);
 SELECT last_insert_id() INTO request_id;

 REPEAT 
  SET loc=LOCATE("\n",headers);

  IF (loc = 0) THEN
   SET s=headers;
  ELSE
   SET s=SUBSTRING(headers,1,loc-1);
   SET headers=SUBSTRING(headers,loc+1);
  END IF;

  SET hloc=LOCATE(':',s);

  IF (hloc = 0) THEN
   SET hdr=s;
   SET hval='';
  ELSE
   SET hdr=SUBSTRING(s,1,hloc-1);
   SET hval=SUBSTRING(s,hloc+1);
  END IF;

  INSERT INTO http_header VALUES (null,request_id,hdr,hval);

 UNTIL (loc=0) END REPEAT;

 COMMIT;

END

这段代码存在一些明显的问题，例如如果标题包含换行符（\n），则它们将被存储不正确。此外，没有错误管理（例如err返回值未正确填充；没有回滚）。修复这些问题留给读者作为练习 ;)

- vhu