2赞

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

作者：帆侮听我悄悄说星星 | 2023-09-01 20:21

如何解决《使用PHP的MySQL最好的排序规则是什么？》经验，为你挑选了10个好方法。

我不知道是否有一个"最佳"的整理在MySQL的综合性网站,你是不是100%确定了所要进入的选择吗？我知道所有编码应该是相同的,例如MySQL,Apache,HTML和PHP中的任何内容.

在过去,我已将PHP设置为以"UTF-8"输出,但哪种排序规则在MySQL中匹配？我想它是UTF-8的人之一,但我已经使用utf8_unicode_ci,utf8_general_ci和utf8_bin之前.

1> Eran Galperi..：

主要区别在于排序准确性(比较语言中的字符)和性能.唯一特别的是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

这适用于DB2而不是MySQL.此外,没有具体的数字或基准,所以你只是基于作者的意见.

更新:对于较新的版本,建议使用`utf8mb4`和`utf8mb4_unicode_520_ci`.这些给你剩下的中文,加上改进的整理.

小的性能改进？你确定吗？http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.admin.nls.doc/doc/c0053263.html您选择的排序规则可以显着影响数据库中查询的性能.

请注意,如果您想使用函数,MySQL中存在一个错误(大多数当前是分布式版本),其中函数总是使用utf8_general_ci返回字符串,如果您对字符串使用另一个排序规则会导致问题 - 请参阅http:// bugs. mysql.com/bug.php?id=24690

2> Vegard Larse..：

实际上,你可能想要使用utf8_unicode_ci或utf8_general_ci.

utf8_general_ci 通过剥离所有重音和排序就像它是ASCII一样排序

utf8_unicode_ci 使用Unicode排序顺序,因此它可以在更多语言中正确排序

但是,如果您仅使用它来存储英文文本,则这些不应该有所不同.

@Adam这真的取决于你的目标受众.排序是正确本地化的棘手问题.例如,在挪威语中,字母ÆØÅ是字母表中的最后3个字母.使用utf8_general_ci,Ø和Å会转换为O和A,这会在排序时将它们置于完全错误的位置(我不确定如何处理Æ,因为它是一个连字,而不是重音字符).几乎所有语言的排序顺序都不同,例如挪威语和瑞典语有不同的顺序(略有不同的字母被认为是相同的):ÆØÅ分类ÅÆØ(实际字母是ÅÄÖ).Unicode解决了这个问题.

@Manatax - 使用任何utf8_归类,数据存储为utf8.整理只是关于人物被认为是平等的,以及他们如何被命令.

@frymaster - 不是真的,按照:http://mathiasbynens.be/notes/mysql-utf8mb4"MySQL的utf8只允许你存储所有可能的Unicode代码点的5.88%"

3> Guus..：

非常,非常了解使用时可能出现的这个问题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;


        
-1:通过对相关列应用唯一键肯定可以解决这个问题.如果两个值是''value'和''valUe',你会看到相同的行为.整理的关键在于,当两个字符串被认为彼此相等时,它为(除其他之外)提供规则. 
问题是,当您输入两个通过排序规则认为相同的用户名时,如果您将coloumn用户名设置为唯一,则不允许这样做,您当然应该这样做! 
但是你将它描述为一个"问题"并导致"错误",而行为正是整理的目的.您的描述是正确的,但只有在DBA选择不适当的排序规则时才会出错. 
这正是我试图说明的问题 - 整理使得两件事情相等,而事实上它们并不是完全相同的(因此,一个独特的约束与你想要实现的完全相反) 
我赞同这个答案和@Hammerite的评论,因为他们两者的结合帮助我了解了整理. 

4> Jeremy Postl..：
最好使用字符集utf8mb4和排序规则utf8mb4_unicode_ci.

字符集utf8仅支持少量UTF-8代码点,大约6%的可能字符.utf8仅支持基本多语言平面(BMP).还有16架飞机.每个平面包含65,536个字符.utf8mb4支持所有17架飞机.

MySQL会截断4个字节的UTF-8字符,导致数据损坏.

该utf8mb4字符集是在2010年3月24日的MySQL 5.5.3中引入的.

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


可能需要在应用程序数据库适配器中进行更改.
需要对my.cnf进行更改,包括设置字符集,整理和切换innodb_file_format到Barracuda
SQL CREATE语句可能需要包括: ROW_FORMAT=DYNAMIC

