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|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|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|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|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|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.

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|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|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>

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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