获取表格行数的最有效方法

Question

我目前拥有一个超过600万行且不断增长的数据库。我目前使用 SELECT COUNT(id) FROM table; 来显示数字给我的用户，但是由于数据库变得越来越大，我没有必要存储所有这些行，除了能够显示数量之外。有没有一种方法可以选择自动增量值来显示，以便我可以清除数据库中的大多数行？使用 LAST_INSERT_ID() 似乎不起作用。

- James Simpson

---

詹姆斯，你尝试过其他解决方案吗？它们似乎更好一些... - Dan

如果您从未删除过记录，那么auto_increment值将是正确的，但否则它会偏移表创建以来被删除的记录数量。 - Reactgular

只是为了澄清差异，获取行数会告诉您现在有多少行。如果您希望计数包括曾经存在但已被删除的行，则确实需要使用auto_increment（尽管这也将包括仅在已回滚而不是已提交的事务中出现的行）。 - octern

或者如果有人硬编码了一个比当前插入的auto_increment更高的值，你也会跳过一些。我认为auto_increment不是一种真正可靠的计数方式。 - Nanne

Answer 1

以下是查找表的下一个AUTO_INCREMENT值的最高效方法。即使在包含数百万个表的数据库上，这也非常快速，因为它不需要查询可能很大的information_schema数据库。

mysql> SHOW TABLE STATUS LIKE 'table_name';
// Look for the Auto_increment column

然而，如果你必须在查询中检索此值，则必须前往information_schema数据库。

SELECT `AUTO_INCREMENT`
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_SCHEMA = 'DatabaseName'
AND    TABLE_NAME   = 'TableName';

- Graham Swan

---

这个功能与广告宣传的一样有效（与LAST_INSERT_ID答案相比），而且非常快。 - Timm

这应该被选为这个问题的正确答案。这种方法更加高效，不需要深入挖掘information_schema.tables，这可以在共享的MySQL服务器上造成负担。 @thinkswan提供了一个很好且简单的解决方案。 - Austin S.

这个问题不是在寻求一个PHP解决方案，而是在寻找一个MySQL解决方案，对吗？ - g .

这并没有回答问题，它不是MySQL语法代码。你需要一个PHP预处理器，例如你不能在DML脚本中使用它。 - Mark McKenna

你为什么觉得需要用PHP代码来回答问题呢？这个问题是关于SQL的。 - Francisco Zarabozo

Answer 2

如果只是想获取记录（行）的数量，我建议使用：

SELECT TABLE_ROWS
FROM information_schema.tables 
WHERE table_name='the_table_you_want' -- Can end here if only 1 DB 
  AND table_schema = DATABASE();      -- See comment below if > 1 DB

（至少对于MySQL）改用它。

- James Skidmore

---

对于大型数据库来说，这是一个糟糕的解决方案。由于它必须搜索整个 information_scheme.tables 表，因此仅这一个查询可能需要数分钟才能完成。对于拥有数千个客户运行数据库的网络主机而言，该单个表变得非常庞大。 - Graham Swan

你可能想要添加 AND table_schema=DATABASE()，因为多个数据库可以拥有相同名称的表。 - Alex Jasmin

@thinkswan，我敢打赌，通过添加table_schema限制，这个查询的性能会大幅提升。 - Mark McKenna

这将返回自增ID的值还是表中的行数？我认为应该是 SELECT AUTO_INCREMENT FROM...。 - jjmontes

@jjmontes 是正确的 - TABLE_ROWS 和 AUTO_INCREMENT 是不同的统计数据，而且 OP 的问题是误导性的。TABLE_ROWS 是指表中的行数，AUTO_INCREMENT 是自动增量计数器的值。我可以有一个只有一行的表，但 AUTO_INCREMENT 设置为 1,000,000。（如果您从表中删除了前 999,999 行，这很容易发生！）AUTO_INCREMENT 值实际上是用户可配置的，因此永远不要假设行数和自动增量是相同的东西。 TABLE_ROWS !== AUTO_INCREMENT - methai

如果有条目被删除，那么这个查询就不准确了，不是吗？ - George

Answer 3

试试这个

执行这个SQL语句：

SHOW TABLE STATUS LIKE '<tablename>'

并获取字段Auto_increment的值

- Abhijit

Answer 4

我不确定为什么没有人提出以下建议。这将只使用SQL（无需使用PHP的mysql_fetch_array）获取自动增量值：

