我可以使mysql表列不区分大小写吗? [英] Can I make mysql table columns case insensitive?

问题描述

我是mysql(和sql)的新手，正在尝试查看是否可以使插入表中列的数据不区分大小写.

I am new to mysql (and sql in general) and am trying to see if I can make data inserted into a column in a table case insensitive.

我正在存储诸如州名，城市名等之类的数据.因此，我希望对这些类型的数据具有唯一的约束，并且使它们不区分大小写，以便我可以依靠唯一性约束.

I am storing data like state names, city names, etc. So I want to have a unique constraint on these types of data and on top of that make them case insensitive so that I can rely on the uniqueness constraint.

在表创建过程中或在列上设置唯一性约束时，mysql是否在列上支持不区分大小写的选项?处理此类问题的通常方法是什么?对于解决此问题的任何其他想法/建议，我将不胜感激.

Does mysql support a case-insensitive option on either the column during table creation or alternatively when setting the uniqueness constraint on the column? What is the usual way to deal with such issues? I would appreciate any alternate ideas/suggestions to deal with this.

如所建议的，是否进行COLLATE我认为仅适用于对插入数据的查询.但是要真正利用唯一性约束，我想对INSERT设置不区分大小写的限制.例如我希望mysql不允许插入加利福尼亚，加利福尼亚和加利福尼亚，因为它们应该相同.但是，如果我能正确地理解唯一性约束，则在StateName上保留它仍将允许上述四个插入.

As suggested, does COLLATE I think only applies to queries on the inserted data. But to really take advantage of the uniqueness contraint, I want to have a case insensitivity restriction on INSERT. For e.g. I want mysql to not allow insertions of California and california and cALifornia as they should be the same. But if I understand the uniqueness constraint prooperly, having it on the StateName will still allow the above four inserts.

推荐答案

默认情况下，MySQL不区分大小写.

CREATE TABLE test
(
    name VARCHAR(20),
    UNIQUE(name)
);

mysql>     INSERT INTO test VALUES('California');
Query OK, 1 row affected (0.00 sec)

mysql>     INSERT INTO test VALUES('california');
ERROR 1062 (23000): Duplicate entry 'california' for key 'name'

mysql>     INSERT INTO test VALUES('cAlifornia');
ERROR 1062 (23000): Duplicate entry 'cAlifornia' for key 'name'

mysql>     INSERT INTO test VALUES('cALifornia');
ERROR 1062 (23000): Duplicate entry 'cALifornia' for key 'name'

mysql> SELECT * FROM test;
+------------+
| name       |
+------------+
| California |
+------------+
1 row in set (0.00 sec)

需要区分大小写时使用BINARY

要在MySQL中区分大小写，请按以下方式使用BINARY关键字

Use BINARY when you need case-sensitivity

To make case-sensitive in MySQL, BINARY keyword is used as follows

mysql>     CREATE TABLE test
    ->     (
    ->         name varchar(20) BINARY,
    ->         UNIQUE(name)
    ->     );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>     INSERT INTO test VALUES('California');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>     INSERT INTO test VALUES('california');
Query OK, 1 row affected (0.00 sec)

mysql>     INSERT INTO test VALUES('cAlifornia');
Query OK, 1 row affected (0.00 sec)

mysql>     INSERT INTO test VALUES('cALifornia');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>     SELECT * FROM test;
+------------+
| name       |
+------------+
| California |
| cALifornia |
| cAlifornia |
| california |
+------------+
4 rows in set (0.00 sec)

这篇关于我可以使mysql表列不区分大小写吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！