MYSQL: простая процедура и настройка выполнения по расписанию.

Стала простая задача.
Есть временная таблица в схеме, которую наполняют каждый день коллеги выборкой из одной из систем, таблица содержит данные "на сегодня".
Я же захотел хранить все слепки данных, для отслеживания изменений в долгосрочном периоде.
Вначале, хотел использовать скрипты на Perl, во - первых, мне так удобно, во вторых мне так удобно, я так привык.

Зная, что сам сервер MYSQL достаточно продвинутый, решил реализовать задачу посредством только сервера.

И так, что имеем:
Некую схему access_rqst_list
в ней таблицу  table_tmp
Заполнять необходимо только значимыми полями таблицу  table_main
Процесс копирования решил с помощью процедуры:

CREATE  PROCEDURE `copy_table_rqst`()
BEGIN
CREATE TABLE IF NOT EXISTS table_main LIKE table_tmp;
INSERT table_main(DateCreate,Status,Customer,RequestType,DocNumber,DateExecuted,AccessType,DateAccessClose,Reason,IRCategory,IRName,IRType,AccessParameters) SELECT DateCreate,Status,Customer,RequestType,DocNumber,DateExecuted,AccessType,DateAccessClose,Reason,IRCategory,IRName,IRType,AccessParameters FROM table_tmp where tstamp BETWEEN TIMESTAMP(CURDATE()) AND TIMESTAMP(CURDATE()+1);
END


Данная процедура, на всякий случай, если таблица  table_main удалена создает ее из структуры  table_tmp, далее копирует необходимые мне поля из  table_tmp в table_main за сегодня tstamp BETWEEN TIMESTAMP(CURDATE()) AND TIMESTAMP(CURDATE()+1);
где tstamp - поле в table_tmp типа TIMESTAMP.

В итоге, в интерфейсе mysql клиента можно вызвать процедуру:

call access_rqst_list.copy_table_rqst();

Но, это еще не все, необходимо. чтобы копирование происходил автоматически.
Для запуска чего, в серевере MYSQL есть понятие EVENT

Для начала включаем поддержку из-под root пользователя MYSQL сервера:

SET GLOBAL event_scheduler = ON;
если понадобиться отключить, просто даем установку - SET GLOBAL event_scheduler = OFF;

смотрим
SHOW PROCESSLIST;

 293 | event_scheduler | localhost                        | NULL             | Daemon  | 1473 | Waiting for next activation | NULL

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

Создаем событие с именем copy_table_acc

CREATE EVENT copy_table_acc ON SCHEDULE EVERY '1' DAY STARTS '2019-04-20 09:00:00' DO call access_rqst_list.copy_table_rqst();

Для проверки, можем посмотреть коммандой
SHOW EVENTS;


+------------------+----------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db               | Name           | Definer        | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+------------------+----------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| access_rqst_list | copy_table_acc | root@localhost | SYSTEM    | RECURRING | NULL       | 1              | DAY            | 2019-04-20 09:00:00 | NULL | ENABLED |          0 | utf8                 | utf8_unicode_ci      | utf8_general_ci    |
+------------------+----------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+

Если необходимо удалить событие, даем команду:
DROP EVENT copy_table_acc;

Краткая шпаргалка по запросам в MySQL

Приведу в качестве шпаргалки несколько запросов, которые я часто использую, возможно пригодиться еще кому-то.

Задача 1.
Входные данные:
таблица:

mysql> describe invent_2011_invent;
+-----------+-----------+------+-----+-------------------+-----------------------------+
| Field     | Type      | Null | Key | Default           | Extra                       |
+-----------+-----------+------+-----+-------------------+-----------------------------+
| id        | int(12)   | NO   | PRI | NULL              | auto_increment              |
| sn        | char(255) | NO   |     | NULL              |                             |
| hw_name   | char(255) | NO   |     | NULL              |                             |
| hw_type   | char(255) | NO   |     | NULL              |                             |
| place     | char(255) | NO   |     | NULL              |                             |
| user      | char(255) | NO   |     | NULL              |                             |
| city      | char(255) | NO   |     | NULL              |                             |
| address   | char(255) | NO   |     | NULL              |                             |
| territory | char(255) | NO   |     | NULL              |                             |
| remarks   | char(255) | NO   |     | NULL              |                             |
| tstamp    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-----------+-----------+------+-----+-------------------+-----------------------------+

Задача:
вывести кличество однотипного оборудования, которое встречается больше 1 раза, тип указан в поле hw_type

SELECT hw_type, count(*) AS count_hw FROM invent_2011_invent GROUP BY hw_type HAVING count_hw>1;

результат будет примерно таким:

