MySQL - выбрать подстроку из столбца, не перехватывая похожие подстроки из того же столбца

Question

MySQL - выбрать подстроку из столбца, не перехватывая похожие подстроки из того же столбца

0

В таблице MySQL у меня есть столбец VARCHAR с именем ShareID.

Если значение ShareID для строки № 1 содержит строку в виде 1
и значение ShareID для строки # 2 содержит строку в виде 10, 1
и значение ShareID для строки № 3 содержит строку в форме 111, 12.

Я хотел бы захватить все строки, где ShareID равен 1. т.е. ТОЛЬКО первый и второй ряды здесь.

Я попытался с помощью команды LIKE, например, так:

SELECT * FROM tablename WHERE ShareWithID LIKE '1%';

но это поймает ВСЕ строки, которые содержат номер 1, то есть строку № 3, что не то, что я хочу.

Я хотел бы запустить команду, которая будет возвращать ТОЛЬКО строки № 1 и № 2 выше, потому что они имеют ShareID 1, содержащийся в нем.

Я пробовал различные команды (включая REGEXP и IN) и управлял "холодным" решением, в котором я ставил бы запятую после КАЖДОГО числа в столбце ShareID, включая последнюю (т.е. 10, 1,), и затем выполните эту команду:

SELECT * FROM tablename WHERE ShareWithID LIKE '%1,%';

Но я бы предпочел использовать правильное решение, а не чертовски.

Любое руководство будет приветствоваться.

Alan N 02 фев. 2019, в 03:33

Источник

Теги:

mysql

sql

select

substring

3 ответа

Ещё вопросы

Gordon Linoff · Answer 1 · 2019-02-02T00-10-00.000Z

Вы не должны хранить списки чисел в строке через запятую. Это действительно плохая идея:

Номер должен храниться как числа, а не как строки.
Ваши номера кажутся идентификаторами. Идентификаторы должны иметь явные внешние ключи.
SQL - вообще - имеет паршивые функции обработки строк.
SQL не может оптимизировать запросы с помощью строковых операций.
SQL имеет отличный способ хранения списков. Это называется стол.

Иногда, однако, мы застряли с другими людьми, действительно, очень, очень плохими решениями по проектированию баз данных. MySQL имеет удобную функцию для этой ситуации:

where find_in_set(1, ShareWithID) > 0

Если в строке есть пробелы, вам необходимо удалить их:

where find_in_set(1, replace(ShareWithID, ' ', '')) > 0

Да, я знаю, что MySQL не обладает способностью обрабатывать строки, но иногда лучше использовать манипуляции со строками. Это избавляет от необходимости тратить время на создание 250 000 строк «таблицы» (не так ли?) Для просмотра нескольких столбцов идентификаторов, когда один столбец в исходной «таблице» мог бы выполнять свою работу точно так же. Пока правильная «встроенная» команда MySQL используется для манипулирования строкой. Так что спасибо, что привели меня к команде 'find_in_set', о которой я не знал. Вот почему такие порталы, как stackoverflow, так хороши, однако есть одна фундаментальная деталь, которую необходимо рассмотреть - объяснено ниже.

Bill Karwin · Answer 2 · 2019-02-02T15-56-00.000Z

... есть встроенная функция

FIND_IN_SET() на самом деле не предназначен для использования со строками, содержащими разделенные запятыми списки. Он предназначен для использования с типом данных MySQL SET. Следовательно, имя FIND_IN_SET(), а не FIND_IN_COMMA_SEPARATED_LIST().

Это избавляет от необходимости тратить время на создание 250 000 строк "таблицы" (не так ли?) Для просмотра нескольких столбцов идентификаторов, когда один столбец в исходной "таблице" мог бы выполнять свою работу так же хорошо.

250 тысяч строк не проблема для MySQL. Я управляю базами данных с миллиардами строк в данной таблице. Если вы выполняете базовую оптимизацию запросов с помощью индексов, большинство запросов в таблице из 250 тыс. Строк вполне подойдут.

Принимая во внимание использование списка через запятую, вы теряете все шансы на оптимизацию запросов. Индекс не помогает искать подстроки, которые могут не быть крайним левым префиксом строки, а поиск числа в списке через запятую в основном ищет подстроку.

