Mysql中查询当前用户、当前数据库等基础信息

前言

生活中有时会出现灵魂三问:我是谁?我在哪?我在做什么?特别的喝醉酒的第二天,完全不记得昨天发生了什么。而在数据库操作中也会出现这种灵魂拷问,我用的是哪个用户,为什么会没有权限?我操作的是哪个数据库,刚刚不会把线上正式服务器数据删了吧?

上面描述的问题常常出现在切换数据库处理问题的时候,通过一个客户端连接到Mysql数据库服务器,操作数据库1,然后切换再操作数据库2,这时如果中间有人打扰,很容易忘记刚刚操作的是哪个数据库,或者中途处理个其他紧急的事情,回来连操作的用户都忘了,这时就需要一些基础信息的查询命令,帮助你来恢复记忆。

数据库基础信息查询

数据库的基础信息涉及到方方面面,这里只列举几个常用的查询命令,用来回答上面的灵魂拷问,其他命令还有很多,用到了再总结吧。

查询当前操作的用户

1
2
3
4
5
6
7
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.07 sec)

查询当前操作的数据库

1
2
3
4
5
6
7
mysql> select database();
+------------+
| database() |
+------------+
| sqltest2 |
+------------+
1 row in set (0.09 sec)

查询当前数据库端口

1
2
3
4
5
6
7
mysql> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306 |
+---------------+-------+
1 row in set (0.07 sec)

查询当前数据库版本

1
2
3
4
5
6
7
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.21-log |
+------------+
1 row in set (0.06 sec)

数据库结构信息查询

这里的结构我指的是DDL中定义的那些元素,比如表、存储过程等,有一些常用的查询命令,要是一段时间不使用还是会忘记,比如查询一个数据库中的存储过程,每次查询时都要上网搜一下,所以今天总结在一起方便查找。

查询当前数据库中的所有表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show tables;
+--------------------+
| Tables_in_sqltest2 |
+--------------------+
| a |
| b |
| c |
| d |
| m |
| p |
| tb_test |
| tb_with_index |
+--------------------+
14 rows in set (0.11 sec)

查询创建表的sql语句

1
2
3
4
5
6
7
8
9
10
mysql> show create table a;
+-------+----------------------------------------+
| Table | Create Table |
+-------+----------------------------------------+
| a | CREATE TABLE `a` (
`id` int(11) DEFAULT NULL,
`num` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------+
1 row in set (0.11 sec)

查询指定表中的所有字段

1
2
3
4
5
6
7
8
mysql> desc a;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| num | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.12 sec)

查询当前数据库中的所有存储过程

这个命令我得吐槽一下,为什么不能像查询当前数据库的中所有表一样,搞个show procedures;命令,非得通过where子句指定数据库呢,具体的原因还不知道,等我弄明白了再回来补充。

1
2
3
4
5
6
7
8
9
mysql> show procedure status where db='sqltest2';
+----------+-----------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+----------+-----------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| sqltest2 | fill_slow_query_test | PROCEDURE | root@localhost | 2019-03-25 11:14:01 | 2019-03-25 11:14:01 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8_general_ci |
| sqltest2 | fill_tb_without_index | PROCEDURE | root@localhost | 2019-03-18 09:53:32 | 2019-03-18 09:53:32 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8_general_ci |
| sqltest2 | fill_tb_with_index | PROCEDURE | root@localhost | 2019-03-18 09:53:33 | 2019-03-18 09:53:33 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8_general_ci |
+----------+-----------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
3 rows in set (0.17 sec)

查询创建存储过程的sql语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> show create procedure fill_tb_with_index;
+--------------------+----------------------------------------------------------------+-------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+--------------------+----------------------------------------------------------------+-------------------+----------------------+----------------------+--------------------+
| fill_tb_with_index | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
CREATE DEFINER=`root`@`localhost` PROCEDURE `fill_tb_with_index`()
BEGIN
DECLARE i int default 1;

WHILE i <= 100000 do
insert into tb_with_index values(i, i, i);
set i = i + 1;
END WHILE;

END | utf8mb4 | utf8mb4_general_ci | utf8_general_ci |
+--------------------+----------------------------------------------------------------+-------------------+----------------------+----------------------+--------------------+
1 row in set (0.09 sec)

查询指定表上的索引

1
2
3
4
5
6
7
8
9
mysql> show index from tb_with_index;
+---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tb_with_index | 1 | id_index | 1 | id | A | 100035 | NULL | NULL | YES | BTREE | | |
| tb_with_index | 1 | num_index | 1 | num | A | 98715 | NULL | NULL | YES | BTREE | | |
| tb_with_index | 1 | money_index | 1 | money | A | 100035 | NULL | NULL | YES | BTREE | | |
+---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.03 sec)

查询当前用户连接的权限

1
2
3
4
5
6
7
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+

查询指定用户连接的权限

1
2
3
4
5
6
7
mysql> show grants for 'guest';
+---------------------------------------------------------------------------------------------------------------+
| Grants for guest@% |
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'guest'@'%' IDENTIFIED BY PASSWORD '*6C8DE74065898C44C21EF74D67A834C5256BFA1C' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

总结

  1. 以上总结的查询语句都是我经常用到,相比Mysql所有查询语句来说简直是冰山一角,总结到一起主要是方便日后查找,同时也希望给他人带来帮助
  2. 查看这些语句会发现,有些是select开头,有些是show开头,实际上很多show开头的都是对information_schema数据库数据的封装
  3. information_schema 数据库是Mysql系统自带的数据库,记录了整个数据库实例上所有数据结构信息,更像是记录数据库的数据库,包含表结构、字符集,权限等太多的信息,有机会后续找时间聊聊这个数据库,在此就不展开了
  4. 正因为很多show开头的都是对information_schema数据库数据的封装,所以这些查询语句基本都可以通过在information_schema数据库查询得到,比如show procedure status where db='sqltest2';就可以改写成select routine_name from information_schema.routines where routine_schema='sqltest2';
  5. 此时此刻,我正在距离天安门500多米的现场等待阅兵仪式的开始,一边学习一边为祖国庆生的感觉真好!(注:500多米多了5公里,现场是卧室床前的电视机旁,这么近的距离不知道一会能不能看见接受检阅的飞机o(\ ̄︶ ̄*)o)*
Albert Shi wechat
欢迎您扫一扫上面的微信公众号,订阅我的博客