MySQL SQL 唯一 varchar 区分大小写问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6448825/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL unique varchar case sensitivity question
提问by Seth
I'm trying to populate a SQL table with a list of words. The table itself it pretty simple:
我正在尝试使用单词列表填充 SQL 表。表格本身非常简单：
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');
The second insert fails with a constraint violation ("Duplicate entry 'seth' for key 'WORD'").
第二次插入失败并违反约束（“重复条目 'seth' 键'WORD'”）。
How can I get the UNIQUEconstraint on WORDto be case sensitive?
我怎样才能让UNIQUE约束WORD区分大小写？
回答by Bill Brasky
Looks like mysql is case insensitive by default:
看起来 mysql默认不区分大小写：
You probably need to create the column with a case sensitive collation (e.g. utf8_bin):
您可能需要使用区分大小写的排序规则（例如 utf8_bin）创建列：
CREATE TABLE WORDS (
ID BIGINT AUTO_INCREMENT,
WORD VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL UNIQUE,
PRIMARY KEY(ID)
);
回答by Bryan Field
By default MySQL ignores differences in case and trailing spaces on varchar.
默认情况下，MySQL 忽略大小写和尾随空格的差异varchar。
If you need it to be case sensitive, you can alter the table to be varchar(...) binary.
如果您需要区分大小写，您可以将表更改为varchar(...) binary.
Use show create tableto better understand how MySQL converts this to full notation.
使用show create table以更好地了解MySQL的这个如何转换为完整的符号。
If you need to pay attention to trailing spaces as well as be case sensitive, use varbinaryinstead of varchar.
如果您需要注意尾随空格以及区分大小写，请使用varbinary代替varchar。
回答by suleman
I made my UNIQUE key varchar(1000). It worked.
我做了我的唯一键 varchar(1000)。有效。
After some trial and error, I found anything greater than or equal to 1100 varchar would fail.
经过反复试验，我发现任何大于或等于 1100 varchar 的值都会失败。
To clarify I did not try between 1001 to 1099.
澄清一下，我没有在 1001 到 1099 之间尝试。
Hope this helps.
希望这可以帮助。
