php mysql 排序规则_使用PHP的MySQL最好的排序规则是什么？

料青山看我应如是

非常，非常了解使用时可能出现的这个问题utf8_general_ci。如果使用utf8_general_ci排序规则，MySQL将不会区分select语句中的某些字符。这可能导致非常讨厌的错误 - 特别是例如涉及用户名的错误。根据使用数据库表的实现，此问题可能允许恶意用户创建与管理员帐户匹配的用户名。这个问题至少在早期的5.x版本中暴露出来 - 我不确定这种行为是否会在以后发生变化。我不是DBA，但为了避免这个问题，我总是选择utf8-bin而不是不区分大小写。下面的脚本通过示例描述了问题。-- first, create a sandbox to play inCREATE DATABASE `sandbox`;use `sandbox`;-- next, make sure that your client connection is of the same -- character/collate type as the one we're going to test next:charset utf8 collate utf8_general_ci-- now, create the table and fill it with valuesCREATE TABLE `test` (`key` VARCHAR(16), `value` VARCHAR(16) )    CHARACTER SET utf8 COLLATE utf8_general_ci;INSERT INTO `test` VALUES ('Key ONE', 'value'), ('Key TWO', 'valúe');-- (verify)SELECT * FROM `test`;-- now, expose the problem/bug:SELECT * FROM test WHERE `value` = 'value';---- Note that we get BOTH keys here! MySQLs UTF8 collates that are -- case insensitive (ending with _ci) do not distinguish between -- both values!---- collate 'utf8_bin' doesn't have this problem, as I'll show next:---- first, reset the client connection charset/collate typecharset utf8 collate utf8_bin-- next, convert the values that we've previously inserted in the tableALTER TABLE `test` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;-- now, re-check for the bugSELECT * FROM test WHERE `value` = 'value';---- Note that we get just one key now, as you'd expect.---- This problem appears to be specific to utf8. Next, I'll try to -- do the same with the 'latin1' charset:---- first, reset the client connection charset/collate typecharset latin1 collate latin1_general_ci-- next, convert the values that we've previously inserted-- in the tableALTER TABLE `test` CONVERT TO CHARACTER SET latin1 COLLATE latin1_general_ci;-- now, re-check for the bugSELECT * FROM test WHERE `value` = 'value';---- Again, only one key is returned (expected). This shows -- that the problem with utf8/utf8_generic_ci isn't present -- in latin1/latin1_general_ci---- To complete the example, I'll check with the binary collate-- of latin1 as well:-- first, reset the client connection charset/collate typecharset latin1 collate latin1_bin-- next, convert the values that we've previously inserted in the tableALTER TABLE `test` CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin;-- now, re-check for the bugSELECT * FROM test WHERE `value` = 'value';---- Again, only one key is returned (expected).---- Finally, I'll re-introduce the problem in the exact same -- way (for any sceptics out there):-- first, reset the client connection charset/collate typecharset utf8 collate utf8_generic_ci-- next, convert the values that we've previously inserted in the tableALTER TABLE `test` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;-- now, re-check for the problem/bugSELECT * FROM test WHERE `value` = 'value';---- Two keys.--DROP DATABASE sandbox;