SELECT AUTO_INCREMENT FROM information_schema.tables WHERE TABLE_NAME = 'table'

- doitlikejustin

---

这绝对是最好的答案，但有一个注意事项。我建议进行编辑，但对于那些在此期间看到此内容的人： 您还应该包括AND TABLE_SCHEMA ='database'，以便针对包含多个具有相同表名的数据库的服务器进行操作。 - matt

Answer 5

如果您直接通过编写select查询来获取最大数字，那么您的查询可能会给出错误的值。 例如，如果您的表有5条记录，则您的增量ID将为6，如果我删除第5条记录，则您的表将具有4条记录，最大ID为4，在这种情况下，您将获得5作为下一个增量ID。 相反，您可以从mysql定义中获取信息。通过在php中编写以下代码：

<?
$tablename      = "tablename";
$next_increment     = 0;
$qShowStatus        = "SHOW TABLE STATUS LIKE '$tablename'";
$qShowStatusResult  = mysql_query($qShowStatus) or die ( "Query failed: " . mysql_error() . "<br/>" . $qShowStatus );

$row = mysql_fetch_assoc($qShowStatusResult);
$next_increment = $row['Auto_increment'];

echo "next increment number: [$next_increment]";
?>

- Pankaj Khairnar

Answer 6

如果您没有“显示状态”的权限， 最好的选择是创建两个触发器和一个新表，用于保存您的十亿记录表的行数。

示例：

TableA >> 十亿条记录 TableB >> 1列和1行

每当在TableA上有插入查询（InsertTrigger）时，将TableB中的行值增加1 每当在TableA上有删除查询（DeleteTrigger）时，将TableB中的行值减少1

- Sateesh D

Answer 7

SELECT id FROM table ORDER BY id DESC LIMIT 1 可以返回最大的id而不是自动递增的id。在某些情况下，两者是不同的。

- maniclorn

Answer 8

除了information_schema建议之外，这个：

SELECT id FROM table ORDER BY id DESC LIMIT 1

如果在id字段上有索引（我相信使用auto_increment必须是这种情况），那么应该也会非常快。

- Evert

---

你还应该在最后添加一个LIMIT 1，这样它就不会为了那个而检索每个单一的id。 - Sasha Chedygov

这并不一定会给你最后的自增值，因为你可以插入/更新特定值到自增列中。 - Jonathan Fingland

实际上，使用 SELECT MAX(id) FROM table 可以减少内存使用。 - Leonid Shevtsov

如果答案无效，我认为应该将其删除。 - Seb

只有在没有进行任何更改的情况下，这才是真正有效的——即没有删除。 - fijiaaron

Answer 9

这些答案似乎都不完全正确。我尝试了它们所有。这是我的结果。

Sending query: SELECT count(*) FROM daximation
91
Sending query: SELECT Auto_increment FROM information_schema.tables WHERE table_name='daximation'
96
Sending query: SHOW TABLE STATUS LIKE 'daximation'
98
Sending query: SELECT id FROM daximation ORDER BY id DESC LIMIT 1
97

这是截图： https://www.screencast.com/t/s8c3trYU

以下是我的PHP代码：

$query = "SELECT count(*) FROM daximation"; 
$result = sendquery($query);
$row = mysqli_fetch_row($result);
debugprint( $row[0]);

$query = "SELECT Auto_increment FROM information_schema.tables WHERE table_name='daximation'"; 
$result = sendquery($query);
$row = mysqli_fetch_row($result);
debugprint( $row[0]);

$query = "SHOW TABLE STATUS LIKE 'daximation'"; 
$result = sendquery($query);
$row = mysqli_fetch_row($result);
debugprint( $row[10]);

$query = "SELECT id FROM daximation ORDER BY id DESC LIMIT 1"; 
$result = sendquery($query);
$row = mysqli_fetch_row($result);
debugprint( $row[0]);

- john k

Answer 10

控制器

SomeNameModel::_getNextID($this->$table)

模型

class SomeNameModel extends CI_Model{

private static $db;

function __construct(){
  parent::__construct();
  self::$db-> &get_instance()->db;
}


function _getNextID($table){
  return self::$db->query("SHOW TABLE STATUS LIKE '".$table."' ")->row()->Auto_increment;
}

... other stuff code

}

- Johnny X. Lemonade