在《“分庫分表" ？選型和流程要慎重，否則會失控》中，我們談到處于驅(qū)動層的sharding-jdbc。開源做到這個水平，已經(jīng)超棒了，不像tddl成了個太監(jiān)。但還是有坑。

不過不能怪框架，畢竟有些sql，只有程序和鬼能懂。

<select id="getCodes" resultMap="BaseResultMap" parameterType="java.util.Map"><foreach collection="orderCodes" index="index" item="item" open="" separator="union all"close="">select<include refid="Base_Column_List"/>from orderwhere orderCode = #{item}</foreach> </select> 復制代碼

不支持的操作

分庫分表后，就成為了一個閹割型的數(shù)據(jù)庫。很多sql的特性是不支持的，需要使用其他手段改進。以下以3.0.0版本進行描述。

distinct

sharding-jdbc不支持distinct，單表可使用group by進行替代。多表聯(lián)查可使用exists替代

select DISTINCTa, b, c, dfrom tablewhere df=0 復制代碼

改成

select a, b, c, dfrom tablewhere df=0group by a, b, c, d 復制代碼

having

sharding-jdbc不支持having，可使用嵌套子查詢進行替代

union

sharding-jdbc不支持union（all），可拆分成多個查詢，在程序拼接

關(guān)于子查詢

sharding-jdbc不支持在子查詢中出現(xiàn)同樣的表，如 以下可以==>

SELECT COUNT(*) FROM (SELECT * FROM t_order o) 復制代碼

以下報錯==>

SELECT COUNT(*) FROM (SELECT * FROM t_order o WHERE o.id IN (SELECT id FROM t_order WHERE status = ?)) 復制代碼

由于歸并的限制，子查詢中包含聚合函數(shù)目前無法支持。

mybatis 注釋

sharding-jdbc不支持sql中的<!-- – >注釋，如必須使用則寫在sql前，或使用/* */

不支持text字段

改為varchar，好幾年的bug了，但是沒改

case when

某些case when是不支持的，比如不在聚合函數(shù)中的case when，需要將這部分sql邏輯寫到程序里。

case when不應該是DBA禁用的函數(shù)么？我們在填坑

一些奇怪的反應

這個是可以的

select a-b from dual 復制代碼

但這個不可以...

select (a-b)c from dual 復制代碼

sharding 也不支持如下形式查詢，解析紊亂

and (1=1 or 1=1) 復制代碼

關(guān)于分頁

嚴禁無切分鍵的深分頁！因為會對SQL進行以下解釋，然后在內(nèi)存運行。

select * from a limit 10 offset 1000 復制代碼

=======>

Actual SQL:db0 ::: select * from a limit 1010 offset 0 復制代碼

關(guān)于表名

表名需與sharding-jdbc配置一致，推薦均為小寫。因為路由是放在hashmap里的，沒有區(qū)分大小寫...所以如果你的sql寫錯了會找不到。

配置冗余

每一張表都要配置路由信息才能夠被正確解析，如果你庫里的表太多，這個配置文件會膨脹的特別大，上千行也是有的。所以在yml中可以將配置文件分開。

spring.profiles.include: sharding 復制代碼

如何掃多庫

比如一些定時任務，需要遍歷所有庫。

方法1：遍歷所有庫

使用以下方式拿到真正的數(shù)據(jù)庫列表

Map<String, DataSource> map = ShardingDataSource.class.cast(dataSource).getDataSourceMap(); 復制代碼

然后在每一個庫上執(zhí)行掃描邏輯。這種情況下無法使用mybaits，需要寫原生jdbc

方法2：根據(jù)切分鍵遍歷

此種方法會拿到一個切分鍵的列表，比如日期等。然后通過遍歷這個列表執(zhí)行業(yè)務邏輯。此種方法在列表特別大的時候執(zhí)行會比較緩慢。

如何驗證

分庫分表很危險，因為一旦數(shù)據(jù)入錯庫，后續(xù)的修理很麻煩。所以剛開始可以將路由信息指向到源表，即：只驗證SQL路由的準確性。等待所有的SQL路由都驗證通過，再切換到真正的分庫或者表。

確保能夠打印SQL

sharding.jdbc.config.sharding.props.sql.show: true 復制代碼

將sql打印到單獨的文件(logback)

<appender name="SQL" class="ch.qos.logback.core.rolling.RollingFileAppender"><file>${LOG_HOME}/sharding.log</file><rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy"><fileNamePattern>${LOG_HOME}/backup/sharding.log.%d{yyyy-MM-dd}</fileNamePattern><maxHistory>100</maxHistory> </rollingPolicy> <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder"><pattern>${ENCODER_PATTERN}</pattern> </encoder> </appender> 復制代碼

寫一些腳本進行SQL文件的驗證。我這里有個通用的，你可以改下你的邏輯。

import sys import re import getoptdef process(SQL):one= "".join(line.strip().replace("\n", " ") for line in SQL)place = [m.groups()[0] if m.groups()[0] else m.groups()[1] for m in re.finditer(r"[ ]+(\w+)[ ]*=[ ]*\?|(\?)", one)]if len(place):mat = re.search(r"::: \[\[(.*)\]\]", one)if mat is not None:vals = [str(i).strip() for i in str(mat.groups()[0]).split(',')]if "splitKey" in place:for i in range(len(place)):part = place[i]//這里寫你的邏輯else:print("no splitKey", one)SQL = [] def process_line(line):global SQLif "Actual SQL" in line:SQL = []SQL.append(line)else:if line.strip().endswith("]]"):SQL.append(line)process(SQL)SQL = []else:SQL.append(line)opts, args = getopt.getopt(sys.argv[1:], "bf")for op, value in opts:if op == "-b":print("enter comman mode , such as 'python x.py -b sharding.log > result'")with open(args[0], "rb") as f:for line in f:process_line(line)elif op== "-f":print("enter stream scroll mode , such as 'python x.py -f sharding.log '")with open(args[0], "rb") as f:f.seek(0,2)while True:last_pos = f.tell()line = f.readline()if line: process_line(line) 復制代碼

其他

你可能要經(jīng)常切換路由，所以某些時候路由信息要放在云端能夠動態(tài)修改。

哦對了，我這里還有一段開發(fā)階段的驗證代碼，能讓你快速驗證SQL能否正確解析。

@RunWith(SpringRunner.class) @SpringBootTest(classes = App.class)public class ShardingTest {@AutowiredDataSource dataSource;@Testpublic void testGet() {try {Connection conn = dataSource.getConnection();PreparedStatement stmt;ResultSet rs;String sql = new String(Files.readAllBytes(Paths.get("/tmp/a.sql")));stmt = conn.prepareStatement(sql);rs = stmt.executeQuery();printRS(rs);} catch (Exception ex) {ex.printStackTrace();}}public static void printRS(ResultSet rs) throws Exception {ResultSetMetaData rsmd = rs.getMetaData();int columnsNumber = rsmd.getColumnCount();while (rs.next()) {for (int i = 1; i <= columnsNumber; i++) {if (i > 1) System.out.print(", ");String columnValue = rs.getString(i);System.out.print(columnValue + " " + rsmd.getColumnName(i));}System.out.println("");}} } 復制代碼

有SQL規(guī)范的團隊是幸福的，分庫分表簡單的很。而動輒幾百行，有各種復雜函數(shù)的SQL，就只能一步一個坑了。

話說回來，如果不是為了事務這個特性，為了支持老掉牙的業(yè)務，誰會用這分完后人不像人，鬼不像鬼的東西。

總結(jié)

以上是生活随笔為你收集整理的非规范SQL的sharding-jdbc实践的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。