Summary of methods for Mysql to obtain the maximum value of id, total number of records in the table and other related issues_MySQL

1. Mysql gets the maximum id of the current field

SQL statement:

select max(id) from yourtable;

2. Get the auto_increment value of mysql table

Auto_increment is an attribute in the table. As long as you get the status of the table, you can also get the auto-increment value

SQL statement:

show table status like “表名”;

php code implementation

$get_table_status_sql = "SHOW TABLE STATUS LIKE '表名'";
$result = mysql_query($get_table_status_sql);
$table_status = mysql_fetch_array($result);
echo $table_status['Auto_increment']; // 这个就是自增值

or

select max(id) from testnotnull;

3. Get the total number of records in a table

select count(*) from table;

or

select count(id) from table;

SELECT SQL_CALC_FOUND_ROWS * FROM table_name;
SELECT FOUND_ROWS();

myisam When downloading count(*)primary key, you need to add a condition. This condition is a type field and the index is invalid

It is very fast without adding any conditions, but it is two orders of magnitude slower after adding it

Using the SHOW TABLE STATUS statement is the most efficient method

Format

SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

Example:

SHOW TABLE STATUS FROM cpdlt LIKE 'lehecai_1202';

Summary

The above is all the content of related issues such as how to get the number of records of a table, get the maximum id of a table, and get the auto_increment value of a table. I hope it will be helpful to everyone's study or work. If you have If you have any questions, you can leave a message to communicate.