数据库 - PostgreSQL、MySQL、Oracle，查询的默认排序是怎样的？

Question

意思是指SELECT语句不加ORDER BY的情况下，是根据什么来排序的？多次查询此排序是否会变化？谢谢。

Answer 1

MySQL does not have a default sorting. If the sorting is not specified, the order of data returned by multiple queries may be different. http://stackoverflow.com/ques...

Answer 2

PG also has no default sorting, and the return order is unreliable if the sorting is not specified
I am the official wiki

Answer 3

This article introduces the Chinese character sorting of PG, MySQL, and Oracle, which may be of some reference: http://mp.weixin.qq.com/s?__b...

Answer 4

You can use the COLLATE keyword to specify the sorting rules when building a MySQL database or table.
For example, WordPress:

数据库:
CREATE DATABASE IF NOT EXISTS `wordpress` 
DEFAULT CHARACTER SET utf8 
COLLATE utf8_general_ci;

评论表:
CREATE TABLE IF NOT EXISTS `wp_comments` (
  `comment_ID` bigint(20) unsigned NOT NULL,
  `comment_post_ID` bigint(20) unsigned NOT NULL DEFAULT '0',
  `comment_author` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `comment_author_email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_author_url` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_author_IP` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_content` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `comment_karma` int(11) NOT NULL DEFAULT '0',
  `comment_approved` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '1',
  `comment_agent` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  `user_id` bigint(20) unsigned NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The sorting rule utf8_general_ci in MySQL is not case sensitive, and ci is the abbreviation of case insensitive, that is, it is not case sensitive.
For example, if the primary key content is A, inserting a will not be allowed, but searching for a can find A. That is, A and a are treated as the same in character judgment.
Collation rules specify the rules for sorting and comparing string data according to the standards of a specific language and locale.

The concept of sorting rules, taking PHP’s intl extension as an example:

<?php
header('Content-Type: text/html; charset=utf-8');
$arr = array('中国','华山','华夏','中华','重阳','重量','b','a',2,1);
collator_sort(collator_create('zh_CN'), $arr);
var_export($arr);
//输出(可见汉字按照拼音排序,但不能识别多音字"重"):
array (
  0 => 'a',
  1 => 'b',
  2 => '华山',
  3 => '华夏',
  4 => '中国',
  5 => '中华',
  6 => '重量',
  7 => '重阳',
  8 => 1,
  9 => 2,
)