【MySQL】常用语句

一. 修改自增长字段值

  1. 自增长起始值修改只能比原来更大,不能更小
  2. alter table testid auto_increment=100;

二. 事务

1. 隔离级别

查看事务隔离级别:show variables like 'transaction_isolation';
默认值:('transaction_isolation', 'REPEATABLE-READ');


三. 数据统计

1. 数据库表信息

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)',
AUTO_INCREMENT as '自增长初始值',
ENGINE,
UPDATE_TIME,
TABLE_COMMENT
from information_schema.tables
where table_schema='库名'
order by table_rows desc, index_length desc;

SELECT count(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA='库名';

  1. table_schema: 记录数据库名
  2. table_name: 记录数据表名
  3. engine : 存储引擎
  4. table_rows: 关于表的粗略行估计
  5. data_length : 记录表的大小(单位字节)
  6. index_length : 记录表的索引的大小
  7. row_format: 可以查看数据表是否压缩过

2. 列信息

SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '数据库名'
ORDER BY TABLE_NAME DESC ;


3. 索引信息

SELECT * FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = '数据库名' and table_name='表名'
ORDER BY INDEX_NAME DESC,SEQ_IN_INDEX ;


4. 事务

  1. 执行时间超过1秒的事务语句
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started)) > 1

posted @ 2021-10-15 10:38    阅读(14)  评论(0编辑  收藏  举报