简短的php / mysql查询删除两个表选择的位置

I need your help to speed up my mysql query

我需要你的帮助来加快我的mysql查询

my php code view looks like this:

我的PHP代码视图如下所示：

$product = query("SELECT `id`, `productname`, MAX(NO) FROM `product_list` WHERE 1");
$count1 = query("SELECT  `products` FROM `count1` WHERE 1");

list($noproduct) = mysql_fetch_array($count1);

if (mysql_num_rows($product) > $noproduct) {

    query("DELETE FROM `product_list` WHERE `NO` > ".$noproduct.");

}

and mysql base looks like this:

和mysql基地看起来像这样：

CREATE TABLE `count1` (
  `id` int(11) NOT NULL,
  `products` int(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `count1` (`id`, `products`) VALUES
(1, 9);

CREATE TABLE `product_list` (
  `id` int(11) NOT NULL,
  `productname` varchar(55) NOT NULL,
  `NO` int(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `product_list` (`id`, `productname`, `NO`) VALUES
(1, 'product1', 1),
(2, 'product2', 2),
(3, 'product3', 3),
(4, 'product4', 4),
(5, 'product5', 5),
(6, 'product6', 6),
(7, 'product7', 7),
(8, 'product8', 8),
(9, 'product9', 9),
(10, 'product10', 10),
(11, 'product11', 11);

ALTER TABLE `product_list`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `count1`
  ADD PRIMARY KEY (`id`);


ALTER TABLE `count1`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;


ALTER TABLE `product_list`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

My code executes three queries every time the page is reloaded and I need to make only one query.

每次重新加载页面时，我的代码都会执行三个查询，我只需要进行一次查询。

I think that it should look like:

我认为它应该看起来像：

Delete from product_list where (select product_list)  > (select count1)

1 个解决方案

#1

I think you are looking for this query:

我想你正在寻找这个查询：

DELETE FROM product_list 
WHERE NO > (SELECT  products FROM count1 LIMIT 1)

The addition of LIMIT 1 is just a safety measure, in case you would have more than one record in products.

如果您在产品中有多个记录，则添加LIMIT 1只是一种安全措施。

Note that there is no real need to first select MAX(NO). It doesn't hurt to run the delete statement if there is nothing to delete. It will just do nothing then.

注意，没有必要首先选择MAX（NO）。如果没有要删除的内容，则运行delete语句没有坏处。它只会做什么。

#1