Как сделать чтобы строка не вставлялась в БД, если не соблюдается формат поля?

Question

[antoshka_basenko]

У меня есть таблица customer, в ней поле phone - varchar. Как сделать чтобы при вставке в таблицу записи оно проверяло соответсвует ли поле phone формату и если не соответсвует, то не добавляло запись.
Формат XXX-XX-XX, где X - это цифра

Answer 1

[Akina]

ALTER TABLE customer
ADD CONSTRAINT CHECK(phone REGEXP '^[0-9]{3}-[0-9]{2}-[0-9]{3}$');

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=71012d...

Answer 2

[DevMan]

мне стыдно за ответы выше.

B – валидация (validation).
это должно проверяться приложением, а не СУБД.

Comments

[Akina]

B – валидация (validation).
это должно проверяться приложением, а не СУБД.

Мне стыдно за этот ответ. Данные должны проверяться как клиентом, так и СУБД. Особенно СУБД - потому что клиенту веры нет от слова "совсем".

За целостность и непротиворечивость данных отвечает-то СУБД, а не клиент, знаете ли...

[DevMan]

Akina, а кто тут про клиент говорил кроме тебя?
я писал про валидацию приложением, которое отправляет запрос в базу.

[Akina]

DevMan, так это приложение и есть клиент.

[DevMan]

Akina, ну если ваше приложение пропускает валидацию – говно ваше приложение.

[Akina]

DevMan, повторю:

Данные должны проверяться как клиентом, так и СУБД.

Вот как, КАК? Как это можно интерпретировать, что приложение-клиент НЕ валидирует данные? Нет, ну как?

[DevMan]

Akina,

Данные должны проверяться как клиентом, так и СУБД.

и откуда дернули эту цитату?

[DevMan]

Akina, и почему решили, что именно база за это отвечает?

что мешает приложению отправить валидные данные или сделать отбой?

[Akina]

DevMan,

что мешает приложению отправить валидные данные или сделать отбой?

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

Валидация на стороне сервера БД - гораздо более надёжная процедура. Хотя бы потому, что этот процесс прошёл гораздо больше проверок, чем любое клиентское приложение. Я сейчас имею в виду не валидацию в виде хранимых процедур - там код опять же пишет программист, который не идеален. Речь о встроенных валидациях - ограничения типов, NOT NULL, внешние ключи, уникальность... и эти валидации не зависят от приложения - вообще.

Даже предложенный мной CHECK constraint, вернее, его выражение - это валидация, написанная программистом и, следовательно, неидеальна. Но мимо самой проверки этого constraint, что бы там не было написано - не пройти никак, сервер в обязательном порядке её проверит. Абсолютно всё равно, что отправит клиент - никакой клиентский код, будь он хоть три раза ошибочный или даже злонамеренный, не может отменить проверки.

[DevMan]

Akina, данные вполне могут/должны валидироваться и на уровне базы, но начальная валидация должна быть на уровне приложения.

кстати, если не верите валидации приложением, откуда уверенность в валидации базой?

[Akina]

DevMan,

кстати, если не верите валидации приложением, откуда уверенность в валидации базой?

Ну тут ответ как бы очевиден. Валидация приложения может быть сама по себе написана неверно или неполно и не учитывать какие-то особые сочетания. А вот вычислениям при валидации я как раз верю - если два плюс два получилось четыре, то это правильный результат, что в приложении, что в СУБД. Пять - никогда не получится.

[DevMan]

Akina, еще раз: если не верите приложению, почему верите своей базе?
а мож ошиблись?

[Akina]

DevMan, БД ничего не создаёт, она хранит то, что даёт приложение. Ей негде и не в чем ошибиться. Это юзер может ввести не ту букву или импортировать не тот файл.
Если данные косые - это приложение их подало косыми, потому что либо это стороннее приложение, либо проверка такого типа косины не предусмотрено валидацией. Либо программист не ту БД сделал текущей. И не вижу ничего плохого в том, что СУБД эту косину обнаружит.

[DevMan]

Akina, у вас сразу два косяка:
1, приложение послало левые данные
2, мои правила валидации правильные, остальные идут нахуй.

в каком пункте найти ошибку?

[Akina]

Осталось сделать так, чтобы только и исключительно Ваше приложение имело возможность обращаться к этой БД... сумеете? Ну тогда обходитесь без контроля на стороне сервера.

