За последние 24 часа нас посетили 20470 программистов и 1199 роботов. Сейчас ищут 603 программиста ...

возможна ли сортировка по числу, которое является частью строки?

Тема в разделе "MySQL", создана пользователем logovo, 5 апр 2024.

  1. logovo

    logovo Активный пользователь

    С нами с:
    18 янв 2017
    Сообщения:
    18
    Симпатии:
    2
    В колонке таблицы MySQL (тип text) находятся записи типа:
    /частьurl/15/
    /частьurl/1235/
    /частьurl/743/
    и т.п.
    где "частьurl" везде одинакова.
    возможно ли отсортировать запрос по числу, чтобы порядок был 15, 743, 1235 (а не как у строки 1235, 15, 743)?
     
  2. artoodetoo

    artoodetoo Суперстар
    Команда форума Модератор

    С нами с:
    11 июн 2010
    Сообщения:
    11.076
    Симпатии:
    1.237
    Адрес:
    там-сям
    вы можете отсортировать по любому выражению, в т.ч. с преобразоыванием типов. вроде ORDER BY CAST((SUBSTRING(f, 11, 5) AS UNSIGNED)
    должно работать, хотя и медленно. см. здесь

    а чтобы оно при этом использовало индекс - наверное только через вычисляемые поля (generated column), но это неточно. ищите и проверяй сами.
     
    logovo нравится это.
  3. Survivor

    Survivor Новичок

    С нами с:
    8 фев 2023
    Сообщения:
    72
    Симпатии:
    11
    Не лучшим решением будет выполнить usort с php, просто если данных реально много, запрос будет достаточно тяжелым.


    PHP:
    1. (int) filter_var(ТВОЙ УРЛ ИЗ ТАБЛИЦЫ, FILTER_SANITIZE_NUMBER_INT);
     
  4. artoodetoo

    artoodetoo Суперстар
    Команда форума Модератор

    С нами с:
    11 июн 2010
    Сообщения:
    11.076
    Симпатии:
    1.237
    Адрес:
    там-сям
    если таблица совсем маленькая и мы точно должны получить её всю на php, тогда можно и так.
    но как только нам понадобится получить часть, LIMIT n, m так сразу отпадает.
     
    logovo нравится это.
  5. Vladimir Kheifets

    Vladimir Kheifets Новичок

    С нами с:
    23 сен 2023
    Сообщения:
    270
    Симпатии:
    52
    Адрес:
    Бавария, Германия
    Добрый день!
    Возно не самое оптимальное решение, но провено, работает
    Код (Text):
    1. SELECT A FROM `myTable` ORDER BY SOUNDEX(A), LENGTH(A), A;
    2. Результат:
    3. /частьurl/15/
    4. /частьurl/743/
    5. /частьurl/1235/
    Удачи!
     
    artoodetoo и logovo нравится это.
  6. MouseZver

    MouseZver Суперстар

    С нами с:
    1 апр 2013
    Сообщения:
    7.768
    Симпатии:
    1.322
    Адрес:
    Лень
    Вам изначально нужно в таблицу дополнить дополнительное поле url_id и туда добавлять номера, которые в php отделяются перед INSERT в БД. Измените таблицу и дополните, вам же лучше потом будет.
     
    don.bidon и logovo нравится это.
  7. logovo

    logovo Активный пользователь

    С нами с:
    18 янв 2017
    Сообщения:
    18
    Симпатии:
    2
    Это была первая мысль. Но дело в том, что таблица содержит все url сайта, в части из которых url_id нет, а в другой части они в другой части url. Так что такое решение представляется невозможным.
    --- Добавлено ---
    Спасибо. Неожиданно.
     
  8. don.bidon

    don.bidon Активный пользователь

    С нами с:
    28 мар 2021
    Сообщения:
    874
    Симпатии:
    138
    Значит не точно ставите задачу и дописываете условия после ответов. url_id самый оптимальный варик.
     
  9. logovo

    logovo Активный пользователь

    С нами с:
    18 янв 2017
    Сообщения:
    18
    Симпатии:
    2
    Задача поставлена точно: возможно ли отсортировать запрос по числу... и т.д. По дополнительному полю (int) я сортировать умею :)
     
  10. Survivor

    Survivor Новичок

    С нами с:
    8 фев 2023
    Сообщения:
    72
    Симпатии:
    11
    PHP:
    1. <?php
    2. declare(strict_types = 1);
    3.  
    4. $host = 'localhost';
    5. $db   = 'test';
    6. $user = 'root';
    7. $pass = '';
    8. $charset = 'utf8mb4';
    9.  
    10. $dsn = "mysql:host=$host;dbname=$db;charset=$charset";
    11. $options = [
    12.     PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    13.     PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    14.     PDO::ATTR_EMULATE_PREPARES   => false,
    15. ];
    16.  
    17. try {
    18.     $pdo = new PDO($dsn, $user, $pass, $options);
    19. } catch (\PDOException $e) {
    20.     throw new \PDOException($e->getMessage(), (int)$e->getCode());
    21. }
    22.  
    23.  
    24.  
    25.  
    26. /*
    27. $stmt = $pdo->prepare('INSERT INTO `url` (`text`) VALUES (:text)');
    28. $pdo->beginTransaction();
    29.  
    30. //- Добавляем 1 млн записей в таблицу
    31. try
    32. {
    33.     for ($i = 0; $i < 1000000; $i++)
    34.     {
    35.         $uniqueText = '/частьurl/' . random_int(100000000000000, 999999999999999);
    36.         $stmt->execute(['text' => $uniqueText]);
    37.     }
    38.     $pdo->commit();
    39. }
    40. catch (Exception $e)
    41. {
    42.     $pdo->rollBack();
    43.     throw $e;
    44. }
    45. */
    46.  
    47.  
    48.  
    49.  
    50.  
    51.  
    52.  
    53. $query = "SELECT * FROM `url` ORDER BY `id` ASC LIMIT 300";
    54. $result = $pdo->query($query);
    55. $rows = $result->fetchAll(PDO::FETCH_ASSOC);
    56.  
    57. usort($rows, function ($a, $b) {
    58.     $numberA = (int) filter_var($a['text'], FILTER_SANITIZE_NUMBER_INT);
    59.     $numberB = (int) filter_var($b['text'], FILTER_SANITIZE_NUMBER_INT);
    60.     return $numberB - $numberA; // Для сортировки по убыванию
    61. });
    62.  
    63.  
    64. foreach ($rows as $row)
    65. {
    66.     echo $row['text'] . " <br />";
    67. }
    1) Закомментировал принцип добавления 1 млн записей.
    2) Обычный запрос ( добавить пагинацию сможешь сам )
    3) Стандартный запрос к бд на получении с учетом сортировки, далее фильтр по text-id
    4) Если тебе прям нужно сортировать изначально по id, что находиться в text, я в общем-то не советую тратить ресурсы сервера, время на решение проблем, а сделать иначе. Всегда можно получить получить все записи и например изменить их/перезаписать. Это будет проще во всех смыслах слова. Люди сверху правильно изначально посоветовали. Решать уже тебе )

    p.s. возможно, что я понял вообще неправильно..)
    --- Добавлено ---
    Код (Text):
    1. SELECT *, CAST(REGEXP_SUBSTR(text, '\\d+') AS UNSIGNED) AS a_url
    2. FROM url
    3. ORDER BY a_url ASC
    4. LIMIT 100;
    Если нужно сразу сортировать, то примерно такой вариант,
    но имхо, это бредовый вариант, запрос порядка 1+- сек выполняется при 1 млн записей.
     
  11. MouseZver

    MouseZver Суперстар

    С нами с:
    1 апр 2013
    Сообщения:
    7.768
    Симпатии:
    1.322
    Адрес:
    Лень
    Возможно, если добавить еще одну колонку url_category_id, и будем знать какие значения url к одной категории относится а какие другие