VARCHAR(192)及更大版本的索引需要DYNAMIC.



注:切换到Barracuda从Antelope,可能需要重新启动MySQL服务不只一次.innodb_file_format_max直到MySQL服务重新启动后才会改变:innodb_file_format = barracuda.

MySQL使用旧的AntelopeInnoDB文件格式.Barracuda支持动态行格式,如果您不希望在切换到charset后遇到创建索引和键的SQL错误,则需要这些格式:utf8mb4


#1709  - 索引列大小太大.最大列大小为767字节.
#1071  - 指定密钥太长; 最大密钥长度为767字节


以下方案已在MySQL 5.6.17上测试过:默认情况下,MySQL配置如下:

SHOW VARIABLES;

innodb_large_prefix = OFF
innodb_file_format = Antelope


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

[client]
default-character-set= utf8mb4

[mysqld]
explicit_defaults_for_timestamp = true
innodb_large_prefix = true
innodb_file_format = barracuda
innodb_file_format_max = barracuda
innodb_file_per_table = true

# Character collation
character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci


示例SQL CREATE语句:

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语句中删除INDEX contact_idx (contact)if ROW_FORMAT=DYNAMIC而生成的错误#1709 .


注意:将索引更改为限制为前128个字符,contact无需使用BarracudaROW_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
+--------------------+---------------------+


在数据库适配器中,您可能希望为连接设置charset和collation:

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'


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

参考文献:


Mysql 5.6参考手册:InnoDB表的限制
如何在MySQL数据库中支持完整的Unicode

        
utf8mb4_unicode_ci绝对应该是2015年新项目的推荐校对. 
更新......`utf8mb4_unicode_520_ci`更好.将来会有`utf8mb4_unicode_800_ci`(或类似的东西),因为MySQL赶上了Unicode标准. 

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

文档中的示例:


  utf8_general_ci德语和法语也令人满意,只是'ß'等于's',而不是'ss'.如果您的应用程序可以接受,那么您应该使用utf8_general_ci它,
   因为它更快.否则,请使用,utf8_unicode_ci因为它更准确.


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

        

6> 小智..：
从本质上讲,它取决于你如何看待一个字符串.

我总是使用utf8_bin,因为Guus强调了这个问题.在我看来,就数据库而言,字符串仍然只是一个字符串.字符串是多个UTF-8字符.角色有二进制表示,那为什么它需要知道你正在使用的语言？通常,人们将为具有多语言站点范围的系统构建数据库.这是使用UTF-8作为字符集的重点.我有点像一个纯粹主义者,但我认为这个错误风险大大超过了索引编制的微小优势.任何与语言相关的规则都应该在比DBMS高得多的水平上完成.

在我的书中,"价值"绝不应该在一百万年内等于"valúe".

如果我想存储一个文本字段并进行不区分大小写的搜索,我将使用MYSQL字符串函数和PHP函数,如LOWER()和php函数strtolower().

        
如果字符串的二进制比较是您想要的比较,那么您当然应该使用二进制排序规则; 但是,如果将其他排序规则视为"错误风险",或仅仅是为了方便索引,则表明您并未完全理解排序规则. 

7> mepcotterell..：
对于UTF-8文本信息,您应该使用utf8_general_ci因为......


utf8_bin:通过字符串中每个字符的二进制值比较字符串

utf8_general_ci:使用通用语言规则比较字符串并使用不区分大小写的比较



也就是说它应该更快/更有效/更有用地搜索和索引数据.

        

8> George Lund..：
接受的答案相当明确地建议使用utf8_unicode_ci,而对于那些非常棒的新项目,我想提及我最近的相反经验,以防它节省任何人一些时间.

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

例如,所有客户端连接不仅具有默认字符集(对我而言),而且还具有默认排序规则(即排序规则将始终默认为unfode的utf8_general_ci).

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

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

        

9> SEoF..：
对于Guus强调的情况,我强烈建议使用utf8_unicode_cs(区分大小写,严格匹配,大部分正确排序)而不是utf8_bin(严格匹配,错误排序).

