时间戳

首页 > 解决方案 > 重写时的数据库查询

php - 重写时的数据库查询

问题描述

我有 PHP 脚本，每 5 分钟运行一次，从 API 获取数据，然后将其写入 MySQL 表。我网站的用户（超过 300 名）可以通过数据表查询该数据，而其他页面则显示其中的一些数据。然后 PHP 脚本获取 API 数据

foreach($array as $row)
{
    $query .= "INSERT INTO table_name 
    (
        col_name1, 
        col_name2, 
        col_name3, 
        col_name4, 
        col_name5
    ) 
    VALUES
    (
        '".$row["value1"]."', 
        '".$row["value2"]."', 
        '".$row["value3"]."', 
        '".$row["value4"]."', 
        '".$row["value5"]."'
    );";
}

mysqli_query($connect, "DELETE FROM table_name");

mysqli_multi_query($connect, $query);

每次脚本运行时，DELETE 然后 INSERT 到那个空表中。该表有 1000 行，并且会随着时间的推移而增长。我收到报告说数据表有时是空的，他们必须刷新几次才能显示任何内容。

有没有更好的方法来构建数据库、表和/或查询。

标签： phpmysqlmysqli

解决方案

为每一行运行单独的插入语句将非常缓慢。

运行多插入会更有效，使用单个语句插入多行。例如，用一条语句插入四行。

INSERT INTO t (a,b,c) VALUES (?,?,?) ,(?,?,?) ,(?,?,?) ,(?,?,?)

一个潜在的缺点是，如果由于错误而无法插入行，则整个语句将回滚，并且不会插入任何行。

SQL 语句的最大长度由 限制max_allowed_packet。不必在单个语句中插入所有行。一次插入 10 行将显着减少语句执行的次数。

假设该表使用 InnoDB 存储引擎...

如果我们禁用自动提交，并在单个事务的上下文中运行DELETE语句和语句，那么该表对于其他会话不会显得“空”。其他会话将继续查看该表的内容，就像它在... 之前一样，直到完成。INSERTDELETECOMMIT

Code Pattern 似乎容易受到 SQL 注入的攻击。（尤其是使用多查询时会遇到很多麻烦。

缓解 SQL 注入的最佳实践是使用带有绑定占位符的准备好的语句。

https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

编辑

作为一种替代方法，如果表具有主键或唯一键，请考虑

加载临时表（不是目标表）。

然后运行语句以应用更改以使目标表与临时表同步。我们将通过名称源来引用临时表。

-- 更新现有行

 UPDATE target t 
   JOIN source s
     ON s.id = t.id
    SET t.col = s.col 
      , t.foo = s.foo
      , t.bar = s.bar

-- 插入新行

INSERT INTO target
SELECT s.*
  FROM ( SELECT r.*
           FROM source r
             -- anti-join 
           LEFT
           JOIN target q
             ON q.id = r.id 
          WHERE q.id IS NULL
        ) s

-- 删除删除的行

 DELETE t.*
   FROM target t
     -- anti-join
   LEFT
   JOIN source s
     ON s.id = t.id
  WHERE s.id IS NULL

这避免了必须“清空”目标表，因此在“同步”目标表时并发 SELECT 语句仍将返回行。

针对目标表的 DML UPDATE/INSERT/DELETE 操作可以在单个事务的上下文中执行。

推荐阅读