Неплохой гайд по стилю написания SQL. Отлично подходит в качестве базы для стандарта внутри команды.
Все заметки с тегом «sql»
-
SQL Style Guide
2 октября 2015
-
Как хранить упорядоченный список в базе данных
15 февраля 2015
Время от времени мне прилетают в почту занятные вопросы. Последний про хранение упорядоченного списка в базе данных.
Имеется возможность загрузки фото в альбомы. Нужно реализовать возможность упорядочивать фото в пределах альбома. Автор уже хранит порядок в поле
order
, но его напрягает, что при перетаскивании какой-либо из картинок нужно обновлятьorder
у фотографий всего альбома.Для начала, стоит оценить частоту изменений порядка фото. Если операция не очень частая и выполняется, например, только админом раз в день и в альбомах не очень много фото, можно смело хранить
order
какinteger
и обновлять, как автор и делает.Если же операция достаточно частая, можно схитрить и хранить
order
какdecimal
. В этом случае при вставке или перемещении фото между двумя другими необходимо обновить толькоorder
непосредственно перемещаемой записи.Если мы перемещаем фото
C
и засовываем его между фотоA
и фотоB
, то значениеorder
для него вычисляется какC.order = A.order + (B.order - A.order) / 2;
UPD: из за ограничений точности следует проверять, влезет ли в базу очередное значение. Если нет — пересчитывать
order
. Даже несмотря на то, что от пересчётов мы не избавились, их частота сократилась для худшего случая на порядок. -
Удобная функция для тех, кто хочет использовать Yii и писать SQL
21 июня 2012
Использовать можно так:
$posts = command('SELECT * FROM post WHERE id IN(%s)', array(1, 2, 3))->queryAll(); foreach($posts as $post) { print_r($post); } $id = command('SELECT id FROM post WHERE name = %s AND status=%d', 'Alexander', 10)->queryScalar(); echo $id; $post = command('SELECT * FROM post WHERE id = %d', 101)->queryRow(); print_r($post);
-
Расширение для анализа производительности SQL-запросов для Yii
24 ноября 2011
Хорошая вариация на тему профайлера SQL под Yii была выложена на днях Владимиром. Кроме списка самих запросов расширение выводит план выполнения запроса (EXPLAIN).
-
Yii DB profiler
14 сентября 2011
Довёл до ума и выложил профайлер SQL-запросов для Yii.
Основан на
CProfileLogRoute
, т.е. всё так же отображает данные внизу страницы. Отличий несколько:- Показываются только SQL-запросы и ничего лишнего.
- Вместо общего времени выполнения и потребляемой памяти показываются время выполнения запросов и их количество.
- Умеет подсвечивать розовым проблемные запросы. Либо те, которые достаточно долго выполняются, либо те, которые выполняются много раз. Количество повторений и время выполнения настраиваются.
-
Yii, получаем количество запросов к базе данных и общее время их выполнения
14 сентября 2011
list($queryCount, $queryTime) = Yii::app()->db->getStats(); echo "Query count: $queryCount, Total query time: ".sprintf('%0.5f',$queryTime)."s";
-
Выбрать посты, содержащие все теги из списка
12 августа 2011
В этом блоге примерно с середины 2007-го, когда я переписал его с файлового велосипедохранилища на нормальный SQL, появилась возможность выбрать посты, содержащие все заданные теги. В 2008-м возможность окончательно оформилась в теговую навигацию.
Изначальный способ выборки, который используется и сейчас:
SELECT * FROM post p JOIN post_tag pt1 ON pt1.post_id = p.id JOIN tag t1 ON t1.id = pt1.tag_id AND t1.name = 'php' JOIN post_tag pt2 ON pt2.post_id = p.id JOIN tag t2 ON t2.id = pt2.tag_id AND t2.name = 'yii' -- для каждого дополнительного тега добавляем ещё JOIN-ы
Недавно Максим подсказал способ поизвращённее:
-- Сначала выбираем посты с тегом yii и посты с тегом php -- через IN. Если у поста и тот и тот тег, он будет выбран -- несколько раз. SELECT * FROM post p JOIN post_tag pt ON pt.post_id = p.id JOIN tag t ON pt.tag_id = t.id WHERE t.name IN ('php', 'yii') -- Считаем количество раз, которые выбрался пост. Если оно -- совпадает с количеством тегов в нашем списке, то это -- означает, что у поста есть и тот и тот тег. GROUP BY p.id HAVING COUNT(pt.tag_id) = 2
Есть ли ещё интересные способы решения этой задачи?
-
SQL, пересекается ли событие с теми, что уже имеются в таблице
9 августа 2011
Довольно частая задача при работе с различными событиями и датами.
Имеется таблица вида:
CREATE TABLE `event` ( `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `start_time` datetime DEFAULT NULL, `end_time` datetime DEFAULT NULL, );
Тут
start_time
иend_time
не обязательноdatetime
. Например, они могут быть простоint
-ами, хранящими обычный unix timestamp.Перед вставкой очередной записи нужно убедиться, что событие не пересечётся с каким-либо уже имеющимся в базе. Решается это так:
SELECT 1 FROM event WHERE :new_start_time BETWEEN start_time AND end_time OR :new_end_time BETWEEN start_time AND end_time OR start_time BETWEEN :new_start_time AND :new_end_time OR end_time BETWEEN :new_start_time AND :new_end_time LIMIT 1
В запросе
:new_start_time
и:new_end_time
соответственно начало и конец события, которое мы собираемся вставлять. Если запрос выше отдал1
, наше новое событие с чем-то пересекается…UPD: менее избыточные варианты (спасибо dec5e):
NOT(:new_start_time > end_time OR :new_end_time < start_time)
или через
AND
:new_start_time <= end_time AND :new_end_time >= start_time
-
Несколько SQL-запросов за один раз через PDO
24 июня 2011
Понадобилось исполнить несколько не однотипных запросов за один раз. Также, как это делает mysqli::multi_query, но через PDO. Сделать это, оказалось, вполне реально, но и без интересностей не обошлось.
Во-первых нагуглить это оказалось почему-то не так просто: вылезали какие-то обсуждения
PDO_MYSQLND
2008-го года и, хоть и свежие, но оставшиеся без внятного ответа вопросы на StackOverflow.Оказалось, что
PDO_MYSQLND
с релизом PHP 5.3 заменилPDO_MYSQL
, который такие вещи не поддерживал. Причём заменил под тем же именемPDO_MYSQL
.Итого, для выполнения нескольких запросов за один раз понадобится:
- PHP 5.3+
- mysqlnd
- Эмуляция prepared statement через
PDO::ATTR_EMULATE_PREPARES
, выставленный в1
(по умолчанию) или, как альтернатива, не использование prepared statements и выполнение запроса напрямую через$pdo->exec
.
Используем exec
$db = new PDO("mysql:host=localhost;dbname=test", 'root', ''); // работает как для реальных statement-ов, так и для эмуляции $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0); $sql = "DELETE FROM car; INSERT INTO car(name, type) VALUES ('car1', 'coupe'); INSERT INTO car(name, type) VALUES ('car2', 'coupe');"; try { $db->exec($sql); } catch(PDOException $e) { echo $e->getMessage(); die(); }
Используем statement-ы
$db = new PDO("mysql:host=localhost;dbname=test", 'root', ''); // Не будет работать с реальными statement-ами. Только с эмуляцией. // Строку ниже можно закомментировать, это умолчание $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1); $sql = "DELETE FROM car; INSERT INTO car(name, type) VALUES ('car1', 'coupe'); INSERT INTO car(name, type) VALUES ('car2', 'coupe');"; try { $stmt = $db->prepare($sql); $stmt->execute(); } catch(PDOException $e) { echo $e->getMessage(); die(); }
-
Импорт больших SQL-дампов через PHP
27 мая 2011
Основная проблема при импорте большого SQL-дампа — нехватка памяти при чтении всего дампа. Решение очевидно: грузить в память только то, что нужно в данный момент.
$f = @fopen("path/to/dump.sql", "r"); if($f) { $q = ''; while(!feof($f)) { // читаем построчно в буфер $q $q .= fgets($f); // пока не упрёмся в ; if(substr(rtrim($q), -1) == ';') { // выполяем запрос execute_sql($q); // обнуляем буфер $q = ''; } } }