mysql查看数据库、表大小、磁盘空间占用以及一些常用mysql代码，持续更新ing

分享数据库查用的非增删改查类实用代码、实战中经常用到！持续更新ing

查看数据库整体大小代码

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)'
from information_schema.tables
where table_schema='test_novel'
order by table_rows desc, index_length desc;

可以查看数据库每个表占用磁盘大小
其中tabl_rows可以替代分页代码的count

show table status where Name = "table_name"

如果行数不对可以重新分析表

ANALYZE TABLE table_name;

查看表详情

DESCRIBE table_name;

查看表ddl详情

Show create table table_name;

查看数据库进程

show processlist 

解析sql,最常用的解析mysql是否命中索引

explain sql

查看数据库mysql最大连接数量

show global status like ‘Max_used_connections';
show variables like "%max_connections%”;

修改最大连接数
常用的修改最大连接数的两种方式如下:
第一种：命令行修改最大连接数(max_connections)，设置最大连接数为1000。

mysql> set global max_connections = 1000;

这种方式有个问题，就是设置的最大连接数只在 MySQL 当前服务进程有效，一旦MySQL重启，又会恢复到初始状态。因为MySQL启动后的初始化工作是从其配置文件中读取数据的，而这种方式没有对其配置文件做更改。

第二种：通过修改配置文件来修改MySQL最大连接数(max_connections)。
进入MySQL安装目录，打开MySQL配置文件 my.ini 或 my.cnf查找 max_connections=100，修改为max_connections=1000，重启MySQL服务即可。

查看自增id sql,如果有某条记录插入失败回滚了需要查看自增id是否有增长

SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name';

cli进入mysql

$ mysql -h 192.168.0.100 -P 3307 -u user -p'passwod'

修改Mysql数据库账号密码

cli进入mysql控制界面
use mysql # 连接权限数据库
update user set password=password('123456') where user='root' and host='localhost'; # 改密码
flush privileges; # 刷新权限

忘记数据库密码重置数据库密码

1. 关闭MySQL服务
2. 打开命令窗口，利用cd命令转到mysql的bin目录：
3. 输入mysqld --skip-grant-tables 回车。--skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。
4. 再开一个DOS窗口（刚才那个DOS窗口已经不能动了），转到mysql的bin目录。
5. 输入mysql回车，如果上面修改成功，将直接出现 mysql> 这样的提示符。
mysql> use mysql # 连接权限数据库
mysql> update user set password=password('123456') where user='root' and host='localhost'; # 改密码
mysql> flush privileges; # 刷新权限
8.exit或者ctrl+c退出，进行重新登陆

– 创建和原表结构一样的临时表
CREATE TABLE TEMP_TABLE LIKE MY_TABLE;

– 将正常数据复制到临时表
INSERT INTO TEMP_TABLE SELECT * FROM MY_TABLE WHERE

– 删除表
DROP TABLE MY_TABLE;

– 重命名表
RENAME TABLE TEMP_TABLE TO MY_TABLE;

– 清空原表数据，但不删除表
TRUNCATE TABLE MY_TABLE;

–千万表加字段无锁变更
alter table orders
add my_column decimal(5, 2) default 0 not null after shipping, ALGORITHM=INSTANT;

–严格模式变更
select @@global.sql_mode
set sql_mode=“NO_ENGINE_SUBSTITUTION”;
其中NO_ENGINE_SUBSTITUTION是无引擎自动替代，创建表的时候没有引擎自动替代inodb

–查看所有系统变量
show global variables;