mysql - VBScript 在同一代码中使用两个 For Next

Question

我需要将六个 txt 文件插入到 MySQL 表中。我想插入文件 txt10.txt 到表 D1，文件 txt20.txt 到表 D2，文件 txt30.txt 到表D3 等。但所有文本文件都插入到所有 MySQL 表中。

我的代码如下:

Arr1 = Array("txt10", "txt20", "txt30", "txt40", "txt50", "txt60") 
Arr2 = Array("D1", "D2", "D3", "D4", "D5", "D6")    

For K = 0 To UBound(Arr1)
    For I = 0 To UBound(Arr2)    
        SQL = " FLUSH TABLE `tbl_" & Arr2(I) & "_" & Year(Date()) & "`; "
        cn.Execute(SQL)   

        SQL = " LOAD DATA LOCAL INFILE 'D:\\nOpen\\" & Arr1(K) & ".txt' "
        SQL = SQL & " INTO TABLE `tbl_" & Arr2(I) & "_" & Year(Date()) & "` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;"
        cn.Execute(SQL) 
    Next
Next

编辑#1

在你的代码中我有错误:

Array index out of range

在这一行:

tbl = Arr2(n)

新代码:

Arr1 = Array("txt10", "txt20", "txt30", "txt40", "txt50", "txt60") 
Arr2 = Array("D1", "D2", "D3", "D4", "D5", "D6")    

For n=0 To UBound(Arr1)
    txt = Arr1(n)
    tbl = Arr2(n)  

    SQL = " FLUSH TABLE `tbl_" & tbl & "_" & Year(Date()) & "`; "
    cn.Execute(SQL)   

    SQL = " LOAD DATA LOCAL INFILE 'D:\\nOpen\\" & txt & ".txt' "
    SQL = SQL & " INTO TABLE `tbl_" & tbl & "_" & Year(Date()) & "` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;"
    cn.Execute(SQL) 
Next 

Answer 1

嗯，是的。按照您的方式运行两个 For 循环将为您提供两个输入数组元素的所有可能组合。

您真正想要的是使用单个循环并选择每个数组的相应元素:

For n=0 To UBound(Arr1)
    txt = Arr1(n)
    tbl = Arr2(n)
    'now insert txt into tbl
Next

或者，您可以构建文件到表的映射(反之亦然)，这样您就不必依赖两个数组的大小相同:

Set map = CreateObject("Scripting.Dictionary")
map.Add "txt10", "D1"
map.Add "txt20", "D2"
...

For Each txt In map.Keys
    tbl = map(txt)
    'now insert txt into tbl
Next

---

无论您选择哪种方式，都不要通过字符串连接来构建 SQL 查询。它会将您的代码打开到 SQL injection .使用 prepared statements相反。