VBA MySQL 一次执行多个查询 [英] VBA MySQL Several queries in one Execute

问题描述

我正在编写一个从本地系统获取数据然后将数据发送到 MySQL 数据库的工具.现在，它运行在Access DB上，但是合并来自少数本地数据库的数据(它在某些计算机上运行)非常耗时.

I'm programming a tool which grabs data from local systems and then sends data to MySQL database. Now, it's running on Access DB, but it's very time-consuming to merge data from few local databases (it's running on some computers).

我决定迁移到 MySQL，连接在执行单个查询时也能正常工作.但是以这种方式这样做在时间上是不可接受的(对于我的数据批次 - 将数据添加到 accdb 需要 1.5 秒，将数据添加到 MySQL 需要将近 80 秒).

I decided to move to MySQL, connection works as well executing single queries. But doing that in this way is not time-acceptable (for my data batch - 1,5 second to add data to accdb and almost 80 seconds to add the same to MySQL).

我知道远程 MySQL 数据库永远不会像本地 Access DB 那样快(MySQL DB 在共享主机上，这是我的私人项目).

我和我的 PHP & 朋友谈过MySQL 开发人员和他告诉我，批量运行查询会缩短执行时间(例如，一次执行 100 个查询而不是 100 个单次执行).

I talked to my friend who is PHP & MySQL dev and he told me that running queries at batch would shorten the execution (e.g. execute 100 queries in one time instead of 100 single executions).

我修改了我的脚本来这样做，但是如果我尝试执行多个查询，我会出现语法错误(当我将相同的查询粘贴到 phpMyAdmin 即时窗口时，它可以工作，所以这不是语法).

I modified my script to do so, but if I try to execute multiple queries I have Syntax Error (when I paste the same query into phpMyAdmin immediate window it works, so this is not syntax).

我读到 ADODB 连接(我使用的)无法执行多个查询 - 但是否有其他选择?

I read that ADODB Connection (which I use) cannot execute multiple queries - but if there any alternative?

我可以编写一个小的控制台 .NET 应用程序，它读取 .sql 文件然后执行批处理查询，但首先我想确定它是否真的必要.

I could write little console .NET application which reads .sql files and then executes a batch query, but first I want to be sure if it's really necessary.

我的连接已声明

Set cn = CreateObject("ADODB.Connection")
cn.Open "Driver={MySQL ODBC 5.3 Unicode Driver};Server=SERVER;Database=DB_NAME;Uid=DB_USER;Pwd=DB_PWD;Option=3" 

strQuery = "CREATE TABLE Z AS (SELECT * FROM tblOffers WHERE 1=2); CREATE TABLE ZZ AS (SELECT * FROM tblOffers WHERE 1=2);"
cn.Execute (strQuery)

这些查询仅用于测试 - 我不是在我的项目中创建新表，而是更新数据(如果重复则插入或更新).

These queries are just for testing - I'm not creating new tables in my project, but upsering data (insert or update if duplicate).

我的单个查询如下所示:

My single query looks like this:

INSERT INTO tblOffers (`TITLE`, `LINK`, `IMG`, `PRICE`, `DATE`, `PLATFORM`, `OFF_ID`, `LOCATION_ID`) 
SELECT * FROM (
SELECT "Zgrywus PC", "https://www","zgrywus-pc-znin.jpg",10,'2017-07-08',"PC","6hZH9","0929865") As Tmp 
ON DUPLICATE KEY 
UPDATE `TITLE` = "Zgrywus PC", 
`LINK` = "https://www", 
`IMG` = "zgrywus-pc-znin.jpg", 
`PRICE` = 10, 
`DATE` = '2017-07-08', 
`PLATFORM` = "PC", 
`OFF_ID` = "6hZH9", 
`LOCATION_ID` = "0929865";

@joanolo，这里是完整的代码片段:

@joanolo, here's full code snippet:

Sub ExportDataToAccess()

    Dim cn As Object
    Dim strQuery As String, strClrQuery As String
    Set cn = CreateObject("ADODB.Connection")

    cn.Open "Driver={MySQL ODBC 5.3 Unicode Driver};Server=SERVER;Database=DB;Uid=UN;Pwd=PWD;Option=3"
    strQuery = "CREATE TABLE Z AS (SELECT * FROM tblOffers WHERE 1=2); CREATE TABLE ZZ AS (SELECT * FROM tblOffers WHERE 1=2);"
    Debug.Print strQuery
    cn.Execute (strQuery)


    cn.Close
    Set cn = Nothing

End Sub

产生的错误是:屏幕

EDIT2，10-07-2017

我决定运行 多个行插入 - 它有效 - 所以我的问题解决了，但是如果有人对此主题的问题有任何解决方案，请分享.

I decided to run multiple row INSERT - it works - so my problem is resolved, but if anyone would have any solve for issue of this topic, please share.

推荐答案

@Krukosz - 今天我遇到了同样的问题，我用另一种方式解决了.那么你可以在 ForEachoder 其他循环中执行每个命令，由虚拟"短划线逗号分隔/拆分;"您在普通"SQL 语句中使用而不是在 VBA 中使用.很酷，你可以在更多的 Execute 上使用 var.

@Krukosz - I had the same question today, I solved it on another way. Well you can execute each command in a ForEach- oder other Loop, separated/splitted by the 'virtual' Dash-Comma ';' you use in 'normal' SQL-Statements not in VBA. It's cool that you can use vars over more Execute's.

这是一个示例代码，在这个示例的最后@start 将是 1082.但它似乎只记住了几分钟的 vars.如果您等待 5 分钟并再次尝试最后一行，@start 的值将为Null".

Here is an example code, and @start will be 1082 in the end of this example. But it seems to remember vars only for a few minutes. If you wait 5 minutes and try the last row again, the value of @start will be 'Null'.

Set rst = dbc.Execute("SET @start = 898")
Set rst = dbc.Execute("SET @start = @start+184 ")
Set rst = dbc.Execute("SELECT @start,'test'")
..
.

这篇关于VBA MySQL 一次执行多个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！