Узнать следующую и предыдущую запись в выборке по полю с неуникальными значениями

Опубликовано в Пнд, 06/08/2012 - 22:37
mysql sql
Есть таблица с данными:

+----+---------------------+
| id | date |
+----+---------------------+
| 1 | 2012-08-06 22:09:40 |
| 2 | 2012-08-06 22:09:44 |
| 3 | 2012-08-06 22:10:00 |
| 4 | 2012-08-06 22:10:00 |
| 5 | 2012-08-06 22:10:17 |
+----+---------------------+
Нужно, зная id и date определённой записи, узнать следующую и предыдущую запись в такой вот выборке:

SELECT *
FROM `table`
ORDER BY `date` ASC, `id` ASC

Следующая запись:

SELECT *
FROM `table`
WHERE `date` > $date OR (`date` = $date AND `id` > $id)
ORDER BY `date` ASC, `id` ASC
LIMIT 1

Предыдущая запись:

SELECT *
FROM `table`
WHERE `date` < $date OR (`date` = $date AND `id` < $id)
ORDER BY `date` DESC, `id` DESC
LIMIT 1

Добавлено: 30 Июля 2018 07:55:14 Добавил: Андрей Ковальчук

Вычислить возраст по дате рождения

Опубликовано в Втр, 17/01/2012 - 12:47
mysql
Более универсальный вариант SQL запроса из официальной документации:

SELECT
  birthday,
  (
    (YEAR(CURRENT_DATE) - YEAR(birthday)) -                             /* step 1 */
    (DATE_FORMAT(CURRENT_DATE, '%m%d') < DATE_FORMAT(birthday, '%m%d')) /* step 2 */
  ) AS age
FROM users

Смысл следующий — первым шагом вычитаем из текущего года, год рождения, вторым шагом вычитаем единичку если дня рождения в этом году ещё не было.

birthday это поле с датой в любом валидном формате (не timestamp), например 1989-07-22 или даже 1989-07-22T00:00:00.

Добавлено: 30 Июля 2018 07:28:00 Добавил: Андрей Ковальчук

Выборка постов с определёнными тегами

Есть таблицы posts (id, title, text) и tags (id, name), связанные отношением многие-ко-многим с помощью таблицы posts_tags (post_id, tag_id).

Задача — выбрать посты с определёнными тегами.

Вариант 1:

SELECT p.id, p.title, p.text
FROM posts p
INNER JOIN posts_tags pt1 ON pt1.post_id = p.id
INNER JOIN posts_tags pt2 ON pt2.post_id = p.id
WHERE
    pt1.tag_id = ID_ПЕРВОГО_ТЕГА AND
    pt2.tag_id = ID_ВТОРОГО_ТЕГА

Таблица posts_tags джойнится столько раз, сколько тегов участвует в выборке (в примере их два).

Вариант 2:

SELECT p.id, p.title, p.text
FROM posts p
WHERE p.id IN (
    SELECT pt.post_id
    FROM posts_tags pt
    INNER JOIN posts_tags pt1 ON pt.post_id = pt1.post_id AND pt1.tag_id = ID_ПЕРВОГО_ТЕГА
    INNER JOIN posts_tags pt2 ON pt.post_id = pt2.post_id AND pt2.tag_id = ID_ВТОРОГО_ТЕГА
)

Таким способом делает выборку модуль Views в Drupal.

Вариант 3:

SELECT p.id, p.title, p.text
FROM posts p
INNER JOIN posts_tags pt ON pt.post_id = p.id
WHERE pt.tag_id IN (ID_ПЕРВОГО_ТЕГА, ID_ВТОРОГО_ТЕГА)
GROUP BY p.id
HAVING COUNT(*) >= 2

В HAVING указывается количество тегов участвующих в выборке (в примере их два). Таким способом делает выборку модуль Search в Drupal.

Добавлено: 29 Июля 2018 22:18:40 Добавил: Андрей Ковальчук

Альтернативы транзакциям

Задача
Вам нужно выполнить транзакцию, но ваш сервер MySQL не поддерживает их.

Решение
Некоторые транзакционные операции можно заменить явной блокировкой таблицы. А в некоторых случаях транзакция вообще не нужна, можно переформулировать запрос так, что необходимость в транзакции полностью отпадет.

Обсуждение
Транзакции очень полезны, но в некоторых ситуациях их невозможно или не нужно использовать:

• Ваш сервер может вообще не поддерживать транзакции. (Он может быть слишком старым или у него могут быть не установлены соответствующие типы таблиц, см. рецепт 15.1). В этом случае остается только придумать какую-то замену транзакциям. Для предотвращения проблем с параллельными операциями можно использовать явную блокировку таблиц.

• Иногда приложения используют транзакции там, где в этом нет необходимости. Можно переформулировать предложения и избавиться от транзакций. В результате может даже улучшиться производительность приложения.

Группировка предложений с помощью блокировок
Если ваш сервер не поддерживает транзакции, а вам требуется выполнить группу предложений, не пересекаясь с другими клиентскими приложениями, используйте LOCK TABLE и UNLOCK TABLE:

• Установите для всех используемых таблиц блокировку при помощи LOCK TABLE. (Применяйте блокировку записи для таблиц, которые вы будете изменять, и блокировку чтения для остальных таблиц.) Тогда другие клиенты не смогут изменять таблицы, пока вы будете их использовать.

• Запустите запросы, которые должны выполниться как группа.

• Снимите блокировку при помощи UNLOCK TABLE. Другие клиенты получат доступ к таблицам.

Блокировки, установленные LOCK TABLE, действуют, пока вы их не снимете, в том числе и на период выполнения нескольких предложений. То есть обеспечивается решение проблемы конкуренции, как и в транзакции. Однако нет отката на случай ошибки, поэтому блокировка таблиц подходит не для каждого приложения. Например, вы можете попытаться выполнить операцию передачи денег от Евы Иде так:

LOCK TABLE money WRITE;
UPDATE money SET amt = amt - 6 WHERE name = 'Eve';
UPDATE money SET amt = amt + 6 WHERE name = 'Ida';
UNLOCK TABLE;


К сожалению, если второе обновление не выполнится, откатить первое будет невозможно. Но несмотря на некоторые ограничения, есть ряд ситуаций, для которых вполне подходят блокировки таблиц:

• Набор предложений, состоящий только из запросов SELECT. Если вы хотите выполнить несколько предложений SELECT и запретить другим клиентам изменять таблицы, пока вы к ним обращаетесь, используйте блокировку. Например, если вам нужно выполнить несколько суммарных запросов для множества таблиц, может оказаться, что итоги формируются для различных наборов данных, если между запросами другие клиенты будут иметь возможность изменять записи. Итоговые данные получатся противоречивыми. Чтобы не допустить этого, заблокируйте таблицы на время использования.

