Связи таблиц в MySQL
Автор: Дмитрий Лебедев
Первоначально я предполагал сделать выпуск "Народной самодеятельности" со сборником ошибок, допущеных в основном мною :). Кое-что я собирал из статей о традиционных ошибках и т.п. Но жизнь оказывается куда интересней! Пишу про очередную самодеятельность прямо по горячим следам.
Итак, некоторый человек, назовем его Вася, спрашивает Общественность форума про MySQL.
В.: Не совсем понятно как создать связь между таблицами, чтобы в поле первой автоматически вставлялись данные второй (есно выборочно и есно по ИД).
Вариант типа SELECT db.user, db.delete_priv, user.user, user.delete_priv FROM db,user WHERE db.user = user.user не совсем подходит, так как связью это можно назвать с большой натяжкой...
O.: Честно говоря, не понимаю, почему эта связь тебе не подходит? Чем эта связь натянута?
В.: Ну не нравится мне связь, основанная на синтаксисе запросов... хотелось бы чего-нибудь более существенного, иначе я вообще не вижу смысла в ключах и индексах.
O.: Более существенное - ты имеешь в виду графический интерфейс как в MS Access?
К сожалению, сам Access работает точно так же — достаточно нажать кнопочку "SQL", и вы увидите эти связи "с большой натяжкой".
В.: Просто мне проще программно все это делать, благо таблицы небольшие... Раз в самом MySQL это не работает.
O.: Через вложенные циклы, рекурсии? Скажи зачем тогда тебе вообще база данных и таблицы?
В.: Никаких вложенных циклов и рекурсий — читаю н-ое колмчество массивов и с ними уже работаю.... А делать связь на основе селекта — не совсем то что мне нужно...
O.: Правильно... храни все в файликах, тогда вообще вопросов не будет.
В общем, считаю своим долгом разложить по полочкам связи таблиц.
Кстати, насчёт связей - вот мнение признанного авторитета - Voodoo ;) : более существенное - это поддержка reference www.mysql.com/documentation/mysql/commented/manual.php?section=CREATE_TABLE
create table..... reference_definition: REFERENCES tbl_name [(index_col_name,...)] [MATCH FULL | MATCH PARTIAL] [ON DELETE reference_option] [ON UPDATE reference_option]
The FOREIGN KEY, CHECK, and REFERENCES clauses don't actually do anything. The syntax for them is provided only for compatibility, to make it easier to port code from other SQL servers and to run applications that create tables with references. See section 5.4 Functionality Missing from MySQL.
Связь без большой натяжки
Вообще, никакой натяжки в описании связей в запросе не было и нет - испокон веков БД работали именно так. Access со стрелочками и формопостроителями появился намного позднее.
Связь между таблицами - суть реляционной базы данных. В идеале вне базы данных не держится никакая информация. Внутри базы разные по сути вещи разделяются на разные таблицы - например, сообщения и форумы, сообщения и их авторы (если есть регистрация участников), возможно даже в отдельную таблицу выносится разрешения доступ к форумам персонально для каждого участника. При этом данные лежат там, где надо, не смешиваются друг с другом, и не повторяются лишний раз. Это и есть основной смысл реляционных БД. За исключением сложных задач (например, построить дерево обсуждений форума), выборка данных производится одним запросом. Никакие массивы использовать не надо.
Как делать неправильно
Например, вот так:
$res1 = mysql_query("SELECT id, name FROM rubs");
while ($row = mysql_fetch_row($res1))
$rub[$row[0]] = $row[1];
Из запроса получены имена рубрик и записаны в массив $rub.
$res2 = mysql_query("SELECT id, url, name, rub FROM sites WHERE какое-то-там-условие");
while ($row = mysql_fetch_array($res2)) {
echo "<a href=", $row["url"], ">", $row["name"], "</a>";
echo "(рубрика <a href=rub.phtml?id=", $row["id"], ">";
echo $rub[$row["rub"]], "</a><br>";
};
Теперь выбираются новости, и вместо выбранного номера рубрики вставляем соответствующий элемент массива рубрик.
На самом деле, можно избавить себя от необходимости тащить сквозь всю программу этот массив $rub (а если у нас к рубрикам обращаются функции - что, через GLOBAL брать?), от возможности ошибиться с $rub[$row["rub"]] - если на странице несколько подобных запросов, то сделать опечатку где-нибудь легко.
Кроме того, под массив $rub требуется некоторый объем памяти (а если рубрик много?). В третьей версии PHP такой скрипт будет выполняться дольше, чем при использовании объединений таблиц, потому что он интерпретирует программу построчно при выполнении (в отличие от 4-го, который компилирует программу и только потом выполняет).
Как надо
В приведённом выше примере можно применить объединение таблиц и избавиться от описанных недостатков.
$res = mysql_query("SELECT sites.id, url, sites.name as sitename, rubs.name as rubsname,
rubs.id as rub_id FROM sites, rubs WHERE sites.rub=rubs.id
и-какое-то-там-условие");
while ($row = mysql_fetch_array($res2)) {
echo "<a href=", $row["url"], ">", $row["sitename"], "</a>";
echo "(рубрика <a href=rub.phtml?id=", $row["rub_id"], ">";
echo $row["rubsname"], "</a><br>";
};
Итак, здесь лучше использовать запрос "SELECT sites.id, url, sites.name as sitename, rubs.name as rubsname, rubs.id as rub_id FROM sites, rubs WHERE sites.rub=rubs.id". Получается, что мы имеем готовый массив, заботимся о выводе только его элементов и пишем меньше кода.
Синтаксис объединений таблиц
Простое соединение - INNER JOIN:
SELECT <fields> FROM table1 INNER JOIN table2 ON table1.field1=table2.field
или
SELECT <fields> FROM table1, table2 WHERE table1.field1=table2.field2
или
SELECT <fields> FROM table1 INNER JOIN table2 USING (field1)
если таблицы объединяются по полю field1.
В таком соединении выбираются только те строки таблиц, которые соответствуют условию объединения - равенство значений полей. Если для строки table1 нет соответствующей строки из table2, строка не попадает в итог запроса. Если же надо подсчитать количество сайтов в рубрике (продолжаю пример с каталогом), такой запрос не совсем подходит - в списке появятся только рубрики, в которых есть сайты. Для подобной операции нужно использовать LEFT JOIN.
SELECT <fields> FROM table1 LEFT JOIN table2 ON table1.field1=table2.field2
или
SELECT <fields> FROM table1 LEFT JOIN table2 USING (field1)
если таблицы объединяются по полю field1.
При этом соответствующей строки в table2 может и не быть, тогда в полях из table2 мы получим NULL, а если это групповая операция, как в случае с количеством сайтов в рубрике, тогда в поле будет 0:
SELECT rubs.id, name, COUNT(sites.id) AS sites FROM rubs LEFT JOIN sites ON rubs.id=sites.rub GROUP BY rubs.id
Заметьте: поля id есть в обеих таблицах, поэтому в их обозначении надо использовать имя таблицы. Кстати, если при объединении не используются групповые операции, всё равно лучше менять имя поля оператором AS, чтобы не возникало путаницы.
