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.
- 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_tblto specify the schema to which the
xxx_tbltable 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.
- 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.
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.
Assume that the SchemaName you use to log on to the PolarDB-X instance is
- Use the SELECT statement to perform cross-schema queries
To perform an aggregate query between
user_tblacross 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_tbltable in the
new_dbdatabase, 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_tbltable and the
user_tbltable 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
/* 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_dbin the current schema, you can execute the following SQL statement:
SHOW CREATE TABLE `new_db`.`new_tbl`;