MySQL 获取表行数的最有效方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/933565/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Most efficient way to get table row count
提问by James Simpson
I currently have a database with over 6 million rows and growing. I currently do SELECT COUNT(id) FROM table; in order to display the number to my users, but the database is getting large and I have no need to store all of those rows except to be able to show the number. Is there a way to select the auto_increment value to display so that I can clear out most of the rows in the database? Using LAST_INSERT_ID()doesn't seem to work.
我目前有一个超过 600 万行并且还在增长的数据库。我目前做 SELECT COUNT(id) FROM table; 为了向我的用户显示数字，但数据库越来越大，除了能够显示数字外，我不需要存储所有这些行。有没有办法选择要显示的 auto_increment 值，以便我可以清除数据库中的大部分行？使用LAST_INSERT_ID()似乎不起作用。
采纳答案by James Skidmore
If it's only about getting the number of records (rows) I'd suggest using:
如果只是获取记录数（行），我建议使用：
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
(at least for MySQL) instead.
（至少对于 MySQL）代替。
回答by Graham Swan
Following is the most performant way to find the next AUTO_INCREMENTvalue for a table. This is quick even on databases housing millions of tables, because it does not require querying the potentially large information_schemadatabase.
以下是查找AUTO_INCREMENT表的下一个值的最高效方法。即使在包含数百万表的数据库上，这也很快，因为它不需要查询潜在的大型information_schema数据库。
mysql> SHOW TABLE STATUS LIKE 'table_name';
// Look for the Auto_increment column
However, if you must retrieve this value in a query, then to the information_schemadatabase you must go.
但是，如果必须在查询中检索此值，则information_schema必须转到数据库。
SELECT `AUTO_INCREMENT`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DatabaseName'
AND TABLE_NAME = 'TableName';
回答by James Skidmore
try this
尝试这个
Execute this SQL:
执行此 SQL：
SHOW TABLE STATUS LIKE '<tablename>'
and fetch the value of the field Auto_increment
并获取该字段的值 Auto_increment
回答by doitlikejustin
I'm not sure why no one has suggested the following. This will get the auto_increment value using just SQL (no need for using PHP's mysql_fetch_array):
我不知道为什么没有人提出以下建议。这将仅使用 SQL 获取 auto_increment 值（无需使用 PHP mysql_fetch_array）：
SELECT AUTO_INCREMENT FROM information_schema.tables WHERE TABLE_NAME = 'table'
回答by Pankaj Khairnar
if you directly get get max number by writing select query then there may chance that your query will give wrong value. e.g. if your table has 5 records so your increment id will be 6 and if I delete record no 5 the your table has 4 records with max id is 4 in this case you will get 5 as next increment id. insted to that you can get info from mysql defination itself. by writing following code in php
如果您通过编写选择查询直接获得最大数量，那么您的查询可能会给出错误的值。例如，如果您的表有 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]";
?>
回答by maniclorn
SELECT id FROM table ORDER BY id DESC LIMIT 1can returns max id not auto increment id. both are different in some conditions
SELECT id FROM table ORDER BY id DESC LIMIT 1可以返回最大 id 而不是自动递增 id。两者在某些条件下是不同的
回答by Sateesh D
If you do not have privilege for "Show Status" then, The best option is to, create two triggers and a new table which keeps the row count of your billion records table.
如果您没有“显示状态”的权限，那么最好的选择是创建两个触发器和一个新表，以保留十亿记录表的行数。
Example:
例子：
TableA >> Billion Records
TableB >> 1 Column and 1 Row
表A>>十亿条记录表B
>>1列1行
Whenever there is insert query on TableA(InsertTrigger), Increment the row value by 1 TableB
Whenever there is delete query on TableA(DeleteTrigger), Decrement the row value by 1 in TableB
每当对表A（InsertTrigger）进行插入查询时，将表B中的行值增加1
每当对表A（DeleteTrigger）上进行删除查询时，将表B中的行值减1
回答by Evert
Next to the information_schema suggestion, this:
在 information_schema 建议旁边，这个：
SELECT id FROM table ORDER BY id DESC LIMIT 1
should also be very fast, provided there's an index on the id field (which I believe must be the case with auto_increment)
也应该非常快，前提是 id 字段上有一个索引（我相信 auto_increment 必须是这种情况）
回答by 23Pstars
$next_id = mysql_fetch_assoc(mysql_query("SELECT MAX(id) FROM table"));
$next_id['MAX(id)']; // next auto incr id
hope it helpful :)
希望它有帮助:)
回答by Johnny X. Lemonade
Controller
控制器
SomeNameModel::_getNextID($this->$table)
MODEL
模型
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
}
