SQL 唯一 varchar 区分大小写问题

2021-11-20 00:00:00 sql mysql varchar unique-constraint case-sensitive

我正在尝试使用单词列表填充 SQL 表.表格本身非常简单:

I'm trying to populate a SQL table with a list of words. The table itself it pretty simple:

CREATE TABLE WORDS(
  ID BIGINT AUTO_INCREMENT, 
  WORD VARCHAR(128) NOT NULL UNIQUE, 
  PRIMARY KEY(ID)
);

我遇到的问题是:当我执行以下背靠背插入时

The problem I'm running into is this: when I do the following inserts back to back

INSERT INTO WORDS(WORD) VALUES('Seth');
INSERT INTO WORDS(WORD) VALUES('seth');

第二次插入失败，违反约束(重复条目 'seth' 键'WORD'").

The second insert fails with a constraint violation ("Duplicate entry 'seth' for key 'WORD'").

如何使 WORD 上的 UNIQUE 约束区分大小写?

How can I get the UNIQUE constraint on WORD to be case sensitive?

推荐答案

看起来 mysql 是 默认不区分大小写:

Looks like mysql is case insensitive by default:

您可能需要使用区分大小写的排序规则(例如 utf8_bin)创建列:

You probably need to create the column with a case sensitive collation (e.g. utf8_bin):

CREATE TABLE WORDS (
    ID BIGINT AUTO_INCREMENT, 
    WORD VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL UNIQUE, 
    PRIMARY KEY(ID)
);

相关文章