Как сделать, чтобы MySQL обрабатывал нижнее подчеркивание как текст?

Question

[MrTNTminer]

Как сделать чтобы SQL обрабатывал нижнее подчеркивание как текст (и вообще все любые другие символы).
Например, хочу найти данные в столбце строго равные Alexey, он спокойно находит и выдает, но если я введу Alex_ey, то он ничего не найдет, тк SQL заменяет "_" на любой символ, что очень раздражает.
LIKE %% не подходит тк он ищет все совпадения, а мне нужно строгое.
Использую библиотеку RedBeanPHP, пример кода ниже.

$searchlogin = R::getAll('select * from teleg where nik = :login',
                    array(':login'=>$lgtg)
                );

Answer 1

[Константин Цветков]

Убрать подчеркивание.
Или LIKE ESCAPE

s_t_u_v_w и нажимаю найти, мне должно вывести все строгие совпадения с этим ником

SELECT * FROM teleg WHERE nik = 's_t_u_v_w'
Или

SELECT * FROM teleg WHERE nik LIKE 's!_t!_u!_v!_w' ESCAPE '!'

В данный момент из-за того что символ _ заменяется, я не могу найти ники с его участием.

SQL сервер ничего не заменяет.

поставил в ячейках ентеры, из-за этого ничего не находило

SELECT * FROM teleg WHERE nik = 's_t_u_v_w' + CHAR(13)

Или

SELECT * FROM teleg WHERE REPLACE(nik, CHAR(13), '') = 's_t_u_v_w'

Не во всех ячейках есть какой-то символ (или что это такое).

SELECT CONVERT(VARCHAR, CAST(nik AS binary), 2) FROM teleg

Находите ваш неправильный символ. Например, CHAR(13) или CHAR(0x0D) и удаляете его

UPDATE teleg SET nik = REPLACE(nik, CHAR(0x0D), '')

TRIM не срабатывает, так как пробел CHAR(0x20) не последний символ.

MySQL:
SELECT HEX(nik) FROM teleg

UPDATE teleg SET nik = REPLACE(nik, CAST( 0x0D AS CHAR ), '')

Comments

[MrTNTminer]

Гениально, но в базе тысячи записей где есть подчеркивание и нужно, чтобы это все находило

[Константин Цветков]

MrTNTminer, Убирать нужно из строки, которую будете искать. Или у вас грязные данные? Уточните что у вас в таблице и что вы хотите найти.

[MrTNTminer]

Константин Цветков, в базе есть подобие аккаунтов в количестве 40.000.000, есть колонка nik в которой хранятся логины по типу: WeB_BackenD, s_t_u_v_w, maksar555 и так далее.
Там могут быть разные символы, от обычной точки до скобок.
Мне нужно, когда я ввожу на сайте ник, допустим s_t_u_v_w и нажимаю найти, мне должно вывести все строгие совпадения с этим ником, совпадения не нужны (s_t_uvsik к примеру).
В данный момент из-за того что символ _ заменяется, я не могу найти ники с его участием.

[MrTNTminer]

Данный метод сработал но как-то странно. он нашел именно этот ник, но другие вообще не находит (где есть _)

SELECT * FROM teleg WHERE nik = 's_t_u_v_w'

[Константин Цветков]

MrTNTminer, SQL сервер ищет точно байт в байт. Если не находит, то возможно несовпадение в других символах. Несовпадение кодировки, параметров сортировки (это виляет на то, что сервер считает одинаковыми символами). Да даже использование кириллицы вместо латинских символов аналогичного вида.

[Dimonchik]

MrTNTminer, сорри за зарказм, вы вообще ответ поняли? вам же второй строкой написали что надо заескейпить подчеркивание и написали как

[MrTNTminer]

Константин Цветков, возможна проблема, если в ячейке был enter или пробел? (после ника)

[MrTNTminer]

dimonchik2013, если я не ответил на ту строку, значит от не было пользы - она мне не помогла.

[Dimonchik]

