Mycat2 BKAJOIN(values)

形式2：Values語句（MySQL8.0.19以后支持Values語法,Mycat2支持使用該形式下推BKAJOIN）

該形式默認(rèn)不啟用，因?yàn)樗c特定數(shù)據(jù)庫版本有關(guān)。

基本形式

SELECT * FROM (VALUES  ROW('4'),
            ROW('8'),
            ROW('1'),
            ROW('5'),
            ROW('9'),
            ROW('2'),
            ROW('6'),
            ROW('3'),
            ROW('7') ) AS `t`
    LEFT JOIN db1.company AS `normal` ON (`t`.`column_0` = `normal`.`id`)
    INNER JOIN db1.global AS `global` ON (`t`.`column_0` = `global`.`id`)

其中 db1.company ，db1.global都是單表或全局表。而且會(huì)先把左表數(shù)據(jù)完整讀取后，再拼接成右表，然后右表數(shù)據(jù)一次查詢。

由于Values不支持0行數(shù)據(jù)，所以左表沒有數(shù)據(jù)的時(shí)候，右表不會(huì)發(fā)送SQL查詢。

它可以使用use_values_join啟用

樣例：

/*+MYCAT:use_values_join(s,e) use_values_join(s,g)*/
SELECT * FROM db1.sharding s LEFT JOIN db1.normal e ON s.id = e.id INNER JOIN db1.global g ON s.id = g.id  ORDER BY s.id


plan
MycatProject(id=[$0], user_id=[$1], traveldate=[$2], fee=[$3], days=[$4], blob=[$5], id0=[$6], companyname=[$7], addressid=[$8], id1=[$9], companyname0=[$10], addressid0=[$11])
  MycatMemSort(sort0=[$0], dir0=[ASC])
    MycatSQLTableLookup(condition=[=($0, $6)], joinType=[left], type=[NONE], correlationIds=[[$cor0]], leftKeys=[[0]])
      MycatView(distribution=[[db1.sharding]])
      MycatView(distribution=[[db1.global, db1.normal]])
Each(targetName=prototype, sql=SELECT * FROM (VALUES  $cor0 ) AS `t`     LEFT JOIN db1.normal AS `normal` ON (`t`.`column_0` = `normal`.`id`)     INNER JOIN db1.global AS `global` ON (`t`.`column_0` = `global`.`id`))
Each(targetName=c0, sql=SELECT * FROM db1_0.sharding_0 AS `sharding` union all SELECT * FROM db1_0.sharding_1 AS `sharding`)
Each(targetName=c1, sql=SELECT * FROM db1_1.sharding_2 AS `sharding` union all SELECT * FROM db1_1.sharding_3 AS `sharding`)