是否应该在数据库级别处理唯一字段的验证? [英] Should validation of unique fields be handled on database level?

问题描述

我正在为我的BSc项目编写相当大的Web应用程序，并使用MySQL在PHP中编写它.

I am writing quite a large web application for my BSc project and am writing it in PHP using MySQL.

在创建SQL表时，我应该使用唯一字段吗?我之所以这样问，是因为我正在向PHP中编写验证方法.例如，如果我想检查数据库中是否已存在电子邮件地址，我将首先执行一条SQL语句，查看是否返回了任何电子邮件地址，然后将初始SQL数据插入数据库中.

When creating the SQL tables should I use unique fields? The reason I ask this is because I am writing validation methods into my PHP. For example, if I wanted to check if an email address already exists in the database I would first make an SQL statement seeing if any email addresses are returned and then insert the initial SQL data into the database.

如果我只插入初始SQL数据，由于电子邮件地址必须唯一，这会在整个语句中引发错误.

If I was to just insert the initial SQL data it would throw an error on the entire statement because email addresses must be unique.

这是进行验证的正确方法吗?

Is this the correct way of approaching validation?

推荐答案

嗯...这是两件完全不同的事情.通过在数据库的列上设置约束(例如唯一)，可以防止将错误的数据集插入数据库.这是确保数据一致性的基本内容.通过向您的应用程序添加验证，您可以添加另一层.在防止将错误的数据集插入数据库的同时，您主要创建一种手段来告诉用户他做错了什么.

Well...these are two entirely different things. By setting contraints (like unique) on columns in the database you can prevent a wrong dataset to be inserted into the database. That's basic stuff to ensure data consistency. By adding validation to your application you add another layer. While preventing that wrong datasets will be inserted into the database, you mainly create means to tell the user what he did wrong.

这两个都需要.通常，唯一约束不应仅是照顾数据库的事情.建议还使用外键约束，如果适用，还应使用更复杂的数据验证(自定义约束).但是，当您使用MySQL时，实际上并没有很多

You need both of those. And in general unique constraints should not be the only things taking care of your database. It's recommended to also use foreign key constraints and if applicable more complex data validations as well (custom constraints). However, as you are using MySQL not a lot of that is really available to you

尽力而为，因为最终它可以避免很多麻烦，直到最终意识到数据库中的数据是错误的，然后才弄清楚为什么会发生特定的错误.那真的很痛苦.

Just go for as much as you can get, because in the end it will prevent a lot of headaches trying to figure out why a particular error occurred until you finally realize that the data in your database is wrong. That can really be a pain.

这篇关于是否应该在数据库级别处理唯一字段的验证?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！