Use a DBLink in queries

The DBLink feature of OceanBase Database enables cross-database access, allowing you to access remote databases from the local database. After a DBLink is created, you can use it to access remote database objects, including tables and views.

Limitations

• Currently, the DBLink read feature in the MySQL mode of OceanBase Database only supports reading data between different MySQL tenants.

• In MySQL mode, you can access data of the following types through DBLinks:

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

  • Datetime 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 of data types.

Prerequisites

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

Query the data of a table in a remote database

The SQL syntax is as follows:

SELECT select_expr_list FROM table_name@dblink_name WHERE where_conditions;

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@dblink_name specifies the tables to be queried in the remote database. Separate the tables 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 specifies the filter condition, which is optional. Only the data that meets the condition is returned.

Examples

To use a DBLink for data query, perform the following steps:

1. Create a DBLink for connecting to a remote OceanBase database in MySQL mode. The DBLink name is my_link, the remote tenant is mysql001, and the default database to be accessed is test.

   CREATE DATABASE LINK my_link CONNECT TO root@mysql001 DATABASE test IDENTIFIED BY '******' HOST '10.10.10.1:2881';
   

2. Query data in the remote database through the DBLink created in Step 1.

   • Query the data of the test_tbl1 table in the remote database test through my_link.

     SELECT * FROM test_tbl1@my_link WHERE id = 1;
     

     The result is as follows:

     +------+------+
     | id   | name |
     +------+------+
     |    1 | A1   |
     +------+------+
     1 row in set
     

   • Read the test_tbl1 table in the remote database test through my_link and join the table with the tbl1 table in the local database.

     SELECT a.id, b.name FROM tbl1 a, test_tbl1@my_link b WHERE a.id = b.id;
     

     The result is as follows:

     +------+------+
     | id   | name |
     +------+------+
     |    1 | A1   |
     |    2 | A2   |
     +------+------+
     2 rows in set
     

   • Read the data of the student table in the remote database student_db through my_link. The database specified when you created the DBLink is test.

     SELECT * FROM student_db.student@my_link LIMIT 3;
     

     The result is as follows:

     +------+---------+--------+------+-------+-----------------+
     | id   | name    | gender | age  | score | enrollment_date |
     +------+---------+--------+------+-------+-----------------+
     |    1 | Emma    |      0 |   20 |    85 | 2021-09-01      |
     |    2 | William |      1 |   21 |  90.5 | 2021-09-02      |
     |    3 | Olivia  |      0 |   19 |  95.5 | 2021-09-03      |
     +------+---------+--------+------+-------+-----------------+
     3 rows in set
     

References

• For more information about data types, see Overview of data types.

• For more information about how to create a DBLink, see Create a DBLink.

• For more information about how to use a DBLink, see Use a DBLink to access data in a remote database.

• For more information about how to use the SELECT statement, see SELECT statement.