MySQL兼容性参数
b_compatibility_mode
参数说明: 该参数是当Vastbase与MySQL语法有相同的内容,而功能有冲突时,区分使用MySQL的功能还是Vastbase的功能,开启该参数则使用MySQL的功能。
该参数属于USERSET类型参数,请参考重设参数表1中对应设置方法进行设置。
取值范围: 布尔型
- on:表示使用MySQL功能。
- off:表示使用Vastbase功能。
默认值: off
enable_set_variable_b_format
参数说明: 该参数用于控制在MySQL数据库模式下是否支持自定义用户变量的功能。
该参数属于USERSET类型参数,请参考重设参数表1中对应设置方法进行设置。
取值范围: 布尔型
- on:表示在MySQL数据库模式下支持自定义用户变量。
- off:表示在MySQL数据库模式下不支持自定义用户变量。
默认值: off
lower_case_table_names
参数说明: 在MySQL兼容模式下,用于控制数据库返回对象名的大小写敏感性,对象名包含schema名、表名、用户名等数据库对象名称。
- 该功能仅在数据库兼容模式为MySQL时支持(即数据库实例初始化时指定DBCOMPATIBILITY='B')。
- 在配置文件$PGDATA/postgresql.conf中修改lower_case_table_names参数取值后应重启数据库,使修改生效。
- 在主备集群的环境中,主备节点的lower_case_table_names参数的设置必须相同。
该参数属于POSTMASTER类型参数,请参考重设参数表1中对应设置方法进行设置。
取值范围: 0,1
0:对象名使用创建时的大小写样式保存,在比较判断时使用大小写敏感的方式。
1:对象名统一使用小写样式保存,在比较判断时使用大小写不敏感的方式。
默认值: 0
若数据库中对象名大小写敏感(lower_case_table_names参数是0)情况下,存在不同对象名AA、Aa和aa,当修改参数设置对象名大小写不敏感(lower_case_table_names=1),则此时大小写敏感的对象AA和Aa将无法被识别。后续必须要重建这些对象,可参见示例3。
若要实现对象名大小写不敏感,则应在创建对象前指定参数取值为1。创建数据库、创建表之前(以及使用vb_dump和vb_dumpall -F p模式导出的SQL语句中包含有创建数据库和表的情况),必须先在实例级别指定参数lower_case_table_names=1,并确保参数已生效,确保后续创建的数据库和表的大小写不敏感。
执行导出和导入操作之前,应确定前后的数据库大小写敏感的设置一致。若vb_dump导出的数据库对象名大小写不敏感,在使用vb_restore命令导入数据之前必须在导入实例指定lower_case_table_names=1,且确认生效,确保导出和导入前后的数据库大小写敏感的设置一致。
示例:
示例1: 设置表名大小写敏感。
1、在配置文件$PGDATA/postgresql.conf中修改lower_case_table_names参数设置为0,即大小写敏感。并重启数据库。
vi $PGDATA/postgresql.conf
lower_case_table_names=0;
vb_ctl restart
2、查看当前数据库下参数lower_case_table_names的值。
show lower_case_table_names;
返回结果为:
lower_case_table_names
------------------------
0
(1 row)
3、创建测试表table2。
CREATE TABLE table2
(
id INTEGER,
name varchar(20)
);
4、向表TABLE2中插入数据。
INSERT INTO TABLE2(id,name) VALUES (2, 'grace');
插入失败,返回结果如下。
ERROR: relation "TABLE2" does not exist on node1
示例2: 设置表名大小写不敏感。
1、在配置文件$PGDATA/postgresql.conf中修改lower_case_table_names参数设置为1,即大小写不敏感。并重启数据库。
vi $PGDATA/postgresql.conf
lower_case_table_names=1
vb_ctl restart
2、查看当前数据库下参数lower_case_table_names的值。
show lower_case_table_names;
返回结果为:
lower_case_table_names
------------------------
1
(1 row)
3、创建测试表table1。
CREATE TABLE table1
(
id INTEGER,
name varchar(20)
);
4、向表TABLE1中插入数据。
INSERT INTO TABLE1(id,name) VALUES(1, 'jack');
5、查看表TABLE1内容。
select * from TABLE1;
返回结果如下,表示数据插入成功,该数据库大小写不敏感。
id | name
----+------
1 | jack
(1 row)
示例3: 创建表后切换lower_case_table_names参数取值。
1、在配置文件$PGDATA/postgresql.conf中修改lower_case_table_names参数设置为0,即大小写敏感。并重启数据库。
vi $PGDATA/postgresql.conf
lower_case_table_names=0
vb_ctl restart
2、查看当前数据库下参数lower_case_table_names的值。
show lower_case_table_names;
返回结果为:
lower_case_table_names
------------------------
0
(1 row)
3、创建大小写不同形式的测试表并为其插入数据,并查看当前参数取值下查询表数据的实际结果。
create table test_1174016(col int);
create table tesT_1174016(col int);
create table TEST_1174016(col int);
insert into test_1174016 values(0);
insert into tesT_1174016 values(1);
insert into TEST_1174016 values(2);
select * from test_1174016;
select * from tesT_1174016 ;
select * from TEST_1174016 ;
返回结果依次为如下:
col
-----
0
(1 row)
col
-----
1
(1 row)
col
-----
2
(1 row)
4、在配置文件$PGDATA/postgresql.conf中修改lower_case_table_names参数设置为1,即大小写不敏感。并重启数据库。
vi $PGDATA/postgresql.conf
lower_case_table_names=1
vb_ctl restart
5、查看当前数据库下参数lower_case_table_names的值。
show lower_case_table_names;
返回结果为:
lower_case_table_names
------------------------
1
(1 row)
6、查询测试表数据。
lower_case_table_names=0时创建的大小写敏感的table,在其参数取值被修改为1(即对象名大小写不敏感)之后,仅对象名为全小写的table可以被识别,其余将无法被识别。
select * from test_1174016;
select * from tesT_1174016 ;
select * from TEST_1174016 ;
返回结果依次为如下,即在大小写不敏感的情况下,以上语句的查询对象均为test_1174016测试表:
col
-----
0
(1 row)
col
-----
0
(1 row)
col
-----
0
(1 row)
7、删除后重新创建测试表并插入数据,查询测试表数据。
drop table TEST_1174016;
create table TEST_1174016(col int);
insert into TEST_1174016 values(3);
select * from test_1174016;
select * from tesT_1174016 ;
select * from TEST_1174016 ;
返回结果为如下,由于查询对象名大小写不敏感,所以上述查询语句的查询的对象均为步骤7中创建的测试表test_1174016。
col
-----
3
(1 row)
col
-----
3
(1 row)
col
-----
3
(1 row)
8、恢复对象名大小写敏感。在配置文件$PGDATA/postgresql.conf中修改lower_case_table_names参数设置为0。并重启数据库。
vi $PGDATA/postgresql.conf
lower_case_table_names=0
vb_ctl restart
9、查看当前数据库下参数lower_case_table_names的值。
show lower_case_table_names;
返回结果为:
lower_case_table_names
------------------------
0
(1 row)
10、查询测试表数据。
select * from test_1174016;
select * from tesT_1174016 ;
select * from TEST_1174016 ;
返回结果为如下,在步骤3中创建的大小写敏感的测试表可以被查询到,且test_1174016表查询结果为重建后的数据:
col
-----
3
(1 row)
col
-----
1
(1 row)
col
-----
2
(1 row)
lower_case_column_names
参数说明: 该参数涉及的功能如下所示:
该参数用于控制数据库返回字段名的大小写敏感性。
控制SELECT LIST中对于引用的别名的大小写敏感性。当该参数设置为0时,SELECT LIST中对于引用的别名也是大小写敏感的,设置为1时则不敏感。
- 该参数仅在数据库兼容模式为MySQL时支持(即数据库实例初始化时指定DBCOMPATIBILITY='B')。
- 该参数支持的Vastbase版本:
- V2.2 Build 15 (Patch No.2)及以后补丁版本。
- V2.2 Build 16及以后版本。
该参数属于POSTMASTER类型参数,请参考重设参数表1中对应设置方法进行设置。
取值范围: 0,1
0:开启大小写敏感。
1:关闭大小写敏感。
默认值: 0
示例1: 控制表别名大小写敏感性。
1、修改数据库配置文件”postgresql.conf”中”lower_case_column_names”参数(0表示开启,1表示关闭,默认为0开启)。
lower_case_column_names=0
2、重启数据库实例。
3、创建测试表并插入数据。
create table test(id int,Home varchar);
insert into test values(1,2),(3,4);
4、查询数据库中字段名称。
select * from test;
结果返回如下,返回字段名中的大小写与建表时的写法相同:
id | Home
----+------
1 | 2
3 | 4
(2 rows)
5、再次向测试表插入数据,插入时指定字段名的写法大小写不敏感。
insert into test(HOme) values(5);
6、插入成功,查看测试表数据。
select * from test;
返回结果如下:
id | Home
----+------
1 | 2
3 | 4
| 5
(3 rows)
示例2: 控制SELECT LIST中对于引用的别名的大小写敏感。
1、修改数据库配置文件”postgresql.conf”中”lower_case_column_names”参数(0表示开启,1表示关闭,默认为0开启)。
lower_case_column_names=0
2、重启数据库实例。
3、创建测试表并插入数据。
create table tab1 (id int, amount int);
insert into tab1 values(1,100),(2,200),(3,150),(4,50);
4、select语句查询时别名大小写敏感。
select id ID, amount Amount from tab1;
select Id, Amount from tab1;
select tab1.Id, tab1.Amount from tab1;
返回结果如下:
ID | Amount
----+------
1 | 100
2 | 200
3 | 150
4 | 50
(4 rows)
Id | Amount
----+------
1 | 100
2 | 200
3 | 150
4 | 50
(4 rows)
Id | Amount
----+------
1 | 100
2 | 200
3 | 150
4 | 50
(4 rows)
sql_ignore_strategy
参数说明: 在MySQL兼容模式下,该参数可控制ignore_error的hint在违反非空约束时的处理策略。其他相关内容可参考INSERT IGNORE。
该功能仅在数据库兼容模式为MySQL时支持(即数据库实例初始化时指定DBCOMPATIBILITY='B')。
该参数属于USERSET类型参数,请参考重设参数表1中对应设置方法进行设置。
取值范围: 枚举类型
ignore_null: 忽略违反非空约束的行的处理,并继续执行剩余数据库操作。
overwrite_null: 将违反约束的null值覆写为目标类型的默认值,并继续执行剩余数据库操作。
默认值: ignore_null
div_precision_increment
参数说明: 当参数大于0时开启功能,将使用numeric类型计算时的最后精度按照MySQL对numeric类型运算后的规则进行一些截断处理。
该参数属于USERSET类型参数,请参考重设参数表1中对应设置方法进行设置。
取值范围: 0~30
默认值: 0
vastbase_sql_mode
参数说明: Vastbase在MySQL兼容模式下,支持使用GUC参数vastbase_sql_mode控制B兼容下的语法校验规则。
- 该功能仅在数据库兼容模式为MySQL时支持(即数据库实例初始化时指定DBCOMPATIBILITY='B')。
- 仅在MySQL兼容模式下校验此参数。在其他兼容模式下修改此参数值并不会报错,但修改不会生效。
- 若将此参数取值设为空字符串,则表示不启用任何相关特性。
- 参数值由若干个配置项用逗号隔开构成,例如:
set vastbase_sql_mode='only_full_group_by,sql_mode_strict';
。
该参数属于USERSET类型参数,请参考重设参数表1中对应设置方法进行设置。
取值范围: 字符串,仅允许合法字符串设定。参数值由若干个配置项用逗号隔开构成。
vastbase_sql_mode取值范围
取值 | 说明 | 用法参考 |
---|---|---|
ONLY_FULL_GROUP_BY | 应用于下列两种场景:
|
SELECT |
ANSI_QUOTES | 主要是针对出现在各种需要使用双引号表示字符串值的地方。
|
双引号解释为标识符 |
SQL_MODE_STRICT | 在为字段设置UNSIGNED属性后,限制插入的列值正负和长度。涉及的类型有tinyint、smallint、int、integer、bigint、mediumint。 | COLUMN定义支持UNSIGNED属性 |
pipes_as_concat | 控制|| 被当成连接符还是或操作符。
|| 被当成连接符。|| 被当成操作符。 |
参考示例1。 |
pad_char_to_full_length | 控制char类型查询时是否删除尾部空格。
|
参考示例2。 |
no_zero_date | 控制 '0000-00-00' 是否为合法日期,支持DATE、DATETIME类型。取值及表现如下所示:
|
参考示例3。 |
ERROR_FOR_DIVISION_BY_ZERO | 用于控制分母为0时不报错,进行告警。当ERROR_FOR_DIVISION_BY_ZERO与SQL_MODE_STRICT同时指定时,分母为0报错。 | - |
默认值: ONLY_FULL_GROUP_BY,ANSI_QUOTES,pipes_as_concat,pad_char_to_full_length
示例
1、创建测试表。
create table test1(a int,b int);
2、设置GUC参数vastbase_sql_mode = ''
。
SET vastbase_sql_mode='';
3、向表中插入数据。
insert into test1 values(1||2,2);
4、查询test1表数据。
select * from test1;
返回结果为:
a | b
---+---
1 | 2
(1 row)
5、设置GUC参数vastbase_sql_mode = 'pipes_as_concat'
,并查询结果。
set vastbase_sql_mode = 'pipes_as_concat';
show vastbase_sql_mode;
返回结果为:
vastbase_sql_mode
-------------------
pipes_as_concat
(1 row)
6、向表中插入数据。
insert into test1 values(1||2,2);
7、查询test1表数据。
select * from test1;
如下所示,由于vastbase_sql_mode参数包含pipes_as_concat选项,因此||
的功能从或运算改为字符串拼接:
a | b
----+---
1 | 2
12 | 2
(2 rows)
示例2: pad_char_to_full_length选项效果展示。
1、创建测试表。
create table t1(x char(5),y varchar(5));
2、向表格中插入数据(char存储5个字符,而varchar存储4个字符)。
insert into t1 values('数据库 ','数据库 ');
3、设置GUC参数vastbase_sql_mode = ''
。
SET vastbase_sql_mode='';
4、查询表中存储数据的长度。
select x,char_length(x),y,char_length(y) from t1;
如下所示,char类型数据会把尾部空格删除,而varchar不会:
x | char_length | y | char_length
----------+-------------+---------+-------------
数据库 | 3 | 数据库 | 4
(1 row)
5、设置GUC参数vastbase_sql_mode = 'pad_char_to_full_length'
,并查询结果。
set vastbase_sql_mode = 'pad_char_to_full_length';
show vastbase_sql_mode;
返回结果为:
vastbase_sql_mode
-------------------------
pad_char_to_full_length
(1 row)
6、再次查询表中存储数据的长度。
select x,char_length(x),y,char_length(y) from t1;
如下所示,由于vastbase_sql_mode参数包含pad_char_to_full_length选项,因此char类型数据尾部空格不会被自动删除:
x | char_length | y | char_length
----------+-------------+---------+-------------
数据库 | 5 | 数据库 | 4
(1 row)
1、设置GUC参数vastbase_sql_mode = 'no_zero_date'
,并查询结果。
set vastbase_sql_mode = 'no_zero_date';
show vastbase_sql_mode;
返回结果为:
vastbase_sql_mode
-------------------
no_zero_date
(1 row)
2、创建测试表。
CREATE TABLE my_table_1160646 (
id INT AUTO_INCREMENT PRIMARY KEY,
date_column DATE
);
3、插入数据。
INSERT INTO my_table_1160646 (date_column) VALUES ('2022-01-01');
INSERT INTO my_table_1160646 (date_column) VALUES ('0000-00-00');
由于设置了vastbase_sql_mode = 'no_zero_date'
,因此'0000-00-00'为非法日期,插入成功但发出告警,返回结果如下:
INSERT 0 1
WARNING: date/time field value out of range: "0000-00-00"
LINE 1: ...NSERT INTO my_table_1160646 (date_column) VALUES ('0000-00-0...
^
CONTEXT: referenced column: date_column
INSERT 0 1
4、设置GUC参数vastbase_sql_mode = 'sql_mode_strict'
,并查询结果。
set vastbase_sql_mode = 'sql_mode_strict';
show vastbase_sql_mode;
返回结果为:
vastbase_sql_mode
-------------------
sql_mode_strict
(1 row)
5、再次插入日期'0000-00-00'。
INSERT INTO my_table_1160646 (date_column) VALUES ('0000-00-00');
插入成功,返回结果如下:
INSERT 0 1
6、查询表内容。
select * from my_table_1160646;
返回结果为:
id | date_column
----+-------------
1 | 2022-01-01
2 | 0000-00-00
3 | 0000-00-00
(3 rows)
b_compatibility_user_host_auth
参数说明: 控制是否允许创建user@host
、'user'@'host'
之类的用户并兼容MySQL的user@host认证鉴权,对兼容MySQL的user@host进行认证时,需要在配置文件postgresql.conf中设为on。
该参数属于USERSET类型参数,请参考重设参数表1中对应设置方法进行设置。
取值范围: 布尔型
on:允许创建
user@host
、'user'@'host'
之类的用户并兼容MySQL的user@host认证鉴权。off:不允许创建
user@host
、'user'@'host'
之类的用户。
默认值: off
default_week_format
参数说明: 参数值为整数,该参数影响B模式下的week函数,该参数的取值范围为[0,7],分别对应8种不同的计算策略,这些策略的详细内容参见week函数。当此GUC参数设置的值超过对应边界值时,会报warning,并且将此GUC参数的值设置为对应边界值。
该参数属于SIGHUP类型参数,请参考重设参数表1中对应设置方法进行设置。
取值范围: 整型,[0, 7]
默认值: 0
示例:
1、查看default_week_format默认值。
show default_week_format;
返回结果如下:
default_week_format
---------------------
0
(1 row)
2、调用week函数。
select week('2000-1-1');
返回结果如下:
week
------
0
(1 row)
3、修改GUC参数并再次调用week函数。
alter system set default_week_format = 2;
select week('2000-1-1');
返回结果如下:
ALTER SYSTEM SET
week
------
52
(1 row)
max_error_count
参数说明: 控制show warnings/errors
语句,输出的error, warning, note信息的最大数量。
该参数属于USERSET类型参数,请参考重设参数表1中对应设置方法进行设置。
取值范围: 整型,0~65535
默认值: 64
enable_convert_illegal_chars
参数说明: 该参数用于控制对无法识别的字符的转换功能。
该参数属于USERSET类型参数,请参考重设参数表1中对应设置方法进行设置。
该参数仅在数据库兼容模式为MySQL时生效(即数据库实例初始化时指定DBCOMPATIBILITY='B')。
取值范围: 布尔型
- on:支持对非法字符进行转换操作,会将非法字符转换为?。
- off:不支持转换无法识别的字符。
默认值: off
func_colname_with_args
参数说明 :该参数用于控制函数返回结果的列名是否带参数列表,即返回结果的字段名称与查询语句中一致。
该参数属于USERSET类型参数,请参考重设参数表1中对应设置方法进行设置。
该参数仅在数据库兼容模式为Oracle或者MySQL时支持(即数据库实例初始化时指定DBCOMPATIBILITY='A'或者'B')。
取值范围:布尔型
- on:函数返回结果带参数。
- off:函数返回结果不带参数。
默认值:off
b_format_behavior_compat_options
参数说明: 数据库B模式兼容性行为配置项,该参数的值由若干个配置项用逗号隔开构成。
该参数属于USERSET类型参数,请参考重设参数表1中对应设置方法进行设置。
取值范围: 字符串
默认值: “”
- 该参数仅在数据库兼容模式为MySQL时生效(即数据库实例初始化时指定DBCOMPATIBILITY='B')。
- 该参数属于会话级别参数,必须在配置文件设置或者session级别设置才会生效。
- 配置多个兼容性配置项时,相邻配置项用逗号隔开,例如:
set b_format_behavior_compat_options='enable_set_variables,set_session_transaction';
当前只支持下表所示的兼容性B模式配置项。
兼容性配置项 | 兼容性行为控制 |
---|---|
enable_set_variables | set语法增强控制开关。
set @v1 = 1; 。 |
set_session_transaction | set session transaction 控制开关。
set session transaction 等效于set local transaction 。 |
enable_modify_column | ALTER TABLE MODIFY 语义控制开关。
ALTER TABLE table_name MODIFY column_name data_type; 只修改列的数据类型。ALTER TABLE table_name MODIFY column_name data_type; 修改整个列定义。 |
default_collation | 默认字符序前向兼容开关。
|
fetch | FETCH语句结尾报错兼容开关。
|
enable_multi_charset | 多字符集功能的控制开关。
|
b_format_escape | V2.2 Build 15(Patch No.3)及以上版本支持选项b_format_escape,用来控制逃逸字符在MySQL兼容模式中的表现。
|
b_format_plpgsql_execute | 控制EXECUTE语句在PL/pgSQL的行为,设置该选项表示EXECUTE statement中的statement为PREPARE处理过的语句,而不是将statement视作一个sqlstring模板执行动态sql。示例参见PREPARE的示例3。该参数仅在数据库版本为V2.2 Build 15(Patch No.4)及以上版本支持。 |
enable_user_as_keyword | V2.2 Build 15(Patch No.4)及以上版本支持选项enable_user_as_keyword,用来控制user是否可以做列名。
|
enable_autoinc_adaptive_maxvalue | V2.2 Build 15(Patch No.4)及以上版本支持选项enable_autoinc_adaptive_maxvalue,用于控制在导出包含自增序列的表时,是否会把序列的maxvalue跟随列的数据类型调整,在以tar或者其他的自定义格式导出时,不能导入到不支持这个可选项的Vastbase版本中,可以导出为sql文件的形式规避这个问题,使用sql文件形式导出的表,导入后会使用默认值(int16_max)作为maxvalue创建autoinc的sequence。 |
set_keyword_as_colname | V2.2 Build 15(Patch No.3)及以上版本支持选项set_keyword_as_colname,用来指定select name '别名'为类型转换。
|
character_set_connection
参数说明: 在兼容B模式(sql_compatibility = 'B'),并且GUC参数b_format_behavior_compat_options设置值包含enable_multi_charset的场景下,连接到数据库时自动设置、生效。用于设置字符串常量的默认字符集。
该参数属于USERSET类型参数,请参考重设参数表1中对应设置方法进行设置。仅在设置会话级时生效,设置数据库级和用户级不生效。
修改此参数会同时将GUC参数collation_connection设置成该字符集的默认字符序。
取值范围: 字符串,合法的字符序的字符串,例如 utf8_general_ci
默认值: 当前数据库的字符集。
collation_connection
参数说明: 该参数用于指定常量字符串的排序规则。只有当参数b_format_behavior_compat_options包含选项enable_multi_charset的场景下才允许设置该参数。
该参数属于USERSET类型参数,请参考重设参数表1中对应设置方法进行设置。
- 该参数仅在数据库兼容模式为MySQL时生效(即数据库实例初始化时指定DBCOMPATIBILITY='B')。
- 该参数属于会话级别参数,必须在配置文件设置或者session级别设置才会生效。
取值范围: 字符串,合法的字符序的字符串,例如utf8_general_ci
默认值: 当前数据库字符集的默认字符序
b_func_display_mode
参数说明: 控制B模式下函数查询时的显示。该参数不支持通过SET语句进行设置,需在postgresql.conf中配置后重启数据库生效。
取值范围: 布尔型
on:查询函数显示的结果与查询语句相同。
off:查询函数显示的结果与函数名称相同。
默认值: off
示例:
1、修改配置文件postgresql.conf设置GUC参数'b_func_display_mode'为'on',并重启数据库使参数生效。
2、一条语句中多次使用同一函数。
select date_format(now(),'yyyymmdd'),date_format(now()+1,'yyyymmdd');
结果返回如下:
date_format(now(),'yyyymmdd') | date_format(now()+1,'yyyymmdd')
-------------------------------+---------------------------------
20231107 | 20231108
(1 row)