MySQL 不区分大小写的选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3936967/
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
MySQL case insensitive select
提问by NoodleOfDeath
Can anyone tell me if a MySQL SELECTquery is case sensitive or case insensitive by default? And if not, what query would I have to send so that I can do something like:
谁能告诉我SELECT默认情况下MySQL查询是区分大小写还是不区分大小写？如果没有，我必须发送什么查询以便我可以执行以下操作：
SELECT * FROM `table` WHERE `Value` = "iaresavage"
Where in actuality, the real value of Valueis IAreSavage.
实际上， 的真正值Value是IAreSavage。
回答by Marc B
They are case insensitive, unless you do a binary comparison.
它们不区分大小写，除非您进行二进制比较。
回答by Colin Hebert
You can lowercase the value and the passed parameter :
您可以将值和传递的参数小写：
SELECT * FROM `table` WHERE LOWER(`Value`) = LOWER("IAreSavage")
Another (better) way would be to use the COLLATEoperator as said in the documentation
另一种（更好的）方法是使用文档中COLLATE所说的操作符
回答by Someone
USE BINARY
使用二进制
This is a simple select
这是一个简单的选择
SELECT * FROM myTable WHERE 'something' = 'Something'
SELECT * FROM myTable WHERE 'something' = 'Something'
= 1
= 1
This is a select with binary
这是一个带有二进制的选择
SELECT * FROM myTable WHERE BINARY 'something' = 'Something'
SELECT * FROM myTable WHERE BINARY 'something' = 'Something'
or
或者
SELECT * FROM myTable WHERE 'something' = BINARY 'Something'
SELECT * FROM myTable WHERE 'something' = BINARY 'Something'
= 0
= 0
回答by ostrokach
Comparisons are case insensitivewhen the column uses a collation which ends with _ci(such as the defaultlatin1_general_cicollation) and they are case sensitivewhen the column uses a collation which ends with _csor _bin(such as the utf8_unicode_csand utf8_bincollations).
当列使用以 结尾的排序规则（例如默认排序规则）时，比较不区分大小写；当列使用以or结尾的排序规则（例如and排序规则）时，比较区分大小写。_cilatin1_general_ci_cs_binutf8_unicode_csutf8_bin
Check collation
检查整理
You can check your server, databaseand connectioncollations using:
您可以使用以下方法检查服务器、数据库和连接排序规则：
mysql> show variables like '%collation%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
and you can check your tablecollation using:
您可以使用以下方法检查您的表格整理：
mysql> SELECT table_schema, table_name, table_collation
FROM information_schema.tables WHERE table_name = `mytable`;
+----------------------+------------+-------------------+
| table_schema | table_name | table_collation |
+----------------------+------------+-------------------+
| myschema | mytable | latin1_swedish_ci |
Change collation
更改排序规则
You can change your database, table, or column collation to something case sensitive as follows:
您可以将数据库、表或列的排序规则更改为区分大小写的内容，如下所示：
-- Change database collation
ALTER DATABASE `databasename` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
-- or change table collation
ALTER TABLE `table` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
-- or change column collation
ALTER TABLE `table` CHANGE `Value`
`Value` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin;
Your comparisons should now be case-sensitive.
您的比较现在应该区分大小写。
回答by Ifan Iqbal
String comparison in WHERE phrase is not case sensitive. You could try to compare using
WHERE 短语中的字符串比较不区分大小写。你可以尝试比较使用
WHERE `colname` = 'keyword'
or
或者
WHERE `colname` = 'KeyWord'
and you will get the same result. That is default behavior of MySQL.
你会得到同样的结果。这是 MySQL 的默认行为。
If you want the comparison to be case sensitive, you could add COLLATEjust like this:
如果您希望比较区分大小写，您可以COLLATE像这样添加：
WHERE `colname` COLLATE latin1_general_cs = 'KeyWord'
That SQL would give different result with this one:
WHERE colnameCOLLATE latin1_general_cs = 'keyword'
该 SQL 将给出与此不同的结果： WHERE colnameCOLLATE latin1_general_cs = 'keyword'
latin1_general_csis common or default collation in most database.
latin1_general_cs在大多数数据库中是常见的或默认的排序规则。
回答by chuck taylor
The collation you pick sets whether you are case sensitive or not.
您选择的排序规则设置是否区分大小写。
回答by Ken Johnson
The default is case insensitive, but the next most important thing you should take a look at is how the table was created in the first place, because you can specify case sensitivity when you create the table.
默认情况下不区分大小写，但您应该首先了解表的创建方式，因为您可以在创建表时指定区分大小写。
The script below creates a table. Notice down at the bottom it says "COLLATE latin1_general_cs". That cs at the end means case sensitive. If you wanted your table to be case insensitive you would either leave that part out or use "COLLATE latin1_general_ci".
下面的脚本创建一个表。注意在底部它写着“COLLATE latin1_general_cs”。最后的 cs 表示区分大小写。如果您希望您的表格不区分大小写，您可以将该部分省略或使用“COLLATE latin1_general_ci”。
CREATE Table PEOPLE (
USER_ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
FIRST_NAME VARCHAR(50) NOT NULL,
LAST_NAME VARCHAR(50) NOT NULL,
PRIMARY KEY (USER_ID)
)
ENGINE=MyISAM DEFAULT CHARACTER SET latin1
COLLATE latin1_general_cs AUTO_INCREMENT=0;
If your project is such that you can create your own table, then it makes sense to specify your case sensitivity preference when you create the table.
如果您的项目可以创建自己的表，那么在创建表时指定区分大小写的首选项是有意义的。
回答by Jacob Nelson
SQL Select is not case sensitive.
SQL Select 不区分大小写。
This link can show you how to make is case sensitive: http://web.archive.org/web/20080811231016/http://sqlserver2000.databases.aspfaq.com:80/how-can-i-make-my-sql-queries-case-sensitive.html
这个链接可以告诉你如何制作区分大小写：http: //web.archive.org/web/20080811231016/http: //sqlserver2000.databases.aspfaq.com: 80/how-can-i-make- my- sql-queries-case-sensitive.html
回答by VS-java
Try with:
尝试：
order by lower(column_name) asc;
回答by Wes Grant
Note also that table names are case sensitive on Linux unless you set the lower_case_table_nameconfig directive to 1. This is because tables are represented by files which are case sensitive in Linux.
另请注意，表名在 Linux 上区分大小写，除非您将lower_case_table_nameconfig 指令设置为1。这是因为表由 Linux 中区分大小写的文件表示。
Especially beware of development on Windows which is not case sensitive and deploying to production where it is. For example:
尤其要注意 Windows 上的开发，它不区分大小写并部署到生产环境。例如：
"SELECT * from mytable"
against table myTable will succeed in Windows but fail in Linux, again, unless the abovementioned directive is set.
除非设置了上述指令，否则针对 table myTable 将在 Windows 中成功但在 Linux 中失败。
Reference here: http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html
参考这里：http: //dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitive.html
