MySQL与PHP的最佳排序规则是什么？

我想知道如果有一个“最佳”的选择，在MySQL的一般网站，你不是100%确定将输入什么？我知道所有的编码应该是相同的，如MySQL，Apache，超文本标记语言和PHP中的任何东西。

过去我将PHP设置为“UTF-8”输出，但是在MySQL中哪个排序规则匹配？我认为它是UTF-8之一，但我以前使用过utf8_unicode_ci、utf8_general_ci和utf8_bin。

481693 次浏览
小开

对于UTF-8文本信息，您应该使用utf8_general_ci，因为…

  • utf8_bin：比较字符串中每个字符的二进制值字符串

  • utf8_general_ci：比较字符串使用一般语言规则和使用不区分大小写的比较

又名。它将使搜索和索引数据更快/更有效/更有用。

小开
最佳答案

主要区别是排序精度（在比较语言中的字符时）和性能。唯一特殊的是utf8_bin，用于比较二进制格式的字符。

utf8_general_ci比utf8_unicode_ci快一些，但不太准确（用于排序）。特定语言utf8编码（例如utf8_swedish_ci）包含额外的语言规则，使它们对这些语言的排序最准确。大多数时候我使用utf8_unicode_ci（我更喜欢准确性而不是小的性能改进），除非我有充分的理由更喜欢特定的语言。

您可以在MySQL手册上阅读有关特定Unicode字符集的更多信息-http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html

小开

实际上，您可能希望使用utf8_unicode_ci或utf8_general_ci。

  • utf8_general_ci通过剥离所有重音并像ASCII一样排序
  • utf8_unicode_ci使用Unicode排序顺序，因此它在更多语言中正确排序

但是，如果您仅使用它来存储英文文本，则它们不应有所不同。

小开

排序规则会影响数据的排序方式以及字符串之间的比较方式。这意味着您应该使用大多数用户期望的排序规则。

来自留档字符集Unicode的示例：

utf8_general_ci也令人满意德语和法语都适用，除了“β”等于“s”，而不是'ss'。如果这对你来说是可以接受的应用程序，那么您应该使用utf8_general_ci因为它更快。否则，使用utf8_unicode_ci因为它更准确。

所以-这取决于您预期的用户群以及您需要正确排序的程度。对于英语用户群，utf8_general_ci应该就足够了，对于其他语言，如瑞典语，已经创建了特殊的排序规则。

小开

非常非常清楚使用utf8_general_ci时可能会出现的问题。

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

这个问题至少在早期的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;
小开

本质上，这取决于你如何看待字符串。

由于Guus强调的问题，我总是使用utf8_bin。在我看来，就数据库而言，字符串仍然只是一个字符串。字符串是许多UTF-8字符。字符具有二进制表示，为什么它需要知道您使用的语言？通常，人们将为具有多语言站点范围的系统构建数据库。这就是使用UTF-8作为字符集的全部意义。我有点纯粹主义者，但我认为bug风险远远超过您在索引方面可能获得的微小优势。任何与语言相关的规则都应该在比DBMS更高的级别上完成。

在我的书中，“价值”在一百万年内永远不会等于“价值”。

如果我想存储一个文本字段并进行不区分大小写的搜索，我将使用带有PHP函数的MYSQL字符串函数，例如LOWER（）和php函数strtolow（）。

小开

对于Guus强调的情况，我强烈建议使用utf8_unicode_cs（区分大小写，严格匹配，大部分正确排序）而不是utf8_bin（严格匹配，不正确排序）。

如果要搜索该字段，而不是为用户匹配，则使用utf8_general_ci或utf8_unicode_ci。两者都是不区分大小写的，将进行有损匹配（'β'等于's'，而不是'ss'）。也有特定语言的版本，如utf8_german_ci，其中有损匹配更适合指定的语言。

[编辑-近6年后]

我不再推荐MySQL上的“utf8”字符集，而是推荐“utf8mb4”字符集。它们几乎完全匹配，但允许更多的Unicode字符。

实际上，MySQL应该更新“utf8”字符集和相应的排序规则以匹配“utf8”规范，而是使用单独的字符集和相应的排序规则，以免影响那些已经使用其不完整的“utf8”字符集的存储指定。

小开

公认的答案相当明确地建议使用utf8_unicode_ci，而对于很棒的新项目，我想联系我最近的相反经验，以防它节省任何人一些时间。

因为utf8_general_ci是MySQL中Unicode的默认排序规则，如果你想使用utf8_unicode_ci那么你最终必须在很多的位置指定它。

例如，所有客户端连接不仅有一个默认字符集（对我来说是有意义的），还有一个默认排序规则（即排序规则将始终默认为Unicode的utf8_general_ci）。

如果您对字段使用utf8_unicode_ci，则可能需要更新连接到数据库的脚本以显式提及所需的排序规则-否则，当您的连接使用默认排序规则时，使用文本字符串的查询可能会失败。

结果是，当将任何大小的现有系统转换为Unicode/utf8时，由于MySQL处理默认值的方式，您最终可能会被迫使用utf8_general_ci。

小开

