After creating a DBLink, you can use it to access objects in a remote database, including tables and views.
Limitations
Currently, OceanBase AI Database user tenants cannot read data from a MySQL database through DBLink, nor can a MySQL database read data from a user tenant of OceanBase AI Database through DBLink.
Data types supported for access via DBLink include:
Numeric types: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL
Time types: DATE, TIME, YEAR, DATETIME, TIMESTAMP
Character types: CHAR, VARCHAR, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, LONGBLOB, LONGTEXT
Other types: ENUM, SET
Notice
Due to limitations of OBClient, when reading table data through DBLink, columns of the
ENUMorSETtype in the remote table are treated asVARCHARtype columns.
For detailed information and descriptions of each data type, see Overview of data types.
Prerequisites
You have created a DBLink. For detailed steps, see Create a DBLink.
Access data in a remote database table
Use the SELECT... FROM table_name@DBLink name syntax to access data in a table of a remote database. The SQL statement is as follows:
SELECT select_expr_list
FROM table_name@dblink_name
WHERE where_conditions;
select_expr_list:
table_name.*
| table_alias_name.*
| expr [[AS] column_alias_name]
Instructions for using the statement:
select_expr_list: Specifies the expressions or column names to query. Separate different columns with commas (,). An asterisk (*) indicates all columns.table_name.*: Specifies to select all columns from the specified table or view.table_alias_name.*: Specifies an alias for the table or view.expr [[AS] column_alias_name]: Specifies an alias for the queried column or expression.ASis optional.
table_name@dblink_name: Specifies the table in the remote database to query. Separate multiple tables with commas (,). By default, it accesses tables in the database specified when creating the DBLink. To access tables in other databases, specify them in the SQL statement in the formatdatabase_name.table_name@dblink_name.where_conditions: Specifies filter conditions. The query result only contains data that meets the conditions. This is optional.
For more DBLink-related query statements, see SELECT.
Example:
Query data from the
tbl1table in the remote database through a DBLink namedob_dblink.SELECT * FROM tbl1@ob_dblink;Read the
tbl1table from the remote databasetestthrough a DBLink, and perform a join operation with thetbl1table in the local database.SELECT a.c_decimal, b.c_double FROM tbl1 a, tbl1@ob_dblink b WHERE a.c_int = b.c_int;Read the
tbl2table from the remote databasemysql(the database specified when creating the DBLink istest) through a DBLink.SELECT * FROM mysql.tbl2@ob_dblink;
References
For more information about DBLink operations, see the following topics: