blocks|key|1543204|text|如果仅仅是为了获取记录(行)的数量，我建议使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1543205|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|code-block|syntax|javascript|1543206|(至少对于MySQL)。|1543207|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

If it's only about getting the number of records (rows) I'd suggest using:

<pre><code>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 &gt; 1 DB
</code></pre>

(at least for MySQL) instead.

blocks|key|1543357|text|下面是查找表的下一个AUTO_INCREMENT值的最有效方法。即使在包含数百万个表的数据库上，这也很快，因为它不需要查询潜在的大型information_schema数据库。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1543358|mysql>+SHOW+TABLE+STATUS+LIKE+'table_name';
//+Look+for+the+Auto_increment+column|code-block|syntax|javascript|1543359|但是，如果必须在查询中检索此值，则必须转到information_schema数据库。|1543360|SELECT+`AUTO_INCREMENT`
FROM+++INFORMATION_SCHEMA.TABLES
WHERE++TABLE_SCHEMA+=+'DatabaseName'
AND++++TABLE_NAME+++=+'TableName';|1543361|entityMap^0|A|E|1U|I|0|0|L|I|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|10|8|@]|D|@]|E|$]]]|P|$]]

Following is the most performant way to find the next <code>AUTO_INCREMENT</code> value for a table. This is quick even on databases housing millions of tables, because it does not require querying the potentially large <code>information_schema</code> database.

<pre><code>mysql&gt; SHOW TABLE STATUS LIKE 'table_name';
// Look for the Auto_increment column
</code></pre>

However, if you must retrieve this value in a query, then to the <code>information_schema</code> database you must go.

<pre><code>SELECT `AUTO_INCREMENT`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DatabaseName'
AND TABLE_NAME = 'TableName';
</code></pre>

blocks|key|1543306|text|尝尝这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1543307|执行以下SQL：|1543308|SHOW+TABLE+STATUS+LIKE+'<tablename>'|code-block|syntax|javascript|1543309|并获取字段Auto_increment的值|offset|length|style|CODE|1543310|entityMap^0|0|0|0|5|E|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@$K|U|L|V|M|N]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

try this

Execute this SQL:

<pre><code>SHOW TABLE STATUS LIKE '&lt;tablename&gt;'
</code></pre>

and fetch the value of the field <code>Auto_increment</code>

blocks|key|1543777|text|我不确定为什么没有人提出以下建议。这将只使用SQL获得PHP值(不需要使用auto_increment的mysql_fetch_array)：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1543778|SELECT+AUTO_INCREMENT+FROM+information_schema.tables+WHERE+TABLE_NAME+=+'table'|code-block|syntax|javascript|1543779|entityMap^0|1G|H|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

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 <code>mysql_fetch_array</code>):

<pre><code>SELECT AUTO_INCREMENT FROM information_schema.tables WHERE TABLE_NAME = 'table'
</code></pre>

blocks|key|1543437|text|如果您通过编写select查询直接获取get+max+number，那么您的查询可能会给出错误的值。例如，如果你的表有5条记录，那么你的增量id将是6，如果我删除5号记录，你的表有4条记录，最大id是4，在这种情况下，你将得到5作为下一个增量id。你可以从mysql定义本身获取信息。通过在php中编写以下代码|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1543438|<?
$tablename++++++=+"tablename";
$next_increment+++++=+0;
$qShowStatus++++++++=+"SHOW+TABLE+STATUS+LIKE+'$tablename'";
$qShowStatusResult++=+mysql_query($qShowStatus)+or+die+(+"Query+failed:+"+.+mysql_error()+.+" "+.+$qShowStatus+);

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

echo+"next+increment+number:+[$next_increment]";
?>|code-block|syntax|javascript|1543439|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

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

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

echo "next increment number: [$next_increment]";
?&gt;
</code></pre>

