Mycat原理的內(nèi)容有很多，這篇主要來說說Mycat對(duì)SQL語(yǔ)句的處理。

Mycat接收到客戶端的sql語(yǔ)句時(shí)，會(huì)統(tǒng)一使用ServerQueryHandler.query(String sql)方法來處理，ServerQueryHandler主要做了兩件事情。

1,.確定sql的類型。比如：SELECT、UPDATE、INSERT、SHOW等

2.將不同類型的sql交給不同的處理器進(jìn)行處理

@Override
public void query(String sql) {
    ServerConnection c = this.source;
    //確定sql類型
    int rs = ServerParse.parse(sql);
    int sqlType = rs & 0xff;

    //將不同類型的sql交給不同的處理器進(jìn)行處理
    switch (sqlType) {
    //explain sql
    case ServerParse.EXPLAIN:
        ExplainHandler.handle(sql, c, rs >>> 8);
        break;
    //explain2 datanode=? sql=?
    case ServerParse.EXPLAIN2:
        Explain2Handler.handle(sql, c, rs >>> 8);
        break;
    case ServerParse.SET:
        SetHandler.handle(sql, c, rs >>> 8);
        break;
    case ServerParse.SHOW:
        ShowHandler.handle(sql, c, rs >>> 8);
        break;
    case ServerParse.SELECT:
        SelectHandler.handle(sql, c, rs >>> 8);
        break;
    case ServerParse.START:
        StartHandler.handle(sql, c, rs >>> 8);
        break;
    case ServerParse.BEGIN:
        BeginHandler.handle(sql, c);
        break;
    //不支持oracle的savepoint事務(wù)回退點(diǎn)
    case ServerParse.SAVEPOINT:
        SavepointHandler.handle(sql, c);
        break;
    case ServerParse.KILL:
        KillHandler.handle(sql, rs >>> 8, c);
        break;
    //不支持KILL_Query
    case ServerParse.KILL_QUERY:
        LOGGER.warn(new StringBuilder().append("Unsupported command:").append(sql).toString());
        c.writeErrMessage(ErrorCode.ER_UNKNOWN_COM_ERROR,"Unsupported command");
        break;
    case ServerParse.USE:
        UseHandler.handle(sql, c, rs >>> 8);
        break;
    case ServerParse.COMMIT:
        c.commit();
        break;
    case ServerParse.ROLLBACK:
        c.rollback();
        break;
    case ServerParse.HELP:
        LOGGER.warn(new StringBuilder().append("Unsupported command:").append(sql).toString());
        c.writeErrMessage(ErrorCode.ER_SYNTAX_ERROR, "Unsupported command");
        break;
    case ServerParse.MYSQL_CMD_COMMENT:
        c.write(c.writeToBuffer(OkPacket.OK, c.allocate()));
        break;
    case ServerParse.MYSQL_COMMENT:
        c.write(c.writeToBuffer(OkPacket.OK, c.allocate()));
        break;
    case ServerParse.LOAD_DATA_INFILE_SQL:
           c.loadDataInfileStart(sql);
           break;
    case ServerParse.MIGRATE:
        MigrateHandler.handle(sql,c);
        break;
    case ServerParse.LOCK:
        c.lockTable(sql);
        break;
    case ServerParse.UNLOCK:
        c.unLockTable(sql);
        break;
    default:
        if(readOnly){
            LOGGER.warn(new StringBuilder().append("User readonly:").append(sql).toString());
            c.writeErrMessage(ErrorCode.ER_USER_READ_ONLY, "User readonly");
            break;
        }
        c.execute(sql, rs & 0xff);
    }
}

確定sql的類型

確定sql類型的工作是交給ServerParse.parse(sql);來完成的。ServerParse的算法很簡(jiǎn)單，就是一個(gè)字符串的匹配過程，下面拿insert和show兩種類型的sql來分析。

insert語(yǔ)句的匹配過程：

static int insertCheck(String stmt, int offset) {
    //因?yàn)閕nsert的長(zhǎng)度最小是6，所以首先判斷sql語(yǔ)句的長(zhǎng)度是不是大于6
    if (stmt.length() > offset + 6) {
        char c1 = stmt.charAt(++offset);
        char c2 = stmt.charAt(++offset);
        char c3 = stmt.charAt(++offset);
        char c4 = stmt.charAt(++offset);
        char c5 = stmt.charAt(++offset);
        char c6 = stmt.charAt(++offset);
        //一個(gè)字符一個(gè)字符進(jìn)行匹配
        if ((c1 == 'N' || c1 == 'n') && (c2 == 'S' || c2 == 's')
                && (c3 == 'E' || c3 == 'e') && (c4 == 'R' || c4 == 'r')
                && (c5 == 'T' || c5 == 't')
                && (c6 == ' ' || c6 == '\t' || c6 == '\r' || c6 == '\n')) {
            //【注意】這里是直接返回INSERT
            return INSERT;
        }
    }
    return OTHER;
}

