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

发表于 | 更新于 | 分类于 |
热度 ℃   |   字数统计: 1,444(字)   |   阅读时长: 6(分) |

前言

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

上面描述的问题常常出现在切换数据库处理问题的时候，通过一个客户端连接到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
欢迎您扫一扫上面的微信公众号，订阅我的博客