Now, Let’s analyze these commands one by one:

• show variable transaction_type

Query the current transaction type.

Input command

mysql> show variable transaction_type;

Output

+------------------+
| TRANSACTION_TYPE |
+------------------+
| LOCAL            |
+------------------+

• set variable transaction_type

Modify the current transaction type (LOCAL, XA, or BASE; case insensitive).

Input command

mysql> set variable transaction_type=XA;

Output

a. If successful, display “Query OK, 0 rows affected”；

b. Execute show variable transaction_type again and the type is XA now.

• show variable cached_connection

Query how many physical database cached connections.

Input command

mysql> show variable cached_connections;

Output

+--------------------+
| CACHED_CONNECTIONS |
+--------------------+
| 0                  |
+--------------------+

• preview SQL

Preview the actual SQL. Here, we give an example in read-write splitting scenario. ShardingSphere supports previewing any SQL commands.

Input command

mysql> preview select * from t_order;

Output

+-----------------+----------------------------------------------+
| datasource_name | sql                                          |
+-----------------+----------------------------------------------+
| read_ds_0       | select * from t_order ORDER BY order_id ASC  |
| read_ds_1       | select * from t_order ORDER BY order_id ASC  |
+-----------------+----------------------------------------------+

Note: This is a Hint example in read-write splitting scenario. We configure two rules: read-write splitting and sharding. The configuration is the following:

rules:
- !READWRITE_SPLITTING
dataSources:
ds_0:
  writeDataSourceName: write_ds_0
  readDataSourceNames: 
    - read_ds_0
ds_1:
  writeDataSourceName: write_ds_1
  readDataSourceNames: 
    - read_ds_1
- !SHARDING
tables:
t_order:
  actualDataNodes: ds_${0..1}.t_order
defaultDatabaseStrategy:
standard:
  shardingColumn: user_id
  shardingAlgorithmName: database_inline
defaultTableStrategy:
none:
shardingAlgorithms:
database_inline:
  type: INLINE
  props:
    algorithm-expression: ds_${user_id % 2}

• show readwrite_splitting hint status

For the current connection only. Query hint status of readwrite_splitting.

Input command

mysql> show readwrite_splitting hint status;

Output

+--------+
| source |
+--------+
| auto   |
+--------+

• set readwrite_splitting hint source

For the current connection only. Set read-write splitting hint strategy (AUTO or WRITE). Supported source types include：AUTO and WRITE（case insensitive）. — AUTO： automated readwrite splitting hint — WRITE：compulsory hint at the master library

Input command

mysql> set readwrite_splitting hint source=write;

Output

a. If sucessful，show “Query OK, 0 rows affected”；

b. Re-execute show readwrite_splitting hint status; show the ource is changed into Write；

c. Execute preview select * from t_orderand see the queried SQL will go to the master database.

mysql> preview select * from t_order;
+-----------------+----------------------------------------------+
| datasource_name | sql                                          |
+-----------------+----------------------------------------------+
| write_ds_0      | select * from t_order ORDER BY order_id ASC  |
| write_ds_1      | select * from t_order ORDER BY order_id ASC  |
+-----------------+----------------------------------------------+

• clear readwrite_splitting hint

For the current connection only. Clear the read-write splitting hint setting.

Input command

mysql> clear readwrite_splitting hint;

Output

a. If successful, show “Query OK, 0 rows affected”;

b. Recover default of readwrite_splitting hint; use show readwrite_splitting hint status command to see the result.

Note: Here is another sharding example for Hint. Hint algorithm is used for both database sharding and table sharding. The sharding configuration rules are shown below:

rules:
- !SHARDING
tables:
t_order_item:
  actualDataNodes: ds_${0..1}.t_order_item_${0..1}
  databaseStrategy:
    hint:
      shardingAlgorithmName: database_inline
  tableStrategy:
    hint:
      shardingAlgorithmName: table_inline
shardingAlgorithms:
database_inline:
  type: HINT_INLINE
  props:
    algorithm-expression: ds_${Integer.valueOf(value) % 2}
table_inline:
  type: HINT_INLINE
  props:
    algorithm-expression: t_order_item_${Integer.valueOf(value) % 2}

show sharding hint status

For the current connection only. Query sharding hint status.

Input command

