PolarDB:Cross-schema queries

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 PolarDB-X 1.0 is fully compatible with that in MySQL.
• PolarDB-X 1.0 does not support cross-schema queries that contain the following statements: CREATE SEQUENCE, ALTER SEQUENCE, and DROP SEQUENCE.
• The version of your PolarDB-X 1.0 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 PolarDB-X 1.0 instance. Horizontal splitting may or may not be performed on the database.
• SchemaName: the name of a database in a PolarDB-X 1.0 instance. The name is unique within the instance.
• Table: a table in a PolarDB-X 1.0 database. Horizontal splitting may or may not be performed on the database.
• TableName: the name of a table in a PolarDB-X 1.0 database. The name is unique within the database.

Examples

If you have created three different schemas in a PolarDB-X 1.0 instance, each of the schemas contains one table and each of the tables corresponds to one sequence, as shown in the following table.

The SchemaName that you use to log on to the PolarDB-X 1.0 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

• SELECT
• INSERT
• REPLACE
• UPDATE
• DELETE
• Sequence
• DAL
• USE