Имя динамического столбца между несколькими таблицами import mySQL
Я импортировал таблицу с именем book1 с именами столбцов: ID, 2018M01, 2018M02, 2018M03
Теперь мне нужно импортировать вторую таблицу с именем book2 с именами столбцов: ID, 2018M05, 2018M06, 2018M07
Между названиями столбцов из двух таблиц есть что-то похожее, то есть год префикса 2018.
Если возможно, я предпочитаю не писать эту часть кода для каждого имени столбца для каждой таблицы, как показано ниже.
CREATE TABLE book1 (
ID VARCHARACTER(10),
2018M01 decimal(4,2),
2018M02 decimal(4,2),
2018M03 decimal(4,2)
);
поскольку на самом деле в реальных таблицах есть несколько столбцов.
В приведенном ниже коде рассматривается первая таблица с некоторым кодом динамического раздела, однако мне нужна помощь для второй таблицы импорта.
DROP TABLE IF EXISTS book1;
CREATE TABLE book1 (
ID VARCHARACTER(10),
2018M01 decimal(4,2),
2018M02 decimal(4,2),
2018M03 decimal(4,2)
);
LOAD DATA LOCAL INFILE '/Users/blytonpereira/Desktop/Book1.csv' REPLACE INTO TABLE book1
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;
SELECT
GROUP_CONCAT(
CONCAT(
'SELECT id, ''', COLUMN_NAME, ''' as month, ', COLUMN_NAME, ' as QTY FROM t1 ') SEPARATOR ' UNION ALL ')
FROM
`INFORMATION_SCHEMA`.`COLUMNS`
WHERE
`COLUMN_NAME` LIKE '2018%'
INTO @sql;
SET @query = CONCAT('select id, month, QTY from (' , @sql , ') x order by id;');
SELECT @query;
PREPARE stmt FROM @query;
EXECUTE stmt;
Я бы хотел избежать явного определения имени столбца для каждого импорта. Каждый импорт, который я выполняю, похож, но немного отличается, то есть имена столбцов обновляются каждый раз, например. в первой таблице это 2018M01..2018M02 ... 2018M03 .... во второй таблице это 2018M02..2018M03..2018M04. Мне нужна помощь в написании этой второй части кода, где происходит импорт второго файла.
Какая помощь вам нужна? С какой проблемой вы столкнулись с этим кодом?
@DanFarrell для ВТОРОЙ таблицы импорта из локального файла .csv, при импорте я могу использовать код LOAD DATA LOCAL INFILE, однако тогда мне нужно заранее создать таблицу в SQL (т.е. имена столбцов и т. д.). Я хочу, чтобы это было динамически, т.е. каждый раз, когда я создаю новый импорт, я не хочу определять имена столбцов в SQL, поскольку есть несколько столбцов, поэтому мне нужна помощь в написании этой части кода, продолжая то, что я уже написал
@ChetanRanpariya, пожалуйста, см. Изображение ibb.co/TbDwNmp Я бы хотел избежать явного определения имени столбца для каждого импорта. Каждый импорт, который я выполняю, похож, но немного отличается, то есть имена столбцов обновляются каждый раз, например. в первой таблице это 2018M01..2018M02 ... 2018M03 .... во второй таблице это 2018M02..2018M03..2018M04. Мне нужна помощь в написании этой второй части кода, где происходит импорт второго файла. Сходство в том, что год похож на 2018 год для имени столбца.
Вы знаете, что MySQL имеет жесткое ограничение для столбцов 4096 в качестве ограничения, но innoDB имеет ограничение в 1000? Кроме того, увеличение имен столбцов - отличный кандидат на нормализацию.
Ответы 1
Это просто предложение, очевидно, без тестирования и с некоторыми внутренними предположениями.
Похоже, вы можете загрузить каждый файл в ОДНУ таблицу и, следовательно, избежать динамических имен столбцов. то есть вы игнорируете верхнюю строку, в которой, как я полагаю, есть имена столбцов
LOAD DATA LOCAL INFILE '/Users/blytonpereira/Desktop/Book1.csv' REPLACE INTO TABLE book1
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;
Итак, «постановку» можно определить так:
CREATE TABLE book_staging (
ID VARCHARACTER(10),
MNTH_A decimal(4,2),
MNTH_B decimal(4,2),
MNTH_C decimal(4,2)
);
И вы можете настроить фиксированный запрос с параметрами, например
set @mnth_a = '2018M04';
set @mnth_b = '2018M05';
set @mnth_c = '2018M06';
select id, month, QTY
from (
SELECT id, @mthn_a as month, MNTH_A as QTY FROM book_staging WHERE MNTH_A IS NOT NULL
UNION ALL
SELECT id, @mnth_b as month, MNTH_B as QTY FROM book_staging WHERE MNTH_B IS NOT NULL
UNION ALL
SELECT id, @mnth_b as month, MNTH_C as QTY FROM book_staging WHERE MNTH_C IS NOT NULL
) x
order by id, month
;
В конце каждого цикла загрузки файла промежуточная таблица может быть усечена или удалена (в зависимости от предпочтительного подхода), чтобы вы были готовы к следующему запуску.
Как наблюдение, имена столбцов, которые также являются именами функций или зарезервированными словами, болезненно использовать. Лично я бы не стал использовать имя столбца month. Точно так же я бы предпочел использовать настоящую дату, например, 2018-01-01, чем строку типа 2018M01, но могут быть и другие причины для использования этих строк, о которых я не знаю.
@usedbyalready. когда я загружаю следующий файл, предполагая, что его имя - Book2.csv .... Я могу просто пропустить часть СОЗДАТЬ ТАБЛИЦУ .... Я не совсем понимаю, что подразумевается под промежуточной частью, которую можно отбросить? Означает ли это, что нам нужна промежуточная часть только один раз, когда мы загружаем первый файл, и больше не нужно?
Основная цель моего предложения заключалась в том, чтобы избежать динамических столбцов. Промежуточная таблица может быть усечена или удалена, это зависит от вашего предпочтительного подхода.
Я не понимаю, в чем твоя проблема. Если то, что вы опубликовали, работает для первой таблицы, похоже, вы на правильном пути. Где ты застрял?