mysql> show sharding hint status;

Output

The initial status output is ：

Verify the hint and input the command:

preview select * from t_order_item;

Output No hint value now. Query is fully dependent on the hint.

-set sharding hint database_value;

For the current connection only. Set the Hint as for database sharding only, and add database value=1.

Input command

mysql> set sharding hint database_value = 1;

Output

a. If successful, show “Query OK, 0 rows affected”；

b. Execute show sharding hint status; show t_order_item‘s database_sharding_values as 1. Update sharding_type value as databases_only.

c. Execute preview select * from t_order_item; SQL all hinted to ds_1：

*Note: According to the sharding rules of YAML configuration, when databasevalue is an odd number, hint at ds1; when databasevalue is an even number, hint at ds0.

-add sharding hint database_value;

For the current connection only. Add t_order_item‘s database sharding value.

Input command

mysql> add sharding hint database_value t_order_item = 5;

Output

a. If successful，show “Query OK, 0 rows affected”；

b. Execute show sharding hint status; Show t_order_item‘s database_sharding_values as 5; update sharding_type value as databases_tables；

c. Execute preview select * from t_order_item; SQL commands are all hinted to ds_1：

Enter the add command again to add an even value.

mysql> add sharding hint database_value t_order_item = 10;

Output：

a. If successful，show “Query OK, 0 rows affected”；

b. Execute show sharding hint status; show t_order_item‘s database_sharding_values = ‘5,10’：

c. Execute preview select * from t_order_item; SQL hint contains ds0 and ds1：（ Because the hint values include both odd and even number so it contains all target data sources）

-add sharding hint table_value;

For the current connection only. Add database sharding value for t_order_item.

Input command

mysql> add sharding hint table_value t_order_item = 0;

Output

a. If successful，show “Query OK, 0 rows affected”；

b. Execute show sharding hint status; show t_order_item‘s database_sharding_values as ‘5,10’ while table_sharding_values is ‘0’：

c. Execute preview select * from t_order_item; the Hint condition is shown in the figure below; Every database only queries t_order_item_0:

Note: According to the sharding rules of YAML configuration, when table_value is an odd number, hint t_order_item_1; when database_value is an even number, hint t_order_item_0. It’s quite similar to add sharding hint database_value; you can set more than one hint values in add sharding hint database_value, to cover more shards.

clear sharding hint

For the current connection only. Clear sharding hint setting.

Input command

mysql> clear sharding hint;

Output

a. If successful, show “Query OK, 0 rows affected”;

b. Clear sharding hint and recover default; use show sharding hint status; to see the result. The initial status is:

clear hint

It is a special command because it contains the features of clear readwrite_splitting hint and clear sharding hint. It can clear all hint values of read-write splitting and sharding. Use the command, and you will get the initial status.

Set hint value and then execute the command;

mysql> clear hint;

Output

a. If successful，show “Query OK, 0 rows affected”;

b. Get readwrite_splitting hint default and sharding hint default; use show readwrite_splitting hint status ; or show sharding hint status;command to see the result.

Note: Please remember: if you need to use DistSQL Hint, you need to enable the configurationproxy-hint-enabledof ShardingSphere-Proxy. For more information, please read:https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-proxy/props/

Additional Useful RAL Commands

RAL not only contains all the SCTL functions, but also provides other useful administrational features including elastic scaling, instance ciruit-breaker, disabling read database for read-write splitting, etc.

For more details about RAL, please consult the relevant documentation: https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-proxy/distsql/syntax/ral/

Conclusion

That’s all folks. If you have any questions or suggestions, feel free to comment on our GitHub Issues or Discussions sections. You’re welcome to submit your pull request and start contributing to the open-source community, too. We’ve also set up a Slack channel, where you can connect with other members of our community and discuss technology with us.

Open Source Project Links

ShardingSphere Github: https://github.com/apache/shardingsphere

ShardingSphere Twitter: https://twitter.com/ShardingSphere

ShardingSphere Slack Channel:https://join.slack.com/t/apacheshardingsphere/shared_invite/zt-sbdde7ie-SjDqo9~I4rYcR18bq0SYTg

GitHub Issues: https://github.com/apache/shardingsphere/issues

Contributor Guide:https://shardingsphere.apache.org/community/cn/contribute/