SQL Custom Routing

The UDB read-write separation middleware supports SQL custom routing functionality. Through two ways, a SQL can be routed to the master node or a specific slave node.

Method 1: SQL template

You can customize SQL through the following middleware and configure SQL routing rules for the middleware:

1. Insert Routing Rule

Command format:

uinsert sql_route("sql_stmt" :  "route_dest")

Parameter explanation:

sql_stmt: That is, the SQL template, the constant replaced with '?' after the SQL statement; For example: select * from t where id=? / call proc1 etc. Where, proc1 is the name of the stored procedure.

route_dest: Routing destination, the values are all, master, slave and udb_id, among them

• all: Route to all nodes, the routing ratio is controlled by the read mode on the console

• master: Only route to the master node

• slave: Only route to the slave node. The routing ratio is controlled by the read mode on the console (excluding the master node ratio)

• udb_id: Route to the specified udb

Special Note:

• The character between sql_stmt and route_dest" is ':' not ','.

• The structure of the SQL template must be completely consistent with the structure of the actual SQL statement. If the SQL template is: select money from t_account where uid=? and name=? Then the business-initiated SQL must ensure that the uid is in front of the where query condition, and name is behind. Otherwise, the middleware will regard the SQL as different from the SQL template

Example:

uinsert sql_route("select * from t where id=?" : "master");  

Function: Route the specified SQL statement to the master node

uinsert sql_route("call proc1" : "all"); 

Function: Route a certain type of call stored procedure statement to all/master/slave nodes

uinsert sql_route("call proc1" : "udbha-123qwe"); 

Function: Route to specified udb node

2. Query Routing Rules

Command format:

ushow all_sql_route

Function: View the currently configured routing rules

Parameter: None

Return:

rule_id: Routing rule id,

sql_stmt: SQL template

sql_md5: String generated by MD5 encryption of the SQL template

route_dest: Routing destination

3. Delete Routing Rules

Command format:

udelete sql_route("rule_id": "id");

Parameter explanation:

id: Routing rule id, which is the value of rule_id returned by the ushow all_sql_route command

Example:

udelete sql_route("rule_id":"1");

Method 2: SQL Hints

For Select statements, you can add the forcemater, forceslave command in the comment before the SQL, to specify that this Select SQL route the master node, or a certain slave node. For example:

/*force_master*/ select money from t_account where uid="tony";

This statement will be routed to the master node

/*force_slave*/ select money from t_account where uid="tony";

This statement will be routed to some node

Note: The comment must be: /* */, # and -- type of SQL does not have this function.