Я уверен, что об этом уже спрашивали / отвечали, но я не знаю, как это действие называется, и мои знания SQL ограничены.
Я ищу один оператор SQL для объединения двух таблиц:
Таблица USER:
ID hash ========= 1 abc 2 def 3 ghi и USER_FIELD:
ID user_id key value ============================= 1 1 firstname John 2 1 lastname Doe 3 2 firstname Justin 4 2 lastname Case
Теперь я хотел бы знать, как я получаю это представление:
ID hash firstname lastname ================================ 1 abc John Doe
Поэтому, если я добавлю дополнительную запись в таблицу USER_FIELD с ключом «email», я получу новый столбец в последнем результате
Возможно ли это только в MySQL или мне нужно пойти и изменить результат в PHP?
Является ли это даже хорошим дизайном БД, или я должен отказаться от этого варианта с другим (какой?)
Пара союзников сделала бы трюк.
SELECT u.id, u.hash, uf_f.value AS firstname, uf_l.value AS lastname FROM user AS u LEFT JOIN user_field AS uf_f ON uf_f.user_id = u.id AND uf_f.key = 'firstname' LEFT JOIN user_field AS uf_l ON uf_l.user_id = u.id AND uf_l.key = 'lastname'
Вы можете сделать это с помощью join и group by :
select u.*, firstname, lastname from user u join (select uf.user_id, max(case when key = 'firstname' then value end) as firstname, max(case when key = 'lastname' then value end) as lastname from user_field uf group by user_id ) uf on uf.user_id = u.id;
Вы также можете сделать это с помощью последовательности соединений:
select u.*. firstname.value, lastname.value from user u join user_field firstname on u.id = firstname.user_id and firstname.key = 'firstname' join user_field lastname on u.id = lastname.user_id and lastname.key = 'lastname';
Ваш результат, похоже, ограничивает это только одним идентификатором пользователя. Возможно, вам понадобится предложение where с этим фильтром.
Как правило, это не считается хорошей практикой для таблиц с плоским типом (например, данных пользователя). Эта структура обычно используется для хранения пользовательских настраиваемых полей или метаданных, где у вас может быть не более одного или сотни из них для одного экземпляра.
Причиной этого избежать является то, что вы хотите получить N пользовательское свойство, которое вы должны выполнить N-1 в MySQL, и, возможно, динамически генерировать запрос в приложении, которое является субоптимальным. Просто поместите все стандартные данные в одну таблицу, и вы можете использовать эту таблицу user_field для настраиваемых полей, которые извлекаются только по запросу.
Если вы застряли в этой структуре, чтобы эффективно выполнять запрос, я бы изменил таблицу user_field, чтобы не иметь идентификатор auto_increment и, скорее, сделать user_id и закрепить PRIMARY KEY для таблицы. Таким образом, все поля для пользователя окажутся рядом друг с другом в табличном пространстве (при условии, что вы используете InnoDB), и поиск будет первичным поиском ключей. Еще одним шагом будет изменение ключа для tinyint и использование идентификаторов поиска вместо фактических символов (вы даже можете использовать CONSTANTS в коде, чтобы сделать его более чистым).
Вы хотите использовать JOIN -statement и использовать ID и user_id в качестве объектов.
Еще один простой способ получения данных с помощью подзапроса.
SELECT hash, (select value from USER_FIELD where key = 'firstname' and user_id=6 ) as firstname, (select value from USER_FIELD where key = 'lastname' and user_id=6 ) as lastname FROM `USER` where ID = 6
но это нехорошо. Вам придется идти на компромисс с производительностью, если база данных будет большой. Вы должны использовать другой шаблон дизайна.