【mysql】query踩过的坑
版本mysql8.0
- 关于自增字段auto_increment
- 怎么查字段自增初始值
- select auto_increment from information.tables 查询初始值。对于已存在的某个表tabXXX 用 show create table tabXXX()查询建表语句 ,结果 create table xxx() engine=Innodb auto_increment=n,这里查到的建表语句展现的auto_increment=n 展示的是下一个可插入的设置了自增的字段的值,比如当前表中记录主键自增id字段已到200,那么 show creata table table_xxx 结果中 anto_increment=201。需要注意。
- 怎么查字段自增步长
-
show variables like '%auto_increment_offset' show variables like '%auto_increment_increment'
-
- 查数据表精确行数
select count(*) from tabxxx.(√)
select table_rows from information_schema.tables where table_name= "tabxxx" 。(×)
当表引擎为innodb时,第二条sql查询到的table_rows字段值是预估的行数,不精确。---官方解释:The number of rows. Some storage engines, such as MyISAM
, store the exact count. For other storage engines, such as InnoDB
, this value is an approximation, and may vary from the actual value by as much as 40% to 50%. In such cases, use SELECT COUNT(*)
to obtain an accurate count.TABLE_ROWS
is NULL
for INFORMATION_SCHEMA
tables.For InnoDB
tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB
table is partitioned.) https://dev.mysql.com/doc/refman/8.0/en/tables-table.html