<rmcreative>

RSS

Все заметки с тегом «SQL»

Можно уточнить:

    (1)
    (1)
    (1)
    (1)
  1. (1)
  2. (1)
  3. (1)
    (1)
    (1)
  1. SQL Style Guide

    2 октября 2015

    Неплохой гайд по стилю написания SQL. Отлично подходит в качестве базы для стандарта внутри команды.

    sqlstyle.guide

    12 комментариев
  2. Как хранить упорядоченный список в базе данных

    15 февраля 2015

    Время от времени мне прилетают в почту занятные вопросы. Последний про хранение упорядоченного списка в базе данных.

    Имеется возможность загрузки фото в альбомы. Нужно реализовать возможность упорядочивать фото в пределах альбома. Автор уже хранит порядок в поле order, но его напрягает, что при перетаскивании какой-либо из картинок нужно обновлять order у фотографий всего альбома.

    Для начала, стоит оценить частоту изменений порядка фото. Если операция не очень частая и выполняется, например, только админом раз в день и в альбомах не очень много фото, можно смело хранить order как integer и обновлять, как автор и делает.

    Если же операция достаточно частая, можно схитрить и хранить order как decimal. В этом случае при вставке или перемещении фото между двумя другими необходимо обновить только order непосредственно перемещаемой записи.

    Если мы перемещаем фото C и засовываем его между фото A и фото B, то значение order для него вычисляется как

    C.order = A.order + (B.order - A.order) / 2;
    

    UPD: из за ограничений точности следует проверять, влезет ли в базу очередное значение. Если нет — пересчитывать order. Даже несмотря на то, что от пересчётов мы не избавились, их частота сократилась для худшего случая на порядок.

    8 комментариев
  3. Удобная функция для тех, кто хочет использовать 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);

    Читаем

    9 комментариев
  4. Расширение для анализа производительности SQL-запросов для Yii

    24 ноября 2011

    Хорошая вариация на тему профайлера SQL под Yii была выложена на днях Владимиром. Кроме списка самих запросов расширение выводит план выполнения запроса (EXPLAIN).

    Пробуем

    Комментировать
  5. Yii DB profiler

    14 сентября 2011

    Довёл до ума и выложил профайлер SQL-запросов для Yii.

    Основан на CProfileLogRoute, т.е. всё так же отображает данные внизу страницы. Отличий несколько:

    1. Показываются только SQL-запросы и ничего лишнего.
    2. Вместо общего времени выполнения и потребляемой памяти показываются время выполнения запросов и их количество.
    3. Умеет подсвечивать розовым проблемные запросы. Либо те, которые достаточно долго выполняются, либо те, которые выполняются много раз. Количество повторений и время выполнения настраиваются.

    Пользуемся

    21 комментарий
  6. Yii, получаем количество запросов к базе данных и общее время их выполнения

    14 сентября 2011

    list($queryCount, $queryTime) = Yii::app()->db->getStats();
    echo "Query count: $queryCount, Total query time: ".sprintf('%0.5f',$queryTime)."s";
    2 комментария
  7. Выбрать посты, содержащие все теги из списка

    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

    Есть ли ещё интересные способы решения этой задачи?

    17 комментариев
  8. 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
    8 комментариев
  9. Несколько 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();
    }
    9 комментариев
  10. Импорт больших 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 = '';
            }
        }
    }
    20 комментариев