MySQL має функцію експорту таблиці у файл CSV. Формат файлу CSV — це значення, розділене комами, яке ми використовуємо для обміну даними між різними програмами, такими як Microsoft Excel, Goole Docs і Open Office. Корисно мати дані MySQL у форматі файлу CSV, що дозволяє аналізувати та форматувати їх так, як ми хочемо. Це текстовий файл, який допомагає нам дуже легко експортувати дані.
MySQL забезпечує простий спосіб експорту будь-якої таблиці у файли CSV, які знаходяться на сервері бази даних. Перед експортом даних MySQL ми повинні переконатися в наступному:
- Процес сервера MySQL має доступ для читання/запису до вказаної (цільової) папки, яка містить файл CSV.
- Зазначений файл CSV не повинен існувати в системі.
Щоб експортувати таблицю у файл CSV, ми будемо використовувати ВИБРАТИ В....OUTFILE заява. Ця заява є компліментом ЗАВАНТАЖИТИ ДАНІ команда, яка використовується для запису даних із таблиці, а потім експорту їх у вказаний формат файлу на сервері. Це необхідно для того, щоб ми мали повноваження використовувати цей синтаксис.
SELECT column_lists INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/filename.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''' LINES TERMINATED BY ' ';
Ми також можемо використовувати цей синтаксис із оператором значень, щоб експортувати дані безпосередньо у файл. Наступне твердження пояснює це більш чітко:
SELECT * FROM (VALUES ROW(1,2,3,4),ROW(5,6),ROW(7,8)) AS table1 INTO OUTFILE '/tmp/selected_values.txt';
Якщо ми хочемо експортувати всі стовпці таблиці , ми будемо використовувати наведений нижче синтаксис. За допомогою цього оператора впорядкування та кількість рядків буде контролюватись СОРТУВАТИ ЗА і МЕЖА пункт.
змінні nginx
TABLE table_name ORDER BY lname LIMIT 1000 INTO OUTFILE '/path/filename.txt' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';;
З вищесказаного,
РЯДКИ ЗАКІНЧУЮТЬСЯ НА ',' : використовується для позначення рядків рядків у файлі, які завершуються оператором коми. Кожен рядок містить дані кожного стовпця у файлі.
ПОЛЯ, ОБМЕЖЕНІ «' : використовується для вказівки поля файлу в подвійних лапках. Це запобігає значенням, які містять розділювачі коми. Якщо значення, що містяться в подвійних лапках, кома не розпізнається як роздільник.
Місце зберігання експортованого файлу
Місце зберігання кожного експортованого файлу в MySQL зберігається в змінній за замовчуванням secure_file_priv . Ми можемо виконати наведену нижче команду, щоб отримати шлях за замовчуванням до експортованого файлу.
linux як перейменувати каталог
mysql> SHOW VARIABLES LIKE 'secure_file_priv';
Після виконання він дасть такий результат, де ми побачимо цей шлях: C:/ProgramData/MySQL/MySQL Server 8.0/Завантаження/ як розташування файлу за замовчуванням. Цей шлях використовуватиметься під час виконання команди експорту.
Якщо ми хочемо змінити стандартне місце експорту файлу CSV, указане в secure_file_priv змінна, нам потрібно відредагувати my.ini конфігураційний файл. На платформі Windows цей файл розташований за таким шляхом: C:ProgramDataMySQLMySQL Server X.Y .
Якщо ми хочемо експортувати дані MySQL, спочатку нам потрібно створити a бази даних принаймні з одним стіл . Ми будемо використовувати цю таблицю як приклад.
Ми можемо створити a база даних і табл виконавши наведений нижче код у редакторах, які ми використовуємо:
CREATE DATABASE testdb; USE testdb; CREATE TABLE employee_detail ( ID int NOT NULL AUTO_INCREMENT, Name varchar(45) DEFAULT NULL, Email varchar(45) DEFAULT NULL, Phone varchar(15) DEFAULT NULL, City varchar(25) DEFAULT NULL, PRIMARY KEY (ID), UNIQUE KEY unique_email (Email), UNIQUE KEY index_name_phone (Name,Phone) ) INSERT INTO employee_detail ( Id, Name, Email, Phone, City) VALUES (1, 'Peter', '[email protected]', '49562959223', 'Texas'), (2, 'Suzi', '[email protected]', '70679834522', 'California'), (3, 'Joseph', '[email protected]', '09896765374', 'Alaska'), (4, 'Alex', '[email protected]', '97335737548', 'Los Angeles'), (5, 'Mark', '[email protected]', '78765645643', 'Washington'), (6, 'Stephen', '[email protected]', '986345793248', 'New York');
Якщо ми виконаємо ВИБРАТИ ми побачимо наступний результат:
Експортуйте дані MySQL у формат CSV за допомогою оператора SELECT INTO ... OUTFILE
Щоб експортувати дані таблиці у файл CSV, нам потрібно виконати запит наступним чином:
SELECT Id, Name, Email, Phone, City FROM employee_detail INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';
Ми отримаємо наступний результат, де ми бачимо, що це стосується шести рядків. Це тому, що зазначена таблиця містить лише шість рядків.
Якщо ми знову виконаємо ту саму інструкцію, MySQL видасть повідомлення про помилку, яке можна побачити у наведеному нижче виводі:
Повідомлення про помилку повідомляє нам, що вказане ім’я файлу вже існує у вказаному місці. Таким чином, якщо ми експортуємо новий файл CSV із тим самим ім’ям і розташуванням, його неможливо створити. Ми можемо вирішити це або видалити наявний файл у вказаному місці, або перейменувати ім’я файлу, щоб створити його в тому самому місці.
Ми можемо перевірити, чи створено файл CSV у вказаному місці, перейшовши за вказаним шляхом таким чином:
Коли ми відкриємо цей файл, він матиме такий вигляд:
список дротиків
На зображенні ми бачимо, що числові поля взяті в лапки. Ми можемо змінити цей стиль, додавши Речення OPTIONALLY перед ENCLOSED BY :
SELECT Id, Name, Email, Phone, City FROM employee_detail INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''' LINES TERMINATED BY ' ';
Експорт даних із заголовком стовпця
Іноді ми хочемо експортувати дані разом із заголовками стовпців, які роблять файл зручнішим. Експортований файл буде більш зрозумілим, якщо перший рядок файлу CSV містить заголовки стовпців. Ми можемо додати заголовки стовпців за допомогою СОЮЗ ВСЕ заяву наступного змісту:
SELECT 'Id', 'Name', 'Email', 'Phone', 'City' UNION ALL SELECT Id, Name, Email, Phone, City FROM employee_detail INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS TERMINATED BY ';' ENCLOSED BY ''' ESCAPED BY ''' LINES TERMINATED BY ' ';
У цьому запиті ми бачимо, що ми додали заголовок для кожної назви стовпця. Ми можемо перевірити вихід, перейшовши за вказаною URL-адресою, де перший рядок містить заголовок для кожного стовпця:
Експортуйте таблицю MySQL у формат CSV
MySQL OUTFILE також дозволяє експортувати таблицю без вказівки імені стовпця. Ми можемо використати наведений нижче синтаксис для експорту таблиці у формат файлу CSV:
TABLE employee_detail ORDER BY City LIMIT 1000 INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';
Якщо ми виконаємо наведену вище інструкцію, наш інструмент командного рядка видасть наступний результат. Це означає, що зазначена таблиця містить шість рядків, які експортуються в employee_backup.csv файл.
Обробка нульових значень
Іноді поля в наборі результатів мають значення NULL, тоді цільовий файл (тип експортованого файлу) міститиме N замість NULL. Ми можемо вирішити цю проблему, замінивши значення NULL на 'не застосовується (N/A)' використовуючи IFNULL функція. Наведене нижче твердження пояснює це більш чітко:
java переміщення до внутр
SELECT Name, Email, Phone, IFNULL(Phone, 'N/A') FROM employee_detail INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';
Експортуйте таблицю у формат CSV за допомогою MySQL Workbench
Якщо ми не хочемо отримати доступ до сервера бази даних для експорту файлу CSV, MySQL пропонує інший спосіб, тобто використання MySQL Workbench. Workbench — це графічний інструмент для роботи з базою даних MySQL без використання інструменту командного рядка. Це дозволяє нам експортувати набір результатів оператора у формат CSV у нашій локальній системі. Для цього нам потрібно виконати наступні дії:
- Виконайте оператор/запит і отримайте його набір результатів.
- Потім на панелі результатів натисніть 'експортувати набір записів у зовнішній файл' варіант. Набір записів використовується для набору результатів.
- Нарешті, буде показано нове діалогове вікно. Тут нам потрібно вказати назву файлу та його формат. Після заповнення реквізиту натисніть на зберегти кнопку. Наступне зображення пояснює це більш чітко:
Тепер ми можемо перевірити результат, перейшовши за вказаним шляхом.