+-------------+----------+
| hw_type     | count_hw |
+-------------+----------+
| ADAPTER     |        4 |
| BATTERY     |        3 |
| DSTAT       |       15 |
| EXTDROM     |       33 |
| EXTHDD      |       41 |
| INTDROM     |       19 |
| INTHDD      |       15 |
| MFU         |       26 |
| MMODEM      |       17 |
| MODEM       |        6 |
| PPC         |       40 |
| PRINTER     |       32 |
| PRINTSERVER |       95 |
| RAM         |       22 |
| REPLICATOR  |       11 |
| ROUTER      |        7 |
| RSERVER     |       11 |
| SCANNER     |       16 |
| UPS         |       12 |
| USBFLASH    |        5 |
+-------------+----------+

Задача 2.
Входные данные:
таблица:

mysql> describe invent_2011_codes;
+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| ID      | int(11) | NO   | PRI | NULL    | auto_increment |
| CODES   | text    | NO   |     | NULL    |                |
| HW_TYPE | text    | NO   |     | NULL    |                |
+---------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

текущая локаль в кодировке UTF-8
Настройки таблици:
- Character Set UTF-8 Unicode
- Collation utf8_general_ci

Задача: импортировать данные в поля CODES и HW_TYPE из текстового файла (/home/emutant/Documents/invent_2011/Rez/invent_2011_codes.txt)
в кодировке UTF-8, но содержащего кирилицу.
В файле значения разделены "," в качестве закрывающих эллементов используются "".

LOAD DATA LOCAL INFILE '/home/emutant/Documents/invent_2011/Rez/invent_2011_codes.txt' INTO TABLE invent_2011_codes CHARACTER SET LATIN1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (CODES,HW_TYPE);

Задача 3.
Входные данные:
таблица из Задачи 2.

Задача:
- найти в поле CODES символы "S/N: " и вырезать их

UPDATE invent_2011_codes CODES = REPLACE(CODES, "S/N: ", "");



Задача 4.
Входные данные:
таблицы из Задачи 1 и 2.

Задача: Вывести все поля и их значение в таблице invent_2011_codes, для которых значение invent_2011_codes.CODES равны invent_2011_invent.sn в файл
(/tmp/EQ_codes.txt)

В файле значения разделены "," в качестве закрывающих эллементов используются "".

SELECT * FROM invent_2011_codes WHERE CODES IN (SELECT sn FROM invent_2011_invent) INTO OUTFILE '/tmp/EQ_codes.txt' CHARACTER SET LATIN1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';


Задача 5.
Входные данные:
- таблица invent_2011_undef, в которой есть поля SEARCH_REZ и ID_SAP;
- таблица ID_SAP_DEF, в которой есть одноименное поле ID_SAP_DEF;

Задача: Проставить метку "ОК" для поля invent_2011_undef.SEARCH_REZ, для записей в которых поле invent_2011_undef.ID_SAP совпадает с ID_SAP_DEF.ID_SAP_DEF.

UPDATE invent_2011_undef SET SEARCH_REZ="OK" WHERE invent_2011_undef.ID_SAP IN (SELECT ID_SAP_DEF FROM ID_SAP_DEF);

mySQL + UTF8 проблема с поиском данных с кириллицей при использовании фунций ( lower/upper )

Исходные данные: Ubuntu server 9.04 + MySQL 5.1.x, была какая-то база MBASE с таблицей abase, в которую вносились данные в кодировке utf8, все запросы происходили посредством самописных Perl скриптов.

Так сложилось, что появилась необходимость использовать регистронезависимый поиск.
Самым простым способом реализации регистронезависимого поиска, является преобразование строки поиска и значения поля(ей) таблиц к верхнему(upper) или нижнему(lower) регистру.

Пример банального SELECT-а ввыглядит бля таблици abase так:

select * form abase where locate(lower('ЯбЛо'),lower(message0))>0;

т.е. строка поиска "ЯбЛо" и значение в поле message0 таблицы abase преобразовывается
к нижнему регистру и ищется вхождение.

К сожалению поиск не работал. Однако, не работал для кирилических символов, т.е. регистрозависимый в стиле:

select * form abase where messages0 LIKE 'Ябло%';

отрабатывал, но как только использовалось преобразование регистров, переставал работать.

Залогинившись mysql -uroot -ppass побросал запросы напрямую - увидел, что после использования функций преобразования регитров символов, отдает какю-то ересь из квадратиков.

Погуглив, нашел подсказку в указании кодировки по-умолчание в файле конфигурации MySQL:


[mysqld]
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci
init-connect="SET NAMES utf8"
skip-character-set-client-handshake

[mysqldump]
default-character-set=utf8

[client]
default-character-set = utf8


Вписав эти инструкции и перегрузив сервер MySQL сервер, получил еще ведро ошибок в стиле:
'Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)'


Вобщем, плохо все, нашел совет перекодировать:

alter table `abase` convert to character set utf8
collate utf8_swedish_ci;

Однако, то ли я не разобрался, то ли еще почему-то, но не получилось.

На самом деле, нужно просто правильно создавать базы вначале с указанием правильных кодировок:

CREATE DATABASE vizor CHARACTER SET utf8 COLLATE utf8_general_ci;

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