前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >【MySQL 系列】冷门 SQL 整理

【MySQL 系列】冷门 SQL 整理

作者头像
框架师
发布于 2022-09-02 11:45:58
4070
发布于 2022-09-02 11:45:58
举报
文章被收录于专栏：墨白的Java基地墨白的Java基地

查看当前链接所有业务数据库

代码语言：javascript
复制
SELECT
	schema_name AS db_name 
FROM
	information_schema.schemata 
WHERE
	schema_name NOT IN ( 'information_schema', 'sys', 'mysql', 'performance_schema' );

PS: information_schema 中的数据默认不是实时的数据，如果需要实时的数据，需要执行下面命令：

代码语言：javascript
复制
SET GLOBAL information_schema_stats_expiry = 0;

效果如下：

代码语言：javascript
复制
+-----------------+
| db_name         |
+-----------------+
| seata           |
| test            |
+-----------------+

批量操作某个前缀的表

代码语言：javascript
复制
SELECT
	concat('select count(*) from DB_NAME.', table_name, ';' ) 
FROM
	information_schema.TABLES 
WHERE
	table_schema = 'DB_NAME' 
	AND table_name LIKE 'a_%';

效果如下：

代码语言：javascript
复制
+------------------------------------------------------+
| concat('select count(*) from test.',table_name,';')  |
+------------------------------------------------------+
| select count(*) from test.test_tables1;              |
| select count(*) from test.test_tables2;         	   |
+------------------------------------------------------+

查找业务库中的非 InnoDB 的表

代码语言：javascript
复制
SELECT
	table_schema,
	table_name,
ENGINE 
FROM
	information_schema.TABLES 
WHERE
	table_schema NOT IN ( 'information_schema', 'sys', 'mysql', 'performance_schema' ) 
	AND ENGINE <> 'InnoDB';

效果如下：

代码语言：javascript
复制
+--------------+--------------------------------------------+--------+
| table_schema | table_name                                 | ENGINE |
+--------------+--------------------------------------------+--------+
| test         | test_table                                 | MyISAM |
+--------------+--------------------------------------------+--------+

批量构造修改存储引擎的语句

代码语言：javascript
复制
SELECT DISTINCT
	concat( 'alter table', table_schema, '.', table_name, 'engine=innodb', ';' ) 
FROM
	information_schema.TABLES 
WHERE
	(
	ENGINE <> 'innodb' 
	AND table_schema NOT IN ( 'information_schema', 'sys', 'mysql', 'performance_schema' ));

效果如下：

代码语言：javascript
复制
+--------------------------------------------------------------------------------+
| concat( 'alter table', table_schema, '.', table_name, 'engine=innodb', ';' ) |
+--------------------------------------------------------------------------------+
| alter table test.tables engine=innodb;            			     |
+--------------------------------------------------------------------------------+

查看每张表数据量，并按数据量排序

代码语言：javascript
复制
SELECT
	table_schema,
	table_name,
	table_rows 
FROM
	information_schema.TABLES 
WHERE
	table_schema NOT IN ( 'information_schema', 'sys', 'mysql', 'performance_schema' ) 
ORDER BY
	table_rows DESC;

效果如下：

代码语言：javascript
复制
+-----------------+--------------------------------------------+------------+
| table_schema    | table_name                                 | table_rows |
+-----------------+--------------------------------------------+------------+
| test            | test.tables1                  			   |    2484153 |
| test            | test.tables2				               |    2228574 |
+-----------------+--------------------------------------------+------------+

某个库所有表的字段详情

代码语言：javascript
复制
SELECT
	table_schema,
	table_name,
	column_name,
	column_type,
	collation_name 
FROM
	information_schema.COLUMNS 
WHERE
	table_schema = 'DB_NAME';

效果如下：

代码语言：javascript
复制
+--------------+--------------------+---------------------+--------------+----------------+
| table_schema | table_name         | column_name         | column_type  | collation_name |
+--------------+--------------------+---------------------+--------------+----------------+
| test         | april_project_list | id                  | bigint(11)   | NULL           |
| test         | april_project_list | project_name        | varchar(50)  | utf8_bin       |
| test         | april_project_list | description         | varchar(255) | utf8_bin       |
| test         | april_project_list | project_description | varchar(255) | utf8_bin       |
| test         | april_project_list | project_start_time  | datetime     | NULL           |
| test         | april_project_list | project_end_time    | datetime     | NULL           |
| test         | april_project_list | company             | varchar(50)  | utf8_bin       |
| test         | april_project_list | technology          | varchar(255) | utf8_bin       |
| test         | april_project_list | post                | char(1)      | utf8_bin       |
+--------------+--------------------+---------------------+--------------+----------------+

