lightdb新增mysql模式下查询列时大小写不敏感、显示列大小写敏感
众所周知,lightdb在23.2版本前,查询列时,无论是否通过AS指定别名,只要没有使用双引号或是反引号,那么查询时会将其转化成小写后与表内字段进行对比,同时显示时也将小写的字段打印出来,给出如下几个示例:
create table test_case_1(identifier1 varchar(10));
select * from test_case_1;
identifier1
-------------
(0 rows)
select IDentifier1 from test_case_1;
identifier1
-------------
(0 rows)
select "IDentifier1" from test_case_1;
ERROR: column "IDentifier1" does not exist
select identifier1 as IDentifier1 from test_case_1;
identifier1
-------------
(0 rows)
lightdb23.2版本在mysql模式下且设置guc参数’lightdb_sql_mode’包含’uppercase_identifier’参数值后,具有查询列时大小写不敏感、显示列时大小写敏感的特性。值得注意的是,guc参数’lightdb_sql_mode‘使能’uppercase_identifier’字段,建议放在lightdb.conf内,且之后尽量不修改,亦不建议通过会话来设置它:
-- 指定数据库级实例模式为mysql模式
create database test_mysql_mode with lightdb_syntax_compatible_type mysql;
\c test_mysql_mode;
create table test_case_1(identifier1 varchar(10));
select * from test_case_1;
identifier1
-------------
(0 rows)
select IDentifier1 from test_case_1;
IDentifier1
-------------
(0 rows)
select identifier1 as IDentifier1 from test_case_1;
IDentifier1
-------------
(0 rows)