最好将字符集utf8mb4与归类utf8mb4_unicode_ci一起使用。

字符集utf8仅支持少量UTF-8代码点，约占可能字符的6%。utf8仅支持基本多语言平面（BMP）。还有16个其他平面。每个平面包含65,536个字符。utf8mb4支持所有17个平面。

MySQL将截断4字节UTF-8字符，导致数据损坏。

utf8mb4字符集于2010-03-24在MySQL 5.5.3中引入。

使用新字符集所需的一些更改并非易事：

  • 可能需要在应用程序数据库适配器中进行更改。
  • 需要对my.cnf进行更改，包括设置字符集、排序规则和innodb_file_formatBarracuda
  • SQLCREATE语句可能需要包括：ROW_FORMAT=DYNAMIC
    • VARCHAR（192）及更大版本上的索引需要DYNAMIC。

注意：从Antelope切换到Barracuda，可能需要多次重新启动MySQL服务。innodb_file_format_max在MySQL服务重新启动到：innodb_file_format = barracuda之前不会更改。

MySQL使用旧的Antelope InnoDB文件格式。Barracuda支持动态行格式，如果您不想在切换到字符集后遇到创建索引和键的SQL错误，您将需要这种格式：utf8mb4

  • #1709-索引列大小太大。最大列大小为767字节。
  • #1071-指定的密钥太长；最大密钥长度为767字节

以下场景已在MySQL 5.6.17上测试：默认情况下，MySQL配置如下：

SHOW VARIABLES;
innodb_large_prefix = OFFinnodb_file_format = Antelope

停止您的MySQL服务并将选项添加到您现有的my.cnf：

[client]default-character-set= utf8mb4
[mysqld]explicit_defaults_for_timestamp = trueinnodb_large_prefix = trueinnodb_file_format = barracudainnodb_file_format_max = barracudainnodb_file_per_table = true
# Character collationcharacter_set_server=utf8mb4collation_server=utf8mb4_unicode_ci

CREATE语句SQL示例：

CREATE TABLE Contacts (id INT AUTO_INCREMENT NOT NULL,ownerId INT DEFAULT NULL,created timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,contact VARCHAR(640) NOT NULL,prefix VARCHAR(128) NOT NULL,first VARCHAR(128) NOT NULL,middle VARCHAR(128) NOT NULL,last VARCHAR(128) NOT NULL,suffix VARCHAR(128) NOT NULL,notes MEDIUMTEXT NOT NULL,INDEX IDX_CA367725E05EFD25 (ownerId),INDEX created (created),INDEX modified_idx (modified),INDEX contact_idx (contact),PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT=DYNAMIC;
  • 如果从CREATE语句中删除ROW_FORMAT=DYNAMIC，您可以看到为INDEX contact_idx (contact)生成的错误ROW_FORMAT=DYNAMIC709。

注意：将索引更改为限制为contact上的前128个字符可以消除将Barracuda与ROW_FORMAT=DYNAMIC一起使用的要求 

INDEX contact_idx (contact(128)),

另请注意：当它说字段的大小为VARCHAR(128)时，那不是128字节。您可以使用有128,4字节字符或128,1字节字符。

这个INSERT语句应该包含第2行中的4字节'poo'字符：

INSERT INTO `Contacts` (`id`, `ownerId`, `created`, `modified`, `contact`, `prefix`, `first`, `middle`, `last`, `suffix`, `notes`) VALUES(1, NULL, '0000-00-00 00:00:00', '2014-08-25 03:00:36', '1234567890', '12345678901234567890', '1234567890123456789012345678901234567890', '1234567890123456789012345678901234567890', '12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678', '', ''),(2, NULL, '0000-00-00 00:00:00', '2014-08-25 03:05:57', 'poo', '12345678901234567890', '💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩', '💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩', '💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩', '', ''),(3, NULL, '0000-00-00 00:00:00', '2014-08-25 03:05:57', 'poo', '12345678901234567890', '💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩', '💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩', '123💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩', '', '');

您可以看到last列使用的空间量：

mysql> SELECT BIT_LENGTH(`last`), CHAR_LENGTH(`last`) FROM `Contacts`;+--------------------+---------------------+| BIT_LENGTH(`last`) | CHAR_LENGTH(`last`) |+--------------------+---------------------+|               1024 |                 128 | -- All characters are ASCII|               4096 |                 128 | -- All characters are 4 bytes|               4024 |                 128 | -- 3 characters are ASCII, 125 are 4 bytes+--------------------+---------------------+

在您的数据库适配器中，您可能希望为您的连接设置字符集和排序规则：

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'

在PHP中，这将设置为：\PDO::MYSQL_ATTR_INIT_COMMAND

参考文献：

小开

我发现这些整理图表很有帮助。http://collation-charts.org/mysql60/。但我不确定哪个是常用utf8_general_ci。

例如，这里是utf8_swedish_ci的图表。它显示了它解释为相同的字符。http://collation-charts.org/mysql60/mysql604.utf8_swedish_ci.html

小开

在您的数据库上传文件中，在任何行之前添加以下行：

SET NAMES utf8;

你的问题应该得到解决。


提交答案