时间戳

首页 > 解决方案 > ORDER BY 在两个几乎相同的服务器中抛出不同的结果

sql - ORDER BY 在两个几乎相同的服务器中抛出不同的结果

问题描述

我对以下排序有疑问：

SELECT name FROM unnest(ARRAY[
    'MIR1290', 'MIR1291', 'MIR129-1', 'MIR1292', 'MIR129-2', 'MIR1293', 'MIR1294', 'MIR1296', 'MIR1297', 'MIR1298', 'MIR1299'
]) name ORDER BY name;

在一台服务器中排序如下：

"name"
"MIR1290"
"MIR129-1"
"MIR1291"
"MIR129-2"
"MIR1292"
"MIR1293"
"MIR1294"
"MIR1296"
"MIR1297"
"MIR1298"
"MIR1299"

在另一个如下：

"name"
"MIR1290"
"MIR1291"
"MIR129-1"
"MIR1292"
"MIR129-2"
"MIR1293"
"MIR1294"
"MIR1296"
"MIR1297"
"MIR1298"
"MIR1299"

如您所见，出于某种原因，“MIR1291”和“MIR129-1”在两个服务器中的排序方式不同。数据库已从同一个文件转储到两台服务器中，因此它不应该是数据库的问题。两台服务器的语言环境相同：

LANG=en_US.UTF-8
LANGUAGE=en_US:en
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

postgres 和版本的配置在两台服务器上是相同的，“SHOW ALL”的输出仅在以下方面有所不同：

 max_connections                     | 500                                      | Sets the maximum number of concurrent connections.

pg_config 是一样的：

    BINDIR = /usr/lib/postgresql/9.6/bin
DOCDIR = /usr/share/doc/postgresql-doc-9.6
HTMLDIR = /usr/share/doc/postgresql-doc-9.6
INCLUDEDIR = /usr/include/postgresql
PKGINCLUDEDIR = /usr/include/postgresql
INCLUDEDIR-SERVER = /usr/include/postgresql/9.6/server
LIBDIR = /usr/lib/x86_64-linux-gnu
PKGLIBDIR = /usr/lib/postgresql/9.6/lib
LOCALEDIR = /usr/share/locale
MANDIR = /usr/share/postgresql/9.6/man
SHAREDIR = /usr/share/postgresql/9.6
SYSCONFDIR = /etc/postgresql-common
PGXS = /usr/lib/postgresql/9.6/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--with-tcl' '--with-perl' '--with-python' '--with-pam' '--with-openssl' '--with-libxml' '--with-libxslt' '--with-tclconfig=/usr/lib/x86_64-linux-gnu/tcl8.6' '--with-includes=/usr/include/tcl8.6' 'PYTHON=/usr/bin/python' '--mandir=/usr/share/postgresql/9.6/man' '--docdir=/usr/share/doc/postgresql-doc-9.6' '--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/' '--datadir=/usr/share/postgresql/9.6' '--bindir=/usr/lib/postgresql/9.6/bin' '--libdir=/usr/lib/x86_64-linux-gnu/' '--libexecdir=/usr/lib/postgresql/' '--includedir=/usr/include/postgresql/' '--enable-nls' '--enable-integer-datetimes' '--enable-thread-safety' '--enable-tap-tests' '--enable-debug' '--disable-rpath' '--with-uuid=e2fs' '--with-gnu-ld' '--with-pgport=5432' '--with-system-tzdata=/usr/share/zoneinfo' '--with-systemd' 'CFLAGS=-g -O2 -specs=/usr/share/dpkg/no-pie-compile.specs -fstack-protector-strong -Wformat -Werror=format-security -I/usr/include/mit-krb5 -fPIC -pie -fno-omit-frame-pointer' 'LDFLAGS=-specs=/usr/share/dpkg/no-pie-link.specs -Wl,-z,relro -Wl,-z,now -Wl,--as-needed -L/usr/lib/mit-krb5 -L/usr/lib/x86_64-linux-gnu/mit-krb5' '--with-krb5' '--with-gssapi' '--with-ldap' '--with-selinux' 'CPPFLAGS=-Wdate-time -D_FORTIFY_SOURCE=2'
CC = gcc
CPPFLAGS = -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include/tcl8.6
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -g -O2 -specs=/usr/share/dpkg/no-pie-compile.specs -fstack-protector-strong -Wformat -Werror=format-security -I/usr/include/mit-krb5 -fPIC -pie -fno-omit-frame-pointer
CFLAGS_SL = -fPIC
LDFLAGS = -specs=/usr/share/dpkg/no-pie-link.specs -Wl,-z,relro -Wl,-z,now -Wl,--as-needed -L/usr/lib/mit-krb5 -L/usr/lib/x86_64-linux-gnu/mit-krb5 -Wl,--as-needed
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -lselinux -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -ledit -lrt -lcrypt -ldl -lm
VERSION = PostgreSQL 9.6.17

我认为两个服务器之间唯一相关的区别是内核：

4.9.0-8-amd64 #1 SMP Debian 4.9.130-2 (2018-10-27)
4.9.0-9-amd64 #1 SMP Debian 4.9.168-1+deb9u3 (2019-06-16)

有谁知道什么会影响两个装置之间的不同排序？

编辑：更多信息，以下查询在两个服务器中返回相同：

select datname, 
       datcollate
from pg_database;

回报：

    datname    | datcollate
---------------+-------------
 postgres      | en_US.UTF-8
 mydb          | en_US.UTF-8

以下内容不会在任何服务器中返回任何内容：

select table_schema, 
       table_name, 
       column_name,
       collation_name
from information_schema.columns
where collation_name is not null
order by table_schema,
         table_name,
         ordinal_position;

标签： sqlpostgresqldebiansql-order-by

解决方案

指定COLLATE以尝试在两台服务器上获得相同的结果。

SELECT name 
FROM unnest(ARRAY[
    'MIR1290', 'MIR1291', 'MIR129-1', 'MIR1292', 'MIR129-2'
  , 'MIR1293', 'MIR1294', 'MIR1296', 'MIR1297', 'MIR1298', 'MIR1299'
]) name 
ORDER BY name COLLATE "en-US-x-icu";

推荐阅读