维护MYSQL中的输入约束(Maintaining constraints on input in MYSQL)

维护MYSQL中的输入约束(Maintaining constraints on input in MYSQL)

我有一个输入列表，其中每个输入都可以有限制，例如它应该是最多15位的数字，或最多20个字符的字母数字。

我不知道如何将它存储在数据库中，或者我应该为每个输入硬编码它在代码中的约束对我来说是不对的。

I have a list of inputs, where each input can be having restriction on it, such as it should be numeric with max 15 digits, or alphanumeric of max 20 characters.

I am not sure how to store it in database, or should I hardcode for each input its constraints in the code which doesn't feel right to me.

最满意答案

数据库上的约束与验证不同。 我认为你真的想要验证你的应用程序的输入。 例如，你提到一个应该是数字的字段，最多15位数，并且，虽然你可以创建一个触发器来验证这些东西，但这是不实际的，要在mysql中存储15位数字（没有小数），你需要允许数字最大为9223372036854775807的BIGINT ，超过15位数。 没有简单的方法来为这种事情设置架构。

例如，使用VARCHAR(20)可以实现其他情况，例如最多20个字符的字母数字，但依赖数据库仍然是不切实际的。

最好的办法是在UI级别验证您的输入，并可能作为一个额外的安全步骤，检查您的数据库模型或在何处执行插入/更新。

为什么在数据库模式上进行约束以及对代码进行特定验证的一个很好的例子是，当您的应用程序业务规则以您需要在输入中灵活变化的方式发生变化时，最终您需要允许特定情况下代码可以要么是10位还是15位，您仍然希望允许数据库存储两个长度的数字，但您可以在应用程序验证中验证这两种情况。

Constraints on a database are a different thing than validations. I think you are looking really to validate input from your application. For instance, you mention a field that should be numeric with max of 15 digits, and, while you can create a trigger to validate such things its just not practical, to store a 15 digits number in mysql (with no decimals) you would need a BIGINT which allows numbers up to 9223372036854775807 which is more than 15 digits. There is no easy way to just setup the schema for that kind of thing.

Other cases, like the one for alphanumeric with 20 chars maximum can be accomplished though, using a VARCHAR(20) for instance, but it is still impractical to rely on the database for that.

Best bet is to validate your input at the UI level and maybe as an added security step, check on your database models or wherever you do the inserts/updates.

One good example for why doing constraints at the database schema and specific validations on the code is when your application business rules change in a way that you need to be flexible in the input, say that eventually you need to allow for specific scenarios where codes can be either 10 or 15 digits, you still want to allow the database to store numbers of both lengths but you would validate either case at your application validations.