[DevMan]

Akina,
1. сумею. легко.
2. если пишите мне, потрудитесь меня упоминуть, а не писать в пустоту..

[Akina]

DevMan, это комментарий к Вашему ответу - Вы должны были получить о нём уведомление, если ранее явно не отключили их.

[DevMan]

Akina, да, это комент в моем треде.
да, я получаю уведомление.
но это не значит, что он адресован мне, а не другим участникам.

[Vitsliputsli]

DevMan, Akina, вы пишите о разных вещах. DevMan описывает схему типичную для веб: фронт-клиент на машине пользователя, бек-приложение на сервере, и оно общается с БД. Akina про схему: клиент-приложение на машине пользователя обращающееся к удаленной СУБД напрямую без промежуточных звеньев.

[Akina]

Vitsliputsli, возможно. В той схеме, которую Вы видите в позиции DevMan, я описываю взаимодействие бэк-приложения с СУБД.

[Vitsliputsli]

Akina, т.е. я не прав, вы все таки описываете, что есть бек-приложение на сервере, которое общается с базой, и к которому обращается клиент с фронта? И при этом предлагаете перекинуть всю валидацию в базу с него? Тогда не понятны утверждения, о том, что проблема организовать доступ к БД только для приложения, почему мы не можем доверять серверному приложению, и откуда взялась непогрешимость валидации в БД.
Мы делаем проверку на клиенте и на сервере не потому что чем больше проверок тем лучше, а потому, что безопасно проверить может только сервер (но не обязательно СУБД), а проверка на клиенте (машина подконтрольная пользователю) позволяет обойтись без лишних запросов на сервер. Если бы не это, можно было бы обойтись и одной проверкой.
Никто не отвергает проверку ограничения типов на стороне БД, но полностью перенести в нее бизнес-логику по валидации проблематично:
- всю ее не перенести, достаточно несложных условий и их уже придется проверять через хранимые процедуры;
- перетягивание логики в базу, усложняет ее версионирование, и очень усложняет деплои и откаты;
- размазывание бизнес-логики по 2 кодовым базам очень неудобно;
- тестирование логики в базе в разы сложнее, чем в коде; и вместо простейшего юнит-теста выполняющегося мгновенно, нам понадобится тест, который будет лезть в базу, т.е. часть тестов замедлится порядка на 3;
- БД в 99% узкое место, не нужно ее еще дополнительно грузить;
- проверка в приложении на порядки быстрее, чем постоянная передача данных на другой сервер для проверки;
- не прошедшая валидация на сервере вызовет Exception на стороне сервера приложений, Exception всегда медленный, это исключительная ситуация, но пользователь своими ошибками может породить их сколь угодно много;
- если часть данных хранится в json, то валидации уже не будет;
- если мы обрабатываем массив от пользователя, и необходимо обработать валидное, а невалидное вернуть обратно, то необходима проверка до вставки в базу, иначе придется вставлять данные в базу построчно.
В принципе вариант возможный, если нам не важна производительность, но я плохо себе представляю, для какого проекта это может быть актуально. Тем более, если мы говорит про MySQL. И уже совсем все плохо с проверками на стороне БД, если мы работаем с высокими нагрузками.

[Akina]

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

Я вообще не рассматриваю то, что находится за пределами соединения (connection) между MySQL-сервером и обращающимся к нему клиентом. Я говорю только и исключительно о приложении, которое посылает SQL-запросы и получает обратно результаты их выполнения, и сервере, обрабатывающим эти запросы. Всё. За пределами этого процесса пусть происходит что угодно, какие угодно валидации и протчая.

полностью перенести в нее бизнес-логику по валидации проблематично:

Это всего лишь очевидные недостатки используемых инструментов. Почему логика на стороне СУБД столь проблемна? Что, там нельзя все описанные операции проводить? да можно! только инструментов для этого никто не делает, ограничиваясь средствами на стороне фреймворка, типа этого достаточно. Ну да, достаточно... А что при этом микроскопом гвозди забиваем - какая ерунда! Гвоздь забит? забит... всё, задача решена.

[DevMan]

Vitsliputsli,

DevMan описывает схему типичную для веб: фронт-клиент на машине пользователя, бек-приложение на сервере, и оно общается с БД.

чойта? я описываю типичное взаимодействие клиент-сервер.
а будет это веб, или десктоп, или эмбед, или другое - вообще не роялит.
в данном контексте клиент - что пишет/читает базу.

