mysqli-prepare语句失败，错误为"no table used" [英] mysqli-- prepare statement failing with error "no table used"

问题描述

用php编写的新手，我正在使用$ mysqli-> prepare()函数准备执行一条语句，但是它一直失败，我不知道为什么. $ mysqli-> error内部的错误是未使用表"，而且我似乎也找不到太多的文档……如果有人可以帮忙，将不胜感激.下面的代码:

new to writing in php, I'm using a $mysqli->prepare() function to get a statement ready for execution, but it keeps failing and I don't know why. THe error inside the $mysqli->error is "no table used," and I can't seem to find much documentation for that either... if anyone could help out, that would be hugely appreciated. Code below:

$datasqli=new mysqli(HOST, USERNAME, PASSWORD, DATABASE); // defined elsewhere
if ($datasqli->connect_errno) {
    printf("Connect failed: %s\n", $datasqli->connect_error);
    exit();
}

if ($usertest = $datasqli->prepare("INSERT INTO eeg (Identifier) 
    SELECT * FROM (SELECT ? ) AS tmp WHERE NOT EXISTS (
    SELECT Identifier FROM eeg WHERE Identifier = ?
    ) LIMIT 1")) {
    // this stuff never gets executed...
} else {
    echo $datasqli->error; // "no table used"
}

我尝试直接在mysql环境中执行该代码块，并且工作正常.

I've tried executing that code block directly inside the mysql environment and it works fine.

推荐答案

显然，

SELECT * FROM (SELECT ? )

...未被识别为有效的MySQL语法.表名丢失.

...is not recognized as a valid MySQL syntax. A table name is missing.

编辑，关于您的评论:

EDIT, Concerning your comments:

首先，请注意，在控制台中通过用常量替换?来执行此语句并不能模拟您的情况，因此我认为结果对于比较无效.

First of all, please note that executing this statement in a console by substituting ? with a constant does not emulate your situation, so I would consider the result invalid for comparison.

但是再执行一次而不替换?自然会给出错误.

But then again, executing it without substituting ? would, naturally, give an error.

这是因为仅执行选择与您的情况无关.在您的php代码中，不是 execution 失败，而是 preparation .因此，使用控制台进行模拟的正确方法是 PREPARE 声明.

That's because executing just the select is irrelevant to your situation. In your php code, it's not the execution that fails but rather the preparation. So the proper way to emulate this using a console, would be the PREPARE statement.

所以做

PREPARE myStmt 
  FROM 'SELECT * FROM (SELECT ? ) AS tmp WHERE NOT EXISTS (
    SELECT Identifier FROM eeg WHERE Identifier = ?
    ) LIMIT 1'

将更准确地重现您的问题.

would reproduce your issue more accurately.

现在，似乎PREPARE难以理解出现在FROM子句中的参数化嵌套查询.看一下这些例子:

Now, it seems that PREPARE has a difficulty understanding parametrized nested queries that appear in the FROM clause. Take a look at these examples:

PREPARE myStmt FROM "select * from (select ? from eeg) tmp"; 

(无效)

PREPARE myStmt FROM "select *,? from (select * from eeg) tmp"; 

(有效)

PREPARE myStmt FROM "select *,? from (select 'asdf') tmp"; 

(有效)

PREPARE myStmt FROM "select * from eeg where Identifier in (select ?)"; 

(有效)

好奇的行为，但是我只能猜测当FROM子句中的嵌套SELECT具有参数时，MySQL缺少为准备该语句的线索.

Curious behaviour, but I can only guess that when a nested SELECT in the FROM clause has parameters, MySQL is missing clues in order to prepare the statement.

关于我的建议，如果我了解您要执行的操作，则在嵌套选择中不需要参数.出于FROM的原因，您可以将其移到外部并在嵌套选择中对常量进行硬编码.以下代码

As for my suggestion, if I understand what you are trying to do, you don't need a parameter in the nested select. You could move it outside and hardcode a constant in the nested select, for the sake of FROM. The following code

if ($usertest = $datasqli->prepare("INSERT INTO eeg (Identifier) 
    SELECT ? from (select 1) tmp WHERE ? NOT IN
      (SELECT Identifier FROM eeg WHERE Identifier = ?)")) {

...应该可以解决问题.

...should do the trick.

这篇关于mysqli-prepare语句失败，错误为"no table used"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！