MySQL и SQLite — регулярные выражения в предикате

    Регулярные выражения могут оказать Вам неоценимую услугу при их разумном применении в SQL-запросах.
    Они могут избавить Вас от необходимости перебирать в курсорных циклах, или (о ужас!) в циклах базового языка приложения солидные куски таблиц. Правда иногда услуга может оказаться «медвежьей».
    Примеры и особености применения этой техники

    Для примера — имеем безнес-логику приложения, в которой на первом этапе пользователь может создавать произвольную запись в базе, а на втором этапе — использовать данную произвольную запись в качестве внешнего ключа.
    Задача вполне предсказуема — требуется соблюдать уникальность записи.
    Могу предложить к рассмотрению вот такое решение (да, оно не универсально, потому что я дополняю произвольную запись пользователя, тем самым формально ее изменяя. С другой стороны, цитируя Кларксона: "-Да, я тиран! Хотите демократии — езжайте в Ирак!"):
    DDL таблицы
    CREATE TABLE `human_link` (
     `translit_link` varchar(255) NOT NULL DEFAULT 'main',
     `owner` int NOT NULL,
     PRIMARY KEY (`translit_link`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8


    * This source code was highlighted with Source Code Highlighter.

    Где обсуждаемая произвольная запись есть поле `translit_link`, которую для соблюдения уникальности мы дополняем цифрой (1) или, если цифра уже есть — увеличиваем цифру на +1.
    Узнать, если ли подобная запись, можно запросом
    SELECT `translit_link` FROM `foo`.`human_link`
    WHERE `translit_link` REGEXP CONCAT('^',?,'[[:digit:]]*$') ORDER BY `translit_link` DESC LIMIT 1;


    * This source code was highlighted with Source Code Highlighter.

    Собственно, остается только разобраться, что к нам вернулось и сделать с возвратом то, что велит нам логика сохранения уникальности поля (более приближеный к жизни пример этой техники, с использованием perl и хранимой процедуры — в читайте моем блоге).
    Но это все была только присказка.
    Сказка вот в чем — EXPLAIN MySql про этот запрос скажет Using where; Using index, а вот при использовании SQLite из комлпекта DBD::SQLite —
    REGEXP function
    Note that regexp matching will not use SQLite indices, but will iterate over all rows, so it could be quite costly in terms of performance.

    Кроме того и в MySQL не все так гладко — в книге «High performance MySQL: optimization, backups, replication, and load balancing» By Jeremy D. Zawodny, Derek J. Balling есть строка том, что выражение типа WHERE last_name RLIKE "(son|ith)$" будет нереально медленно по объяснимым причинам, а вот выражение типа WHERE rev_last_name RLIKE "^(thi|nos)" окажется медленне двух union по WHERE rev_last_name like «thi%» и «nos%», потому что что «MySQL оптимизатор никогда не пытается оптимизировать основаный на регулярном выражении запрос».
    Из чего делаем вывод, что не все йогурты одинаково полезны.

    PS. Буду весьма признателен за подсказку как обстоят дела в PostgreSQL, Oracle и в стане MS. :)
    PPS. Я в курсе существования ON DUPLICATE KEY UPDATE — процедура ведет себя некорректно при использовании составного уникального индекса (multiple unique indexes). Т.е. ИМХО теоретически она вообще небезопасна для бизнес-логики, т.к. накладывает неявные ограничения на создаваемые индексы.
    Ads
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More

    Comments 9

      +1
      счётчик можно вынести в отдельное поле, либо ставить между ними символ недопустимый в идентификаторе…
        0
        Безусловно, счетчик можно вынести в отдельное поле, можно даже это отдельное поле сделать автоинкрементом, а индекс сделать по «значимому + счетчик» и получить автоинкременирование для каждого уникального значения значимого поля. Правда, если индекс в последствии будет удален — получим такое, что и подумать страшно.
        либо ставить между ними символ недопустимый в идентификаторе
        Эм. Как не пытался — не постиг тайного смысла. Какую проблему мы при этом решаем?
          0
          возможность использования лайка вместо регэкспа…
            0
            RLIKE позволяет реализовать вот такой подход:
            Шаг      Значение поля  Суффикс
            1            my_fild                   []
            2            my_fild1                [1]
            3            my_fild2                [2]
            

            В один и тот же запрос, т.е и при наличии суффикса, и при его отсутствии.
            Поясните, как Ваш метод позволит сделать то же самое?
        0
        field LIKE «xxx+%»

        матчатся:
        xxx+
        xxx+1
        xxx+22

        не матчатся:
        yyy+
        xxx1+

        плюсик с конца нужно убирать при выводе и добавлять при вводе
          0
          Ну и нафига козе баян? В смысле — маскарад с добавлением-удалением при каждой операции ввода-вывода, при условии что RLIKE не медленнее LIKE.
            0
            sqlite
              0
              Согласен, только еще стоит добавить, что
              4.0 The LIKE optimization

              Terms that are composed of the LIKE or GLOB operator can sometimes be used to constrain indices. There are many conditions on this use:

              1. The left-hand side of the LIKE or GLOB operator must be the name of an indexed column.
              2. The right-hand side of the LIKE or GLOB must be a string literal that does not begin with a wildcard character.
              3. The ESCAPE clause cannot appear on the LIKE operator.
              4. The build-in functions used to implement LIKE and GLOB must not have been overloaded using the sqlite3_create_function() API.
              5. For the GLOB operator, the column must use the default BINARY collating sequence.
              6. For the LIKE operator, if case_sensitive_like mode is enabled then the column must use the default BINARY collating sequence, or if case_sensitive_like mode is disabled then the column must use the built-in NOCASE collating sequence.
              И далее по тексту.
                0
                это достаточно очевидные вещи…

        Only users with full accounts can post comments. Log in, please.