Existing SQL script to list in the SQL statement in the script involves the target table name, the target table all of the fields, the target table field corresponds to the original name of the table, mapping relationship, how can fast finish? A more than 400 lines of SQL took two hours to find out, want to cry, looked at more than 100 script for bosses give an idea, have what method can analyze from the SQL statement field corresponds to the original and target table?

CodePudding user response:

Using SQL, it's hard to put the words in python is very simple, traverse the SQL files, SQL statements to the list, then just regular expression match

CodePudding user response:

This is to parse SQL, find tools to directly,

I found a Java, but don't know can support large complex SQL
https://github.com/alibaba/druid/wiki/Druid_SQL_AST

CodePudding user response:

Pick a set of code, but should not achieve your requirement, if the table alias blind to write, people have no idea, let alone is machine,

 import Java. Util. List; 

The import com. Alibaba. Druid. SQL. Ast. SQLStatement; 
The import com. Alibaba. Druid. SQL. The dialect. Mysql. Parser. MySqlStatementParser; 
The import com. Alibaba. Druid. SQL. The dialect. Mysql. The visitor. MySqlOutputVisitor; 
The import com. Alibaba. Druid. SQL. The dialect. Oracle. Parser. OracleStatementParser; 
The import com. Alibaba. Druid. SQL. The dialect. Oracle. The visitor. OracleOutputVisitor; 
The import com. Alibaba. Druid. Util. Utils; 

Public class MySqlPerfTest {

Private String SQL; 

Public static void main (String [] args) throws the Exception {
MySqlPerfTest test=new MySqlPerfTest (); 
The test setUp (); 
Test. ExecMySql (test. SQL); 
System. The out. Println (test. ExecMySql (test. SQL)); 
} 

Protected void setUp () throws the Exception {
//SQL="SELECT * FROM T"; 
SQL="SELECT ID, NAME, AGE, FROM the USER WHERE ID=? Union all SELECT ID, NAME, AGE FROM USER2, and user3 t2 where 1=2 and 33=DFG and USER2. D=t2. Asdf "; 

//SQL=Utils. ReadFromResource (" benchmark/SQL/ob_sql. TXT "); 
} 

Public void test_pert () throws the Exception {
For (int I=0; i <10; + + I) {
PerfMySql (SQL); 
} 
} 

Long perfMySql (String SQL) {
.//long startYGC=TestUtils getYoungGC (); 
.//long startYGCTime=TestUtils getYoungGCTime (); 
.//long startFGC=TestUtils getFullGC (); 

Long startMillis=System. CurrentTimeMillis (); 
For (int I=0; i <1000 * 1000; + + I) {
ExecMySql (SQL); 
} 
Long millis=System. CurrentTimeMillis () - startMillis; 

.//long ygc=TestUtils getYoungGC () - startYGC; 
.//long ygct=TestUtils getYoungGCTime () - startYGCTime; 
.//long FGC=TestUtils getFullGC () - startFGC; 

//System. Out. Println (" MySql \ t + millis + ", "ygc" + ygc + ", ygct "+ ygct +", FGC "+ FGC); 
Return millis; 
} 

Private String execMySql (String SQL) {
The StringBuilder out=new StringBuilder (); 
OracleOutputVisitor visitor=new OracleOutputVisitor (out); 
//MySqlOutputVisitor visitor=new MySqlOutputVisitor (out); 
//MySqlStatementParser parser=new MySqlStatementParser (SQL); 
OracleStatementParser parser=new OracleStatementParser (SQL); 
List StatementList=parser. ParseStatementList (); 
For (SQLStatement statement: statementList) {
Statement. The accept (the visitor); 
The visitor. Println (); 

} 
Return out. The toString (); 
} 
} 

CodePudding user response:

reference nayi_224 reply: 3/f

pulled a set of code, but should not achieve your request, if the table alias blind to write, people have no idea, let alone is machine,

 import Java. Util. List; 

The import com. Alibaba. Druid. SQL. Ast. SQLStatement; 
The import com. Alibaba. Druid. SQL. The dialect. Mysql. Parser. MySqlStatementParser; 
The import com. Alibaba. Druid. SQL. The dialect. Mysql. The visitor. MySqlOutputVisitor; 
The import com. Alibaba. Druid. SQL. The dialect. Oracle. Parser. OracleStatementParser; 
The import com. Alibaba. Druid. SQL. The dialect. Oracle. The visitor. OracleOutputVisitor; 
The import com. Alibaba. Druid. Util. Utils; 

Public class MySqlPerfTest {

Private String SQL; 

Public static void main (String [] args) throws the Exception {
MySqlPerfTest test=new MySqlPerfTest (); 
The test setUp (); 
Test. ExecMySql (test. SQL); 
System. The out. Println (test. ExecMySql (test. SQL)); 
} 

Protected void setUp () throws the Exception {
//SQL="SELECT * FROM T"; 
SQL="SELECT ID, NAME, AGE, FROM the USER WHERE ID=? Union all SELECT ID, NAME, AGE FROM USER2, and user3 t2 where 1=2 and 33=DFG and USER2. D=t2. Asdf "; 

//SQL=Utils. ReadFromResource (" benchmark/SQL/ob_sql. TXT "); 
} 

Public void test_pert () throws the Exception {
For (int I=0; i <10; + + I) {
PerfMySql (SQL); 
} 
} 

Long perfMySql (String SQL) {
.//long startYGC=TestUtils getYoungGC (); 
.//long startYGCTime=TestUtils getYoungGCTime (); 
.//long startFGC=TestUtils getFullGC (); 

Long startMillis=System. CurrentTimeMillis (); 
For (int I=0; i <1000 * 1000; + + I) {
ExecMySql (SQL); 
} 
Long millis=System. CurrentTimeMillis () - startMillis; 

.//long ygc=TestUtils getYoungGC () - startYGC; 
.//long ygct=TestUtils getYoungGCTime () - startYGCTime; 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull