Erlo

PostgreSQL 大小写敏感的那些事

时间:2021-06-09   阅读:34次   来源:开源中国
页面报错
点赞

PostgreSQL和Oracle一样,默认都是大小写不敏感的,但两者仍然存在区别:

  • Oracle:默认是大小写不敏感,表名、字段名等不区分大小写,小写字母会自动转换为大写字母; 需要用小写字母时需要使用双引号,或借助函數upper()和lower();
  • PostgreSQL:默认是大小写不敏感,表名、字段名等不区分大小写,大写字母会自动转换为小写字母; 需要用大写字母时需要使用双引号,或借助函數upper()和lower();

1、表、列名中的大小写敏感

例如我们创建表test,表名写成test、Test、TEST结果均是一样的:

bill@bill=>create table TEST(id int,info text);
CREATE TABLE
bill@bill=>d test
                Table 'public.test'
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |
 info   | text    |           |          |

列名也是同样如此:

bill@bill=>create table test(Id int,INFO text);
CREATE TABLE
bill@bill=>select id,info from test;
 id | info
----+------
(0 rows)

那么如果我们想要指定表或者列名为大写该怎么办呢?

使用双引号即可。

但是可以看到这种方法也很麻烦,因为我们需要查询的时候也要用双引号,所以建议不要这么去使用。

bill@bill=>create table 'TEST'(id int,info text);
CREATE TABLE
bill@bill=>select *  from test;
ERROR:  relation 'test' does not exist
LINE 1: select *  from test;
                       ^
bill@bill=>select * from TEST;
ERROR:  relation 'test' does not exist
LINE 1: select * from TEST;
                      ^
bill@bill=>select * from 'TEST';
 id | info
----+------
(0 rows)

2、查询数据中的大小写敏感

当我们进行数据匹配查询时,是区分大小写的。

例如:

bill@bill=>insert into test values(1,'Bill');
INSERT 0 1
bill@bill=>select * from test where info = 'bill';
 id | info
----+------
(0 rows)
bill@bill=>select * from test where info = 'Bill';
 id | info
----+------
  1 | Bill
(1 row)

这主要是因为pg查询中是否区分大小写取决于操作符,例如我们上面例子中用到的=号。

bill@bill=>do+ =
                                                           List of operators
   Schema   | Name |        Left arg type        |       Right arg type        | Result type |         Function         |  Description
------------+------+-----------------------------+-----------------------------+-------------+--------------------------+---------------
 pg_catalog | =    | 'char'                      | 'char'                      | boolean     | chareq                   | equal
 pg_catalog | =    | aclitem                     | aclitem                     | boolean     | aclitemeq                | equal
 pg_catalog | =    | anyarray                    | anyarray                    | boolean     | array_eq                 | equal
 pg_catalog | =    | anyenum                     | anyenum                     | boolean     | enum_eq                  | equal
 pg_catalog | =    | anyrange                    | anyrange                    | boolean     | range_eq                 | equal
 pg_catalog | =    | bigint                      | bigint                      | boolean     | int8eq                   | equal
 pg_catalog | =    | bigint                      | integer                     | boolean     | int84eq                  | equal
 pg_catalog | =    | bigint                      | smallint                    | boolean     | int82eq                  | equal
 pg_catalog | =    | bit                         | bit                         | boolean     | biteq                    | equal
 pg_catalog | =    | bit varying                 | bit varying                 | boolean     | varbiteq                 | equal
 pg_catalog | =    | boolean                     | boolean                     | boolean     | booleq                   | equal
 pg_catalog | =    | box                         | box                         | boolean     | box_eq                   | equal by area
 pg_catalog | =    | bytea                       | bytea                       | boolean     | byteaeq                  | equal
 pg_catalog | =    | character                   | character                   | boolean     | bpchareq                 | equal
 pg_catalog | =    | cid                         | cid                         | boolean     | cideq                    | equal
 pg_catalog | =    | circle                      | circle                      | boolean     | circle_eq                | equal by area
 pg_catalog | =    | date                        | date                        | boolean     | date_eq                  | equal
 pg_catalog | =    | date                        | timestamp with time zone    | boolean     | date_eq_timestamptz      | equal
 pg_catalog | =    | date                        | timestamp without time zone | boolean     | date_eq_timestamp        | equal
 pg_catalog | =    | double precision            | double precision            | boolean     | float8eq                 | equal
 pg_catalog | =    | double precision            | real                        | boolean     | float84eq                | equal
 pg_catalog | =    | inet                        | inet                        | boolean     | network_eq               | equal
 pg_catalog | =    | integer                     | bigint                      | boolean     | int48eq                  | equal
 pg_catalog | =    | integer                     | integer                     | boolean     | int4eq                   | equal
 pg_catalog | =    | integer                     | smallint                    | boolean     | int42eq                  | equal
 pg_catalog | =    | interval                    | interval                    | boolean     | interval_eq              | equal
 pg_catalog | =    | jsonb                       | jsonb                       | boolean     | jsonb_eq                 | equal
 pg_catalog | =    | line                        | line                        | boolean     | line_eq                  | equal
 pg_catalog | =    | lseg                        | lseg                        | boolean     | lseg_eq                  | equal
 pg_catalog | =    | macaddr                     | macaddr                     | boolean     | macaddr_eq               | equal
 pg_catalog | =    | macaddr8                    | macaddr8                    | boolean     | macaddr8_eq              | equal
 pg_catalog | =    | money                       | money                       | boolean     | cash_eq                  | equal
 pg_catalog | =    | name                        | name                        | boolean     | nameeq                   | equal
 pg_catalog | =    | name                        | text                        | boolean     | nameeqtext               | equal
 pg_catalog | =    | numeric                     | numeric                     | boolean     | numeric_eq               | equal
 pg_catalog | =    | oid                         | oid                         | boolean     | oideq                    | equal
 pg_catalog | =    | oidvector                   | oidvector                   | boolean     | oidvectoreq              | equal
 pg_catalog | =    | path                        | path                        | boolean     | path_n_eq                | equal
 pg_catalog | =    | pg_lsn                      | pg_lsn                      | boolean     | pg_lsn_eq                | equal
 pg_catalog | =    | real                        | double precision            | boolean     | float48eq                | equal
 pg_catalog | =    | real                        | real                        | boolean     | float4eq                 | equal
 pg_catalog | =    | record                      | record                      | boolean     | record_eq                | equal
 pg_catalog | =    | smallint                    | bigint                      | boolean     | int28eq                  | equal
 pg_catalog | =    | smallint                    | integer                     | boolean     | int24eq                  | equal
 pg_catalog | =    | smallint                    | smallint                    | boolean     | int2eq                   | equal
 pg_catalog | =    | text                        | name                        | boolean     | texteqname               | equal
 pg_catalog | =    | text                        | text                        | boolean     | texteq                   | equal
 pg_catalog | =    | tid                         | tid                         | boolean     | tideq                    | equal
 pg_catalog | =    | time with time zone         | time with time zone         | boolean     | timetz_eq                | equal
 pg_catalog | =    | time without time zone      | time without time zone      | boolean     | time_eq                  | equal
 pg_catalog | =    | timestamp with time zone    | date                        | boolean     | timestamptz_eq_date      | equal
 pg_catalog | =    | timestamp with time zone    | timestamp with time zone    | boolean     | timestamptz_eq           | equal
 pg_catalog | =    | timestamp with time zone    | timestamp without time zone | boolean     | timestamptz_eq_timestamp | equal
 pg_catalog | =    | timestamp without time zone | date                        | boolean     | timestamp_eq_date        | equal
 pg_catalog | =    | timestamp without time zone | timestamp with time zone    | boolean     | timestamp_eq_timestamptz | equal
 pg_catalog | =    | timestamp without time zone | timestamp without time zone | boolean     | timestamp_eq             | equal
 pg_catalog | =    | tsquery                     | tsquery                     | boolean     | tsquery_eq               | equal
 pg_catalog | =    | tsvector                    | tsvector                    | boolean     | tsvector_eq              | equal
 pg_catalog | =    | uuid                        | uuid                        | boolean     | uuid_eq                  | equal
 pg_catalog | =    | xid                         | integer                     | boolean     | xideqint4                | equal
 pg_catalog | =    | xid                         | xid                         | boolean     | xideq                    | equal
 pg_catalog | =    | xid8                        | xid8                        | boolean     | xid8eq                   | equal
 public     | =    | citext                      | citext                      | boolean     | citext_eq                |