blocks|key|1328259|text|SELECT+id+FROM+table+ORDER+BY+id+DESC+LIMIT+1可以返回最大id，而不是自动递增id。两者在某些情况下是不同的|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1328260|entityMap^0|0|19|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

<code>SELECT id FROM table ORDER BY id DESC LIMIT 1</code> can returns max id not auto increment id. both are different in some conditions

blocks|key|1543604|text|如果你没有“显示状态”的特权，那么最好的选择是，创建两个触发器和一个新的表，它保持你的十亿记录表的行数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1543605|示例：|1543606|TableA+>>十亿条记录|1543607|TableB+>>+1列和1行|1543608|只要对TableA(InsertTrigger)执行insert查询，就会将行值增加1+TableB|1543609|只要对TableA(DeleteTrigger)执行delete查询，TableB中行的值就会减少1|1543610|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|P|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|Q|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|M|$]]

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

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

blocks|key|1328021|text|在information_schema建议的旁边，这是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1328022|SELECT+id+FROM+table+ORDER+BY+id+DESC+LIMIT+1|code-block|syntax|javascript|1328023|也应该非常快，只要id字段有索引(我相信auto_increment也是这样)|1328024|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Next to the information_schema suggestion, this:

<pre><code>SELECT id FROM table ORDER BY id DESC LIMIT 1
</code></pre>

should also be very fast, provided there's an index on the id field (which I believe must be the case with auto_increment)

blocks|key|1543716|text|$next_id+=+mysql_fetch_assoc(mysql_query("SELECT+MAX(id)+FROM+table"));
$next_id['MAX(id)'];+//+next+auto+incr+id|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1543717|希望对您有所帮助:)|unstyled|1543718|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>$next_id = mysql_fetch_assoc(mysql_query("SELECT MAX(id) FROM table"));
$next_id['MAX(id)']; // next auto incr id
</code></pre>

hope it helpful :)

blocks|key|1543820|text|这些答案似乎没有一个是完全正确的。我都试过了。这是我的结果。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1543821|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|code-block|syntax|javascript|1543822|这是屏幕截图：https://www.screencast.com/t/s8c3trYU|offset|length|1543823|下面是我的PHP代码：|1543824|$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]);|1543825|entityMap|0|LINK|mutability|MUTABLE|url|https://www.screencast.com/t/s8c3trYU^0|0|0|7|11|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@$I|Z|J|10|1|11]]|A|$]]|$1|K|3|L|5|6|7|12|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

None of these answers seem to be quite right. I tried them all. Here are my results. 

<pre><code>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
</code></pre>

here's the screenshot:
<a href="https://www.screencast.com/t/s8c3trYU" rel="nofollow noreferrer">https://www.screencast.com/t/s8c3trYU</a>

Here is my PHP code:

<pre><code>$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]);
</code></pre>

blocks|key|1328424|text|难道您不能只在一个单独的表中创建一条记录，或者使用名为Users的列创建一条记录，并使用用户注册时最后插入的id来更新它吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1328425|然后，您只需使用一个简单的查询来检查此字段。|1328426|它可能很粗糙，但它会完美地工作。|1328427|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Couldn't you just create a record in a separate table or whatever with a column called Users and UPDATE it with the last inserted id on User Registration?

Then you would just check this field with a simple query.

It might be rough but it would work perfectly.

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 <code>LAST_INSERT_ID()</code> doesn't seem to work.

Most efficient way to get table row count

数据库

MySQL

我目前有一个超过600万行的数据库，而且还在不断增长。我目前正在从表中选择COUNT(id)；以便向我的用户显示数字，但是数据库越来越大，除了能够显示数字之外，我不需要存储所有这些行。有没有一种方法可以选择要显示的auto_increment值，以便清除数据库中的大多数行？使用LAST_INSERT_ID()似乎行不通。

问获取表行计数的最有效方法
EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问获取表行计数的最有效方法EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问获取表行计数的最有效方法
EN