GBase 8c Compatibility Guide

GBase 8c, a high-performance relational database management system, supports various storage modes such as row storage, column storage, and memory storage. It can be deployed in multiple forms including standalone, master-slave, and distributed setups. GBase 8c offers high performance, high availability, elastic scalability, and robust security. It can be deployed on physical machines, virtual machines, containers, private clouds, and public clouds, providing secure, stable, and reliable data storage and management services for critical industry core systems, internet business systems, and government enterprise systems. Through extensive design optimization, GBase 8c achieves high compatibility with various mainstream databases, offering users flexible data processing solutions. This article aims to provide a detailed introduction to the compatibility features of GBase 8c, helping users understand and fully utilize GBase 8c’s advantages in database migration, integration, and multi-source data processing.

1. Compatibility Overview

GBase 8c has undergone extensive design and optimization to support the syntax and functions of multiple relational databases, including Oracle, PostgreSQL, MySQL, and TD. This broad compatibility allows users to reduce conversion costs and improve development efficiency during database migration, data integration, or cross-platform development.

2. Compatibility Implementation

This section explains the compatibility implementation from the perspectives of SQL standards, data types, SQL queries, and functions.

2.1 SQL Standard Syntax Compatibility

Support for standard SQL syntax:

• Cartesian join in SQL92:

  SELECT * FROM test_1, test_2 LIMIT 10;
  

• Full outer join in SQL99:

  SELECT * FROM test_1 t1 FULL OUTER JOIN test_2 t2 ON t1.col = t2.col LIMIT 10;
  

• Full join in SQL2003:

  SELECT * FROM test_1 t1 FULL JOIN test_2 t2 ON t1.col = t2.col;
  

2.2 Data Type Compatibility

GBase 8c supports the data types and functions of corresponding databases in different compatibility modes.

For MySQL compatibility, the modifications include:

1. Added support for optional modifiers (n) for INT/TINYINT/SMALLINT/BIGINT, allowing the use of TINYINT(n)/SMALLINT(n)/BIGINT(n) syntax. The value of n has no practical effect.
2. Added MEDIUMINT(n) data type, an alias for INT4, with no practical effect. It occupies 4 bytes of storage and ranges from -2,147,483,648 to +2,147,483,647.
3. Added FIXED[(p[,s])] data type, an alias for NUMERIC, with user-declared precision. Each four decimal digits occupy two bytes, with an additional eight-byte overhead.
4. Added float4(p[,s]), equivalent to dec(p[,s]).
5. Added double data type, an alias for float8.
6. Added support for optional modifiers (n) for float4/float, allowing the use of float4(n)/float(n) syntax. When n is between 1 and 24, float4(n)/float(n) represents single-precision floating point; when n is between 25 and 53, float4(n)/float(n) represents double-precision floating point.
7. For decimal/dec/fixed/numeric data types, the default precision is (10,0) when not specified.
8. Added UNSIGNEDINT/TINYINT/SMALLINT/BIGINT types, where the highest bit is a digit rather than a sign bit. Additionally, TINYINT is unsigned by default in GBase8s, whereas it is signed by default in MySQL.
9. Added zerofill attribute support for syntax only, with no actual zero-filling effect. Equivalent to UNSIGNED.
10. Added cast function type conversion parameters signed/unsigned, with castasunsigned converting to uint8 and castassigned converting to int8.
11. Added float(p,s), double(p,s), real(p,s), doubleprecision(p,s) syntax. float(p,s), real(p,s), doubleprecision(p,s) are roughly equivalent to dec(p,s), with the difference that p and s for float(p,s), real(p,s), doubleprecision(p,s) must be integers, while double(p,s) is fully equivalent to dec(p,s).

In Oracle compatibility mode, users can use Oracle-specific data types like NUMBER and VARCHAR2, and call Oracle built-in functions:

2.3 SQL Query Compatibility

In MySQL compatibility mode, you can write SQL queries using MySQL syntax. For example, using MySQL’s LIMIT clause to limit the number of query results:

2.4 Function Usage

In PostgreSQL compatibility mode, you can use PostgreSQL built-in functions. For example, using the TO_CHAR function to convert a date to a specific string format:

SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS') AS current_time;

3. Conclusion

GBase 8c achieves high compatibility with various mainstream relational databases by providing multiple compatibility modes. This compatibility not only reduces the difficulty of database migration and integration but also improves development efficiency and system stability. Through proper use and maintenance, GBase 8c can offer users more flexible and efficient data processing solutions.