Geek asks and answers

MySQL stored procedure - insert multiple rows

My application registers the data of each HTTP request in several MySQL tables through a stored procedure that returns a unique request identifier to the application.

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

Now I also want to write all the headers of the HTTP requests to the table, each header on a separate line:

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`))

The problem is that each client has different numbers and types of headers. I did not find a way to transfer all the header data to my stored procedure, and then paste them into the above table.

Currently, I need to generate and execute a second insert request from my application after calling a stored procedure to save the headers:

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');

? ?

+3
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 , ). ​​ ;)

0

More articles:


All Articles