Экспорт данных из QUIK по ODBC. Как налету преобразовывать VARCHAR-поля в правильные (DATE, DECIMAL, ENUM)?

Question

[Иван Мельников]

Экспортирую данные из QUIK 8 по ODBC. Проблема в том, что QUIK экспортирует поля как строки (кроме чисел, с ними всё ок), а мне нужно их складывать в компактную табличку с правильными компактными полями. Как это можно сделать наиболее правильным способом?
Пока есть идея лить данные в первичную таблицу с VARCHAR-полями, а на ней поставить триггер на каждый INSERT. При каждом INSERT-е в первичную таблицу триггер будет вызывать процедурку, которая будет преобразовывать поля в нужные и INSERT-ить уже в целевую компактную табличку.
Насколько такой подход грамотный?

Answer 1

[Rsa97]

Подход абсолютно неграмотный. MySQL при вставке в таблицу (как и при большинстве операций) автоматически приводит данные к нужному типу.
А поля DATA и ENUM вообще всегда передаются как строки, только DATA должно быть в правильном формате, 'YYYY-MM-DD'.

Comments

[Иван Мельников]

Спасибо за ответ!

А поля DATA и ENUM вообще всегда передаются как строки, только DATA должно быть в правильном формате, 'YYYY-MM-DD'.

Тогда возникает вопрос.
Дата в QUIK представима в виде 'DD.MM.YYYY' и при выгрузке в таблицу даты '28.09.2021'

CREATE TABLE `deals` (
   `number` BIGINT UNSIGNED NOT NULL,
   `date` DATE NOT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=cp1251

дата в итоге пишется как '2028.09.20'.
Как мне это победить?

И ещё вопрос. Допустим в выгружаемой таблице есть поле operation с двумя допустимыми значениями (''Купля, 'Продажа'). И я просто сделал таблицу-справочник, в главной таблице просто определил внешний ключ в поле operation на таблицу-справочник (связь через поле TINYINT). В этом случае MySQL сам определит значение внешнего ключа по переданному строковому значению?

[Rsa97]

Иван Мельников, Дату надо преобразовать в формат YYYY-MM-DD. Это можно сделать как на клиенте, так и в запросе через STR_TO_DATE.
Значение внешнего ключа MySQL сам не определит. Но если вы не планируете других операций, то можете использовать вместо этого ENUM('Купля', 'Продажа'). Ну или использовать при вставке (SELECT `id` FROM `operation_types` WHERE `name` = 'Купля').

[Иван Мельников]

Rsa97,

Ну или использовать при вставке (SELECT `id` FROM `operation_types` WHERE `name` = 'Купля').

Дело в том, что при настройке экспорта данных из QUIK я не имею возможности править SQL-запросы.

[Rsa97]

Иван Мельников, Если все запросы генерируются автоматически и нет возможности вмешаться в них, то я бы предложил использовать generated columns. Например для даты можно сделать так:

`quik_date` VARCHAR(10),
`date` DATE AS (STR_TO_DATE(`quik_date`, '%d.%m.%Y')) STORED

Ну а для операций использовать ENUM.

[Иван Мельников]

Rsa97, правильно ли я понял, что в данном случае невозможно налету лить данные в таблицу с внешними ключами?

[Rsa97]

Иван Мельников, Да. Это единственный случай, когда есть необходимость использовать триггер. Но и тут можно сделать две колонки в одной таблице - поле из quik и соответствующее поле с внешним ключом.
Опять же, если у вас только 'Купля' и 'Продажа', то поле ENUM с ними само может быть внешним ключом. И реально храниться в нём будут номера опций из ENUM, то есть 0 и 1.

Answer 2

[Akina]

В тексте запроса ЛЮБЫЕ данные представлены как их строковые представления. Да что там - сам SQL-запрос есть строковый литерал.

Поэтому следует озаботиться исключительно правильным представлением. Форматом. Если данные - число, то десятичным разделителем должна быть точка. Если это дата или строка - должны присутствовать обрамляющие кавычки, причём дата должна быть в формате, понятном текущей СУБД (предпочтительно YYYY-MM-DD - этот формат понимают все СУБД), а в строке должны быть экранированы все символы, являющиеся служебными. И т.п.

Если выгруженные данные не соответствуют этому формату, то в запросе на добавление можно использовать не напрямую значение (и потом маяться дурью в процедуре/триггере), а, используя встроенные функции, преобразовать данные к правильному типу. Например, если в поле надо вставить число, а в выгрузке использована запятая в качестве разделителя, это будет

INSERT ... VALUES ( ... , CAST(REPLACE(@value, ',', '.') AS DOUBLE), ...