In most cases, multiple schemas are used in a Distributed Relational Database Service (DRDS) instance. DRDS allows you to execute SQL statements to perform cross-schema queries. The results are similar to those of cross-schema queries in MySQL.

Note

  • To use the cross-schema query syntax, you must prefix the destination TableName with the corresponding SchemaName in your SQL statement. For example, if the TableName is xxx_tbl and the corresponding SchemaName is yyy_db, you must use yyy_db.xxx_tbl to specify the schema to which the xxx_tbl table belongs. The cross-schema query syntax in DRDS is fully compatible with that in MySQL.
  • DRDS does not support cross-schema queries that contain the following statements: CREATE SEQUENCE, ALTER SEQUENCE, and DROP SEQUENCE.
  • The version of your DRDS instance must be V5.3.8-15517870 or later.
  • Before you perform a cross-schema query, you must be granted the required permissions on the related schemas. For more information about the syntax for granting permissions, see Manage accounts and permissions.

Terms

  • Schema: a database in a DRDS instance. Horizontal splitting may or may not be performed on the database.
  • SchemaName: the name of a database in a DRDS instance. The name is unique within the instance.
  • Table: a table in a DRDS database. Horizontal splitting may or may not be performed on the database.
  • TableName: the name of a table in a DRDS database. The name is unique within the database.

Examples

If 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

The SchemaName that you use to log on to the DRDS instance is trade_db. You can execute the following SQL statements to perform cross-schema queries:

  • Execute the SELECT statement to perform cross-schema queries

    To perform an aggregate query across the trade_tbl schema and the user_tbl schema, 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
  • Execute the INSERT statement to perform cross-schema queries

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

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

    To update or delete the new_tbl table and the user_tbl table in a distributed transaction and commit the operations by using one request, you can execute the following SQL statements:

        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 in the `new_db` schema as a sequence to insert data.*/
    INSERT INTO `new_db`.`new_tbl` (id, name) values ( null, 'test_seq' );
    /* This SQL statement uses the `AUTO_SEQ_new_tbl` table in the `new_db` schema as a sequence to insert data. In the sequence, the SchemaName is specified.*/
    INSERT INTO `new_db`.`new_tbl` (id, name) values ( `new_db`.AUTO_SEQ_new_tbl.nextval, 'test_seq' );
  • Execute the SHOW CREATE TABLE statement to perform cross-schema queries

    To query the data of another schema such as new_db in the current schema, you can execute the following SQL statement:

        SHOW CREATE TABLE `new_db`.`new_tbl`;

SQL statements that support cross-schema queries