将 Mysql 表列更改为区分大小写
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3396253/
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
Altering Mysql Table column to be case sensitive
提问by PaiS
I have a table in my Mysql database, which is used for authentication. And now, I need to make the authentication case sensitive. Googling around, I have realized Mysql columns are case insensitive (contrary to Oracle) for search operations and the default behavior can be changed while creating the table by specifying the "binary" ie.
我的 Mysql 数据库中有一个表，用于身份验证。现在，我需要使身份验证区分大小写。谷歌搜索，我已经意识到 Mysql 列对于搜索操作不区分大小写（与 Oracle 相反），并且可以通过指定“二进制”即在创建表时更改默认行为。
CREATE TABLE USERS
(
USER_ID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
USER_NAME VARCHAR(50) BINARY NOT NULL
)
Can someone please tell me how to alter the table in Mysql to add the "Binary" to an existing column of a DB?
有人可以告诉我如何更改 Mysql 中的表以将“二进制”添加到数据库的现有列中吗？
Thanks!
谢谢！
回答by deinst
ALTER TABLE USERS CHANGE USER_NAME USER_NAME VARCHAR(50) BINARY NOT NULL;
回答by defines
Please see http://dev.mysql.com/doc/refman/5.0/en/charset-conversion.html
请参阅http://dev.mysql.com/doc/refman/5.0/en/charset-conversion.html
Example:
例子：
ALTER TABLE some_table MODIFY some_column BLOB;
ALTER TABLE some_table MODIFY some_column VARCHAR(50) BINARY;
The first line converts to a binary data type (attempt to minimize character loss) and the second converts back to the VARCHARtype with BINARYcollation.
第一行转换为二进制数据类型（尝试最小化字符丢失），第二行转换回VARCHAR带有BINARY排序规则的类型。
It may actually be preferable to store as one of the binary types (BLOB, BINARY, or VARBINARY) rather than simply collating BINARY. I would suggest you compare a bit, your mileage may vary depending on your actual data and the queries you need to run.
实际上，最好将其存储为二进制类型 ( BLOB, BINARY, 或VARBINARY) 之一，而不是简单地整理BINARY。我建议你比较一下，你的里程可能会有所不同，具体取决于你的实际数据和你需要运行的查询。
回答by Falle1234
You should be able to do something like this:
你应该能够做这样的事情：
Edit: Misread what you intended to do:
编辑：误读你打算做什么：
ALTER TABLE USERS MODIFY
USER_NAME VARCHAR(50)
CHARACTER SET latin1
COLLATE latin1_bin;
回答by SimonDowdles
Rather than altering your table, you can still perform case sensitive queries on your table when authenticating, use the BINARY option as follows:
验证时，您仍然可以对表执行区分大小写的查询，而不是更改您的表，请使用 BINARY 选项，如下所示：
SELECT BINARY * FROM USERS where USER_ID = 2 AND USER_NAME = 'someone' LIMIT 1;
Does this help?
这有帮助吗？
