Use a DBLink to access data in a remote database

After you create a DBLink, you can use it to access remote database objects, including tables and views.

Limitations

• At present, MySQL tenants of OceanBase Database cannot read data from a MySQL database by using DBLink. Similarly, MySQL databases cannot read data from MySQL tenants of OceanBase Database by using DBLink.

• You can use a DBLink to access the following types of data in the MySQL mode of OceanBase Database:

  • Numeric data types: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT, DOUBLE, and DECIMAL

  • Time data types: DATE, TIME, YEAR, DATETIME, and TIMESTAMP

  • String data types: CHAR, VARCHAR, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, LONGBLOB, and LONGTEXT

  • Other data types: ENUM and SET

    Notice

    Due to limitations of OBClient, when you use a DBLink to read table data, ENUM and SET columns in the remote table are treated as VARCHAR columns.

  For more information about the data types, see Overview.

Prerequisites

You have created a DBLink. For more information about how to create a DBLink, see Create a DBLink.

Access data of tables in a remote database

You can execute the SELECT... FROM table name@DBLink name statement to access data in a table in the remote database. The syntax 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]

The parameters in the syntax are described as follows:

• select_expr_list: the expressions or column names to be queried. Separate the column names with commas (,). An asterisk (*) indicates all columns.

  • table_name.*: all columns in the specified table or view.

  • table_alias_name.*: the alias of the table or view.

  • expr [[AS] column_alias_name]: the alias of the column or expression that you want to query. AS is optional.

• table_name@dblink_name: the tables to be queried in the remote database. Separate the table names with commas (,). By default, the tables in the database specified when you created the DBLink are accessed. If you want to access a table in another database, you can specify the table in the SQL statement in the format of database_name.table_name@dblink_name.

• where_conditions: the filter condition, which is optional. Only the data that meets the condition is returned.

For more information about DBLink-related query statements, see SELECT statement.

Here are some examples:

• Use a DBLink named ob_dblink to query data from the tbl1 table in a remote database.

  SELECT * FROM tbl1@ob_dblink;
  

• Use the DBLink to read the tbl1 table from the remote database test, and perform a JOIN with the tbl1 table 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;
  

• Use the DBLink to read the tbl2 table from the remote database mysql (the remote database specified while creating the DBLink is test).

  SELECT * FROM mysql.tbl2@ob_dblink;
  

References

For more information about operations on DBLinks, see the following topics:

• Create a DBLink

• Query existing DBLinks

• Drop a DBLink