GBase 8c配置大小写敏感

GBase 8c配置大小写敏感

GBase8c数据库mysql/sql server兼容大小写敏感

1、对象名（表名、列名等）支持大小些敏感
2、数据查询支持大小写模糊查询

---

1、列名支持大小写敏感

为满足8c兼容mysql和sql server，支持默认列名的大小写。
首先需要创建兼容B模式的数据库。

--创建数据库
CREATE DATABASE test DBCOMPATIBILITY 'B' encoding 'UTF-8' LC_COLLATE 'zh_CN.utf8' LC_CTYPE 'zh_CN.utf8';
--创建表
test=# create table t1(Name varchar(10),iD int);
CREATE TABLE
test=# \d+ t1
                                 Table "public.t1"
 Column |         Type          | Modifiers | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
 Name   | character varying(10) |           | extended |              |
 iD     | integer               |           | plain    |              |
Has OIDs: no
Options: orientation=row, compression=no

test=# select column_name from information_schema.columns where table_name='t1';
 column_name
-------------
 iD
 Name
(2 rows)

test=# select id from t1;
 id
----
(0 rows)

test=# insert into t1(name,ID) values ('Test',1);
INSERT 0 1
test=# update t1 set name='new_test' where Id=1;
UPDATE 1
test=# select * from t1;
   Name   | iD
----------+----
 new_test |  1
(1 row)

上面例子可以看出，在创建表时，可以指定列名的大小写，增删改查会忽略大小写，且在场景中满足mysql与sql server的兼容。

2、表名支持大小写敏感

默认情况下，8c数据中大小写时不敏感的，如果非要强制大小写，有两种方法进行操作。
第一种：需要添加"",例如：

test=# create table "T2" ( id int,Name varchar(10));
CREATE TABLE
test=# \d+
                                                  List of relations
 Schema |             Name              | Type  | Owner |    Size    |             Storage              | Description
--------+-------------------------------+-------+-------+------------+----------------------------------+-------------
 public | T2                            | table | gbase | 0 bytes    | {orientation=row,compression=no} |
 public | index_statistic               | view  | gbase | 0 bytes    |                                  |
 public | pg_type_nonstrict_basic_value | view  | gbase | 0 bytes    |                                  |
 public | t1                            | table | gbase | 8192 bytes | {orientation=row,compression=no} |
(4 rows)

test=# \d+ t2
Did not find any relation named "t2".
test=# \d+ "T2"
                                 Table "public.T2"
 Column |         Type          | Modifiers | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
 id     | integer               |           | plain    |              |
 Name   | character varying(10) |           | extended |              |
Has OIDs: no
Options: orientation=row, compression=no

这种增加" "虽然满足强制大小写，但是在调用和操作过程中仍需要添加双引号。
第二种：使用参数（dolphin.lower_case_table_names）进行调整。

test=# alter database test set dolphin.lower_case_table_names to 0;
ALTER DATABASE
test=# \q      --alter database 当前session需要重新进入后生效
[gbase@gbase8c ~]$ gsql -r test -p 15400
test=# show dolphin.lower_case_table_names;
 dolphin.lower_case_table_names
--------------------------------
 0
(1 row)

test=# create table T3(id int,NAme varchar(10));
CREATE TABLE
test=# \d+ T3
                                 Table "public.T3"
 Column |         Type          | Modifiers | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
 id     | integer               |           | plain    |              |
 NAme   | character varying(10) |           | extended |              |
Has OIDs: no
Options: orientation=row, compression=no

test=# select * from T3;
 id | NAme
----+------
(0 rows)

test=# select * from t3;
ERROR:  relation "t3" does not exist on dn_6001_6002
LINE 1: select * from t3;
                      ^

以上满足对表的大小写敏感。

3、数据支持大小写不敏感

在mysql和sql server支持对数据的大小写不敏感。

mysql> create table t4(id int,name varchar(100)) COLLATE utf8_general_ci;
Query OK, 0 rows affected, 1 warning (0.61 sec)

mysql> insert into t4 values(1,'ABC'),(2,'ABc'),(3,'abc');
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t4 where name='abc';
+------+------+
| id   | name |
+------+------+
|    1 | ABC  |
|    2 | ABc  |
|    3 | abc  |
+------+------+
3 rows in set (0.01 sec)

mysql>

在mysql种存在着很多的utf8编码格式，每种编码都有不同的区别，比如utf8_general_ci编码就是大小写不敏感，对查询不区分大小写。在GBase8c最新的5.0.0版本已经兼容了utf8_general_ci编码。看下面例子：

test=# select * from pg_collation where collcollate='utf8_general_ci';
    collname     | collnamespace | collowner | collencoding |   collcollate   |    collctype    | collpadattr | collisdef
-----------------+---------------+-----------+--------------+-----------------+-----------------+-------------+-----------
 utf8_general_ci |            11 |        10 |            7 | utf8_general_ci | utf8_general_ci | PAD SPACE   |
(1 row)

test=# create table t4(id int,name varchar(100)) COLLATE utf8_general_ci;
CREATE TABLE
test=# insert into t4 values(1,'ABC'),(2,'ABc'),(3,'abc');
INSERT 0 3
test=# select * from t4 where name='abc';
 id | name
----+------
  1 | ABC
  2 | ABc
  3 | abc
(3 rows)

test=# select * from t4 where name='ABC';
 id | name
----+------
  1 | ABC
  2 | ABc
  3 | abc
(3 rows)

4、注意：

1、以上功能支持5.0.0以上版本；
2、创建的数据库编码为UTF8；
3、exclude_reserved_words不设置。