查看某张表的具体信息

代码语言：javascript
复制
SELECT
	* 
FROM
	information_schema.TABLES 
WHERE
	table_schema = 'DB_NAME' 
	AND table_name = 'TABLE_NAME';

效果如下：

代码语言：javascript
复制
+---------------+--------------+--------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME         | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+--------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| def           | test         | april_project_list | BASE TABLE | InnoDB |      10 | Dynamic    |          0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2022-07-26 04:01:39 | NULL        | NULL       | utf8_bin        | NULL     |                |               |
+---------------+--------------+--------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+

查看 MySQL 所有用户

代码语言：javascript
复制
SELECT DISTINCT
	concat( "'", USER, '''@''', HOST, "';" ) AS USER 
FROM
	mysql.USER;

效果如下：

代码语言：javascript
复制
+------------------------------+
| USER                         |
+------------------------------+
| 'root'@'%';                  |
| 'root'@'localhost';          |
+------------------------------+

查看某个库所有表的碎片率

代码语言：javascript
复制
SELECT
	table_name,
	data_free / ( data_free + data_length + index_length ) AS value,
	data_free,
	data_length,
	index_length 
FROM
	information_schema.TABLES 
WHERE
	table_schema = 'DB_NAME' 
ORDER BY
	data_length DESC;

效果如下：

代码语言：javascript
复制
+--------------------+--------+-----------+-------------+--------------+
| table_name         | aaa    | data_free | data_length | index_length |
+--------------------+--------+-----------+-------------+--------------+
| april_project_list | 0.0000 |         0 |       16384 |            0 |
+--------------------+--------+-----------+-------------+--------------+

查询指定数据库所占空间大小

代码语言：javascript
复制
SELECT concat( round( sum( data_length )/( 1024 * 1024 ), 2 ) + round( sum( index_length )/( 1024 * 1024 ),
		2 
		),
	'MB' 
) AS 'DB Size' 
FROM
TABLES 
WHERE
	table_schema = 'DB_NAME';

效果如下：

代码语言：javascript
复制
+---------+
| DB Size |
+---------+
| 0.02MB  |
+---------+

查看索引所占的空间大小

代码语言：javascript
复制
SELECT concat( round( sum( index_length )/( 1024 * 1024 ),
		2 
		),
	'MB' 
) AS 'DB Size' 
FROM
TABLES 
WHERE
	table_schema = 'test';

效果如下：

代码语言：javascript
复制
+---------+
| DB Size |
+---------+
| 0.02MB  |
+---------+

查询数据库最大链接数

代码语言：javascript
复制
SHOW VARIABLES LIKE '%max_connections%';

效果如下：

代码语言：javascript
复制
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

查看数据库当前连接数，并发数

代码语言：javascript
复制
SHOW STATUS LIKE 'Threads%';

效果如下：

代码语言：javascript
复制
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 5     |
| Threads_connected | 50    |
| Threads_created   | 164   |
| Threads_running   | 1     |
+-------------------+-------+

Threads_cached: 代表当前此时此刻线程缓存中有多少空闲线程。 Threads_connected: 代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。 Threads_created: 代表从最近一次服务启动，已创建线程的数量。 Threads_running: 代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于 sleep 状态，这里相对应的线程也是 sleep 状态。

查看数据文件存放路径

代码语言：javascript
复制
SHOW VARIABLES LIKE '%datadir%';

效果如下：

代码语言：javascript
复制
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2022-07-28，如有侵权请联系 cloudcommunity@tencent.com 删除
数据库
sql

本文分享自 作者个人站点/博客 前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ，欢迎热爱写作的你一起参与！

数据库
sql
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
目录
  • 查看当前链接所有业务数据库
  • 批量操作某个前缀的表
  • 查找业务库中的非 InnoDB 的表
  • 批量构造修改存储引擎的语句
  • 查看每张表数据量，并按数据量排序
  • 某个库所有表的字段详情
  • 查看某张表的具体信息
  • 查看 MySQL 所有用户
  • 查看某个库所有表的碎片率
  • 查询指定数据库所占空间大小
  • 查看索引所占的空间大小
  • 查询数据库最大链接数
  • 查看数据库当前连接数，并发数
  • 查看数据文件存放路径
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建，轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量，更专注于业务发展，让企业一站式享受数据上云及分布式架构的技术红利！
产品介绍
精选特惠 用云无忧
领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

Copyright © 2013 - 2024 Tencent Cloud.

All Rights Reserved. 腾讯云 版权所有

登录 后参与评论