Профессиональная разработка Web-приложений.  
Боишься нашего дизайна?
Новости
PDF журнал
Участники проектa
Сотрудничество
Ссылки
Карта сайта
Комментарии
Комментарии к статье
Добавить комментарий
Обсудить на форуме
Информация об авторе
Оценка статьи

Народная самодеятельность — связи таблиц в 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, чтобы не возникало путаницы.




For comment register here
   2000-12-28 07:16
только изучаю PHP+MySQL, Ваша статья оказалась крайне полезной, спасибо!
SELECT <fields> FROM table1 LEFT JOIN table2 USING (field1) этой команды я не знал до прочтения (почему то в MySQL Reference Manual ее не нашел, но теперь буду пользовать...

   2001-01-10 22:50
Уважаемый detail. Все это красиво - спору нет. и знакомом с детсва. Но не замечал ли ты, что два простых запроса обрабатываются Мускулом быстрее чем один сложный!?... понаблюдай, советую.
с уважением, Андресон.

   Unknown 2001-01-10 23:43
Это так, только в задачу входит количество строк в результате выполнения запроса. Так, например, выборка всех логов по главной странице сайта заняла 2.25 секунд, а count(*) этих строк заняла всего 0.22 секунды. Если вы предлагаете делать два простых запроса вместо одного сложного, а потом их результаты обрабатывать черезе php, то imho это зря. Такая схема будет дольше работать, если строк много.

   2001-01-11 08:05
to: Андресон
А что понимается под двумя простыми запросами и одним сложным?
Можно мне пример привести, где сложный выполняется медленее?
Я лично использую весьма сложные запросы (было и по 10 таблиц)
И все они выполняются достаточно быстро.
Я видел тормоза если в условиях есть оператор OR - он очень плохо оптимизируется MySQL'ем.
А пока связи типа AND MySQL оптимизирует запросы очень хорошо.
Правда я в последнее время живу на v 3.23.

   2001-01-15 13:33
Join это просто, код выглядит прекрасно. Я делал обработчик базы 200 тыс записей, объединение таблиц жутко тормозило, пришлось все переделать через простые запросы. Прирост скорости 10-20 раз.

   Unknown 2002-10-28 16:25
Хех) Спасибо Вам за "разбор полетов")
Я как раз пишу на php под mysql и оказываеться многих тонкостей не знал) Скажите, а есть ли у Вас какая либо книга, которую можно было бы скачать в Электронном варианте?

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

   2004-03-22 16:05
Так постойте, зачем кого-то критиковать, если до конца все и не известно?
Сначала необходимо отметить, что MySQL полностью поддерживает лишь одну транзакционную таблицу InnoDB. Кроме того необходима в таблице, которая ссылается на первичный ключ, явная индексация поля внешнего ключа. MySQL версии 3,23 поддерживает только действия при удалении, и даже версии четвертой MySQL очень плохо поддерживают каскадирование при апдейте (если вообще поддерживают).
Все было бы очень прекрасно, если бы большинство хостеров либо ставила MySQL с поддержкой InnoDB, либо нестабильные четвертые версии СУБД. Ноэтого не происходит. Так что пока рано отказываться от ручной работы при работе с базами и цитировать тексты чужих людей, тем более вырезанных из мануаля.

   2005-01-13 00:10
> объединение таблиц жутко тормозило
для быстрого объединения на полях по которых объединяют нужны индексы. EXPLAIN SELECT вам верный друг и товарищ. И ещё запросы можно оптимизировать, почитайте раздел мана "5.2.6 How MySQL Optimises LEFT JOIN and RIGHT JOIN". Ещё, возможно, если есть другие более узкие условия отбора WHERE - поставить их на первое место а потом JOIN.
Никогда не поверю что програмно на клиенте вы сделаете объединение лучше (ефективнее) чем это сделает СУБД.

Четыре причины, почему не надо браться за написание большого приложения ради обучения языку

 
 
 
    © 1997-2008 PHPClubTeam
[]