У меня есть таблица, состоящая из 45 столбцов, но только некоторые из них еще завершены. Эта таблица постоянно обновляется и добавляется и т. Д. В моей функции автозаполнения я хочу выбрать эти записи, упорядоченные по наиболее заполненным полям (надеюсь, вы поймете)?
Одно из решений – создать еще одно поле (поле «ранг») и создать функцию php, которая выбирает * записи и дает рейтинг для каждой записи.
… но мне было интересно, есть ли более простой способ сделать это только с одним ORDER BY?
Насколько мне известно, MySQL не имеет функции для подсчета количества полей, отличных от NULL, в строке.
Поэтому единственный способ, которым я могу думать, – использовать явное условие:
SELECT * FROM mytable ORDER BY (IF( column1 IS NULL, 0, 1) +IF( column2 IS NULL, 0, 1) ... +IF( column45 IS NULL, 0, 1)) DESC; … это уродливо, как грех, но должно делать трюк.
Вы также можете разработать TRIGGER, чтобы увеличить дополнительный столбец «fields_filled». Триггер стоит вам на UPDATE , 45 IFs причиняют вам боль в SELECT ; вам придется моделировать то, что более удобно.
Обратите внимание, что индексирование всех полей для ускорения SELECT будет стоить вам при обновлении (а 45 различных индексов, вероятно, стоят столько же, сколько сканирование таблицы при выборе, а не в том, что индексированное поле является VARCHAR ). Запустите некоторые тесты, но я считаю, что решение 45-IF, вероятно, будет лучшим в целом.
ОБНОВЛЕНИЕ : если вы можете переработать структуру таблицы, чтобы ее нормализовать, вы можете поместить поля в таблицу my_values . Тогда у вас будет «таблица заголовков» (возможно, только с уникальным идентификатором) и «таблица данных». Пустые поля вообще не существовали бы, и тогда вы могли бы отсортировать по количеству заполненных полей, используя RIGHT JOIN , считая заполненные поля с помощью COUNT() . Это также значительно ускорит операции UPDATE и позволит вам эффективно использовать индексы.
ПРИМЕР (от установки таблицы до двух нормализованных таблиц) :
Скажем, у нас есть набор записей Customer . У нас будет короткое подмножество «обязательных» данных, таких как идентификатор, имя пользователя, пароль, электронная почта и т. Д .; то у нас будет, возможно, гораздо большее подмножество «необязательных» данных, таких как псевдоним, аватар, дата рождения и т. д. В качестве первого шага предположим, что все эти данные являются varchar (это, на первый взгляд, выглядит как ограничение по сравнению с единственным решением таблицы, где каждый столбец может иметь собственный тип данных).
Итак, у нас есть таблица,
ID username .... 1 jdoe etc. 2 jqaverage etc. 3 jkilroy etc.
Затем у нас есть таблица дополнительных данных. Здесь Джон Доу заполнил все поля, Джо Q. Среднее всего два, а Килрой нет (даже если бы он был здесь).
userid var val 1 name John 1 born Stratford-upon-Avon 1 when 11-07-1974 2 name Joe Quentin 2 when 09-04-1962
Чтобы воспроизвести вывод «single table» в MySQL, нам нужно создать довольно сложный VIEW с большим количеством LEFT JOIN s. Это мнение будет, тем не менее, очень быстрым, если у нас есть индекс, основанный на (userid, var) (даже лучше, если мы используем числовую константу или SET вместо varchar для типа данных var :
CREATE OR REPLACE VIEW usertable AS SELECT users.*, names.val AS name // (1) FROM users LEFT JOIN userdata AS names ON ( users.id = names.id AND names.var = 'name') // (2) ;
Каждое поле в нашей логической модели, например «имя», будет содержаться в кортеже (id, «name», value) в дополнительной таблице данных.
И он предоставит строку формы <FIELDNAME>s.val AS <FIELDNAME> в разделе (1) вышеуказанного запроса, ссылаясь на строку формы LEFT JOIN userdata AS <FIELDNAME>s ON ( users.id = <FIELDNAME>s.id AND <FIELDNAME>s.var = '<FIELDNAME>') в разделе (2). Таким образом, мы можем построить запрос динамически, объединив первую текстовую строку вышеуказанного запроса с динамическим разделом 1, текстом «FROM users» и динамически построенным разделом 2.
Как только мы это сделаем, SELECT в представлении точно совпадают с предыдущим, но теперь они извлекают данные из двух нормализованных таблиц через JOIN.
EXPLAIN SELECT * FROM usertable;
скажут нам, что добавление столбцов в эту установку не замедляет заметные операции, т. е. это решение достаточно хорошо масштабируется.
INSERT должны быть изменены (мы вставляем только обязательные данные и только в первую таблицу) и UPDATE: мы либо ОБНОВЛЯЕМ таблицу обязательных данных, либо одну строку дополнительной таблицы данных. Но если целевой строки нет, тогда она должна быть INSERTED.
Поэтому мы должны заменить
UPDATE usertable SET name = 'John Doe', born = 'New York' WHERE id = 1;
с «upsert», в этом случае
INSERT INTO userdata VALUES ( 1, 'name', 'John Doe' ), ( 1, 'born', 'New York' ) ON DUPLICATE KEY UPDATE val = VALUES(val);
(Нам нужен UNIQUE INDEX on userdata(id, var) для ON DUPLICATE KEY для работы).
В зависимости от размера строки и проблем с дисками это изменение может дать заметное увеличение производительности.
Обратите внимание: если эта модификация не будет выполнена, существующие запросы не приведут к ошибкам – они будут терпеть неудачу .
Здесь, например, мы модифицируем имена двух пользователей; у одного есть имя в записи, другое – NULL. Первый изменен, второй – нет.
mysql> SELECT * FROM usertable; +------+-----------+-------------+------+------+ | id | username | name | born | age | +------+-----------+-------------+------+------+ | 1 | jdoe | John Doe | NULL | NULL | | 2 | jqaverage | NULL | NULL | NULL | | 3 | jtkilroy | NULL | NULL | NULL | +------+-----------+-------------+------+------+ 3 rows in set (0.00 sec) mysql> UPDATE usertable SET name = 'John Doe II' WHERE username = 'jdoe'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> UPDATE usertable SET name = 'James T. Kilroy' WHERE username = 'jtkilroy'; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> select * from usertable; +------+-----------+-------------+------+------+ | id | username | name | born | age | +------+-----------+-------------+------+------+ | 1 | jdoe | John Doe II | NULL | NULL | | 2 | jqaverage | NULL | NULL | NULL | | 3 | jtkilroy | NULL | NULL | NULL | +------+-----------+-------------+------+------+ 3 rows in set (0.00 sec)
Чтобы узнать ранг каждой строки, для тех пользователей, которые имеют ранг, мы просто извлекаем количество строк userdata для каждого идентификатора:
SELECT id, COUNT(*) AS rank FROM userdata GROUP BY id
Теперь, чтобы извлечь строки в порядке «заполненный статус», мы делаем:
SELECT usertable.* FROM usertable LEFT JOIN ( SELECT id, COUNT(*) AS rank FROM userdata GROUP BY id ) AS ranking ON (usertable.id = ranking.id) ORDER BY rank DESC, id;
LEFT JOIN гарантирует, что ранжированные люди также получат доступ, а дополнительный порядок по id гарантирует, что люди с одинаковым рангом всегда выходят в одном порядке.