Cong LiMySQL and SQL Server support case sensitivity. How does GBase 8c handle this? Let's explore GBase 8c's performance in terms of case sensitivity for object names and case-insensitive data queries.
1. Column Names Support Case Sensitivity
To ensure compatibility with MySQL and SQL Server, start by creating a GBase 8c database in compatibility mode.
Creating a Database and Table
In the GBase 8c Database Management System, execute the following commands to create a database named test and a table named t1:
CREATE DATABASE test DBCOMPATIBILITY 'B' encoding 'UTF-8' LC_COLLATE 'zh_CN.utf8' LC_CTYPE 'zh_CN.utf8';
CREATE TABLE t1(Name varchar(10), iD int);
Check the table structure:
\d+ t1
Output:
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
Verify the column names:
select column_name from information_schema.columns where table_name='t1';
Output:
column_name
-------------
iD
Name
(2 rows)
Insert data and perform update operations:
insert into t1(name, ID) values ('Test', 1);
update t1 set name='new_test' where Id=1;
select * from t1;
Output:
Name | iD
-------+----
new_test | 1
(1 row)
As demonstrated, GBase 8c allows case-sensitive column names while ignoring case during DML operations, ensuring compatibility with MySQL and SQL Server.
2. Table Names Support Case Sensitivity
By default, GBase 8c is case-insensitive. To enforce case sensitivity, two methods can be used.
Method 1: Using Double Quotes
To create a table with a case-sensitive name, use double quotes:
CREATE TABLE "T2" (id int, Name varchar(10));
Check the tables:
\d+
Output:
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+------+-------+-------+------------+----------------------------------+-------------
public | T2 | table | gbase | 0 bytes | {orientation=row, compression=no} |
public | t1 | table | gbase | 8192 bytes | {orientation=row, compression=no} |
Verify table structure:
\d+ t2
Output:
Did not find any relation named "t2".
Check with double quotes:
\d+ "T2"
Output:
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
This method requires using double quotes for all operations involving case-sensitive names.
Method 2: Using dolphin.lower_case_table_names Parameter
To enforce case sensitivity without using double quotes, adjust the dolphin.lower_case_table_names parameter:
ALTER DATABASE test SET dolphin.lower_case_table_names TO 0;
Reconnect to the database for the changes to take effect:
gsql -r test -p 15400
Verify the parameter value:
SHOW dolphin.lower_case_table_names;
Output:
dolphin.lower_case_table_names
--------------------------------
0
Create and check a new table:
CREATE TABLE T3(id int, NAme varchar(10));
\d+ T3
Output:
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
Query the table:
SELECT * FROM T3;
Output:
id | NAme
----+------
(0 rows)
Attempt querying with a different case:
SELECT * FROM t3;
Output:
ERROR: relation "t3" does not exist
This ensures case-sensitive table names without needing double quotes.
3. Data Case-Insensitive Queries
MySQL and SQL Server support case-insensitive data queries. GBase 8c also supports this with the utf8_general_ci collation.
Example in MySQL
CREATE TABLE t4(id int, name varchar(100)) COLLATE utf8_general_ci;
INSERT INTO t4 VALUES (1, 'ABC'), (2, 'ABc'), (3, 'abc');
SELECT * FROM t4 WHERE name='abc';
Output:
id | name
----+------
1 | ABC
2 | ABc
3 | abc
Example in GBase 8c
First, ensure the utf8_general_ci collation is supported:
SELECT * FROM pg_collation WHERE collcollate='utf8_general_ci';
Output:
collname | collcollate | collctype
---------+--------------+-----------
utf8_general_ci | utf8_general_ci | utf8_general_ci
Create a table with the utf8_general_ci collation:
CREATE TABLE t4(id int, name varchar(100)) COLLATE utf8_general_ci;
INSERT INTO t4 VALUES (1, 'ABC'), (2, 'ABc'), (3, 'abc');
SELECT * FROM t4 WHERE name='abc';
SELECT * FROM t4 WHERE name='ABC';
Output for both queries:
id | name
----+------
1 | ABC
2 | ABc
3 | abc
To use this feature, ensure the database encoding is UTF8 and exclude_reserved_words is not set.
This configuration guide ensures that GBase 8c handles case sensitivity for both object names and data queries effectively, maintaining compatibility with MySQL and SQL Server.
