Как разделить строку на несколько частей и поместить в несколько полей таблицы MySQL?
В настоящее время я получаю список каталогов с нескольких разных жестких дисков и сохраняю каждый из них в виде текстового файла. Затем я объединяю эти файлы в один текстовый файл большего размера, а затем импортирую текстовый файл в таблицу MySQL, чтобы я мог извлечь каждый компонент из строки (каждую строку текстового файла) в соответствующие поля моей таблицы. Каждая строка форматируется следующим образом:
Диск:\имя_папки\имя_файла.EXT размер_файла
пример:
C:\example\example.txt 123456
Я уже давно успешно делаю это в MS Access. Единственная проблема в Access, каждая команда должна быть по одной.
Я хочу использовать файл PHP, чтобы сделать весь длительный процесс более автоматизированным и менее подверженным ошибкам. Я новичок в программировании на PHP и во многом полагался на этот замечательный сайт в своем прогрессе, которого я достиг до сих пор.
Команды SQL, которые я использую в MS Access:
UPDATE DirList_FORMATED SET fileSizeRAW = Mid([pathRAW], InStrRev([pathRAW]," ")+1);
UPDATE DirList_FORMATED SET path = REPLACE(pathRAW, fileSizeRAW, '');
UPDATE DirList_FORMATED SET path = Trim(path);
UPDATE DirList_FORMATED SET fileExt = Mid([path], InStrRev([path],".")+1);
UPDATE DirList_FORMATED SET drive = Left(path, 1);
UPDATE DirList_FORMATED SET path = REPLACE(path, 'M:\', '') WHERE Left(path, 1) = 'M';
UPDATE DirList_FORMATED SET path = REPLACE(path, 'N:\', '') WHERE Left(path, 1) = 'N';
UPDATE DirList_FORMATED SET path = REPLACE(path, 'O:\', '') WHERE Left(path, 1) = 'O';
UPDATE DirList_FORMATED SET path = REPLACE(path, 'P:\', '') WHERE Left(path, 1) = 'P';
UPDATE DirList_FORMATED SET path = Left([path], InStrRev([path],".")-1);
UPDATE DirList_FORMATED SET folderName = Left([path], InStr([path],"\")-1);
UPDATE DirList_FORMATED SET fileName = Mid([path], InStrRev([path],"\")+1);
UPDATE DirList_FORMATED SET flag = 'y'
Where fileExt = 'srt'
or fileExt = 'db'
Delete from DirList_FORMATED
where flag = 'y';
UPDATE DirList_FORMATED SET fileSizeGB = (((fileSizeRAW /1024)/1024)/1024);
UPDATE DirList_FORMATED SET fileSizeGB = ROUND(fileSizeGB, 3);
- Импортируйте текстовый файл в таблицу с именем DirList_RAW (используя phpMyAdmin).
используя PHP-код, я успешно:
- Я могу подключиться к своей БД
- Очистите временную таблицу, которую я использую для форматирования строки под названием DirList_FORMATTED.
- Вставьте поле pathRAW из DirList_RAW в поле pathRAW в DirList_FORMATED.
Следующим шагом является получение компонентов строки pathRAW и сохранение их в соответствующем поле, но пока мне это не удалось. Мне нужно извлечь размер файла, имя файла, расширение (расширение файла), имя папки и диск (букву диска) из каждой строки и поместить их в соответствующие поля таблицы.
Любая помощь, которая поможет мне начать или указать правильное направление, будет очень признательна :)
мой php-код на данный момент:
<?php
// Include the database config file
include_once 'assets/includes/dbConfig.php';
$query = "DELETE FROM DirList_FORMATED";
$conn->query($query);
echo 'DirList_FORMATED emptied <br/>';
$query = "INSERT INTO DirList_FORMATED (pathRAW) SELECT pathRAW FROM DirList_RAW WHERE pathRAW is not null";
$conn->query($query);
echo 'DirList_RAW inserted <br/>';
$query = "UPDATE DirList_FORMATED SET fileSizeRAW = Mid(['pathRAW'], InStrRev(['pathRAW'], ' ' )+1);";
$conn->query($query);
echo 'fileSizeRAW created <br/>';
?>
последняя строка продолжает выдавать ошибку синтаксиса
Я попробовал несколько вариантов цитат, но все равно ничего не понравилось :(
В MySQL нет MID, вместо этого вы можете использовать SUBSTRING для начала.
Вы не ставите [] вокруг имен в MySQL, это только для SQL-Server или Access. MySQL использует обратные кавычки для кавычек идентификаторов. Также не должно быть кавычек внутри [], я не знаю, почему вы написали ['pathRAW'] вместо [pathRaw].
Крис Хаас... Извините за путаницу, я пытаюсь написать php-файл для выполнения этих шагов, чтобы выполнить то же самое, что и приведенные выше команды, которые я использовал из окна SQL в Access.
+1 к тому, что сказал Крис. Повторное обновление такой таблицы просто испортит ваш диск и заставит все работать как усыпленная черепаха.
Функции Mid и InStrRev специфичны для MS Access и не работают в MySQL. Вместо этого вы можете использовать строковые функции MySQL.
IT goldman Спасибо, поищу синтаксис и начну экспериментировать :)
Саммич.... Спасибо за ваш вклад.. У меня не возникло никаких проблем с жестким диском. Возможно, наличие SSD-накопителя имеет значение.
@Тим, если это так, то я не вижу никакого PHP-кода для манипуляций со строками, только код SQL, встроенный в PHP, поэтому я не знаю, с чего начать. Для ясности: я говорю об нулевой базе данных до тех пор, пока сами данные не станут идеальными.
Изучите Советы, как правильно задать вопрос на языке структурированных запросов (SQL). Предоставьте структуру таблицы (№5), образец текстового файла (3–5 строк), желаемое окончательное состояние данных (№3).
@Крис Хаас ... Большое спасибо за ответ. Я использовал дизайн запросов Access SQL для заполнения соответствующих полей в моей таблице в течение длительного времени, пока недавно не настроил сервер WAMP с намерением упростить этот процесс с помощью php для обработки нескольких запросов sql. Ваш комментарий заставил меня переосмыслить свой подход и понять, что мне следует использовать php вместо команд SQL для управления строкой и построения таблицы SQL. Я так долго использовал SQL и Access, что еще не до конца осознал новые возможности, которые на самом деле предоставляет использование WAMP и php. еще раз спасибо
@Тим, пожалуйста! Я пришел оттуда же 20+ лет назад, хотя ASP Classic + Access, потом MSSQL Server. Прыжок на самом деле не такой уж и большой, если наловчишься. Удачи в этом!
Ответы 1
Образец.
Файл для загрузки:
C:\example\example.txt 123456
D:\example2\example2.txt 123456789
E:\example3.data\example3.txt.txt 123456789012
F:\example3.txt 123
Структура таблицы:
CREATE TABLE test (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
drive CHAR(1),
path TEXT,
filename TEXT,
fileext TEXT,
filesize BIGINT UNSIGNED,
raw TEXT
);
Запрос:
INSERT INTO test(drive, path, filename, fileext, filesize, raw)
SELECT LEFT(raw, 1),
REGEXP_REPLACE(raw, '^..(.*\\\\)[^\\\\]+$', '$1'),
REGEXP_REPLACE(raw, '^.+\\\\([^\\\\]+)\\.[^.]* \\d+$', '$1'),
SUBSTRING_INDEX(SUBSTRING_INDEX(raw, '.', -1), ' ', 1),
SUBSTRING_INDEX(raw, ' ', -1),
raw
FROM JSON_TABLE(CONCAT('["', REPLACE(REPLACE(CAST(LOAD_FILE('C:/ProgramData/MySQL/MySQL Server 8.4/Uploads/test.csv') AS CHAR), '\\', '\\\\'), '\r\n', '","'), '"]'),
'$[*]' COLUMNS (raw TEXT PATH '$')) jsontable;
Результат:
mysql> SELECT * FROM test;
+----+-------+-----------------+--------------+---------+--------------+------------------------------------------------+
| id | drive | path | filename | fileext | filesize | raw |
+----+-------+-----------------+--------------+---------+--------------+------------------------------------------------+
| 1 | C | \example\ | example | txt | 123456 | C:\example\example.txt 123456 |
| 2 | D | \example2\ | example2 | txt | 123456789 | D:\example2\example2.txt 123456789 |
| 3 | E | \example3.data\ | example3.txt | txt | 123456789012 | E:\example3.data\example3.txt.txt 123456789012 |
| 4 | F | \ | example3 | txt | 123 | F:\example3.txt 123 |
+----+-------+-----------------+--------------+---------+--------------+------------------------------------------------+
очень эффективное и элегантное решение SQL, имеющее только одну проблему. Я импортирую текстовый файл (который заключен в кавычки, а каждая строка в txt-файле является новой строкой в таблице) в таблицу под названием DirListRAW. Таблица состоит только из одного поля, содержащего строку pathRAW. Если я прав, ваше решение использует файл JSON. Я рассмотрел, как преобразовать мой CSV-файл в JSON, но пытаюсь изучить синтаксис JSON и форматирование, необходимые для того, чтобы ваше решение работало на меня. Какие изменения потребуются в CONCAT, чтобы изменить ваше решение с JSON на мой файл формата CSV, как в вашем примере?
@Tim Пожалуйста, прочитайте мой комментарий к вашему вопросу, изучите предоставленную ссылку, предоставьте необходимые образцы данных.
Прочитал ваш комментарий, изучил ссылку. Я надеюсь, что продолжу учиться тому, как лучше использовать REGEXP, но ему наверняка придется учиться. Формат импортированного файла CSV и необходимый конечный желаемый результат указаны в моем исходном сообщении. Еще раз спасибо за ваше время и терпение :)
Мне очень трудно уследить за тем, что вы пытаетесь сделать, однако, если я правильно читаю, я думаю, что большинство людей будут делать то, что вы пытаетесь сделать, на PHP, а не на SQL. Это не значит, что вы не можете или не должны, просто (возможно) легче отлаживать подобные манипуляции со строками, которые вы выполняете.