首页 \ 问答 \ 在MySQL中运行dplyr，grepl(Running dplyr, grepl in MySQL)

在MySQL中运行dplyr，grepl(Running dplyr, grepl in MySQL)

 我在SQL中有一个表，我正在测试dplyr 。 我有两个输入日期范围和一个公司文本字段 
 

 我在下面运行以下R代码 
 

  # Filter on the Customer and the date range the user selects
  mydf <- db %>%
    tbl(table) %>%
    filter(TableDate >= start_date & TableDate <= end_date) %>% 
    filter(grepl('company', company_name)) %>% 
    collect()

 

 因此理论上，这应该从公司company start_date和end_date之间的单个表中选择信息， 如模糊匹配，因为公司名称包含X行业和Y合并的内容 
 

 我收到如下错误 
 

 
 
 .local（conn，statement，...）出错：无法运行语句：执行命令被拒绝用户'闪亮'@'％'用于例程'mydb.GREPL' 
 

 

 当我看看sql 
 

myDF <- tbl(db,table)
myDF1 <- filter(myDF, TableDate >= start_date & TableDate <= end_date)
myDF2 <- filter(myDF1, grepl('company', company_name))

explain(myDF2)

 

 我得到了输出 
 

<SQL>
SELECT *
FROM (SELECT *
FROM `table`
WHERE (`TableDate ` >= '2015-01-01' AND `TableDate ` <= '2015-06-01')) `lpknlorhcn`
WHERE (GREPL('company', `company_name`))

 

 
 
 .local（conn，statement，...）出错：无法运行语句：执行命令被拒绝用户'闪亮'@'％'用于例程'mydb.GREPL' 
 

 

 出于隐私原因，我已稍微修改了代码和输出 
 

 问题 
 

     
 
  •  有谁知道如何正确设置它（我知道它在mysql端的权限事项，但我不知道用户闪亮的执行访问权限在哪里 
    •  
 
  •  为什么解释计划中的SQL看起来如此有趣（例如它有两个where子句）以及什么是lpknlorhcn 
    •  

 

 谢谢你的帮助 

I have a table in SQL that I'm testing out dplyr. I have two inputs a date range and a text field which is a company
 

I run the following R code below
 

  # Filter on the Customer and the date range the user selects
  mydf <- db %>%
    tbl(table) %>%
    filter(TableDate >= start_date & TableDate <= end_date) %>% 
    filter(grepl('company', company_name)) %>% 
    collect()

 

So in theory this should select information from a single table between start_date and end_date for the company company like a fuzzy match as the company names contain things like X industries and Y incorporated
 

I get an error like follows
 

 
 
Error in .local(conn, statement, ...) : could not run statement: execute command denied to user 'shiny'@'%' for routine 'mydb.GREPL'
 

 

when i have a look at the sql
 

myDF <- tbl(db,table)
myDF1 <- filter(myDF, TableDate >= start_date & TableDate <= end_date)
myDF2 <- filter(myDF1, grepl('company', company_name))

explain(myDF2)

 

I get the output
 

<SQL>
SELECT *
FROM (SELECT *
FROM `table`
WHERE (`TableDate ` >= '2015-01-01' AND `TableDate ` <= '2015-06-01')) `lpknlorhcn`
WHERE (GREPL('company', `company_name`))

 

 
 
Error in .local(conn, statement, ...) : could not run statement: execute command denied to user 'shiny'@'%' for routine 'mydb.GREPL'
 

 

I have modified the code and output slightly for privacy reasons
 

Questions
 

     
 
  • Does anyone know how to set this up correctly (I know its a permissions thing on the mysql side but im not sure where as the user shiny has execute access
    •  
 
  • Why does the SQL in the explain plan look so funny (it has two where clauses for instance) and what is lpknlorhcn
    •  

 

Thank you kindly for your help

原文：https://stackoverflow.com/questions/39618827
更新时间：2022-11-02 15:11

最满意答案

 问题是你的onreadystatechange处理程序是一个闭包，它引用XHR和element的当前值。 由于这些更改在每个循环迭代中进行，每个处理程序在执行时都会引用XHR和element的最新值。 由于循环可能在第一个请求完成之前退出，所有处理程序都会引用最后分配的值。 为了解决这个问题，你需要在声明处理程序时捕获XHR和element的当前值。 最简单的方法是使用IIFE （您也可以创建一个单独的函数来返回所需的函数，并避免使用错误的变量创建闭包）： 
 

XHR.onreadystatechange = (function(elt, req) {
    return function() {
        if (req.readyState == 4 && req.status == 200) {
            elt.innerHTML = friendlyExpress(JSON.parse(req.responseText).shares);
        }
    };
}(element, XHR));


The problem is that your onreadystatechange handler is a closure that refers to the current values of XHR and element. Since those change at each loop iteration, each handler, when it executes, refers to the latest values of XHR and element. Since the loop probably exits before the first request completes, all the handlers reference the last assigned values. To fix this, you need to capture the current values of XHR and element when declaring the handler. The easiest is with an IIFE (you could also create a separate function to return the required function and avoid creating the closure with the wrong variables):
 

XHR.onreadystatechange = (function(elt, req) {
    return function() {
        if (req.readyState == 4 && req.status == 200) {
            elt.innerHTML = friendlyExpress(JSON.parse(req.responseText).shares);
        }
    };
}(element, XHR));

相关问答

更多