Как найти дубликаты в MySQL

По работе столкнулся с проблемой: некоторые объекты на карте имели одинаковые координаты. Это приводило к ошибке кластеризации на Яндекс.Картах. Чтобы исправить данную проблему, мне понадобилась функция поиска дубликатов в таблице MySQL. Найденные координаты нужно было исправить, чтобы они немного отличались (например, если объекты находятся в одном доме, их можно разнести по разным подъездам этого дома).

Дубликаты одного поля

Найти дубликаты переменной x в таблице table_name:

SELECT * FROM table_name WHERE x IN(SELECT x FROM table_name GROUP BY x HAVING COUNT(x)>1) ORDER BY x

Эта функция ищет дубликаты только одного поля. Если нужно сравнивать несколько полей, например, координаты (x,y) или (имя,фамилия,отчество) — принцип тот же. Насколько я понял, умных алгоритмов для поиска дубликатов в MySQL нет, поэтому мы просто объединяем нужные поля в одно слово командой z=CONCAT(a,b,c ....) — и ищем дубликаты по переменной z.

Дубликаты нескольких полей

Найти дубликаты пар (x,y) в таблице table_name:

SELECT * FROM table_name WHERE CONCAT(x,y) IN (SELECT CONCAT(x,y) AS z FROM table_name GROUP BY z HAVING COUNT(z)>1)

В скобках может быть указано любое кол-во полей.

Дополнительные условия

В моём случае нужно дополнительно исключить ситуации с нулевыми координатами (это ещё не заполненные поля):

SELECT * FROM table_name WHERE CONCAT(x,y) IN (SELECT CONCAT(x,y) AS z FROM table_name WHERE x!=0 AND y!=0 GROUP BY z HAVING COUNT(z)>1)

Сохранить данные в базе данных в виде текста

Иногда эффективнее хранить данные в БД в текстовом виде, всего лишь в одном текстовом поле. Это применимо, если небольшой объём информации запрашивается и редактируется целиком, и если в ближайшем будущем не планируется усложнение структуры.

Например, расписание занятий по фитнесу. Занятия могут проходить в двух разных залах.

В БД хранится одно текстовое поле:

19:00^Фитнес микс|Zumba^|^|^|^Фитнес микс|^|
20:00^|^|^Здоровая спина|Zumba^|^Zumba|^|
21:00^|^Zumba|^|^|^|^|

Время, далее дни с ПН по СБ, и каждый день поделен на два зала.

Текстовое представление удобно для разработчика: оно визуально подобно блоку на сайте или в админке. Ещё один плюс — всего один запрос к БД.

Вывести это на сайт или в админку просто: заранее придуманные символы-разделители — в нашем случае это ^ и | — заменяются на теги tr, td или div или любые другие. На теги навешиваются стили, и получается красивый блок:

Расписание на сайте

В админке структура полностью сохраняется, это удобно для менеджера:

Строчки можно добавлять, удалять, все поля редактируемые, названия занятий — любые

После редактирования html-код обратными заменами превращается в чистый текст:

19:00^Фитнес микс|Zumba^|^|^|^Фитнес микс|^|
20:00^|^|^Здоровая спина|Zumba^|^Zumba|^|
21:00^|^Zumba|^|^|^|^|

Однако если залов для фитнеса, тренеров и т.п., станет много, то посетитель сайта захочет посмотреть занятия в конкретном зале (или у своего тренера). Потребуется не самая простая выборка данных из текста или фильтрация на стороне клиента. Насколько это удобно, и что лучше — быстрый старт или удобное дальнейшее развитие (и будет ли оно) — вопрос индивидуальный.

Как изменить порядок столбцов в MySQL

Порядок столбцов обычно ни на что не влияет (кроме эстетической красоты), поэтому данному вопросу в интернете уделено мало внимания.

Но вот я пишу CMS-ку, которая сама определяет структуру таблиц и выводит их в таком же виде, как в базе (как в phpmyadmin). Как изменить структуру уже созданных таблиц, чтобы их было удобнее редактировать в админке?

Мне подошла функция MODIFY COLUMN, которая перемещает заданный столбец на позицию до или после (BEFORE / AFTER) другого столбца:

ALTER TABLE table_name MODIFY COLUMN col1_name col1_type AFTER col2_name

После имени столбца надо обязательно указать его тип, например, int(5) — без этого не работает. Не понятно, почему это уточнение является обязательным — ведь логичнее всего переставлять столбец в том виде, как он есть, без изменения типа.