alan_beaulieu-learning_sql-ru (865932), страница 47
Текст из файла (страница 47)
Но сервер MySQL предпринялспециальные меры предосторожности для подобных ситуаций. Вот со держимое файла comma1.txt:1,This string has no commas,This string\, however\, has two commasКак видите, запятые в третьем столбце экранированы обратным слэ шем, размещенным перед ними. Если выполнить этот же запрос, ноиспользовать формат с разделителями «|», запятые не будут экраниро ваны. Если хочется использовать в выходном файле другой символ эк ранирования, например еще одну запятую, его можно задать с помо щью подблока fields escaped by.Кроме разделителей столбцов можно задавать символ, используемыйдля разделения разных записей в файле данных.
Если требуется, что бы каждая запись в выходном файле была отделена не символом новойстроки, а каким то другим, можно воспользоваться подблоком lines:mysql> SELECT emp_id, fname, lname, start_dateMySQLCрасширения языка SQL265> INTO OUTFILE 'C:\\TEMP\\emp_list_atsign.txt'> FIELDS TERMINATED BY '|'> LINES TERMINATED BY '@'> FROM employee;Query OK, 18 rows affected (0.03 sec)Поскольку в файле emp_list_atsign.txt для разделения записей не ис пользуется символ новой строки, весь файл выглядит как одна длин ная текстовая строка, в которой каждая запись отделена символом '@':1|Michael|Smith|20010622@2|Susan|Barker|20020912@3|Robert|Tyler|20000209@4|Susan|Hawthorne|20020424@5|John|Gooding|20031114@6|Helen|Fleming|20040317@7|Chris|Tucker|20040915@8|Sarah|Parker|20021202@9|Jane|Grossman|20020503@10|Paula|Roberts|20020727@11|Thomas|Ziegler|20001023@12|Samantha|Jameson|20030108@13|John|Blake|20000511@14|Cindy|Mason|20020809@15|Frank|Portman|20030401@16|Theresa|Markham|20010315@17|Beth|Fowler|20020629@18|Rick|Tulman|20021212@Если понадобится сгенерировать файл данных для загрузки в элек тронную таблицу или рассылки в/за пределы организации, блок intooutfile обеспечит достаточную гибкость для создания файла любогонеобходимого формата.Сочетание выражений insert/updateДопустим, требуется создать таблицу для сбора информации о посеще нии клиентами отделений банка.
Таблица должна содержать ID кли ента, ID отделения и столбец datetime с датой и временем последнегопосещения отделения клиентом. Строки в таблицу добавляются вся кий раз, когда клиент посещает определенное отделение. Но если кли ент уже был в этом отделении, следует просто обновить столбец datetime существующей строки. Вот описание таблицы:CREATE TABLE branch_usage(branch_id SMALLINT UNSIGNED NOT NULL,cust_id INTEGER UNSIGNED NOT NULL,last_visited_on DATETIME,CONSTRAINT pk_branch_usage PRIMARY KEY (branch_id, cust_id));Кроме трех столбцов таблица branch_usage определяет ограничение пер вичного ключа для столбцов branch_id и cust_id.
Следовательно, серверотклонит любую добавляемую в таблицу строку, пара значений отде ление/клиент которой уже есть в таблице.Скажем, таблица создана, и клиент с ID 5 посещает главное отделение(отделение с ID 1) за первую неделю три раза. После первого визитав таблицу branch_usage можно вставить запись, поскольку для клиентас ID 5 и отделения с ID 1 записи еще нет:mysql> INSERT INTO branch_usage (branch_id, cust_id, last_visited_on)> VALUES (1, 5, CURRENT_TIMESTAMP( ));Query OK, 1 row affected (0.02 sec)266Приложение BОднако при следующем посещении клиентом того же отделения потре буется обновить существующую запись, а не вставлять новую.
В про тивном случае будет получена следующая ошибка:ERROR 1062 (23000): Duplicate entry '15' for key 1Чтобы избежать этой ошибки, можно запросить таблицу branch_usageи посмотреть, имеется ли данная пара значений клиент/отделение,а затем уже вставить запись, если таковой не найдено, или обновитьимеющуюся строку, если она уже существует. Однако чтобы избавитьпользователей от хлопот, разработчики MySQL расширили выраже ние insert и обеспечили возможность определять необходимость изме нения одного или нескольких столбцов, если выражение insert даетсбой из за дублирования ключей. Следующее выражение предписыва ет серверу изменять столбец last_visited_on, если данные клиент и от деление уже есть в таблице branch_usage:mysql> INSERT INTO branch_usage (branch_id, cust_id, last_visited_on)> VALUES (1, 5, CURRENT_TIMESTAMP( ))> ON DUPLICATE KEY UPDATE last_visited_on = CURRENT_TIMESTAMP( );Query OK, 2 rows affected (0.02 sec)Блок on duplicate key (при дублировании ключа) позволяет выполнятьодно и то же выражение при каждом появлении клиента с ID 5 в отде лении с ID 1.
Если выражение выполняется 100 раз, в результате пер вого прогона в таблицу добавляется одна строка. Следующие 99 выпол нений обеспечивают изменение столбца last_visited_on соответственноЗамещение команды replaceДо версии 4.1 сервера MySQL операции с возможностью обновле ния и вставки осуществлялись с помощью команды replace (за местить). Это собственное выражение, которое сначала удаляетсуществующую строку, если такое значение первичного ключауже существует, а потом уже вставляет новую строку в таблицу.Выполняя операции с возможностью обновления и вставки приработе с версией 4.1 и более поздними, можно выбирать междукомандами replace и insert...on duplicate key.Однако команда replace выполняет операцию удаления привстрече дублирующихся значений ключей, что может обусло вить цепную реакцию, если используется механизм храненияInnoDB и наложены ограничения внешнего ключа. Если ограни чения созданы посредством опции on delete cascade, при удале нии строки целевой таблицы команда replace может автоматиче ски удалить и строки других таблиц.
Поэтому обычно более без опасным считается использование блока on duplicate key выраже ния insert, а не более старой команды replace.MySQLCрасширения языка SQL267текущему времени. Такой тип операций часто называют операциямис возможностью обновления и вставки (upsert), т. е. сочетанием выра жений update и insert.Упорядоченные обновления и удаленияРанее здесь было показано, как с помощью сочетания блоков limitи order by можно писать запросы, формирующие ранжированную вы борку (например, три лучших сотрудника по количеству открытыхсчетов).
MySQL тоже позволяет использовать блоки limit и order byв выражениях update и delete, обеспечивая таким образом возмож ность изменять или удалять определенные строки таблицы на основа нии их ранга. Предположим, например, что требуется удалить строкитаблицы, используемой для отслеживания регистраций пользовате лей в онлайновой банковской системе. Вот таблица, отслеживающаяID клиента и дату/время регистрации:CREATE TABLE login_history(cust_id INTEGER UNSIGNED NOT NULL,login_date DATETIME,CONSTRAINT pk_login_history PRIMARY KEY (cust_id, login_date));Следующее выражение заполняет таблицу login_history некоторымиданными путем формирования перекрестного соединения между таб лицами account и customer и формирования дат регистрации на основа нии значений столбца open_date таблицы account:mysql> INSERT INTO login_history (cust_id, login_date)> SELECT c.cust_id,> ADDDATE(a.open_date, INTERVAL a.account_id * c.cust_id HOUR)> FROM customer c CROSS JOIN account a;Query OK, 312 rows affected (0.03 sec)Records: 312 Duplicates: 0 Warnings: 0Теперь таблица заполнена 312 строками относительно случайных дан ных.
Ваша задача – раз в месяц просмотреть данные таблицы login_history, составить для руководства отчет о тех, кто использует онлайновуюбанковскую систему, и затем удалить все записи кроме 50 последних.Один из возможных подходов – написать запрос с использованием бло ков order by и limit для поиска 50 самых свежих регистраций:mysql> SELECT login_date> FROM login_history> ORDER BY login_date DESC> LIMIT 49,1;++| login_date|++| 20040702 09:00:00 |++1 row in set (0.00 sec)268Приложение BВооружившись этой информацией, уже можно создать выражениеdelete, удаляющее все строки, значение столбца login_date которыхменьше даты, возвращенной запросом:mysql> DELETE FROM login_history> WHERE login_date < '20040702 09:00:00';Query OK, 262 rows affected (0.02 sec)Теперь таблица содержит 50 последних регистраций.
Однако MySQL расширения позволяют достичь тех же результатов с помощью единст венного выражения delete с блоками limit и order by. Возвратив исход ные 312 строк в таблицу login_history, можно выполнить следующеевыражение:mysql> DELETE FROM login_history> ORDER BY login_date ASC> LIMIT 262;Query OK, 262 rows affected (0.05 sec)Это выражение сортирует строки по возрастанию значений столбцаlogin_date, затем первые 262 строки удаляются, а 50 самых свежихстрок остаются.В этом примере для построения блока limit необходимо былознать число строк в таблице (312 исходных строк – 50 остав шихся строк = 262 удалений).
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