show語(yǔ)句的匹配過程：

static int sCheck(String stmt, int offset) {
    //判斷字符s的下一個(gè)字符
    if (stmt.length() > ++offset) {
        switch (stmt.charAt(offset)) {
        case 'A':
        case 'a':
            return savepointCheck(stmt, offset);
        case 'E':
        case 'e':
            return seCheck(stmt, offset);
        case 'H':
        case 'h':
            //下一個(gè)字符是s，繼續(xù)判斷是不是show
            return showCheck(stmt, offset);
        case 'T':
        case 't':
            return startCheck(stmt, offset);
        default:
            return OTHER;
        }
    }
    return OTHER;
}
// SHOW' '
static int showCheck(String stmt, int offset) {
    //因?yàn)閟how的長(zhǎng)度最小是4，并且offset是從字符h開始的，所以首先判斷sql語(yǔ)句的長(zhǎng)度是不是大于offset+3
    if (stmt.length() > offset + 3) {
        char c1 = stmt.charAt(++offset);
        char c2 = stmt.charAt(++offset);
        char c3 = stmt.charAt(++offset);
        //逐個(gè)字符進(jìn)行判斷
        if ((c1 == 'O' || c1 == 'o') && (c2 == 'W' || c2 == 'w')
                && (c3 == ' ' || c3 == '\t' || c3 == '\r' || c3 == '\n')) {
            //【注意】這里是offset左移8位再和SHOW進(jìn)行或操作的結(jié)果。
            //這個(gè)設(shè)計(jì)的巧妙之處就在于通過一個(gè)返回值，返回了兩個(gè)信息：offset和sql類型。
            //唯一需要注意的就是sql類型的常量值不能超過8位，也就是0~255
            return (offset << 8) | SHOW;
        }
    }
    return OTHER;
}

//下面是Mycat目前支持的sql類型對(duì)應(yīng)的常量值。
public static final int OTHER = -1;
public static final int BEGIN = 1;
public static final int COMMIT = 2;
public static final int DELETE = 3;
public static final int INSERT = 4;
public static final int REPLACE = 5;
public static final int ROLLBACK = 6;
public static final int SELECT = 7;
public static final int SET = 8;
public static final int SHOW = 9;
public static final int START = 10;
public static final int UPDATE = 11;
public static final int KILL = 12;
public static final int SAVEPOINT = 13;
public static final int USE = 14;
public static final int EXPLAIN = 15;
public static final int EXPLAIN2 = 151;
public static final int KILL_QUERY = 16;
public static final int HELP = 17;
public static final int MYSQL_CMD_COMMENT = 18;
public static final int MYSQL_COMMENT = 19;
public static final int CALL = 20;
public static final int DESCRIBE = 21;
public static final int LOCK = 22;
public static final int UNLOCK = 23;
public static final int LOAD_DATA_INFILE_SQL = 99;
public static final int DDL = 100;
public static final int MIGRATE  = 203;

經(jīng)過上面的分析，我們可以看出，ServerParse.parse(sql)的返回值有下面兩種情況：

1.直接返回sql類型。例如INSERT、DDL

2.返回sql類型+offset。例如SHOW、SELECT

所以，對(duì)于ServerParse.parse(sql)的返回值，后續(xù)的處理方式是下面這樣的。

int rs = ServerParse.parse(sql);
//獲取sql類型
int sqlType = rs & 0xff;
//獲取offset
int offset = rs >>> 8;

將不同類型的sql交給不同的處理器進(jìn)行處理

SQL的處理方式，大致可以分為下面幾種：

1.對(duì)于大部分的sql，都有專門的處理器就行處理。

EXPLAIN——ExplainHandler
EXPLAIN2——Explain2Handler
SET——SetHandler
SHOW——ShowHandler
SELECT——SelectHandler
START——StartHandler
BEGIN——BeginHandler
SAVEPOINT——SavepointHandler
KILL——KillHandler
USE——UseHandler
MIGRATE——MigrateHandler

2.對(duì)于不支持的sql，直接返回錯(cuò)誤信息

KILL_QUERY、HELP

3.Mycat能直接處理掉的，直接處理

COMMIT、ROLLBACK、LOCK、UNLOCK、MYSQL_CMD_COMMENT、MYSQL_COMMENT

4.需要通過MySQL來處理的，執(zhí)行execute

UPDATE、DELETE、DDL

在動(dòng)力節(jié)點(diǎn)的Mycat教程當(dāng)中還有更多的內(nèi)容在等著大家去學(xué)習(xí)，當(dāng)然也有相關(guān)的配套視頻教程能夠免費(fèi)下載，課程內(nèi)容詳細(xì)，通俗易懂，適合初學(xué)者，希望對(duì)大家的學(xué)習(xí)能夠有所幫助哦。