а если копипаст забыть и гуглануть про экранирование ? у тебя может сервер с другим синтаксисом

[MrTNTminer]

dimonchik2013, проблему обнаружил уже, оказывается скрипт импорта базы зачем то поставил в ячейках ентеры, из-за этого ничего не находило.
Константин Цветков, Спасибо за помощь.

[Константин Цветков]

MrTNTminer, Это вам решать: нужно точное совпадение или нет.

[MrTNTminer]

Константин Цветков,

SELECT * FROM teleg WHERE nik = 's_t_u_v_w' + CHAR(13)

Или

SELECT * FROM teleg WHERE REPLACE(nik, CHAR(13), '') = 's_t_u_v_w'

Идея использовать это очень хорошая, но проблема в 2х местах сразу:
1. Не во всех ячейках есть какой-то символ (или что это такое).
2. Это оказался (вроде как) не enter в ячейках, а что-то другое. Если попробовать удалить через PHP \n\r, то ничего не сработает, но если прописать trim - все пройдет успешно и этот невидимый символ уберется.

Попытавшись через SQL сделать trim у меня ничего не вышло (UPDATE teleg SET nik = TRIM(nik);). Вы случаем не знаете как SQL командой во всем столбце сделать trim?
(а еще мне походу сегодня вообще не везет, ибо при написании этого текста у меня ПК словил синий экран, хорошо хоть текст сохранился)

[Константин Цветков]

MrTNTminer, Да, чистить грязные данные не просто, так как их не видно невооруженным глазом.

[MrTNTminer]

Константин Цветков, спасибо большое за вашу помощь, но видимо что-то не так делаю.
Выдает такую ошибку (версия 5.7.30-0ubuntu0.16.04.1 - (Ubuntu), не понимаю что нему не нравится):
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARCHAR, CAST(nik AS binary), 2) FROM teleg LIMIT 0, 25' at line 1
Используя:

SELECT CONVERT(VARCHAR, CAST(nik AS binary), 2) FROM teleg

[Константин Цветков]

MrTNTminer, Добавил версию для MySQL.

[MrTNTminer]

Константин Цветков, Спасибо за помощь, колонка очистилась

Answer 2

[N]

А старый "детовский" способ экранирования религия не позволяет применить?

$str_like = str_replace(['_', '%'], ['\_', '\%'], 'Alex_ey');

$sql = "SELECT * FROM `table` WHERE `field` LIKE '".$str_like."'";

Ну канеш про sql-инъекции не забываем...выше запрос не безопасен, если $str_like приходит хз от кого...это уже другая тема...

P.S.: Почитал комменты к предыдущему ответу...там походу дело в самом подходе и хранении данных...

Comments

[MrTNTminer]

Спасибо за помощь, но да, проблема в другом, там какие-то символ(ы) в ячейках и я не понимаю как их убрать.
Подробно я описывал в комментах поста выше.

[N]

MrTNTminer, На крайняк "перелей" в новую таблицу с trim в PHP...костылёк...но если это всего лишь раз сделать и дальше пользоваться новой таблицей + новые поступающие данные обрабатывать на входе по одному принципу, то, чем не вариант...чем несколько часов биться с такой проблемой...=)

И как выше писали...возможно одинаковые русские буквы с английскими перепутаны в данных...их тоже можно "пофиксить" заодно, если использовать костыль выше...

[MrTNTminer]

N, там нету русских букв, там просто какой-то символ странный, который фиг уберешь. Новые данные в базу не поступают и не будут поступать.
База уже готовая, но из-за этих проблем с символом не получается сделать поиск, а если пропустить через PHP это уже извращение + обработка 40кк строк будет очень долго идти.
Попытаюсь переформировать базу завтра (если руки дойдут, уже нет сил на него, неделю тупо просрал)

[N]

а если пропустить через PHP это уже извращение + обработка 40кк строк будет очень долго идти

Если разово, то можно и сделать...+ смотря как будешь обрабатывать и загонять в БД обратно...40 млн небольшая цифра...можно избежать "долго"...