Преобразование результатов запроса MySQL в JSON

Говорят, MySQL сервер начиная с версии 5,7 поддерживает преобразование данных в JSON.
Но что же делать, когда версия сервера не такая продвинутая? Попробуем создать вывод в JSON самостоятельно.

Для этого запрос
SELECT
  collname,
  colldesc
FROM collection
WHERE productid = 1585546
изменим на
SELECT
  CONCAT("[",
          GROUP_CONCAT(
               CONCAT("{collname:'",collname,"'"),
               CONCAT(",colldesc:'",colldesc),"'}")
     ,"]") AS collection_json
FROM collection
WHERE productid = 1585546

Результатом запроса будет строка JSON
[
  {
    collname: 'Название 1',
    colldesc: 'Описание 1'
  },
  {
    collname: 'Название 2',
    colldesc: 'Описание 2'
  }
]

Use this ;)

Группировка результатов запроса MySQL в строку с разделителем

Пришлось создавать сложный (для меня) запрос MySQL, в котором нужно было выгружать значения из разных таблиц.
И всё бы хорошо, но для загрузки некоторых параметров была связь Many To One, что для меня означало требование использовать подзапросы. И тут ничего страшного нет, но результат подзапроса должен быть один, а у меня список, к примеру, ИДишников или результатов теста студентов.

Мне на помощь пришла функция GROUP_CONCAT(). Пример:
SELECT student_name,
        GROUP_CONCAT(test_score)
         FROM student
         GROUP BY student_name;

В данном случае результатам будет строка ['Имя студента','25,15,47,18']

Если, по какой либо причине, MySQL сервер возвращает результат в бинарном виде, то нужно добавить к результату конкатенацию с пустой строкой.
CONCAT(GROUP_CONCAT(test_score), "")

Use this ;)

Двухуровневое дерево MySQL + PHP

Немного хотелось бы описать процесс хранения двухуровневого дерева в MySQL, а так же его выгрузку одним запросом и формирование PHP массива для отображения в шаблоне и т.д.

Начну с структуры таблицы MySQL, на примере таблицы для хранения тегов Категория -> Тег.

Структура таблицы
CREATE TABLE table_tag (
  tag_id int(11) NOT NULL AUTO_INCREMENT COMMENT 'ИД тега',
  tag_name varchar(50) NOT NULL COMMENT 'ИМЯ тега',
  tag_type enum ('part', 'tag') NOT NULL DEFAULT 'tag' COMMENT 'ТИП тега',
  tag_parent int(10) UNSIGNED DEFAULT NULL COMMENT 'ИД родителя',
  PRIMARY KEY (tag_id),
  INDEX IDX_tag_parent (tag_parent),
  INDEX IDX_tag_type (tag_type)
)
ENGINE = MYISAM
COMMENT = 'Таблицы тегов';

Заполнение данными
Естественно, что заполнение данными для каждого конкретного случая будет если не уникально, то как минимум, отличаться. В данном случае, я буду использовать часть данных тегов уже существующего проекта.

Читать дальше

Журнал запросов MySQL в режиме реального времени (mysql realtime log)

Задался я вопросом, как вывести лог запросов MySQL в режиме реального времени.

Тут конечно много разных решений есть, и запрос mysql realtime log выдаст целый список сайтов с целой кучей вариантов) Но, я выбрал для себя простейший и достаточный, для организации слежения за потомком запросов на MySQL сервере.

Итак, нам нужен доступ в консоль, в которой выполняем команду:
mysqladmin -u<USER_NAME> -p<PASSWORD> -i 1 processlist

где:
  • USER_NAME — имя пользователя БД
  • PASSWORD — пароль пользователя базы данных
  • "-i" — интервал обновления
  • «processlist» — команда для вывода данных SHOW PROCESSLIST

В результате, мы получаем в окне терминала результат команды SHOW PROCESSLIST с интервалом обновления — 1 секунда.

П.С. для вывода более подробных данных, нужно в конце строки указать параметр "-v".
mysqladmin -u<USER_NAME> -p<PASSWORD> -i 1 processlist -v

MySQL функция преобразование битовой маски в множество

MySQL функция преобразование битовой маски в множество, в данном случае, 10-тичного вида битовой маски.

Не так давно, я попытался написать подробную (с примерами, как для себя) инструкцию, применения битовой маски в MySQL запросах.
Сегодня мне пришлось немного расширить функционал своего модуля и дописать выборку из другой таблице по полю значений маски. Не буду увлекаться подробностями, скажу только, что кроме как разложить битовую маску на множество (строку) вида «1,3,7» для использование в условии:

WHERE `field` IN(1,3,7)

Для этих целей решил набросать маленькую MySQL функцию.
В качестве аргумента, функция принимает INT число — значение битовой маски (10 разряд).
Результатом работы функции будет строка вида «1,3,7», соответствующая номерам битов с значением 1.

Сама функция имеет комментарии и потому дополнительно описывать её не вижу смысла.

Читать дальше

Создание и использование битовой маски в PHP

PHP

Пример создание и использование битовой маски в PHP

Пишу этот материал только потому, что сам долго вникал, что к чему)))
Наверное, чтоб проще понять как использовать битовую маску (bitmask), нужно на примере показать, для чего она используется.

Пример использования битовой маски:
Задача
На сайте есть система опроса пользователей, которая содержит 10 вариантов ответа. Каждый вариант имеет свой уникальный номер — ID. Ответы нужно сохранять в БД.

