A DRDS instance usually contains multiple schemas. DRDS allows you to execute Structured Query Language (SQL) statements to perform cross-schema queries. The results are similar to those of cross-schema queries in MySQL.

Precautions

  • To use the cross-schema query syntax, you must prefix the target TableName with the corresponding SchemaName in the SQL statement. For example, change the table name xxx_tbl to yyy_db.xxx_tbl to specify the schema to which the xxx_tbl table belongs. In this way, the cross-schema query syntax in PolarDB-X is fully compatible with that in MySQL.
  • PolarDB-X does not support cross-schema queries with a CREATE SEQUENCE, ALTER SEQUENCE, or DROP SEQUENCE statement.
  • The version of the DRDS instance must be 5.3.8-15517870 or later.
  • Before you execute a cross-schema query, you must be granted permissions for the related schemas.

Terms

  • Schema: a database in a DRDS instance. The database may have been either horizontally partitioned or not.
  • SchemaName: the name of a database in a DRDS instance. The name is unique within the instance.
  • Table: a table in a DRDS database. The table may have been either horizontally partitioned or not.
  • TableName: the name of a table of in a DRDS database. The name is unique within the database.

Use cases

Assume that you have created three different schemas in a DRDS instance. Each of the schemas contains one table, and each of the tables corresponds to one sequence, as shown in the following table.

SchemaName TableName Sequence
new_db new_tbl AUTO_SEQ_new_tbl
trade_db trade_tbl AUTO_SEQ_trade_tbl
user_db user_tbl AUTO_SEQ_user_tbl

Assume that the SchemaName you use to log on to the PolarDB-X instance is trade_db.

  • Use the SELECT statement to perform cross-schema queries

    To perform an aggregate query between trade_tbl and user_tbl across schemas, you can execute the following SQL statement:

    SELECT COUNT(DISTINCT u.user_id)
    FROM `trade_tbl` AS t 
    INNER JOIN `user_db`.`user_tbl` AS u ON t.user_id=u.user_id
    WHERE u.user_id >= 10000
    GROUP BY t.title
  • Use the INSERT statement to perform cross-schema queries

    To insert data to the new_tbl table in the new_db database, you can execute the following SQL statement:

    INSERT INTO `new_db`.`new_tbl` (user_id, title) VALUES ( null, 'test' );
  • Perform cross-schema queries in a distributed transaction

    To update or delete the new_tbl table and the user_tbl table in a distributed transaction and merge commit the results, you can execute the following SQL statement:

        SET AUTOCOMMIT=off;
        SET drds_transaction_policy = 'XA';
        UPDATE `new_db`.`new_tbl` SET name='abc' WHERE use_id=1;
        DELETE FROM `user_db`.`user_tbl` WHERE user_id=2;
        COMMIT;
  • Use sequences to perform cross-schema queries

    To explicitly use sequences to perform cross-schema INSERT operations, you must explicitly prefix the sequence name with the SchemaName, for example, change xxx_seq to yyy_db.xxx_seq.

    /* This SQL statement uses the `AUTO_SEQ_new_tbl` table of the `new_db` database as the sequence to insert data.*/
    INSERT INTO `new_db`.`new_tbl` (id, name) values ( null, 'test_seq' );
    /* This SQL uses the `AUTO_SEQ_new_tbl` table of the `new_db` database as the sequence to insert data. Note that the sequence here specifies the SchemaName.*/
    INSERT INTO `new_db`.`new_tbl` (id, name) values ( `new_db`.AUTO_SEQ_new_tbl.nextval, 'test_seq' );
  • Use the SHOW CREATE TABLE statement to perform cross-schema queries

    To query the CREATE TABLE statement of other schemas such as new_db in the current schema, you can execute the following SQL statement:

        SHOW CREATE TABLE `new_db`.`new_tbl`;

SQL clauses that support cross-schema queries