MySQL 数据库中所有行的准确计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10912693/
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
Exact count of all rows in MySQL database
提问by Connor Deckers
I'm currently using the script
我目前正在使用脚本
SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'Tables';
However, it's not accurate, because the engine used by my MySQL tables is InnoDB (I only realised this could be an issue now, be these databases have existed for a while).
但是，它不准确，因为我的 MySQL 表使用的引擎是 InnoDB（我现在才意识到这可能是一个问题，因为这些数据库已经存在一段时间了）。
Is there any way to get an exact count of every row in every table of a database with MySQL?
有什么方法可以使用 MySQL 获得数据库每个表中每一行的精确计数？
Cheers.
干杯。
回答by jkrcma
I think the only accurate (and slower) way is to do for every single table:
我认为唯一准确（且较慢）的方法是对每张表都做：
SELECT COUNT(*) FROM Table
回答by hbere
This SQL statement will generate a union query that can complete the task in one step:
这个 SQL 语句会生成一个联合查询，可以一步完成任务：
SELECT CONCAT('SELECT \'', table_name ,'\' as tbl, (SELECT COUNT(*) FROM ', table_name, ') AS ct UNION')
FROM information_schema.tables
WHERE table_schema = 'your_database';
After running the SQL statement, just paste the output into a text editor, delete the word "UNION" from the end of the output, and you will have the union query.
运行 SQL 语句后，只需将输出粘贴到文本编辑器中，从输出末尾删除“UNION”一词，您将拥有联合查询。
Beware that running the union query could take a lot of resources depending on your database size, load, and hardware, so you may not want to run it all at once.
请注意，根据您的数据库大小、负载和硬件，运行联合查询可能会占用大量资源，因此您可能不想一次全部运行。
回答by FurryMachine
I was having the same problem which led me to this question.
我遇到了同样的问题，这让我想到了这个问题。
Thanks to computerGuy12345's answer, I came up with a stored procedure that does this without having to copy-paste. As he said, beware that this will scan each table and might take a while.
多亏了computerGuy12345的回答，我想出了一个无需复制粘贴就可以做到这一点的存储过程。正如他所说，请注意这将扫描每个表并且可能需要一段时间。
DELIMITER $$
CREATE PROCEDURE `all_tables_rowcount`(databaseName VARCHAR(250))
BEGIN
DECLARE p_done INT DEFAULT FALSE;
DECLARE p_queryString varchar(250) ;
DECLARE p_cur CURSOR FOR SELECT queryString FROM tmp.tableCountQueries;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET p_done = TRUE;
DROP TEMPORARY TABLE IF EXISTS tmp.tableCountQueries;
DROP TEMPORARY TABLE IF EXISTS tmp.tableCounts;
CREATE TEMPORARY TABLE tmp.tableCounts(TableName varchar(250), RowCount BIGINT) ;
CREATE TEMPORARY TABLE tmp.tableCountQueries(queryString varchar(250)) AS
(
SELECT CONCAT(
'INSERT INTO tmp.tableCounts(TableName, RowCount) SELECT "',
table_name,
'", COUNT(*) AS RowCount FROM ',
table_schema,
'.',
table_name
) AS queryString
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = databaseName
) ;
OPEN p_cur;
read_loop: LOOP
FETCH p_cur INTO p_queryString;
IF p_done THEN
LEAVE read_loop;
END IF;
SET @queryString = p_queryString ;
PREPARE rowcountTable FROM @queryString;
EXECUTE rowcountTable ;
END LOOP;
SELECT * FROM tmp.tableCounts ;
END
回答by Venaikat
Select Sum(column_Name) from table,can not give the exact count of rows in a table , it wil give total row count+1 , wil give the next data inserting row also. and one more thing is, in sum(Column_Name)the column_Name should be int ,if it is varchar or char sum function wont work. soo the best thing is use
Select Count(*) from tableto get exact number of rows in a table.
Select Sum(column_Name) from table, 不能给出表中行的确切计数，它会给出总行数+1，也会给出下一个数据插入行。还有一件事是，在sum(Column_Name)column_Name 中应该是 int ，如果它是 varchar 或 char sum 函数将不起作用。所以最好的办法是
Select Count(*) from table用来获取表中的确切行数。
ThnQ,
TnQ，
Venkat
文卡特
回答by saiprathapreddy
select table_name,table_rows from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='database name';
table_namemeans number of tables which is present in "data base "
table_name表示“数据库”中存在的表数
table_rowsmeans number rows in each column
table_rows表示每列中的行数
table_schemais the database name. from which data base you are looking for the counts
table_schema是数据库名称。您要从哪个数据库中查找计数
回答by Rinzler
this worked for me
这对我有用
<?php
mysql_connect("localhost", "root", "pass") or die(mysql_error());
mysql_select_db("csl") or die(mysql_error());
$dtb=mysql_query("SHOW TABLES") or die (mysql_error());
$jmltbl=0; $jml_record=0; $jml_record=0; $total =0;
while($row=mysql_fetch_array($dtb))
{
$sql=mysql_query("select * from $row[0]");
$jml_record=mysql_num_rows($sql);
$total += $jml_record; // this counts all the rows
$jmltbl++; $jml_record+=$jml_record;
}
// echo"--------$jmltbl Tables, $jml_record records."; // will print count of indivual table
echo $total; // this will print you count of all rows in MySQL database
?>
i have tested this code and its works
我已经测试了这段代码及其工作
output 1145 for me
为我输出 1145
