Mycat2 SQL支持

拦截器与SQL命令

Mycat2通过拦截器机制匹配SQL并决定处理方式：

interceptor:
  defaultHandler:
    command: execute
    tags:
      targets: prototype
      forceProxy: true
  schemas:
    - tables: ['db1.travelrecord']
      sqls:
        - sql: 'select {selectItems} from {any}'
          command: distributedQuery
        - sql: 'delete {any}'
          command: execute
          tags:
            executeType: UPDATE
            getMetaData: true
            needTransaction: true

SQL命令类型

execute 标签参数

MetaData支持的SQL格式

# 查询
SELECT [ALL|DISTINCT] {*|projectItem [,projectItem]*}
FROM tableExpression
[WHERE booleanExpression]
[GROUP BY {groupItem [,groupItem]*}]
[HAVING booleanExpression]
[ORDER BY orderItem [,orderItem]*]
[LIMIT [start,] {count|ALL}]
[OFFSET start {ROW|ROWS}]

# 支持UNION / INTERSECT / EXCEPT
# 支持(CROSS|LEFT|RIGHT|FULL) JOIN
# 支持子查询

执行计划 (EXPLAIN)

mysql> EXPLAIN SELECT * FROM db1.travelrecord WHERE id = 1;

# 输出示例:
+--------+-------------+------+
| target | sql         | ...  |
+--------+-------------+------+
| dn1    | SELECT * ...| ...  |
+--------+-------------+------+

事务支持

# XA事务
SET xa = on;
BEGIN;
INSERT INTO db1.travelrecord VALUES(1, 'user1', '2026-01-01', 100, 3);
INSERT INTO db1.travelrecord VALUES(2, 'user2', '2026-01-02', 200, 5);
COMMIT;
SET xa = off;

# Proxy事务
BEGIN;
INSERT INTO db1.travelrecord VALUES(3, 'user3', '2026-01-03', 300, 7);
COMMIT;

# 自动提交
SET autocommit=on;
SET autocommit=off;

SQL改写功能

# 配置SQL改写
sqls:
  - name: 'mysql set names'
    sql: 'SET NAMES {utf8}'
    explain: 'SET NAMES utf8mb4'
    command: execute
    tags:
      targets: prototype
      forceProxy: true

# 实际效果: 客户端发送 SET NAMES utf8
# Mycat2自动改写为 SET NAMES utf8mb4 发送到后端

EXPLAIN查看执行计划

# 在SQL前加EXPLAIN
mysql> EXPLAIN select * from travelrecord;

# 查看路由信息
mysql> EXPLAIN insert into travelrecord values(1,'a','2026-01-01',100,3);

# 查看UPDATE路由
mysql> EXPLAIN update travelrecord set fee=200 where id=1;