(63 rows)

其用到的函数为texteq,我们可以看下该函数的定义(src/backend/utils/adt/varlena.c):

Datum
texteq(PG_FUNCTION_ARGS)
{
    Oid            collid = PG_GET_COLLATION();
    bool        result;
    check_collation_set(collid);
    if (lc_collate_is_c(collid) ||
        collid == DEFAULT_COLLATION_OID ||
        pg_newlocale_from_collation(collid)->deterministic)
    {
        Datum        arg1 = PG_GETARG_DATUM(0);
        Datum        arg2 = PG_GETARG_DATUM(1);
        Size        len1,
                    len2;
        /*
         * Since we only care about equality or not-equality, we can avoid all
         * the expense of strcoll() here, and just do bitwise comparison.  In
         * fact, we don't even have to do a bitwise comparison if we can show
         * the lengths of the strings are unequal; which might save us from
         * having to detoast one or both values.
         */
        len1 = toast_raw_datum_size(arg1);
        len2 = toast_raw_datum_size(arg2);
        if (len1 != len2)
            result = false;
        else
        {
            text       *targ1 = DatumGetTextPP(arg1);
            text       *targ2 = DatumGetTextPP(arg2);
            result = (memcmp(VARDATA_ANY(targ1), VARDATA_ANY(targ2),
                             len1 - VARHDRSZ) == 0);
            PG_FREE_IF_COPY(targ1, 0);
            PG_FREE_IF_COPY(targ2, 1);
        }
    }
    else
    {
        text       *arg1 = PG_GETARG_TEXT_PP(0);
        text       *arg2 = PG_GETARG_TEXT_PP(1);
        result = (text_cmp(arg1, arg2, collid) == 0);
        PG_FREE_IF_COPY(arg1, 0);
        PG_FREE_IF_COPY(arg2, 1);
    }
    PG_RETURN_BOOL(result);
}

可以看到,就是直接使用memcmp函数对字符直接进行比较,自然是会去区分大小写。所以想要不区分大小写除非我们数据写入的时候就不区分大小写。

因此我们可以使用citext模块来实现忽略大小写的查询:

bill@bill=>create extension citext ;
CREATE EXTENSION
bill@bill=>create table test(id int,info citext);
CREATE TABLE
bill@bill=>insert into test values(1,'Bill');
INSERT 0 1
bill@bill=>select * from test where info = 'bill';
 id | info
----+------
  1 | Bill
(1 row)

3、数据排序中的大小写敏感

排序也是Oracle一样,默认是区分大小写的。

例如:

bill@bill=>create table test (c1 text);  
CREATE TABLE
bill@bill=>insert into test values ('a'),('b'),('c'),('A'),('B'),('C');  
INSERT 0 6
bill@bill=>select * from test order by c1;
 c1 
----
 A
 B
 C
 a
 b
 c
(6 rows)

不过从pg12开始支持不区分大小写,或者区分大小写的排序的collate。

