含有变量的myql查询再php中无法执行

在数据库可以执行出结果，但是在php中无法运行，如下该如何修改？
数据库执行：

SET @EE='';
select @EE :=CONCAT(@EE,'sum(if(subject= \'',subject,'\',score,0)) as ',subject, ',') AS aa FROM (SELECT DISTINCT subject FROM (SELECT * FROM tb_score WHERE time<='2023-05-02')T) A ;
SET @QQ = CONCAT('select ifnull(userid,\'TOTAL\')as userid,',@EE,'sum(score) as TOTAL from tb_score group by userid WITH ROLLUP');
SELECT @QQ;PREPARE stmt FROM @QQ;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

php中无法执行

    $data = $pdo->query("
select @EE :=CONCAT(@EE,'sum(if(subject= \'',subject,'\',score,0)) as ',subject, ',') AS aa FROM (SELECT DISTINCT subject FROM (SELECT * FROM tb_score WHERE time<='2023-05-02')T) A ;
SET @QQ = CONCAT('select ifnull(userid,\'TOTAL\')as userid,',@EE,'sum(score) as TOTAL from tb_score group by userid WITH ROLLUP');
SELECT @QQ;PREPARE stmt FROM @QQ;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

")->fetchAll(PDO::FETCH_ASSOC);
  $json = json_encode(array(
    "code" => 0,
    "msg" => "",
    "count" =>  $num,
    "data" => $data
  ), JSON_UNESCAPED_UNICODE); //JSON_UNESCAPED_UNICODE注意不加会乱码
  echo $json;

参考GPT和自己的思路：这个问题是因为你在PHP中的SQL查询语句中包含了MySQL中的变量，而PHP和MySQL的变量定义方式不同，导致PHP无法识别和执行这个SQL查询语句。

解决方法是用PHP来定义MySQL变量并执行查询，而不是在PHP的SQL查询语句中引用MySQL变量。可以尝试将MySQL查询语句中的变量定义部分移到PHP中，然后将整个查询语句放在一个字符串变量中，最后通过PDO预处理语句来执行查询。代码示例如下：

$pdo = new PDO("mysql:host=localhost;dbname=mydatabase", "username", "password");

// 定义MySQL变量并执行查询
$pdo->exec("SET @EE=''");
$pdo->exec("
    SELECT @EE := CONCAT(@EE, 'sum(if(subject=\'', subject, '\', score, 0)) as ', subject, ',') as aa
    FROM (
        SELECT DISTINCT subject
        FROM (
            SELECT * FROM tb_score
            WHERE time <= '2023-05-02'
        ) T
    ) A
");
$pdo->exec("
    SET @QQ = CONCAT(
        'SELECT ifnull(userid,\'TOTAL\') as userid,', @EE, 'sum(score) as TOTAL
         FROM tb_score
         GROUP BY userid WITH ROLLUP'
    )
");

// 执行查询并获取结果
$stmt = $pdo->prepare("SELECT @QQ");
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 将结果转化为JSON格式输出
$json = json_encode(array(
    "code" => 0,
    "msg" => "",
    "count" => count($data),
    "data" => $data
), JSON_UNESCAPED_UNICODE);
echo $json;

参考GPT和自己的思路：您好，您的问题是因为这段查询语句里面带有MySQL的变量，但是在PHP中无法直接执行，需要通过PDO的prepare和execute方法来执行。您需要将这段查询语句分成多行，并将其中的MySQL变量替换为占位符，然后通过PDO的prepare方法绑定参数，最后通过execute方法执行。代码示例如下：

// 构建含有占位符的查询语句
$sql = "
SET @EE='';
select @EE :=CONCAT(@EE,'sum(if(subject= ?,',subject,',score,0)) as ',subject,',') AS aa FROM (SELECT DISTINCT subject FROM (SELECT * FROM tb_score WHERE time<='2023-05-02')T) A ;
SET @QQ = CONCAT('select ifnull(userid,\'TOTAL\')as userid,',@EE,'sum(score) as TOTAL from tb_score group by userid WITH ROLLUP');
SELECT @QQ;
PREPARE stmt FROM @QQ;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
";

// 准备查询
$stmt = $pdo->prepare($sql);

// 绑定占位符的参数
$subject = ''; // 待查询的subject
$stmt->bindParam(1, $subject, PDO::PARAM_STR);

// 执行查询并获取结果
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 处理结果
$json = json_encode(array(
  "code" => 0,
  "msg" => "",
  "count" =>  $num,
  "data" => $data
), JSON_UNESCAPED_UNICODE);
echo $json;

请注意，这只是示例代码，具体的细节还需要根据您的实际情况进行适当调整。希望对您有帮助。

  • 这有个类似的问题, 你可以参考下: https://ask.csdn.net/questions/7764875
  • 这篇博客也不错, 你可以看下PHP连接数据库MySQL打造xxx管理系统，实现简单的增删改查功能
  • 除此之外, 这篇博客: 刷php面试题中的 mysql中表的存储引擎常用的两种，他们有什么区别？-2019.03.23 部分也许能够解决你的问题, 你可以仔细阅读以下内容或跳转源博客中阅读:

    1. 在mysql中一个数据库中可以同时存在多种引擎，存储引擎是针对表的。

    2. 事物

      MyISAM：不支持事物
INNODE：支持事物

    3. 存储空间

       MyISAM：的表存储成3个文件。文件的名字与表名相同，frm文件存储表的结构，MYD文件存储数据，MYI文件存储索引。
 INNODE：所有的表都保存在同一个数据文件中。

    4. 表锁差异

       MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁
 InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，
 只是在WHERE的主键是有效的

    5. 外键

       MyISAM：不支持
 InnoDB：支持

    6. MyISAM和InnoDB两者的应用场景

       MyISAM管理非事务表。它提供高速存储和检索。如果应用中需要执行大量的SELECT查询，那么
 MyISAM是更好的选择。
 InnoDB用于事务处理应用程序，具有众多特性，包括事务支持。如果应用中需要执行大量的INSERT或UPDATE、
 操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。
  • 您还可以看一下 黄菊华老师的PHP+Mysql网上购物服装商城毕业设计 大学生毕业设计教学视频课程中的 php+mysql服装商城 后台功能操作演示（毕业设计教学）小节, 巩固相关知识点
  • 以下回答由chatgpt基于相关博客总结生成:

    针对该问题，可以考虑以下解决方案：

    1.使用预处理语句

    PHP中可使用预处理语句（Prepare Statement）来防止SQL注入，并且可以提高查询效率。具体的操作步骤如下：

    ①连接数据库：

    $conn = new mysqli($servername, $username, $password, $dbname);

    ②准备查询语句：

    $sql = "SELECT * FROM table_name WHERE column_name = ?";

    ③预处理语句：

    $stmt = $conn->prepare($sql);

    ④绑定参数：

    $stmt->bind_param('s', $var);

    ⑤执行SQL语句：

    $stmt->execute();

    ⑥获取结果：

    $result = $stmt->get_result();

    通过使用预处理语句，我们可以将变量传递给SQL语句，从而避免了SQL注入的风险，并且可以提高查询效率，减少数据库的压力。

    2.优化查询语句

    在编写SQL查询语句时，应该注意以下几点：

    ①避免在循环中执行查询语句，可以在循环外部执行一次查询，并将查询结果存储在一个变量中，然后在循环中使用该变量；

    ②避免使用SELECT *，应该明确指定要查询的列；

    ③使用索引可以提高查询效率，应该选取合适的索引类型来优化查询；

    ④避免使用子查询，尽量使用JOIN语句代替。

    3.调整MySQL配置参数

    在MySQL配置中，有一些参数可以影响查询的效率，如果设置不当可能会导致查询缓慢。可以根据实际情况调整以下参数：

    ①max_connections：控制在任何给定时间内允许多少个客户端同时连接到服务器。如果连接数过多，会导致查询缓慢。默认值为151。

    ②key_buffer_size：MyISAM存储引擎使用的索引缓存大小，对查询效率有影响。默认值为8M。

    ③query_cache_size：启用查询缓存，加快重复查询的速度。默认值为0。

    4.优化数据库结构

    对于大型数据库，优化数据库结构也是提高查询效率的重要因素。可以采取以下措施来优化数据库结构：

    ①合理设计表结构，不要过度规范化，避免过多的表关联；

    ②使用复合索引，可以提高查询效率；

    ③避免使用大型表或者分区表，可以将表分解为多个小表，提高查询效率。

    综上所述，我们可以通过使用预处理语句、优化查询语句、调整MySQL配置参数以及优化数据库结构等方式来解决PHP中无法执行含有变量的MySQL查询语句的问题，从而提高查询效率并减少数据库的压力。