如果要搜索该字段,而不是为用户匹配,则使用utf8_general_ci或utf8_unicode_ci.两者都不区分大小写,一个将失去匹配('ß'等于's',而不是's'').还有特定于语言的版本,例如utf8_german_ci,其中丢失匹配更适合于指定的语言.

[编辑 - 近6年后]

我不再推荐MySQL上的"utf8"字符集,而是推荐使用"utf8mb4"字符集.它们几乎完全匹配,但允许一些(很多)更多的unicode字符.

实际上,MySQL应该更新"utf8"字符集和各自的排序规则以匹配"utf8"规范,而是单独的字符集和相应的排序规则,以便不影响那些已经使用其不完整的"utf8"字符集的存储指定.

        
仅供参考:`utf8_unicode_cs`不存在.唯一区分大小写的utf8是`utf8_bin`.问题是`utf8_bin`排序不正确.请参阅:http://stackoverflow.com/questions/15218077/does-a-utf8-unicode-cs-collation-exist/15916398#15916398 

10> 小智..：
我发现这些整理表很有帮助。http://collation-charts.org/mysql60/。我不确定哪个是使用的utf8_general_ci。

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



    

    

    
        推荐阅读
        
            
                                
                    
                        程序员
                        具有支持多线程的限制的异步任务的队列
                    

                    
                                                
                        如何解决《具有支持多线程的限制的异步任务的队列》经验，为你挑选了1个好方法。 ...
                        [详细]
                    
                    

                


                                
                    
                        程序员
                        域驱动设计自动增量实体密钥
                    

                    
                                                
                        如何解决《域驱动设计自动增量实体密钥》经验，为你挑选了1个好方法。 ...
                        [详细]
                    
                    

                


                                
                    
                        程序员
                        使用泛型的覆盖方法
                    

                    
                                                
                        如何解决《使用泛型的覆盖方法》经验，为你挑选了1个好方法。 ...
                        [详细]
                    
                    

                


                                
                    
                        程序员
                        python延迟函数调用
                    

                    
                                                
                        如何解决《python延迟函数调用》经验，为你挑选了1个好方法。 ...
                        [详细]
                    
                    

                


                                
                    
                        程序员
                        如何删除滚动RecyclerView上的阴影
                    

                    
                                                
                        如何解决《如何删除滚动RecyclerView上的阴影》经验，为你挑选了1个好方法。 ...
                        [详细]
                    
                    

                


                                
                    
                        程序员
                        在<URL>中阻止脚本执行.因为文档的框架是沙箱,并且未设置"allow-scripts"权限
                    

                    
                                                
                        如何解决《在<URL>中阻止脚本执行.因为文档的框架是沙箱,并且未设置"allow-scripts"权限》经验，为你挑选了4个好方法。 ...
                        [详细]
                    
                    

                


                                
                    
                        程序员
                        检测ListView何时"向上"或"向下"滚动？Windows Phone 8.1 ListView
                    

                    
                                                
                        如何解决《检测ListView何时"向上"或"向下"滚动？WindowsPhone8.1ListView》经验，为你挑选了1个好方法。 ...
                        [详细]
                    
                    

                


                                
                    
                        程序员
                        Telegram bot api键盘
                    

                    
                                                
                        如何解决《Telegrambotapi键盘》经验，为你挑选了1个好方法。 ...
                        [详细]
                    
                    

                


                                
                    
                        程序员
                        链接是什么:功能"参数"(范围,元素,attrs)？AngularJS
                    

                    
                                                
                        如何解决《链接是什么:功能"参数"(范围,元素,attrs)？AngularJS》经验，为你挑选了1个好方法。 ...
                        [详细]
                    
                    

                


                                
                    
                        程序员
                        添加id并使用它的jquery脚本不起作用
                    

                    
                                                
                        如何解决《添加id并使用它的jquery脚本不起作用》经验，为你挑选了0个好方法。 ...
                        [详细]
                    
                    

                


                                
                    
                        程序员
                        plotly js:如何在加载绘图图像后运行我的javascript
                    

                    
                                                
                        如何解决《plotlyjs:如何在加载绘图图像后运行我的javascript》经验，为你挑选了0个好方法。 ...
                        [详细]
                    
                    

                


                                
                    
                        程序员
                        Stata访问元素作为标量或宏的矩阵
                    

                    
                                                
                        如何解决《Stata访问元素作为标量或宏的矩阵》经验，为你挑选了1个好方法。 ...
                        [详细]
                    
                    

                


                                
                    
                        程序员
                        重构Ruby
                    

                    
                                                
                        如何解决《重构Ruby》经验，为你挑选了1个好方法。 ...
                        [详细]
                    
                    

                


                                
                    
                        程序员
                        java:如何为变量自动生成自定义方法
                    

                    
                                                
                        如何解决《java:如何为变量自动生成自定义方法》经验，为你挑选了0个好方法。 ...
                        [详细]
                    
                    

                


                                
                    
                        程序员
                        自定义URI方案和Skype链接
                    

                    
                                                
                        如何解决《自定义URI方案和Skype链接》经验，为你挑选了0个好方法。 ...
                        [详细]
                    
                    

                


                                
                    
                        程序员
                        有没有办法关闭JIT编译器,这样做会对性能产生影响吗？
                    

                    
                                                
                        如何解决《有没有办法关闭JIT编译器,这样做会对性能产生影响吗？》经验，为你挑选了1个好方法。 ...
                        [详细]
                    
                    

                


                                
                    
                        程序员
                        因大小而无法上传文件
                    

                    
                                                
                        如何解决《因大小而无法上传文件》经验，为你挑选了0个好方法。 ...
                        [详细]
                    
                    

                


                                
                    
                        程序员
                        当键盘出现在swift中时,向上滚动UITableView
                    

                    
                                                
                        如何解决《当键盘出现在swift中时,向上滚动UITableView》经验，为你挑选了1个好方法。 ...
                        [详细]
                    
                    

                


                                
                    
                        程序员
                        Android上的Cordova全屏启动画面仍显示标题栏
                    

                    
                                                
                        如何解决《Android上的Cordova全屏启动画面仍显示标题栏》经验，为你挑选了2个好方法。 ...
                        [详细]
                    
                    

                


                                
                    
                        程序员
                        Swing Ui倍增面板重影
                    

                    
                                                
                            
                        
                                                
                        如何解决《SwingUi倍增面板重影》经验，为你挑选了1个好方法。 ...
                        [详细]
                    
                    

                


                

            
        
    

    
        吐了个 "CAO" !
        
            
                吐个槽吧,看都看了
            
            
                
                                        会员登录 | 用户注册
























    

    
        
            
            
                
                    
                
            

            
                帆侮听我悄悄说星星            

            
                这个屌丝很懒，什么也没留下！            
            
            

                                
                    
                    关注作者
                            

        
    


    
        Tags | 热门标签
        
            
                                
                    actionscrip
                
                                
                    bash
                
                                
                    c#
                
                                
                    c++
                
                                
                    c语言
                
                                
                    erlang
                
                                
                    flutter
                
                                
                    go
                
                                
                    golang
                
                                
                    java
                
                                
                    javascript
                
                                
                    lua
                
                                
                    node.js
                
                                
                    perl
                
                                
                    php
                
                                
                    python
                
                                
                    scala
                
                                
                    typescript
                
                                
            
        
    


    
        RankList | 热门文章
        
            
                                
                    1跳过前nn个字符，其余则使用RegEx
                
                                
                    2在Elastisearch中实施电子邮件提醒的最佳方式是什么？
                
                                
                    3在Phaser中循环播放音频
                
                                
                    4查找2D数组C++中每一行的最大值
                
                                
                    5凤凰城:在视图中使用"conn"和"case"
                
                                
                    6动态IoC控制器解析ASP.NET 5
                
                                
                    7C++尝试块和异常处理
                
                                
                    8使用subset()时确定导致"未定义列选定"的列名称错误
                
                                
                    9如何在散景中设置所选/未选定字形的属性
                
                                
                    10半稳健的报纸柱提取
                
                                
                    11无法读取配置节'entityFramework',因为它缺少节声明
                
                                
                    12如何在pre标签周围创建div
                
                                
                    13ClassNotFoundException  -  com.microsoft.jdbc.sqlserver.SQLServerDriver
                
                                
                    14是否有可能从std :: abort恢复？
                
                                
                    15如何在Javascript中使用异步等待函数对象？
                
                                
                    16401在访问资源时未经授权以编程方式创建azure应用程序
                
                                
                    17ReSharper自定义模式与多个成员定义不明确
                
                                
                    18批量分配问题
                
                                
                    19添加Controller时SpringDataRest @RepositoryEventHandler未运行
                
                                
                    20如何使用Visual Studio的Node Tools调试Gulp任务？