Original: Taste of Little Sister (wechat official ID: XjjDog), welcome to share, please reserve the source. Any reprint that does not retain this statement is plagiarism.

We talked about Sharding-JDBC at the driver level in “Select and process carefully or you’ll get out of control”. Open source at this level is awesome, not like TDDL being a eunuch. But there are still holes.

But don’t blame the framework, after all, some SQL, only programs and ghosts can understand.

<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 order
       	where  orderCode =  #{item}
    </foreach>
</select>
Copy the code

An unsupported operation

After dividing the database into tables, it becomes a castrated database. Many SQL features are not supported and need to be improved by other means. The following is described in version 3.0.0.

distinct

Sharding-jdbc does not support distinct, and a single table can be replaced by group BY. Multiple table linkage can be replaced by EXISTS

select DISTINCT
        a, b, c, d
        from  table
        where df=0
Copy the code

to

select a, b, c, d
        from  table
        where df=0
        group by a, b, c, d
Copy the code

having

Sharding-jdbc does not support HAVING and nested subqueries can be used instead

union

Sharding-jdbc does not support union (all), which can be split into multiple queries and spliced in the program

About subqueries

Sharding-jdbc does not support the same table in a subquery, if ==>

SELECT COUNT(*) FROM (SELECT * FROM t_order o)
Copy the code

The following error is reported ==>

SELECT COUNT(*) FROM (SELECT * FROM t_order o WHERE o.id IN (SELECT id FROM t_order WHERE status =?). )Copy the code

Due to merging limitations, subqueries containing aggregate functions are currently not supported.

Mybatis annotation

Sharding-jdbc does not support
comments, if you must use before SQL, or use /* */

Text fields are not supported

Changed to VARCHar, buggy for several years, but did not change

case when

Some case WHEN is not supported, such as case when not in the aggregate function, requiring that part of the SQL logic be written to the program.

Shouldn’t case When be a dbA-disabled function? We’re filling a hole

Some strange reactions

This is ok

select  a-b from dual  
Copy the code

But this is not…

select (a-b)c from dual  
Copy the code

Sharding also does not support the following form of query, parsing disorder

and (1=1 or 1=1)
Copy the code

On the page

Deep paging without shards is strictly prohibited! The SQL is interpreted as follows and then run in memory.

select *  from a limit 10 offset 1000
Copy the code

= = = = = = = >

Actual SQL:db0 ::: select *  from a limit 1010 offset 0
Copy the code

About the name of the table

The table name must be the same as the sharding-JDBC configuration and lowercase is recommended. Because routes are in hashmap, they are case insensitive… So if you write your SQL wrong, you won’t find it.

Configuration redundancy

Each table has to be configured with routing information to be properly parsed. If you have too many tables, the configuration file can swell to thousands of rows. So you can separate configuration files in YML.

spring.profiles.include: sharding
Copy the code

How to scan multiple libraries

For example, some scheduled tasks require traversing all libraries.

Method 1: Iterate through all libraries

Use the following method to get the actual database list

Map<String, DataSource> map = ShardingDataSource.class.cast(dataSource).getDataSourceMap();
Copy the code

The scan logic is then performed on each library. In this case, mybaits cannot be used and native JDBC needs to be written

Method 2: Traverse according to the shard key

This method returns a list of shard keys, such as dates, etc. The business logic is then executed by iterating through the list. This method is slow to execute when the list is very large.

How to validate

Separate tables are dangerous because once data is stored in the wrong repository, subsequent repairs are cumbersome. So you can initially point the routing information to the source table, that is, verify only the accuracy of the SQL route. Wait for all SQL routes to be validated before switching to a real branch or table.

Make sure you can print SQL

sharding.jdbc.config.sharding.props.sql.show: true
Copy the code

Print SQL to a separate file (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>
Copy the code

Write some scripts to validate SQL files. I have a generic one here, so you can change your logic.

import sys import re import getopt def 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)): Else: print("no splitKey", one) SQL = [] def process_line(line): global SQL if "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)Copy the code

other

You may have to switch routes a lot, so at some point the routing information needs to be in the cloud and can be changed dynamically.

By the way, I also have some development-stage validation code here that will allow you to quickly verify that your SQL is properly parsed.

@RunWith(SpringRunner.class) @SpringBootTest(classes = App.class) public class ShardingTest { @Autowired DataSource dataSource; @Test public 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(""); }}}Copy the code

Welcome to star: github.com/xjjdog/bcma… . It includes ToB complex business, Internet high concurrency business, cache applications; DDD, microservices guidance. Model driven, data driven. Understand the evolution of large-scale services, coding skills, Learning Linux, performance tuning. Docker/ K8S power, monitoring, log collection, middleware learning. Front-end technology, back-end practices, etc. Main technology: SpringBoot+JPA+Mybatis-plus+Antd+Vue3.

Teams with SQL specifications are happy, and it is very easy to separate libraries and tables. SQL, with hundreds of lines and complex functions, is one step at a time.

After all, if it wasn’t for the transactional nature of the business, to support the old business, who would use this part of the non-human, non-ghost ghost thing.

Xjjdog is a public account that doesn’t allow programmers to get sidetracked. Focus on infrastructure and Linux. Ten years architecture, ten billion daily flow, and you discuss the world of high concurrency, give you a different taste. My personal wechat xjjdog0, welcome to add friends, further communication.