Решение
Можно конечно, не заморачиваться и сделать для каждого варианта ответа отдельное поле в таблице результатов, но это не наши методы. Мы попробуем хранить все ответы в одном поле типа INT.

Предположим, что модуль голосования прислал нам массив результатов:
array(1,2,3,12);
это значит, что пользователь указал в голосовании пункты 1,2,3,12. Давайте представим результат нашего голосования как строку из 0 и 1, в которой 1 соответствуют выбранные в голосовании пункты. В результате такого преобразования мы получим число 100000000111 в двоичной системе, что будет соответствовать числу 2055 в десятичной системе.


Читать дальше

Преобразование поля LineString в массив точек JSON

Преобразование поля LineString в массив точек JSON, или как хранить координаты точки в MySQL

Недавно обнаружил в MySQL возможность хранить координаты ГЕО точек. Более подробно про функции для работы с геометрическими функциями в MySQL можно прочитать на страницах руководства:

Я не буду в этой статье описывать все функции для работы с типом LineString, так как там нет ничего сложного и достаточно информации в официальном руководстве. Хочу же более детально остановиться на конкретной задаче, а именно:

«Как из поля с типом LineString получить массив координат в формате JSON».
Для чего это нужно?
Например, есть таблица с маршрутами, каждая строка — это маршрут, в строке есть поле points типа LineString, в котором хранятся все координаты точек маршрута и нужно получить массив этих точек в читаемом формате.

Вариант решения предлагаю следующий:
  1. Создадим функции в MySQL для преобразования LineString в JSON в формате [{lat: val1, long: val2}, {lat: val3, long: val4}...].
  2. Создадим запрос для выгрузки из таблицы маршрутов.


Читать дальше

Полнотекстовый поиск для сайта OpenCart используя PHP + MySQL

Сегодня хочу рассказать о реализации полнотекстового поиска на проекте, «в ядре» которого используется OpenCart. Почему «в ядре», да потому, что 60% там переписано. Но, ближе к теме)

Технология поиска
В качестве технологии для полнотекстового поиска будем использовать FULLTEXT индекс таблицы MyISAM MySQL.
Я не буду расписывать полностью готовый и рабочий пример. Скорее опишу саму технологию, так сказать подход к решению задачи.

Описание задачи
Есть большое количество товара, в данном случае репродукции картин. У них есть параметры, назову их ключи поиска:
  • название работы,
  • автор,
  • категории,
  • теги.
Нужно реализовать полнотекстовый поиск по этим полям. Как дополнение, если полнотекстовый поиск не дал результатов то нужно выполнить поиск вхождения искомой строки в том же множестве ключей.


Реализация задачи

Нужен ли индекс по полю tinyint(1) в MySql

Нужен ли индекс по полю boolean tinyint(1) в MySql?
Не могу сказать, что это принципиальный вопрос и что он очень давно меня мучает. Но, недавно разрабатывал базу данных с таблицей в которой много полей tinyint(1). И этот вопрос возник снова.

Мучила мысль, есть ли смысл использовать индекс в поле tinyint(1)? Будет ли прирос производительности?
И снова Google мне в помощь) И теперь я постараюсь процитировать «mysql ораторов» на своём блоге.

Первое, на что нужно обратить внимание, это количество планируемых записей в таблице, по которым нужно будет делать выборку. Если их количество не превышает 10 000, то сразу можно забывать про индекс по полю tinyint(1).

Если в запросе выборка идёт только по полю tinyint(1), то индекс не даст прироста в скорости, но может увеличить потребление памяти.

При большом количестве записей прирост составит около 10%.

Индекс по полю tinyint(1) есть смысл использовать в составных индексах.


Читать дальше

Использование утилиты mysqldump

Бекап базы при помощи mysqldump
Когда я понятия не имел что это такое, эта фраза казалось мне чем то очень сложным)))
Но, уже не один год я делаю дампы базы MySQL при помощи утилиты mysqldump. И вот, решил поделиться на своём блоге)

Понятно, что информации в сети достаточно. Но, сегодня у нас в компании все с катушек съехали и начали тотальный бекап… Видимо, календарь Майя делает своё дело)

Чтоб сделать бекап базы достаточно знать её имя, имя пользователя и пароль, а дальше все сделает утилита mysqldump.
bash# mysqldump -uИМЯ_ПОЛЬЗОВАТЕЛЯ_БД -pПАРОЛЬ_ПОЛЬЗОВАТЕЛЯ_БД имя_бд > имя_файла_куда_слить_дамп.sql

Но, что делать, если база находится не на localhost?
Добавим один ключ
bash# mysqldump -h СЕРЕВЕР_БАЗ_ДАННЫХ_MYSQL -uИМЯ_ПОЛЬЗОВАТЕЛЯ_БД -pПАРОЛЬ_ПОЛЬЗОВАТЕЛЯ_БД имя_бд > имя_файла_куда_слить_дамп.sql


Импорт дампа из файла в базу данных MySql
Для импорта базы данных используем следующую команду:
mysql -uИМЯ_ПОЛЬЗОВАТЕЛЯ_БД -pПАРОЛЬ_ПОЛЬЗОВАТЕЛЯ_БД имя_бд < dump_file_name.sql


Так же читайте — Импорт дампа большого размера в phpmyadmin

Помните! Регулярный backup — залог сохранения данных!