MySQL从csv字符串中提取数据并插入到第二个表中 [英] MySQL extract data from csv string and insert into a second table
问题描述
我有一个表格，其中一列是一个字符串，元素用分号分隔.每行最多有 K 个元素.一个例子在表 1 中.
I have a table where one column is a string with element separated with a semicolon. in each row there are up to K elements. An example is in Table1.
我必须提取分号之间的值并将它们插入到一个新表中，其中每个元素都有自己的列，如表 2 所示.
I have to extract the values between the semicolon and insert them into a new table where each element will have its column, as in Table2.
我的第一种方法是在 Java 中创建一个例程来执行此操作(我正在编写的程序已使用该数据库进行分析)，但是有没有一种方法可以仅在 MySQL 中执行此操作?我找到了这两个例子 (1, 2)，但我的问题有点不同.我想从第一个表中提取一组值并将其插入到第二个表中，但我不知道这是否可行.
My first approach would be to create a routine in Java to do it (the database is already used by a program that I'm writing to do the analysis), but is there a method to do it only in MySQL? I have found these two examples (1, 2), but my problem is a little different. I was thinking to extract an array of values from the first table and insert it in the second, but I don't know if this is possible.
非常感谢任何建议，
Table1
*****************************
* ID * Col1 *
*****************************
* 1 * abc;def;ghi;lmn;opq *
* 2 * agf;hdgk;jsud; *
* * *
* n * jd;l;qpekd;kdh; *
*****************************
Table2
*****************************************
* ID * Col1 * Col2 * Col3 * * ColK *
*****************************************
* 1 * abc * def * ghi * * *
* 2 * agf * hdgk * jsud * * *
* * * * * * *
* n * jd * l * qpekd* * *
*****************************************
不幸的是，我不得不用 Java 完成这项工作，如果有人发现同样的问题，我会留下解决方案.但是关于如何在 MySQL 中做到这一点的问题仍然悬而未决
Unfortunately I had to do the work in Java, I leave the solution if someone will find the same problem. But the question on how to do it in MySQL is still open
public void analyse_Logs(Connection as DB_Connection) {
String MySQL_String, messageString;
Long nInitial, nFinal, iRow;
try{ // begin try-catch for Query_Connection
this.checkConnection();
Statement MySQL_Statement = DB_Connection.createStatement();
try { // begin try-finally for Query_Statement
MySQL_String = "SELECT message FROM logs";
ResultSet MySQL_ResultSet = MySQL_Statement.executeQuery(MySQL_String);
try { // begin try-finally for Query_ResultSet
iRow = Long.Valueof(0);
while (MySQL_ResultSet.next()) {
messageString = MySQL_ResultSet.getString("message"); //for each row, extract log message
this.logMessageAnalysis(iRow, messageString); //call log analysis routine
iRow = iRow+1;
}
} finally {
try { MySQL_ResultSet.close(); }
catch (SQLException ex) { System.out.println("Error - " + ex.toString()); }
} // end try-finally for Query_ResultSet
} finally {
try { MySQL_Statement.close(); }
catch (SQLException ex) { System.out.println("Error - " + ex.toString()); }
} // end try finally for Query_Statement
} catch(SQLException ex) {
System.out.println("Error - " + ex.toString());
} // end try-catch for Query_Connection
}
public void logMessageAnalysis(Long iRow, String logMessage, Connection DB_Connection){
//split string in array
String inputArray[] = logMessage.split(";");
String[] outputArray = new String[16];
outputArray[0] = String.valueOf(iRow);
for(int i=1; i<inputArray.length; i=i+1){
if (inputArray[i].length() > 45) {
inputArray[i] = inputArray[i].substring(0, 45);
}
outputArray[i] = inputArray[i];
}
try{ // begin try-catch for Query_Connection
this.checkConnection();
Statement MySQL_Statement = DB_Connection.createStatement();
try { // begin try-finally for Query_Statement
String Query_String = "INSERT INTO logs_2 VALUES ('";
for (int i=0; i<15; i=i+1) {
Query_String = Query_String + outputArray[i] + "','";
}
Query_String = Query_String + outputArray[15] + "')";
MySQL_Statement.executeUpdate(Query_String);
} finally {
try { MySQL_Statement.close(); }
catch (SQLException ex) { System.out.println("Error - " + ex.toString()); }
} // end try finally for Query_Statement
} catch(SQLException ex) {
System.out.println("Error - " + ex.toString());
} // end try-catch for Query_Connection
}
推荐答案
INSERT INTO table2 (ID, col1, col2, col3, col4)
SELECT
t1.id
, substring_index(t1.col1,';',1)
, substring_index(t1.col1,';',2)
, substring_index(t1.col1,';',3)
, substring_index(t1.col1,';',4)
FROM table1 t1
参见:http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index
这篇关于MySQL从csv字符串中提取数据并插入到第二个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
