Use a DBLink to access data in a remote database

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

Limitations

• Currently, MySQL tenants of OceanBase Database cannot read data from a MySQL database through DBLink. Similarly, MySQL databases cannot read data from MySQL tenants of OceanBase Database through DBLink.

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

  • Numeric types: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT, DOUBLE, and DECIMAL
  • Time types: DATE, TIME, YEAR, DATETIME, and TIMESTAMP
  • Character types: CHAR, VARCHAR, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, LONGBLOB, and LONGTEXT
  • Other 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 Data types.

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

Execute the SELECT... FROM table name@DBLink name statement to access data in a table in the remote database. Here is an example:

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]

where

• select_expr_list specifies 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 specifies the tables to be queried in the remote database. Separate the tables with commas (,). By default, 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 specifies 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 is an example:

• 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 a DBLink

• Drop a DBLink