• Блокировку можно использовать для набора предложений, в котором только последнее является обновлением. Тогда предыдущие предложения не вносят никаких изменений в таблицы, и если обновление не выполнится, нет необходимости в откате более ранних предложений.

Переформулируем запросы, чтобы не использовать транзакции
Иногда приложения используют транзакции без необходимости. Предположим, что у вас есть таблица meeting, в которой содержится информация о собраниях и заседаниях (включая количество оставшихся билетов), и вы пишете приложение на Perl, содержащее функцию get_ticket(), которая распределяет билеты. Одним из способов реализации функции является проверка счетчика билетов, его уменьшение, если он положительный, и возвращение статуса, показывающего доступность билета. Чтобы воспрепятствовать попыткам нескольких клиентов одновременно завладеть последним билетиком, будем выдавать запросы в транзакции:

sub get_ticket
{
my ($dbh, $meeting_id) = @_;
my $ref = transact_init ($dbh);
my $count = 0;
eval
{
# проверяем текущий счетчик билетов
$count = $dbh->selectrow_array (
"SELECT tix_left FROM meeting
WHERE meeting_id = ?", undef, $meeting_id);
# если билеты остались, уменьшаем счетчик
if ($count > 0)
{
$dbh->do ("UPDATE meeting SET tix_left = tix_left-1
WHERE meeting_id = ?", undef, $meeting_id);
}
$dbh->commit ();
};
$count = 0 if $@; # если произошла ошибка, то билетов больше нет
transact_finish ($dbh, $ref, $@);
return ($count > 0)
}


Функция корректно распределяет билеты, но выполняет ненужную работу. Можно сделать то же самое, не прибегая к транзакции. Будем уменьшать счетчик билетов, только если он положительный, затем проверять, изменило ли предложение строку:

sub get_ticket
{
my ($dbh, $meeting_id) = @_;
my $count = $dbh->do ("UPDATE meeting SET tix_left = tix_left-1
WHERE meeting_id = ? AND tix_left > 0",
undef, $meeting_id);
return ($count > 0);
}


В MySQL счетчик строк, возвращаемый предложением UPDATE, указывает на количество измененных строк. То есть если билетов на мероприятие не осталось, то UPDATE не изменит строки, и счетчик будет равен нулю. Легче определить, доступен ли билет, при помощи одного запроса, чем выполнять несколько запросов, используя транзакционный подход. Мораль в том, что несмотря на всю важность и полезность транзакций, может оказаться, что в них нет необходимости, и, избавившись от них, вы повысите производительность своего приложения. (Решение, состоящее из одного запроса, – это пример того, что справочное руководство по MySQL называет «атомарной операцией». В руководстве такие операции рассматриваются как эффективная альтернатива транзакциям.)

Добавлено: 23 Июля 2018 20:51:39 Добавил: Андрей Ковальчук

Использование транзакций в программах на Java

Задача
Вы хотите выполнить транзакцию в сценарии JDBC.

Решение
Используйте стандартный механизм поддержки транзакций JDBC.

Обсуждение
Для выполнения транзакции в Java используйте ваш объект Connection для отключения режима автофиксации. После запуска запросов используйте метод commit()объекта для фиксации транзакции или rollback() для ее отката. Обычно предложения транзакции выполняются в блоке try, в конце которого стоит commit(). Для обработки ошибок вызовите rollback() в соответствующем обработчике исключений:

try
{
conn.setAutoCommit (false);
Statement s = conn.createStatement ();
// передать деньги от одного человека другому
s.executeUpdate ("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'");
s.executeUpdate ("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'");
s.close ();
conn.commit ();
conn.setAutoCommit (true);
}
catch (SQLException e)
{
System.err.println ("Transaction failed, rolling back.");
Cookbook.printErrorMessage (e);
// пустой обработчик исключений, если откат не удался
try
{
conn.rollback ();
conn.setAutoCommit (true);
}
catch (Exception e2) { }
}

Добавлено: 23 Июля 2018 20:50:49 Добавил: Андрей Ковальчук

Использование транзакций в программах на Python

Задача
Вы хотите использовать транзакцию в сценарии DB-API.

Решение
Используйте стандартный механизм поддержки транзакций DB-API.

Обсуждение
В DB-API имеется абстракция, обеспечивающая контроль обработки транзакций за счет методов объектов соединений с базой данных. Вызовите begin() для начала транзакции и commit() или rollback() для ее завершения.

Вызовы begin() и commit() помещены в блок try, а rollback() – в соответствующий блок except для отмены транзакции в случае возникновения ошибки:

try:
conn.begin ()
cursor = conn.cursor ()
# передать деньги от одного человека другому
cursor.execute ("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'")
cursor.execute ("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'")
cursor.close ()
conn.commit()
except MySQLdb.Error, e:
print "Transaction failed, rolling back. Error was:"
print e.args
try: # пустой обработчик исключения, если откат не удался
conn.rollback ()
except:
pass

Добавлено: 23 Июля 2018 20:50:14 Добавил: Андрей Ковальчук

Использование транзакций в программах на PHP

Задача
Вы хотите выполнить транзакцию в PHP-сценарии.

Решение
Запускайте предложения SQL, которые начинают и завершают транзакции.

Обсуждение
В PHP нет специального механизма транзакций, так что следует непосредственно создавать соответствующие предложения SQL. То есть вы можете или использовать BEGIN для начала транзакции, или самостоятельно включать и выключать режим автофиксации, используя SET AUTOCOMMIT. В следующем примере используется BEGIN. Предложения транзакции помещены в функцию, чтобы избежать ненужной проверки ошибок. Чтобы определить, нужно ли произвести откат, нужно просто проверить результат функции:

function do_queries ($conn_id)
{
# передать деньги от одного человека другому
if (!mysql_query ("BEGIN", $conn_id))
return (0);
if (!mysql_query ("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'", $conn_id))
return (0);
if (!mysql_query ("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'", $conn_id))
return (0);
if (!mysql_query ("COMMIT", $conn_id))
return (0);
return (1);
}
if (!do_queries ($conn_id))
{
print ("Transaction failed, rolling back. Error was:\n"
. mysql_error ($conn_id) . "\n");
mysql_query ("ROLLBACK", $conn_id);
}


Функция do_queries() проверяет каждый метод и возвращает «ложь», если хотя бы один из них не выполнен. Такой вид проверки применяется в тех случаях, когда может потребоваться провести дополнительную обработку между предложениями или после выполнения предложений, но до возвращения успешного результата. В приведенном примере никакой дополнительной обработки не требуется, и можно переформулировать do_queries() как одно длинное предложение:

function do_queries ($conn_id){
# передать деньги от одного человека другому
return
(
mysql_query ("BEGIN", $conn_id)
&&
mysql_query ("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'", $conn_id)
&&
mysql_query ("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'", $conn_id)
&&
mysql_query ("COMMIT", $conn_id)
);
}

Добавлено: 23 Июля 2018 20:49:31 Добавил: Андрей Ковальчук

Использование транзакций в программах на Perl

Задача
Вы хотите выполнить транзакцию в DBI-сценарии.

Решение
Используйте стандартный механизм поддержки транзакций DBI.

Обсуждение
Механизм реализации транзакции в DBI базируется на явном управлении режимом автофиксации. Процедура выглядит так:

1. Если это еще не сделано, включите атрибут RaiseError и выключите Print-Error. Вы хотите, чтобы ошибки порождали исключения и ничего не выводили; если же оставить PrintError включенным, в некоторых случаях это может помешать обнаружению ошибки.

2. Отключите атрибут AutoCommit, чтобы фиксация происходила только по вашему указанию.

3. Выполните предложения транзакции в блоке eval так, чтобы ошибки вызывали исключение и завершали блок. Последним в блоке должен быть вызов commit(), который фиксировал бы транзакцию в случае успешного выполнения всех ее предложений.

4. После выполнения eval проверьте переменную $@. Если она содержит пустую строку, транзакция выполнена успешно. В противном случае этоозначает, что произошла какая-то ошибка, и $@ будет содержать сообщение об ошибке. Вызовите rollback() для отмены транзакции. Если вы хотите отобразить для пользователя сообщение об ошибке, выведите $@ перед вызовом rollback().

Следующий код показывает, как реализовать процедуру выполнения нашей транзакции. Текущие значения атрибутов обработки ошибок и автофиксации сохраняются перед выполнением транзакции, а после ее выполнения возвращаются в исходное состояние. Для ваших приложений это может оказаться избыточным. Например, если вы знаете, что RaiseError и PrintError уже установлены так, как надо, нет необходимости в их сохранении и восстановлении.

# сохраняем атрибуты обработки ошибок и автофиксации,
# затем убеждаемся в том, что они установлены корректно.
$save_re = $dbh->{RaiseError};
$save_pe = $dbh->{PrintError};
$save_ac = $dbh->{AutoCommit};
$dbh->{RaiseError} = 1; # исключение в случае ошибки
$dbh->{PrintError} = 0; # не выводить сообщение об ошибке
$dbh->{AutoCommit} = 0; # отключить автофиксацию
eval
{
# передать немного денег от одного человека другому
$dbh->do ("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'");
$dbh->do ("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'");
# все предложения выполнены успешно, зафиксировать транзакцию
$dbh->commit ();
};
if ($@) # произошла ошибка
{
print "Transaction failed, rolling back. Error was:\n$@\n";
# откат внутри eval, чтобы ошибка отката не привела к завершению работы сценария
eval { $dbh->rollback (); };
}
# восстановить исходное состояние атрибутов
$dbh->{AutoCommit} = $save_ac;
$dbh->{PrintError} = $save_pe;
$dbh->{RaiseError} = $save_re;


Как видите, приходится проделать большую работу для того, чтобы выдать пару предложений. Чтобы упростить обработку транзакции, можно создать несколько функций, которые займутся обработкой до и после выполнения eval:

sub transact_init
{
my $dbh = shift;
my $attr_ref = {}; # создать хеш для хранения атрибутов$attr_ref->{RaiseError} = $dbh->{RaiseError};
$attr_ref->{PrintError} = $dbh->{PrintError};
$attr_ref->{AutoCommit} = $dbh->{AutoCommit};
$dbh->{RaiseError} = 1; # исключение в случае ошибки
$dbh->{PrintError} = 0; # не выводить сообщение об ошибке
$dbh->{AutoCommit} = 0; # отключить автофиксацию
return ($attr_ref); # вернуть атрибуты в вызывающую программу
}
sub transact_finish
{
my ($dbh, $attr_ref, $error) = @_;
if ($error) # произошла ошибка
{
print "Transaction failed, rolling back. Error was:\n$error\n";
# откат внутри eval, чтобы ошибка отката не привела
# к завершению работы сценария
eval { $dbh->rollback (); };
}
# восстановить исходное состояние атрибутов обработки ошибок и автофиксации
$dbh->{AutoCommit} = $attr_ref->{AutoCommit};
$dbh->{PrintError} = $attr_ref->{PrintError};
$dbh->{RaiseError} = $attr_ref->{RaiseError};
}
Если использовать две эти функции, наша транзакция значительно упрос-
тится:
$ref = transact_init ($dbh);
eval
{
# передать деньги от одного человека другому
$dbh->do ("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'");
$dbh->do ("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'");
# все предложения выполнены успешно, зафиксировать транзакцию
$dbh->commit ();
};
transact_finish ($dbh, $ref, $@);


Начиная с DBI 1.20 есть альтернатива ручному управлению атрибутом Auto-Commit – можно начинать транзакцию, вызывая begin_work(). Этот метод отключает AutoCommit и автоматически разрешает его при последующем вызове commit() или rollback().

Добавлено: 23 Июля 2018 20:48:51 Добавил: Андрей Ковальчук

Выполнение транзакций в программах

Задача
Вы пишете программу, в которой необходимо использовать транзакции.

Решение
Используйте абстракцию транзакции в API для вашего языка программирования, если такая существует. Если нет, используйте обычный механизм выполнения запроса в API для непосредственного запуска транзакционных предложений SQL.

Обсуждение
При интерактивном запуске запросов из mysql (как в примерах предыдущего раздела) вы можете увидеть, успешно ли выполнено предложение, и на основе этой информации определить, следует зафиксировать или откатить изменения. Если же вы выполняете запросы в неинтерактивном сценарии SQL, хранящемся в файле, то все не так просто. У вас нет возможности выполнять фиксацию и откат в зависимости от результата выполнения предложения, так как в MySQL нет инструкции IF/THEN/ELSE, которая управляла бы логикой сценария. (Есть функция IF(), но это не то же самое.) Поэтому обработка транзакций обычно производится в программе, где можно применить средства базового языка для выявления ошибок и выполнения соответствующего действия. В этом разделе представлены основные сведения о том, как это делать. Следующие разделы посвящены особенностям реализации транзакций в программных интерфейсах Perl, PHP, Python и Java.

Все API поддерживают транзакции хотя бы в том смысле, что вы можете явно создавать транзакционные предложения SQL, такие как BEGIN и COMMIT. Однако некоторые API предлагают абстракцию транзакции, которая позволяет управлять поведением транзакций без непосредственного обращения к SQL. Этот подход скрывает детали и обеспечивает переносимость на другие базы данных, поддерживающие транзакции, синтаксис SQL которых может несколько отличаться от исходного. MySQL-интерфейсы Perl, Python и Java предоставляют такую абстракцию. В PHP вам придется создавать предложения SQL самостоятельно.

В следующих разделах для иллюстрации программной реализации транзакций рассматривается один и тот же пример. В них используется таблица t, исходное содержимое которой показывает, сколько денег было у каждого из двух человек:

+------+------+
| name | amt |
+------+------+
| Eve | 10 |
| Ida | 0 |
+------+------+

Примером транзакции будет простая финансовая операция перевода денег, использующая два предложения UPDATE, чтобы передать Иде 6 долларов Евы:

UPDATE money SET amt = amt - 6 WHERE name = 'Eve';
UPDATE money SET amt = amt + 6 WHERE name = 'Ida';


В результате таблица станет такой:

+------+------+
| name | amt |
+------+------+
| Eve | 4 |
| Ida | 6 |
+------+------+

Необходимо включить оба предложения в транзакцию, чтобы быть уверенным в том, что они вступают в силу одновременно. В отсутствие транзакции деньги Евы просто пропадут, если не выполнится второе предложение. Если же использовать транзакцию, то в случае невыполнения предложения таблица останется неизмененной.

Программы примеров для каждого языка хранятся в каталоге transactions дистрибутива recipes. Если сравнить их, то станет видно, что все они используют одну и ту же схему обработки транзакций:

• Предложения транзакции группируются в управляющую структуру вместе с операцией фиксации.

• Если статус управляющей структуры указывает на то, что она не была успешно завершена, транзакция откатывается.

Эту логику можно выразить следующим образом (block – это управляющая структура, используемая для группировки предложений):

block:
statement 1
statement 2
...
statement n
commit
if the block failed:
roll back

В Perl управляющая структура – это блок eval, который или выполняется успешно, или завершается с ошибкой, возвращая ее код. Python и Java используют блок try, который выполняется до конца, если транзакция была успешной. В случае ошибки генерируется исключение, которое инициирует выполнение соответствующего блока обработки ошибок для отката транзакции. В PHP нет подобных конструкций, но вы можете получить тот же результат, выполняя предложения транзакции и ее фиксацию в функции. Если функция завершается с ошибкой, выполняется откат.

Преимущество такого структурирования кода в том, что минимизируется количество проверок, необходимых для определения необходимости отката. Альтернативный вариант – проверка результата каждого предложения втранзакции и откат при ошибках отдельных предложений, быстро сделает ваш код беспорядочным и трудночитаемым.

При выполнении отката в языке, генерирующем исключения, необходимо помнить о том, что сам откат может не удаться, что вызовет новое исключение. Если вы не хотите иметь с этим дело, выполняйте откат в отдельном блоке с пустым обработчиком исключения. Именно так поступают программы на Perl, Python и Java.

Добавлено: 23 Июля 2018 20:48:03 Добавил: Андрей Ковальчук

Выполнение транзакций средствами SQL

Задача
Вам требуется создать ряд запросов, которые должны успешно выполниться или же не выполниться все вместе как единое целое.

Решение
Настройте режим автофиксации MySQL так, чтобы были разрешены транзакции, включающие несколько предложений, затем фиксируйте или откатывайте предложения в зависимости от успешности их выполнения.

Обсуждение
Этот раздел описывает предложения SQL, управляющие поведением транзакций в MySQL. В последующих разделах рассказано о программной реализации транзакций. Некоторые API требуют запуска соответствующих предложений SQL, другие предлагают собственный механизм, обеспечивающий работу с транзакциями без непосредственного участия SQL. Однако даже в последнем случае механизм API сопоставляет программным операциям транзакционные предложения SQL, так что прочтите этот раздел, чтобы лучше представлять себе то, что API делает для вас.

Обычно MySQL работает в режиме автофиксации, то есть результат каждого предложения фиксируется сразу после его выполнения. (Фактически, каждое предложение – это транзакция.) Чтобы выполнить транзакцию, состоящую из нескольких предложений, необходимо отключить режим автофиксации, выдать предложения, составляющие транзакцию, затем или принять или откатить сделанные изменения. В MySQL есть два способа сделать это:

• Создайте предложение BEGIN (или BEGIN WORK) для временного отключения режима автофиксации, затем запустите запросы, входящие в транзакцию, запишите результат в базу данных и завершите транзакцию, выполнив предложение COMMIT:

mysql> CREATE TABLE t (i INT) TYPE = InnoDB;
mysql> BEGIN;
mysql> INSERT INTO t (i) VALUES(1);mysql> INSERT INTO t (i) VALUES(2);
mysql> COMMIT;
mysql> SELECT * FROM t;


+------+
| i |
+------+
| 1 |
| 2 |
+------+

В случае ошибки не используйте COMMIT. Отменить транзакцию можно, выполнив предложение ROLLBACK. В следующем примере таблица t остается пустой после выполнения транзакции, так как предложения INSERT откатываются:

mysql> CREATE TABLE t (i INT) TYPE = InnoDB;
mysql> BEGIN;
mysql> INSERT INTO t (i) VALUES(1);
mysql> INSERT INTO t (x) VALUES(2);
ERROR 1054 at line 5: Unknown column 'x' in 'field list'
mysql> ROLLBACK;
mysql> SELECT * FROM t;

Empty set (0.00 sec)

• Другой способ группировки предложений – явное отключение режима автофиксации. Тогда каждое выдаваемое предложение становится частью текущей транзакции. Чтобы завершить транзакцию и начать следующую, выполните предложение COMMIT или ROLLBACK:

mysql> CREATE TABLE t (i INT) TYPE = InnoDB;
mysql> SET AUTOCOMMIT = 0;
mysql> INSERT INTO t (i) VALUES(1);
mysql> INSERT INTO t (i) VALUES(2);
mysql> COMMIT;
mysql> SELECT * FROM t;


+------+
| i |
+------+
| 1 |
| 2 |
+------+

Для возврата в режим автофиксации выполните такую команду:

mysql> SET AUTOCOMMIT = 1;

Добавлено: 23 Июля 2018 20:47:25 Добавил: Андрей Ковальчук

Проверка поддержки транзакций

Задача
Вы хотите использовать транзакции, но не знаете, поддерживает ли их ваш сервер MySQL.

Решение
Проверьте версию сервера, чтобы убедиться в том, что она достаточно свежая, и определите, какие типы таблиц поддерживаются. Или можно попы-таться создать таблицу транзакционного типа и посмотреть, действительно ли MySQL использует такой тип для определения таблицы.

Обсуждение
Для того чтобы использовать транзакции в MySQL, необходима достаточно свежая версия сервера, которая поддерживала бы соответствующие табличные дескрипторы, а приложения должны использовать таблицы транзакционного типа. Используйте следующий запрос для проверки версии вашего сервера:

mysql> SELECT VERSION();


+----------------+
| VERSION() |
+----------------+
| 4.0.4-beta-log |
+----------------+

Поддержка транзакций была введена в версии MySQL 3.23.17 с включением таблиц BDB (Berkeley DB) транзакционного типа. С того времени появился еще тип InnoDB, и, начиная с MySQL 3.23.29, можно работать с обоими типами таблиц. Я бы рекомендовал использовать последнюю из доступных версий MySQL. Поддержка транзакций (как и сам сервер) была значительно усовершенствована с момента выхода версии 3.23.29.

Даже если сервер достаточно современен для того, чтобы обеспечивать поддержку транзакций, может оказаться, что на самом деле он не обладает такими возможностями. Обработчики соответствующих типов таблиц могли быть не сконфигурированы при компиляции сервера. Может оказаться, что обработчики присутствуют, но отключены – если сервер был запущен с опцией --skip-bdb или --skip-innodb. Чтобы проверить доступность и статус обработчиков транзакционных таблиц, используйте предложение SHOW VARIABLES:

mysql> SHOW VARIABLES LIKE 'have_bdb';


+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| have_bdb | YES |
+----------------+-------+

mysql> SHOW VARIABLES LIKE 'have_innodb';


+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| have_innodb | YES |
+----------------+-------+

Приведенный результат запроса показывает, что таблицы BDB и InnoDB могут быть использованы. Если бы какой-то из запросов не возвращал значения или выводил в столбце Value что-то отличное от YES (NO или DISABLED), то соответствующий тип таблиц был бы недоступен.Еще один способ проверки доступности определенного типа таблиц – попытаться создать таблицу такого типа. Создайте предложение SHOW CREATE TABLE, чтобы посмотреть, какой тип MySQL использует фактически. Например, попробуем создать t как таблицу InnoDB, выполнив следующие предложения:

mysql> CREATE TABLE t (i INT) TYPE = InnoDB;
mysql> SHOW CREATE TABLE t\G

*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`i` int(11) default NULL
) TYPE=InnoDB

Если тип InnoDB доступен, то в последней части выражения SHOW будет содержаться TYPE=InnoDB. В противном случае MySQL создаст таблицу, используя тип по умолчанию – MyISAM, тогда последняя часть предложения будет выглядеть как TYPE=MyISAM. (Для проверки типа таблицы также можно применить SHOW TABLE STATUS.)

Если ваш сервер MySQL не содержит обработчиков транзакционных таблиц, необходимо заменить его. При установке MySQL из дистрибутива в исходных текстах инструкции инсталлятора подскажут вам, какие конфигурационные флаги следует использовать для разрешения соответствующих обработчиков. Если вы предпочитаете двоичный дистрибутив, проверьте, что устанавливаете тот из них, в который включены обработчики BDB или InnoDB.

После того как вы убедитесь в том, что сервер поддерживает соответствующие типы транзакционных таблиц, можно начинать использовать их в приложениях:

• Если вы пишете новое приложение, то можете сразу создать таблицы как таблицы транзакционного типа. Для этого нужно только добавить TYPE = тип_таблицы в конец предложения CREATE TABLE:

CREATE TABLE t1 (i INT) TYPE = BDB;
CREATE TABLE t2 (i INT) TYPE = INNODB;


• Если вы изменяете существующее приложение так, что появляется необходимость в применении транзакций для уже имеющихся таблиц, которые изначально не предназначались для транзакций, можно изменить тип таких таблиц. Например, типы ISAM и MyISAM относятся к нетранзакционным. Попытка использовать их для транзакций приведет к выводу некорректных результатов, так как таблицы не поддерживают операцию отката. Можно применить предложение ALTER TABLE для преобразования таблицы к транзакционному типу. Предположим, что t – это таблица MyISAM. Чтобы преобразовать ее к типу InnoDB, выполните:

ALTER TABLE t TYPE = INNODB;


Имейте в виду, что изменение типа таблицы для поддержки транзакций может изменить и другие аспекты ее поведения. Например, таблицы MyISAM обеспечивают более гибкую обработку столбцов AUTO_INCREMENT, чем другие типы. Если вы используете возможности последовательнос-тей, доступные только для MyISAM, то изменение типа таблицы может создать проблемы.

Если сервер не поддерживает транзакции и у вас нет возможности заменить его другим, можно достичь аналогичных результатов без использования транзакций. В некоторых случаях можно заблокировать таблицу на период выполнения нескольких предложений при помощи предложений LOCK и UNLOCK. Тогда другие клиентские приложения не смогут вмешаться в вашу работуоднако откатить какое-то неудавшееся предложение будет невозможно.

Добавлено: 23 Июля 2018 20:46:46 Добавил: Андрей Ковальчук

Удаление дубликатов из таблицы

Задача
Вы хотите удалить дубликаты из таблицы, чтобы она содержала только уникальные записи.

Решение
Выберите уникальные строки из таблицы в другую таблицу и замените ею исходную. Или добавьте в таблицу уникальный индекс, используя ALTER TABLE, в результате чего дубликаты исчезнут. Или примените DELETE ... LIMIT n для удаления всех, кроме одного, экземпляров повторяющихся строк.

Обсуждение
Если при создании таблицы вы забыли определить уникальный индекс, предотвращающий наличие дубликатов в таблице, то в дальнейшем вы можетестолкнуться с необходимостью как-то избавиться от дубликатов. Таблица cat_mailing из примеров предыдущих разделов как раз относится к подобным таблицам, так как содержит по несколько записей об одних и тех же людях:

mysql> SELECT * FROM cat_mailing ORDER BY last_name, first_name;


+-----------+--------------+-----------------------------+
| last_name | first_name | street |
+-----------+--------------+-----------------------------+
| Baxter | Wallace | 57 3rd Ave. |
| BAXTER | WALLACE | 57 3rd Ave. |
| Baxter | Wallace | 57 3rd Ave., Apt 102 |
| Brown | Bartholomew | 432 River Run |
| Isaacson | Jim | 515 Fordam St., Apt. 917 |
| McTavish | Taylor | 432 River Run |
| Pinter | Marlene | 9 Sunset Trail |
| Pinter | Marlene | 9 Sunset Trail |
+----------+---------------+-----------------------------+

Таблица содержит избыточные данные, и неплохо было бы удалить их, чтобы избежать повторной рассылки и снизить почтовые издержки. Есть несколько способов сделать это:

• Выберите уникальные строки таблицы в другую таблицу, затем используйте новую таблицу для замены исходной. В результате дубликаты будут удалены. Такой способ подходит, когда под дубликатами понимаются полностью совпадающие строки.

• Добавьте в таблицу уникальный индекс при помощи ALTER TABLE. Эта операция устранит повторяющиеся строки, основываясь на содержимом индексированных столбцов.

• Дубликаты из указанного набора повторяющихся строк можно удалить с помощью предложения DELETE ... LIMIT n, удалив все строки кроме одной.

В этом разделе подробно рассмотрены все перечисленные способы. Размышляя о том, какой из них выбрать в определенных условиях, помните, что применимость метода к конкретной задаче часто определяется ответами на два вопроса:

• Требуется ли, чтобы таблица имела уникальный индекс?

• Если столбец, в котором встречаются дубликаты, допускает использование NULL, удалит ли метод повторяющиеся значения NULL?

Удаление дубликатов путем замены таблицы
Одним из способов удаления дубликатов из таблицы является выбор ее уникальных записей в новую таблицу, имеющую такую же структуру. Затем исходная таблица заменяется новой. Если строка считается дубликатом другой строки только в случае их полного совпадения, то можно использовать для выбора уникальных строк SELECT DISTINCT:

mysql> CREATE TABLE tmp SELECT DISTINCT * FROM cat_mailing;
mysql> SELECT * FROM tmp ORDER BY last_name, first_name;


+-----------+--------------+-----------------------------+
| last_name | first_name | street |
+-----------+--------------+-----------------------------+
| Baxter | Wallace | 57 3rd Ave. |
| Baxter | Wallace | 57 3rd Ave., Apt 102 |
| Brown | Bartholomew | 432 River Run |
| Isaacson | Jim | 515 Fordam St., Apt. 917 |
| McTavish | Taylor | 432 River Run |
| Pinter | Marlene | 9 Sunset Trail |
+-----------+--------------+-----------------------------+

Этот способ работает в отсутствие индекса (хотя для больших таблиц он может быть медленным), а из таблиц, содержащих повторяющиеся значения NULL, он такие дубликаты удаляет. Обратите внимание на то, что в данном случае немного отличающиеся значениями street строки для Wallace Baxter считаются различными.

Если дубликаты определяются только относительно подмножества столбцов таблицы, создайте новую таблицу, имеющую уникальный индекс, а затем выберите в нее строки, используя INSERT IGNORE.

mysql> CREATE TABLE tmp (
-> last_name CHAR(40) NOT NULL,
-> first_name CHAR(40) NOT NULL,
-> street CHAR(40) NOT NULL,
-> PRIMARY KEY (last_name, first_name));
mysql> INSERT IGNORE INTO tmp SELECT * FROM cat_mailing;
mysql> SELECT * FROM tmp ORDER BY last_name, first_name;


+-----------+--------------+-----------------------------+
| last_name | first_name | street |
+-----------+--------------+-----------------------------+
| Baxter | Wallace | 57 3rd Ave. |
| Brown | Bartholomew | 432 River Run |
| Isaacson | Jim | 515 Fordam St., Apt. 917 |
| McTavish | Taylor | 432 River Run |
| Pinter | Marlene | 9 Sunset Trail |
+----------+---------------+-----------------------------+

Индекс не допускает вставки записей с дублирующимися значениями ключа в tmp, а IGNORE указывает MySQL на то, что не следует завершаться с ошибкой в случае обнаружения дубликата. Недостатком метода является то, что если индексированные столбцы могут содержать значения NULL, то необходимо использовать индекс UNIQUE вместо PRIMARY KEY, и тогда не будут удаляться повторяющиеся ключи со значениями NULL (индекс UNIQUE допускает несколько значений NULL).

После создания новой таблицы tmp, содержащей только уникальные строки, используйте ее для замены исходной таблицы cat_mailing. В результате cat_mailing больше не будет содержать дубликатов:

mysql> DROP TABLE cat_mailing;
mysql> ALTER TABLE tmp RENAME TO cat_mailing;


Удаление дубликатов путем добавления индекса
Чтобы удалить дубликаты из таблицы непосредственно «на месте», добавьте в таблицу уникальный индекс при помощи ALTER TABLE, используя ключевое слово IGNORE для того, чтобы указать на необходимость удаления записей с дублирующимися значениями ключа в процессе построения индекса. Исходная таблица cat_mailing без индекса выглядит так:

mysql> SELECT * FROM cat_mailing ORDER BY last_name, first_name;


+-----------+------------+-------------------------------+
| last_name | first_name | street |
+-----------+------------+-------------------------------+
| Baxter | Wallace | 57 3rd Ave. |
| BAXTER | WALLACE | 57 3rd Ave. |
| Baxter | Wallace | 57 3rd Ave., Apt 102 |
| Brown | Bartholomew | 432 River Run |
| Isaacson | Jim | 515 Fordam St., Apt. 917 |
| McTavish | Taylor | 432 River Run |
| Pinter | Marlene | 9 Sunset Trail |
| Pinter | Marlene | 9 Sunset Trail |
+----------+---------------+-----------------------------+

Добавьте уникальный индекс и посмотрите, как это подействует на содержимое таблицы:

mysql> ALTER IGNORE TABLE cat_mailing
-> ADD PRIMARY KEY (last_name, first_name);
mysql> SELECT * FROM cat_mailing ORDER BY last_name, first_name;


+-----------+--------------+-----------------------------+
| last_name | first_name | street |
+-----------+--------------+-----------------------------+
| Baxter | Wallace | 57 3rd Ave. |
| Brown | Bartholomew | 432 River Run |
| Isaacson | Jim | 515 Fordam St., Apt. 917 |
| McTavish | Taylor | 432 River Run |
| Pinter | Marlene | 9 Sunset Trail |
+----------+---------------+-----------------------------+

Если индексированные столбцы могут содержать значения NULL, то вместо индекса PRIMARY KEY нужно использовать UNIQUE. Тогда индекс не будет удалять повторяющиеся значения NULL.

Удаление дубликатов определенной строки
Начиная с версии MySQL 3.22.7, вы можете использовать инструкцию LIMIT для ограничения действия предложения DELETE на подмножество строк, которое оно в противном случае удалило бы. В такой форме предложение можно применять для удаления повторяющихся записей. Предположим, что у вас есть таблица t с таким содержимым:

+-------+
| color |
+-------+
| blue |
| green |
| blue |
| blue |
| red |
| green |
| red |
+-------+

В таблице трижды присутствует blue (голубой) и дважды – green (зеленый) и red (красный). Чтобы удалить дополнительные экземпляры каждого цвета, выполните:

mysql> DELETE FROM t WHERE color = 'blue' LIMIT 2;
mysql> DELETE FROM t WHERE color = 'green' LIMIT 1;
mysql> DELETE FROM t WHERE color = 'red' LIMIT 1;
mysql> SELECT * FROM t;


+-------+
| color |
+-------+
| blue |
| green |
| red |
+-------+

Прием работает в отсутствие уникального индекса и удаляет повторяющиеся значения NULL. Его удобно использовать, если вам нужно удалить дубликаты только для определенного множества строк таблицы. Однако если следует удалить много различных наборов дубликатов, подобную процедуру вряд ли захочется проводить вручную. Процесс можно автоматизировать, используя методы для выявления дубликатов. В том рецепте мы создали функцию make_dup_count_query() для генерирования запроса, подсчитывающего количество повторяющихся значений в указанном множестве столбцов таблицы:

sub make_dup_count_query
{
my ($tbl_name, @col_name) = @_;
return (
"SELECT COUNT(*)," . join (",", @col_name)
. "\nFROM $tbl_name"
. "\nGROUP BY " . join (",", @col_name)
. "\nHAVING COUNT(*) > 1"
);
}


Можно написать еще одну функцию delete_dups(), использующую make_dup_count_query() для определения того, какие значения таблицы повторяются и как часто. Из этой информации можно понять, сколько дубликатов следует удалить при помощи DELETE … LIMIT n, чтобы в таблице остался только один экземпляр записи. Функция delete_dups() выглядит так:

sub delete_dups
{
my ($dbh, $tbl_name, @col_name) = @_;
# Создать и выполнить запрос, который находит дубликаты
my $dup_info = $dbh->selectall_arrayref (
make_dup_count_query ($tbl_name, @col_name)
);
return unless defined ($dup_info);
# Для каждого повторяющегося множества значений удалить все вхождения строк,
# содержащих эти значения, кроме одного
foreach my $row_ref (@{$dup_info})
{
my ($count, @col_val) = @{$row_ref};
next unless $count > 1;
# Построить строку условия для сравнения значений, не забывая про IS NULL
my $str;
for (my $i = 0; $i < @col_name; $i++)
{
$str .= " AND " if $str;
$str .= defined ($col_val[$i])
? "$col_name[$i] = " . $dbh->quote ($col_val[$i])
: "$col_name[$i] IS NULL";
}
$str = "DELETE FROM $tbl_name WHERE $str LIMIT " . ($count - 1);
$dbh->do ($str);
}
}


Предположим, что у нас есть таблица employee со следующими записями:

mysql> SELECT * FROM employee;


+-----------+--------------+
| name | department |
+-----------+--------------+
| Fred | accounting |
| Fred | accounting |
| Fred | accounting |
| Fred | accounting |
| Bob | shipping |
| Mary Ann | shipping |
| Mary Ann | shipping |
| Mary Ann | sales |
| Mary Ann | sales |
| Mary Ann | sales |
| Mary Ann | sales |
| Mary Ann | sales |
| Mary Ann | sales |
| Boris | NULL |
| Boris | NULL |
+-----------+-------------+

Для того чтобы использовать функцию delete_dups() для удаления дубликатов в столбцах name и department таблицы employee, вызовите ее так:

delete_dups ($dbh, "employee", "name", "department");


Функция delete_dups() вызывает функцию make_dup_count_query() и выполняет формируемый ею запрос SELECT. Для таблицы employee этот запрос выводит такой результат:

+-------------+------------+--------------+
| COUNT(*) | name | department |
+-------------+------------+--------------+
| 2 | Boris | NULL |
| 4 | Fred | accounting |
| 6 | Mary Ann | sales |
| 2 | Mary Ann | shipping |
+-------------+------------+--------------+

Функция delete_dups() использует данную информацию для формирования следующих предложений DELETE:

DELETE FROM employee
WHERE name = 'Boris' AND department IS NULL LIMIT 1
DELETE FROM employee
WHERE name = 'Fred' AND department = 'accounting' LIMIT 3
DELETE FROM employee
WHERE name = 'Mary Ann' AND department = 'sales' LIMIT 5
DELETE FROM employee
WHERE name = 'Mary Ann' AND department = 'shipping' LIMIT 1


В целом, способ с использованием DELETE ... LIMIT n, вероятно, работает медленнее, чем удаление дубликатов при помощи создания второй таблицы или добавления уникального индекса. Эти методы хранят данные на сервере и позволяют ему выполнить всю работу. DELETE ... LIMIT n требует большого объема взаимодействия клиента с сервером, так как использует запрос SELECT для извлечения информации о дубликатах, а затем ряд предложений DELETE для удаления копий дублирующихся строк.

Добавлено: 23 Июля 2018 20:45:15 Добавил: Андрей Ковальчук

Устранение дубликатов из результата самообъединения

Задача
Самообъединения часто выводят строки, близкие к дубликатам, то есть строки, которые содержат одинаковые значения, но в другом порядке. В этом случае запрос SELECT DISTINCT не удалит дубликаты.

Решение
Выберите значения полей записи в определенном порядке, чтобы сделать идентичными строки с повторяющимся набором значений. Тогда можно будет использовать SELECT DISTINCT для устранения дубликатов. Или можно извлечь строки так, что близкие к дубликатам строки просто не будут выбраны.

Обсуждение
Самообъединения могут формировать строки, которые являются повторяющимися (в том смысле, что содержат одни и те же значения), хотя и не идентичными. Рассмотрим запрос, который применяет самообъединение для поиска всех пар штатов, вступивших в Союз в один и тот же год:

mysql> SELECT YEAR(s2.statehood) AS year, s1.name, s2.name
-> FROM states AS s1, states AS s2
-> WHERE YEAR(s1.statehood) = YEAR(s2.statehood)
-> AND s1.name != s2.name
-> ORDER BY year, s1.name, s2.name;


+-----+----------------+----------------+
| year | name | name |
+-----+----------------+----------------+
| 1787 | Delaware | New Jersey |
| 1787 | Delaware | Pennsylvania |
| 1787 | New Jersey | Delaware |
| 1787 | New Jersey | Pennsylvania |
| 1787 | Pennsylvania | Delaware |
| 1787 | Pennsylvania | New Jersey |
...
| 1912 | Arizona | New Mexico |
| 1912 | New Mexico | Arizona |
| 1959 | Alaska | Hawaii |
| 1959 | Hawaii | Alaska |
+-----+----------------+---------------+

Условие инструкции WHERE, требующее, чтобы названия штатов в паре не были идентичными, устраняет тривиальные совпадения, показывающие, что каждый штат вступил в Союз в том же году, что и он сам. Но каждая из оставшихся пар штатов опять-таки выводится дважды. Например, одна строка содержит Delaware и New Jersey, а другая – New Jersey и Delaware. Каждая такая пара строк может считаться дубликатами, так как они содержат одинаковые значения. Однако из-за того, что значения приведены в строкахв разном порядке, строки не идентичны, и от таких повторений невозможно избавиться при помощи добавления в запрос ключевого слова DISTINCT.

Одним из путей решения проблемы может быть задание определенного постоянного порядка следования столбцов в строке. Для этого выбирайте названия с помощью двух выражений, которые располагают первыми в списке вывода меньшие значения:

IF(val1<val2,val1,val2) AS lesser_value,
IF(val1<val2,val2,val1) AS greater_value


Применение этого приема к запросу о парах штатов выводит следующий результат (выражения обеспечивают отображение названий штатов в лексическом порядке внутри каждой строки):

mysql> SELECT YEAR(s2.statehood) AS year,
-> IF(s1.name<s2.name,s1.name,s2.name) AS state1,
-> IF(s1.name<s2.name,s2.name,s1.name) AS state2
-> FROM states AS s1, states AS s2
-> WHERE YEAR(s1.statehood) = YEAR(s2.statehood)
-> AND s1.name != s2.name
-> ORDER BY year, state1, state2;


+-----+-------------+----------------+
| year | state1 | state2 |
+-----+-------------+----------------+
| 1787 | Delaware | New Jersey |
| 1787 | Delaware | New Jersey |
| 1787 | Delaware | Pennsylvania |
| 1787 | Delaware | Pennsylvania |
| 1787 | New Jersey | Pennsylvania |
| 1787 | New Jersey | Pennsylvania |
...
| 1912 | Arizona | New Mexico |
| 1912 | Arizona | New Mexico |
| 1959 | Alaska | Hawaii |
| 1959 | Alaska | Hawaii |
+-----+-------------+----------------+

В выводе все еще присутствуют дубликаты, но теперь повторяющиеся строки идентичны, и ненужные копии можно удалить, добавив в запрос DISTINCT:

mysql> SELECT DISTINCT YEAR(s2.statehood) AS year,
-> IF(s1.name<s2.name,s1.name,s2.name) AS state1,
-> IF(s1.name<s2.name,s2.name,s1.name) AS state2
-> FROM states AS s1, states AS s2
-> WHERE YEAR(s1.statehood) = YEAR(s2.statehood)
-> AND s1.name != s2.name
-> ORDER BY year, state1, state2;


+-----+--------------+----------------+
| year | state1 | state2 |
+-----+--------------+----------------+
| 1787 | Delaware | New Jersey |
| 1787 | Delaware | Pennsylvania |
| 1787 | New Jersey | Pennsylvania |
...
| 1912 | Arizona | New Mexico |
| 1959 | Alaska | Hawaii |
+-----+-------------+----------------+

Альтернативный подход к удалению неидентичных дубликатов заключается не в их выявлении и удалении, а в выборе строк таким образом, чтобы в результате запроса появлялась только одна строка из каждой пары. Это избавляет от необходимости переупорядочивания значений в строках и использования ключевого слова DISTINCT. Для запроса о парах штатов, выбор только тех строк, где название первого штата лексически меньше второго, автоматически удаляет строки, в которых имена присутствуют в обратном порядке:

mysql> SELECT YEAR(s2.statehood) AS year, s1.name, s2.name
-> FROM states AS s1, states AS s2
-> WHERE YEAR(s1.statehood) = YEAR(s2.statehood)
-> AND s1.name < s2.name
-> ORDER BY year, s1.name, s2.name;


+-----+--------------+----------------+
| year | name | name |
+-----+--------------+----------------+
| 1787 | Delaware | New Jersey |
| 1787 | Delaware | Pennsylvania |
| 1787 | New Jersey | Pennsylvania |
...
| 1912 | Arizona | New Mexico |
| 1959 | Alaska | Hawaii |
+-----+-------------+----------------+

Добавлено: 23 Июля 2018 20:43:50 Добавил: Андрей Ковальчук

Устранение дубликатов из результата запроса

Задача
Вы хотите выбрать строки в результирующее множество так, чтобы оно не содержало дубликатов.

Решение
Используйте SELECT DISTINCT.

Обсуждение
Строки результирующего множества могут содержать дубликаты. Обычно так бывает при выборе только подмножества столбцов таблицы, так как уменьшается количество информации, которая могла бы отличать одну строку от другой. Чтобы результат содержал только уникальные строки, удалитедубликаты, добавив ключевое слово DISTINCT, которое указывает MySQL на необходимость возврата одного экземпляра для каждого набора значений столбцов. Например, если вы выбираете названия столбцов из таблицы cat_mailing, не используя DISTINCT, то выводится несколько дубликатов:

mysql> SELECT last_name, first_name
-> FROM cat_mailing ORDER BY last_name, first_name;


+-----------+--------------+
| last_name | first_name |
+-----------+--------------+
| Baxter | Wallace |
| BAXTER | WALLACE |
| Baxter | Wallace |
| Brown | Bartholomew |
| Isaacson | Jim |
| McTavish | Taylor |
| Pinter | Marlene |
| Pinter | Marlene |
+-----------+--------------+

Если добавить DISTINCT, дубликаты исчезают:

mysql> SELECT DISTINCT last_name, first_name
-> FROM cat_mailing ORDER BY last_name;


+-----------+---------------+
| last_name | first_name |
+-----------+---------------+
| Baxter | Wallace |
| Brown | Bartholomew |
| Isaacson | Jim |
| McTavish | Taylor |
| Pinter | Marlene |
+-----------+---------------+

Альтернативой инструкции DISTINCT является инструкция GROUP BY, содержащая имена выбираемых столбцов. В результате удаляются дубликаты и выводятся только уникальные комбинации значений указанных столбцов:

mysql> SELECT last_name, first_name FROM cat_mailing
-> GROUP BY last_name, first_name;


+-----------+--------------+
| last_name | first_name |
+-----------+--------------+
| Baxter | Wallace |
| Brown | Bartholomew |
| Isaacson | Jim |
| McTavish | Taylor |
| Pinter | Marlene |
+-----------+--------------+

Добавлено: 23 Июля 2018 20:43:01 Добавил: Андрей Ковальчук