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
不设置。