Akina, логика на стороне базы проблемна по нескольким причинам.
из очевидного:
1. требует других скиллов от разработчика.
2. зависит от конкретной СУБД.

конкретно по валидации логика на стороне приложения/клиента выигрывает всем.

Answer 3

[Александр Карабанов]

Можно нормализовать номер, вырезав из него всё кроме цифр перед записью и не придумывать валидаторы на стороне базы.

Comments

[antoshka_basenko]

а что если человек попытается вставить не число?

[Александр Карабанов]

antoshka_basenko, а в номере есть не числа? Тогда это не номер.

[antoshka_basenko]

Александр Карабанов, ну вот я про это и говорю, если это не номер, то в таком случае нужно просто не вставлять эту запись

[Александр Карабанов]

antoshka_basenko, не вставляй. В чём проблема вырезать всё кроме цифр, убедится, что их осталось 7, а если нет не записывать?

[Adamos]

Александр Карабанов, если человек думает, что все возможные телефоны можно привести к единому формату, причем в этом формате будут только цифры, да еще и определенное их количество - ему рановато проектировать БД для бизнеса.

Answer 4

[mayton2019]

Обычно в продуктовых системах загрузка данных идет в 2 этапа.
1) Грузят данные в специальные сырые таблицы (staging area). Грузят без первичных ключей и без проверок вообще. Вот как есть. С мусором и с битыми кодировками.
2) Запускают специальные процедуры (раньше хранимки) а сейчас - процессы в application которые гоняют все-все бизнес-валидации и загружают то что есть в продуктовые таблицы (бизнес-факты). А по всем битым данным формируют отчёт. Типа не удалось загрузить сто тыщ телефонов.

А то что в топике предлагают на триггерах или на чеках - это так щас не делают. Просто ... головняка много. И никто не будет помнить что за чек или триггер там висит.

Comments

[DevMan]

ты в каком мире живёшь? какие сырые таблицы? ты слоупок?

[Adamos]

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

[mayton2019]

Adamos, когда грузят много данных - то отключают любую логику проверки. В частности триггеры могут быть узким местом. Или констрейнты. Особенно если они проверяют регулярку. Впрочем это - up to you. Если тебе надо загрузить десяток строк - то такой метод нормален. Просто тебе надо будет на каждое срабатывание реагировать. Согласись если битых строк много то оператор просто устанет нажимать ОК в UI. А битых строк бывает много.

[Adamos]

mayton2019, ваше-то предложение я понял. Вопрос к комментарию DevMan.

[DevMan]

Adamos, потому что он не нужен.
ну какой большой объём данных для вставки может быть у оператора?

[Adamos]

DevMan, обновление пятигиговой базы, купленной на черном рынке.
Да мало ли, какие залежи могут быть у оператора...
Мне как-то пришлось расковыривать старый гиговый дамп MSSQL-сервера в MySQL. Мягко говоря, не минутное было дело, даже при копировании один-в-один.

[mayton2019]

Модальное окно которое выскакивает 100500 раз способно вывести из себя любого терпеливого оператора. :)

[DevMan]

Adamos,a мож ты поймешь контекст вопроса и оставишь свои придумки в стороне?

[Adamos]

DevMan, контекст-то у вопроса жидковат, две строчки. ТС грузит какие-то базы клиентов в БД и хочет проверять телефоны. Все.

Ну, допустим, продали его клиенту базу "все предприятия Москвы" в Ёкселе.
Оператор этот самый Ёксель кидает в систему, он там по-быстрому разбирается и кидается без разбора во временное хранилище - "сырую" таблицу БД, чтобы сказать оператору "ок", и он мог заниматься своими делами. Потом уже система понемногу, не сжирая все ресурсы, валидирует и перетаскивает данные в основную базу, составляя отчет - что удалось в нее вставить, а что нет.
Что не так?

[mayton2019]

Вообще интересно что автор куда-то смылся и не комментирует. Хотелось-бы узнать детали.

[DevMan]

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

есть вопросы? создай вопрос.

[Adamos]

DevMan, блин, так я и докопался не к ответу (вы там с Akina зарубились на ровном месте), а к комментарию насчет "слоупока".
Видимо, он останется без обоснования.

[DevMan]

Adamos, да, останется.
ибо ответ дается в контексте вопроса, а не о "любые ситуации".