blocks|key|866065|text|如果你想使用DB端验证，你可以使用触发器。请参阅适用于SQLite的this和适用于MySQL的detailed+how-to。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|866066|所以真正的问题是你是否应该使用数据库验证。如果您有多个客户机--无论它们是不同的程序，还是多个用户(可能有不同版本的程序)+--那么使用数据库绝对是最好的。数据库是集中化的(希望如此)，因此您可以将验证的一些细节解耦。在您的特定情况下，您可以验证插入到列中的值是否包含在单独的表中，该表只是列出了有效值。|866067|另一方面，如果您几乎没有使用数据库的经验，计划以几个不同的数据库为目标，并且没有时间开发专业知识，那么简单的应用程序级验证可能是最有利的选择。|866068|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sqlite.org/lang_createtrigger.html|1|http://rpbouman.blogspot.com/2009/12/validating-mysql-data-entry-with_15.html^0|Y|4|0|1C|F|1|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@$A|S|B|T|1|U]|$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|6|7|Y|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|Z|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|10|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]|P|$5|K|L|M|C|$N|Q]]]]

If you want to go with DB-side validation, you can use triggers. See <a href="http://www.sqlite.org/lang_createtrigger.html" rel="nofollow">this</a> for SQLite, and this <a href="http://rpbouman.blogspot.com/2009/12/validating-mysql-data-entry-with_15.html" rel="nofollow">detailed how-to</a> for MySQL.

So the question is really whether you should use Database validation or not. If you have multiple clients -- whether they are different programs, or multiple users (with possibly different versions of the program) -- then going the database route is definitely best. The database is (hopefully) centralized, so you can decouple some of the details of validation. In your particular case, you can verify that the value being inserted into the column is contained in a separate table that simply lists valid values.

On the other hand, if you have little experience with databases, plan to target several different databases, and don't have the time to develop expertise, perhaps simple application level validation is the most expedient choice.

blocks|key|866126|text|您将使用check约束。在SQL+Server中，它是这样工作的|type|unstyled|depth|inlineStyleRanges|entityRanges|data|866127|ALTER+TABLE+Vehicles
ADD+CONSTRAINT+chkVehicleType+CHECK+(VehicleType+in+('car','bike','van'));|code-block|syntax|javascript|866128|我不确定这是否是美国国家标准协会的标准，但我确定MySQL也有类似的结构。|866129|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You would use a check constraint. In SQL Server it works like this

<pre><code>ALTER TABLE Vehicles
ADD CONSTRAINT chkVehicleType CHECK (VehicleType in ('car','bike','van'));
</code></pre>

I'm not sure if this is ANSI standard but I'm certain that MySQL has a similar construct.

blocks|key|866188|text|添加一个包含这些传输方式的新表，并使您的列成为该表的外键。将来可以向表中添加新的传输方式，而您的列定义保持不变。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|866189|通过这种构造，我最终会选择在DB级别进行规范，而不是在应用程序级别。|866190|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Add a new table containing these means of transport, and make your column a foreign key to that table. New means of transport can be added to the table in future, and your column definition remains the same.

With this construction, I would definitively choose to regulate this at the DB level, rather than that of the application.

blocks|key|866283|text|可以，建议添加check约束。Check约束用于确保数据库中数据的有效性并提供数据完整性。如果在数据库级别使用它们，则使用数据库的应用程序将无法添加无效数据或修改有效数据，因此即使应用程序本身接受无效数据，数据也会变为无效。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|866284|在SQLite中：|866285|create+table+MyTable
(
++++name+string+check(name+=+"car"+or+name+=+"bike"+or+name+=+"van")
);|code-block|syntax|javascript|866286|在MySQL中：|866287|create+table+MyTable
(
++++name+ENUM('car',+'bike',+'van')
);|866288|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Yes, it is recommended to add check constraints. Check constraints are used to ensure the validity of data in a database and to provide data integrity. If they are used at the database level, applications that use the database will not be able to add invalid data or modify valid data so the data becomes invalid, even if the application itself accepts invalid data.

In SQLite:

<pre><code>create table MyTable
(
 name string check(name = "car" or name = "bike" or name = "van")
);
</code></pre>

In MySQL:

<pre><code>create table MyTable
(
 name ENUM('car', 'bike', 'van')
);
</code></pre>

I would like to restrict a column value in a SQL table. For example, the column values can only be "car" or "bike" or "van". My question is how do you achieve this in SQL, and is it a good idea to do this on the DB side or should I let the application restrict the input.

I also have the intention to add or remove more values in the future, for example, "truck".

The type of Databases I am using are SQLite and MySQL.

How to restrict a column value in SQLite / MySQL

数据完整性

SQL Server

数据库

SQLite

MySQL

我想限制SQL表中的列值。例如，列值只能是"car“、"bike”或"van“。我的问题是，如何在SQL中实现这一点，在数据库端实现这一点是个好主意，还是应该让应用程序限制输入。我还打算在将来添加或删除更多的值，例如，"truck“。我使用的数据库类型是SQLite和MySQL。

问如何在SQLite / MySQL中限制列值
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQLite / MySQL中限制列值EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQLite / MySQL中限制列值
EN