Как распарсить данные триггером перед вставкой mysql?

Question

[sbh]

Добрый день.
В запросах приходят данные вида:

07-Mar-2020 18:11:34.456 queries: info: client 55.44.33.22#49230 (111.222.333.444.in-addr.arpa): view All_View: query: 111.111.111.111.in-addr.arpa IN PTR + (22.22.22.22)

Необходимо распарсить данные и зыписывать в таблицу logs соответственно данные
07-Mar-2020 18:11:34.456
55.44.33.22
111.111.111.111.in-addr.arpa

в поля:
date
client
request

Было бы оптимально это делать с помощью триггера MySQL Before Insert

Answer 1

[Valentyn]

SUBSTRING_INDEX(string, delimiter, number) (https://www.w3schools.com/sql/func_mysql_substring...

SELECT
	SUBSTRING_INDEX("07-Mar-2020 18:11:34.456 queries: info: client 55.44.33.22#49230 (111.222.333.444.in-addr.arpa): view All_View: query: 111.111.111.111.in-addr.arpa IN PTR + (22.22.22.22)", " queries:", 1) as `date`,
	SUBSTRING_INDEX(
		SUBSTRING_INDEX("07-Mar-2020 18:11:34.456 queries: info: client 55.44.33.22#49230 (111.222.333.444.in-addr.arpa): view All_View: query: 111.111.111.111.in-addr.arpa IN PTR + (22.22.22.22)", "#", 1),
		" ",
		-1
	) as `client`,
	SUBSTRING_INDEX(
		SUBSTRING_INDEX(
			"07-Mar-2020 18:11:34.456 queries: info: client 55.44.33.22#49230 (111.222.333.444.in-addr.arpa): view All_View: query: 111.111.111.111.in-addr.arpa IN PTR + (22.22.22.22)",
			"query: ",
			-1
		),
		" IN PTR",
		1
	) as `request`

Результат:

Comments

[sbh]

непонятно только как это использовать в тригере

[sbh]

Valentyn, запросы в БД приходят с syslog-а сразу.
Данные нужно:
1) распарсить и записать
2) обеспечить ротацию данных при достижении определенного количества строк БД, т.к. место на диске не резиновое.

По парсингу вот нашел вроде то что нужно - https://www.syslog-ng.com/community/b/blog/posts/p...

По ротации вопрос остается открытым. Пока на уме либо хранимая процедура и тригер который ее вызывает при записи новых данных, либо просто тригер который сам чистит старое перед записью нового.

[Valentyn]

sbh, какая структура таблицу в которой syslog делает записи?

В теле триггера будет что-то вроде:

DECLARE date tinytext;
DECLARE client tinytext;
DECLARE request tinytext;
DECLARE query text;

SELECT
  SUBSTRING_INDEX(NEW.syslog_text, " queries:", 1) as `date`,
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(NEW.syslog_text, "#", 1),
    " ",
    -1
  ) as `client`,
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(
      NEW.syslog_text,
      "query: ",
      -1
    ),
    " IN PTR",
    1
  ) as `request`
INTO date, client, request;

SET query = "INSERT INTO `logs` (`date`, `client`, `request`) VALUES(?, ?, ?)";

PREPARE stmt FROM query;
EXECUTE stmt USING date, client, request;
DEALLOCATE stmt;

syslog_text - строка, в которой хранится информация.
P.S. Работоспособность не проверял - пробуйте.

[sbh]

Valentyn, структура таблицы

seq - bigint (id-шник, первичный ключ таблицы)
message - нераспарсенные данные (пока так)
date - timestamp

а планируется что будет:
id - bigint (id-шник, первичный ключ таблицы)
date
client
request

[sbh]

Valentyn, в phpmyadmin когда ваш код вставляешь ругается на некорректный код возле DECLARE

[Valentyn]

sbh, ротация совсем другая тема.

Как по мне самый понятный вариант это при достижении определенного кол-ва записей копировать текущие записи в "хранилище", таблицу чистить и записывать строку в пустую таблицу.

Все зависит от того насколько это ценные данные и каков их объем.

[sbh]

Valentyn, а без промежуточной таблицы нельзя? Судя по тем форумам которые я уже прочел - невозможно менять строки старые в той же таблице куда пишется новая строка. Следовательно невозможно тригером реализовать ротацию данных. Может подскажите как еще можно? (по крону не пойдет)

[Valentyn]

sbh, крон вообще никакой не хотите использовать? В MySQL есть Event scheduler (https://dev.mysql.com/doc/refman/5.7/en/event-sche... работает аналогично крону.

Если сервис пишет логи и нету возможности добавить ротацию в код сервиса, то простой очевидный вариант это крон.

[sbh]

Valentyn, Dynamic SQL is not allowed in stored function or trigger при создании триггера

Answer 2

[d-stream]

Вот именно такое в триггер лучше не пихать.... во избежание пулевых отверстий в коленях и дыр в подошвах от граблей...

Comments

[sbh]

рекомендации как такой парсинг делать будут?

[d-stream]

Регулярными выражениями в ленивом варианте. Если в ключе стрельбы по ногам, то вероятно - https://dev.mysql.com/doc/refman/8.0/en/regexp.html

В более-менее феншуйном варианте:
https://www.php.net/manual/ru/function.preg-match.php
www.cplusplus.com/reference/regex
https://docs.microsoft.com/ru-ru/dotnet/standard/b...
https://perldoc.perl.org/perlre.html

[sbh]

d-stream, данные поступают с syslog-а напрямую в mysql