Direct Support for MySQL Syntax
1. Variable Definition
mysql_b=# alter database mysql_b set enable_set_variable_b_format = on;
ALTER DATABASE
mysql_b=# set @var_name := 123;
SET
mysql_b=# select @var_name;
@var_name
-----------
123
(1 row)
2. DEFINER Definition
mysql_b=# create definer=bus_user view test_definer as select * from atest limit 5;
CREATE VIEW
3. AUTO_INCREMENT Column
mysql_b=# create table test_AUTO_INCREMENT
mysql_b-# (id int AUTO_INCREMENT,
mysql_b(# val varchar(10),
mysql_b(# PRIMARY KEY (id)
mysql_b(# );
NOTICE: CREATE TABLE will create implicit sequence "test_auto_increment_id_seq" for serial column "test_auto_increment.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_auto_increment_pkey" for table "test_auto_increment"
CREATE TABLE
mysql_b=# insert into test_AUTO_INCREMENT(val) values ('a');
INSERT 0 1
mysql_b=# insert into test_AUTO_INCREMENT(val) values ('b');
INSERT 0 1
mysql_b=# select * from test_AUTO_INCREMENT;
id | val
----+-----
1 | a
2 | b
(2 rows)
4. Group By Multiple Columns
Error:
ERROR: column "su.user_type" must appear in the GROUP BY clause or be used in an aggregate function
Solution:
show dolphin.sql_mode;
Disable sql_mode_full_group:
alter database database_test3 set dolphin.sql_mode = 'sql_mode_strict,pipes_as_concat,ansi_quotes,no_zero_date,pad_char_to_full_length,auto_recompile_function,error_for_division_by_zero';
5. Implicit Type Conversion
In GBase 8c, incompatible field types will result in a warning for implicit conversion:
select '2020-05-22' - interval '22 day';
Solution:
alter database database_test1_new set dolphin.b_compatibility_mode = on;
Before revision:
After revision:
6. LIKE Matching Function
In MySQL, default collation makes string values case-insensitive for "=" and "LIKE". Accurate string matching requires the "binary" keyword.
Solution:
set B_COMPATIBILITY_MODE = on;
SELECT 'a' like 'A';
SELECT 'a' like binary 'A';
7. ENGINE, CHARSET, COLLATE Keywords in Table Creation
GBase 8c syntax supports MySQL's ENGINE, CHARSET, and COLLATE keywords in CREATE TABLE.
8. dolphin.sql_mode Introduction
Default Value:
‘sql_mode_strict,sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date,pad_char_to_full_length’
Parameter Explanation:
- sql_mode_strict: Handles data type conversions and default values for non-null columns.
-
sql_mode_full_group: Ensures columns in
SELECTnot using aggregate functions must appear inGROUP BY. -
pipes_as_concat: Controls whether
||is treated as a concatenation operator or logical OR. - ansi_quotes: Treats double quotes as object references instead of string literals.
- no_zero_date: Controls the legality of '0000-00-00' as a date.
-
pad_char_to_full_length: Controls whether trailing spaces are removed in
CHARtype queries.
Indirect Support for MySQL Syntax
1. Interval Function with Expressions
GBase 8c does not support expressions within functions directly (e.g., select (curdate() - interval (weekday(curdate()) + 7) day)).
Solution:
Extract the function from the interval, and change to:
(solution idea: first calculate the result in the expression, and then convert the unit by multiplying with interval '1 day')
SELECT (CURRENT_DATE - (weekday(curdate()) + 7) * INTERVAL '1 day');
2. Collation Determination Error
Error:
ERROR: could not determine which collation to use for view column "check_name"
HINT: Use the COLLATE clause to set the collation explicitly.
Solution:
Add COLLATE utf8mb4_general_ci to the specified fields.
3. Explicit Type Conversion
GBase 8c automatically converts right function parameters to bigint.
Error:
ERROR: function right(character varying, bigint) does not exist
Solution:
Add explicit type conversion.
Before revision:
After revision:
4. Column Alias Conflicts with GBase 8c Keywords
Solution:
Add double quotes around the conflicting keyword.
Before revision:
After revision:
5. Union of Multiple Query Result Sets
Solution:
Ensure consistent character set collation for all tables.
Top comments (0)