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

非常，非常了解使用时可能出现的这个问题utf8_general_ci。

如果使用utf8_general_ci排序规则，MySQL将不会区分select语句中的某些字符。这可能导致非常讨厌的错误 - 特别是例如涉及用户名的错误。根据使用数据库表的实现，此问题可能允许恶意用户创建与管理员帐户匹配的用户名。

这个问题至少在早期的5.x版本中暴露出来 - 我不确定这种行为是否会在以后发生变化。

我不是DBA，但为了避免这个问题，我总是选择utf8-bin而不是不区分大小写。

下面的脚本通过示例描述了问题。

-- first, create a sandbox to play in

CREATE 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 values

CREATE 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 type

charset utf8 collate utf8_bin

-- next, convert the values that we've previously inserted in the table

ALTER TABLE `test` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

-- now, re-check for the bug

SELECT * 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 type

charset latin1 collate latin1_general_ci

-- next, convert the values that we've previously inserted

-- in the table

ALTER TABLE `test` CONVERT TO CHARACTER SET latin1 COLLATE latin1_general_ci;

-- now, re-check for the bug

SELECT * 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 type

charset latin1 collate latin1_bin

-- next, convert the values that we've previously inserted in the table

ALTER TABLE `test` CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin;

-- now, re-check for the bug

SELECT * 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 type

charset utf8 collate utf8_generic_ci

-- next, convert the values that we've previously inserted in the table

ALTER TABLE `test` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- now, re-check for the problem/bug

SELECT * FROM test WHERE `value` = 'value';

--

-- Two keys.

--

DROP DATABASE sandbox;