|
Задавайте вопросы, мы ответим
Вы не зашли.
Приветствую всех. Борюсь со следующей проблемой.
Имеются две таблицы:
_test_table(field1,field2,created,modified,base_id,other)
CREATE TABLE `_test_table` ( `field1` longtext NOT NULL, `field2` longtext NOT NULL, `created` datetime NOT NULL default '0000-00-00 00:00:00', `modified` datetime NOT NULL default '0000-00-00 00:00:00', `base_id` bigint(20) NOT NULL auto_increment, `other` varchar(20) NOT NULL default 'sometext', UNIQUE KEY `base_id` (`base_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=0;
_test_table_lng(field1,field2,created,modified,lang,base_id)
CREATE TABLE `_test_table_lng` ( `field1` longtext NOT NULL, `field2` longtext NOT NULL, `created` datetime NOT NULL default '0000-00-00 00:00:00', `modified` datetime NOT NULL default '0000-00-00 00:00:00', `lang` varchar(3) NOT NULL default '', `base_id` bigint(20) NOT NULL default '0' ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Как видно, вторая таблица содержит локализацию данных некоторых столбцов из первой таблицы на разных языках. Во второй таблице первичным ключом является комбинация столбцов base_id и lang.
Пусть имеется следующее наполнение:
_test_table ----------------------------------------------------------------------------------------------------------------------+ field 1 | field 2 | created | modofied | base_id | other | ----------------------------------------------------------------------------------------------------------------------- default lang fld1 | default lang fld2 | 2008-04-02 00:00:00 | 2008-04-02 00:00:01 | 1 | other text default lang | ----------------------------------------------------------------------------------------------------------------------+ _test_table_lng ---------------------------------------------------------------------------------------------------+ field 1 | field 2 | created | modofied | lang | base_id | ---------------------------------------------------------------------------------------------------- русский fld1 | русский lang fld2 | 2008-04-02 00:00:00 | 2008-04-02 00:00:02 | rus | 1 | ---------------------------------------------------------------------------------------------------- deutch fld1 | deutch lang fld2 | 2008-04-02 00:00:00 | 2008-04-02 00:00:03 | deu | 1 | ---------------------------------------------------------------------------------------------------+
Выборка статьи происходит по ее идентификатору (base_id) и языку (lang).
Хочется достичь следующего: при выборке языка, отличного от языка по-умолчанию происходила бы выборка локализованных столбцов из таблицы _test_table_lng и остальных нелокализованных столбцов из таблицы _test_table.
В результате запроса по id и lang должна быть получена таблица с одной строкой со следующими колонками:
из второй таблицы, т.к. имеется локализация | из первой таблицы, т.к. нет локализации | ----------------------------------------------------------+-----------------------------------------+ field 1 | field 2 | created | modofied | lang | base_id | other | ----------------------------------------------------------+-----------------------------------------+
В идеале: название и количество столбцов в обоих таблиц не известно. Известно лишь то, что все столбцы второй таблицы являются локализованными, т.е. при выборке статьи требуется выбрать все столбцы из второй таблицы + те столбцы из первой таблицы, которых нет во второй.
В реале: поскольку средств для определения количества и названий столбцов (show columns адекватным не считаю в данной ситуации) не имеется, то возможна ситуация, когда мы знаем....
а) все столбцы второй таблицы
б) все нелокализованные столбцы первой таблицы, т.е. разницу в столбцах между первой и второй таблицей (уникальные столбцы первой таблицы).
Хотел бы выслушать мнения на счет того, как реализовать запрос статьи по id и lang в описываемой ситуации (желательно в идеале 
). Один человек мне подсказывал, что это можно сделать при помощи запросов LEFT INNER JOIN, но у меня как-то вяло получилось.
Заранее спасибо! Очень надеюсь на вашу помощь.
Неактивен
1. Во второй таблице нет PK, так что фраза, что он там есть, - неверна 
2. LEFT INNER JOIN не бывает, бывает LEFT OUTER JOIN
3. Особого смысла держать колонки типа field1 в первой таблице нет - они никогда не будут использоваться.
4. Если Вас интересует принципиальная возможность - она есть. Но она будет работать очень небыстро.
a. Список столбцов в таблице можно получить запросом типа
SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='mysql' AND TABLE_NAME='user';
б. Получившиеся пунктом а запросы можно объединить в одну строку-запрос и выполнить через PREPARE.
5. Ну и, разумеется, если Вы хотите получить что-то более-менее быстрое, нужно фиксировать набор столбцов
Неактивен
Огромное спасибо за комментарий!
Во второй таблице нет PK, так что фраза, что он там есть, - неверна
Он там есть, просто я не весь запрос скопировал. )) иначе смысла в этой таблице нет.
Если бы не было первой таблицы, то запись во второй можно идентифицировать по составному ключу (base_id, lang). Если есть первая, то, наверно, это уже внешний ключ получается...
Особого смысла держать колонки типа field1 в первой таблице нет - они никогда не будут использоваться
Вы правы, поскольку я потом подумал, что рациональнее держать все локализации в одной таблице и не делать язык по-умолчанию, а в первой таблице хранить только общие нелокализуемые поля.
Если Вас интересует принципиальная возможность - она есть. Но она будет работать очень небыстро.
a. Список столбцов в таблице можно получить запросом типа
SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='mysql' AND TABLE_NAME='user';
б. Получившиеся пунктом а запросы можно объединить в одну строку-запрос и выполнить через PREPARE.
Да уж, действительно, легче через серверный скрипт прогнать )))
У меня была идея выполнить следующую последовательность:
а. Выбрать запросом SHOW COLUMNS FROM _test_table и SHOW COLUMNS FROM _test_table_lng
б. В серверном скрипте преобразовать выборки в массивы и выполнить их пересечение
в. Из полученного пересечения взять список столбцов и двумя селектами выбрать записи из двух таблиц
но я тогда думал, что есть красивый способ сделать это парой SQL-запросов. Как оказалось, нет ))
Ну и, разумеется, если Вы хотите получить что-то более-менее быстрое, нужно фиксировать набор столбцов
Если б все было так просто в нашей жизни )) я бы тогда тему эту не начинал
LEFT INNER JOIN не бывает, бывает LEFT OUTER JOIN
Стыжусь, не часто с JOIN-выборками приходится дело иметь. Хотя вроде такие серьезные вещи делаю типа движка для CMS...
Но все равно, такой нормализации до 5NF я никогда не делал. Я считаю, что лучше пусть будет избыточность в некоторых местах, чем пострадает время доступа.
Еще раз огромное вам спасибо!
P.S. Сейчас пришла одна замечательная мысль. Если хранить все локализованные данные во второй таблце, а уникальные - в первой, то зачем тогда весь этот огород...
SELECT * FROM _test_table LEFT JOIN _test_table_lng USING (base_id) WHERE _test_table.base_id=1 AND _test_table_lng.lang='rus'
В этом случае будет избыточность в виде повторяющегося столбца base_id. Хотя, разве можно назвать избыточностью лишний integer в выборке из одной записи?
Отредактированно Gintonic (04.04.2008 11:00:37)
Неактивен
Вы столкнулись с необходимостью компромисса между производительностиью и нормализацией. Это вполне естественно, что нормализацией приходится частично пожертвовать ради быстродействия, но
1. оптимизировать нужно только узкие места (то есть те запросы, которые проявили себя или обещают проявить себя как замедляющие приложение)
2. денормализацию нужно проводить только после того, как проведена нормализация, хотя бы до 3-й нормальной формы (в случае 2 таблиц это не так актуально)
3. когда вводите избыточность, убедитесь, что у Вас есть свой механизм контроля за целостностью данных
Неактивен
Спасибо за ценные замечания, с Вами полностью согласен.
Естественно, контроль целостности имеется на уровне скриптов PHP. Отнимает время, конечно, для кодирования, но надо чем-то жертвовать.
P.S. и это мы обсуждаем в форуме "Раздел для начинающих"... ))
P.P.S. Возвращаясь к обсуждавшимся ранее таблицам, хочу отметить следующий факт: после того, как был сделан запрос
SELECT * FROM _test_table LEFT JOIN _test_table_lng USING (base_id) WHERE _test_table.base_id=1 AND _test_table_lng.lang='rus'
при помощи механизма mysqli в PHP, при обработке результата (mysqli_fetch_row/array/assoc) этот механизм отбросит все повторяющиеся столбцы в выборке (т.к. названия колонок являются ключами в массиве результата; если будут повторения, то невозможно идентифицировать элемент в массиве). Приятная неожиданность.
Неактивен
в fetch_array ключом также является номер столбца. Лучше давать алиасы
SELECT t1.a a1, t2.a a1 FROM table1 t1 LEFT JOIN table2 t2 USING(id);
Неактивен