CREATE COLLATION [ IF NOT EXISTS ] name (
    [ LOCALE = locale, ]
    [ LC_COLLATE = lc_collate, ]
    [ LC_CTYPE = lc_ctype, ]
    [ PROVIDER = provider, ]
    [ DETERMINISTIC = boolean, ]
    [ VERSION = version ]
)
CREATE COLLATION [ IF NOT EXISTS ] name FROM existing_collation

其中几个关键参数:

  • PROVIDER:指定用于与此排序规则相关的区域服务的提供程序。可能的值是: icu、libc。 默认 是libc。但若要设置大小写不敏感,目前只支持icu。
  • DETERMINISTIC:设置成not deterministic表示大小写不敏感。

例子:

bill@bill=> CREATE COLLATION case_insensitive (provider = icu, locale = 'zh_Hans', deterministic = false);  
CREATE COLLATION 
bill@bill=> select * from test order by c1 collate 'case_insensitive';  
 c1   
----  
 a  
 A  
 b  
 B  
 c  
 C  
(6 rows)

说到collate,不知道大家有没有遇过这种情况:

bill@bill=>select 'a' > 'A' collate 'en_US';
 ?column?
----------
 f
(1 row)
bill@bill=>select 'a' > 'A' collate 'C';
 ?column?
----------
 t
(1 row)

使用不同的collate字符串进行比较的结果竟然不一样。
因为上述字符串比较使用的是text_gt函数。当collate为C时,就是使用memcmp直接对被比较的字符串的比较,而collate为其它时,则会使用使用strcoll_l或strcoll进行比较。

/*
     * Unfortunately, there is no strncoll(), so in the non-C locale case we
     * have to do some memory copying.  This turns out to be significantly
     * slower, so we optimize the case where LC_COLLATE is C.  We also try to
     * optimize relatively-short strings by avoiding palloc/pfree overhead.
     */

同理,我们使用索引查询时也需要注意:创建索引时用的collate和查询时的collate一致才可以使用到索引。

4、其它场景下的大小写敏感

当我们使用数组类型时,是否区分大小写呢?

bill@bill=>select 'a' = any(array['A','1']);
 ?column?
----------
 f
(1 row)

可以看到和普通的字符串查询一样有区分大小写,那么我们该怎么去忽略大小写呢?

和字符串不同,我们没办法数据进行lower操作:

bill@bill=>select 'a' = lower(any(array['A','1']));
ERROR:  syntax error at or near 'any'
LINE 1: select 'a' = lower(any(array['A','1']));
                           ^


不过我们可以自己写一个函数来将数据中的元素转换为小写。

bill@bill=>create or replace function lower(text[]) returns text[] as $$
bill$# select array_agg(lower(x)) from unnest($1) t(x);
bill$# $$ language sql strict immutable;
CREATE FUNCTION
bill@bill=>select lower(array['A','a']);
 lower
-------
 {a,a}
(1 row)

再次查询:

bill@bill=>select 'a' = any( lower(array['A','1']) );
 ?column?
----------
 t
(1 row)

当然除此之外还有其它方法,例如自定义操作符等等。

总结:

PostgreSQL中和Oracle一样是大小写不敏感的,不同的是其默认是将字符转换成小写。
目前pg中没有参数去从数据库级别设置大小写敏感,但是针对不同的场景我们还是可以利用pg自身强大的功能来解决。

参考链接:

https://www.postgresql.org/docs/14/citext.html
https://www.postgresql.org/docs/14/sql-createcollation.html

提交留言

评论留言

还没有评论留言,赶紧来抢楼吧~~

吐槽小黑屋()

* 这里是“吐槽小黑屋”,所有人可看,只保留当天信息。

  • Erlo吐槽

    Erlo.vip2021-06-25 21:22:46Hello、欢迎使用吐槽小黑屋,这就是个吐槽的地方。
  • 返回顶部

    给这篇文章打个标签吧~

    棒极了 糟糕透顶 好文章 PHP JAVA JS 小程序 Python SEO MySql 确认