Вы делаете свои запросы невозможными для оптимизации, используя список через запятую. Каждый запрос, использующий FIND_IN_SET() будет сканированием таблицы, которое будет замедляться в линейной зависимости от количества строк в вашей таблице.

Есть и другие недостатки использования списка, разделенного запятыми, помимо индексации, о котором я писал в своем ответе на этот старый пост: действительно ли плохо хранить список с разделителями в столбце базы данных?

Я бы предпочел использовать правильное решение, а не чертовски.

Затем сохраните один идентификатор в строке. В реляционной базе данных это правильное решение.

Спасибо за объяснение Билла и ссылку на ваш старый пост. Я действительно ценю, что хранение чисел в строке - это, как правило, большое нет-нет, но в моем случае это прекрасно работает для ограниченного количества операций ввода-вывода, для которых я намерен использовать его. Я никогда не использовал индексы в своих базах данных, просто потому, что пока не знаю, как это сделать. Кажется, что правильный путь - если для всех 250 000 строк нужны ассоциативные столбцы для значений SharedWithID, мне потребуется новая таблица на 250 000 строк с соответствующим количеством столбцов. Теперь я знаю два способа взломать этот орех, хотя один кажется более профессионально принятым, чем другой.
Вам может понравиться моя презентация « Как создать индексы», «Действительно» или видео .
Спасибо за ссылки на презентации, Билл. Они находятся в моей учебной папке MySQL. Также я решил отказаться от метода хранения «string» и пошел по «целочисленному» маршруту. Я буду динамически добавлять дополнительные столбцы в основную таблицу по мере необходимости. Не должно быть более 5 дополнительных столбцов. Однако, если это число существенно возрастет, я создам новую таблицу.
Поэтому, когда вам нужно найти конкретное число, вам придется искать 5 столбцов, потому что вы не знаете, в каком столбце хранится искомое число. Это означает, что вы все равно не можете использовать индекс. Я говорю вам, что это решенная проблема - создайте дочернюю таблицу и сохраните один идентификатор на строку в одном столбце. Затем вы можете индексировать этот столбец и эффективно искать любое значение.
Мне вспоминается цитата, приписываемая Уинстону Черчиллю: «Вы всегда можете рассчитывать на то, что американцы поступят правильно - после того, как они попробуют все остальное».
Уинстон Черчилль говорил много глупостей в свое время. Я не собираюсь использовать индексы для решения этой «конкретной» проблемы. Просто приятно знать, что они существуют. У меня была проблема с подсчетом количества записей из большой таблицы, связанных с «более» чем одним ShareWithID. Я хотел лучшее решение, и поэтому я разместил вопрос на этой доске, потому что я не знал ответа! Я использовал метод «поиск по нескольким столбцам», написал и протестировал код и поместил его в хранимую процедуру. Работает именно так, как я хочу. Задача решена. Ура!
Хорошо, я прошу прощения, если я вас раздражаю. Я надеюсь, что этот обмен не сделает вас устойчивым к рассмотрению вопроса о нормализации вашей базы данных в будущем. С наилучшими пожеланиями.

Alan N · Answer 3 · 2019-02-02T14-37-00.000Z

Решением этой проблемы является использование предложения Гордона Линоффа о команде FIND_IN_SET в сочетании с правильной конфигурацией рассматриваемого столбца таблицы, например, так:

SELECT * FROM tablename WHERE FIND_IN_SET('1', ShareWithID);

Однако, поскольку команда FIND_IN_SET позволяет вам найти положение строки в списке строк, разделенных запятыми, вы ДОЛЖНЫ убедиться, что содержимое столбца содержит запятую после каждого элемента и НЕ содержит пробелов после запятой.

Таким образом, содержимое этого столбца, используемое в сочетании с вышеприведенной командой, вернет "0" строк: 111, 1

В то время как содержимое этого столбца будет возвращать строку "1": 111,1

Как будет этот: 33,1

А этот: 44,1415

Похоже, FIND_IN_SET() в конце концов, не очень FIND_IN_SET() решение.
Почему это Билл Карвин? Он работал нормально для меня в сочетании с правильной конфигурацией колонки. Это может быть не классическое кодирование, но есть встроенная функция.
Слишком много, чтобы ответить в комментарии. Я напишу ответ.