<p>使用MySQL时，需要了解当前数据库的情况，例如当前的数据库大小、字符集、用户等等。下面总结了一些查看数据库相关信息的命令</p><p>1：查看显示所有数据库</p><pre class="brush:bash;toolbar:false">mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|INVOICE|
|mysql|
|performance_schema|
|test|
+--------------------+
5rowsinset(0.00sec)
mysql></pre><p>2：查看当前使用的数据库</p><pre class="brush:bash;toolbar:false">mysql>selectdatabase();
+------------+
|database()|
+------------+
|INVOICE|
+------------+
1rowinset(0.00sec)
mysql>
clip_image001[1]</pre><p>3：查看数据库使用端口</p><pre class="brush:bash;toolbar:false">mysql>showvariableslike'port';
+---------------+-------+
|Variable_name|Value|
+---------------+-------+
|port|3306|
+---------------+-------+
1rowinset(0.00sec)
clip_image002[1]</pre><p>4：查看当前数据库大小</p><p>例如，我要查看INVOICE数据库的大小，那么可以通过下面SQL查看</p><pre class="brush:bash;toolbar:false">mysql>useinformation_schema
Readingtableinformationforcompletionoftableandcolumnnames
Youcanturnoffthisfeaturetogetaquickerstartupwith-A
Databasechanged
mysql>selectconcat(round(sum(data_length)/(1024*1024),2)+round(sum(index_length)/(1024*1024),2),'MB')as'DBSize'
->fromtables
->wheretable_schema='INVOICE';
+-----------+
|DBSize|
+-----------+
|7929.58MB|
+-----------+
1rowinset,1warning(0.00sec)</pre><p>查看数据所占的空间大小</p><pre class="brush:bash;toolbar:false">mysql>useinformation_schema;
Readingtableinformationforcompletionoftableandcolumnnames
Youcanturnoffthisfeaturetogetaquickerstartupwith-A
Databasechanged
mysql>selectconcat(round(sum(data_length)/(1024*1024),2),'MB')as'DBSize'
->fromtables
->wheretable_schema='INVOICE';
+-----------+
|DBSize|
+-----------+
|6430.26MB|
+-----------+
1rowinset,1warning(0.00sec)
mysql></pre><p>查看索引所占的空间大小</p><pre class="brush:bash;toolbar:false">mysql>selectconcat(round(sum(index_length)/(1024*1024),2),'MB')as'DBSize'
->fromtables
->wheretable_schema='INVOICE';
+-----------+
|DBSize|
+-----------+
|1499.32MB|
+-----------+
1rowinset,1warning(0.13sec)</pre><p>mysql></p><p>5：查看数据库编码</p><pre class="brush:bash;toolbar:false">mysql>showvariableslike'character%';
+--------------------------+----------------------------+
|Variable_name|Value|
+--------------------------+----------------------------+
|character_set_client|utf8|
|character_set_connection|utf8|
|character_set_database|utf8|
|character_set_filesystem|binary|
|character_set_results|utf8|
|character_set_server|latin1|
|character_set_system|utf8|
|character_sets_dir|/usr/share/mysql/charsets/|
+--------------------------+----------------------------+
8rowsinset(0.00sec)
clip_image003[1]</pre><p>character_set_client 为客户端编码方式；</p><p>character_set_connection 为建立连接使用的编码；</p><p>character_set_database 为数据库的编码；</p><p>character_set_results 为结果集的编码；</p><p>character_set_server 为数据库服务器的编码；</p><p>只要保证以上采用的编码方式一样，就不会出现乱码问题。</p><pre class="brush:bash;toolbar:false">mysql>showvariableslike'collation%';
+----------------------+-------------------+
|Variable_name|Value|
+----------------------+-------------------+
|collation_connection|utf8_general_ci|
|collation_database|utf8_general_ci|
|collation_server|latin1_swedish_ci|
+----------------------+-------------------+
3rowsinset(0.00sec)
clip_image004</pre><p>status也可以查看数据库的编码</p><pre class="brush:bash;toolbar:false">mysql>status;
--------------
mysqlVer14.14Distrib5.6.20,forLinux(x86_64)usingEditLinewrapper
Connectionid:1
Currentdatabase:INVOICE
Currentuser:root@localhost
SSL:Notinuse
Currentpager:stdout
Usingoutfile:''
Usingdelimiter:;
Serverversion:5.6.20-enterprise-commercial-advancedMySQLEnterpriseServer-AdvancedEdition(Commercial)
Protocolversion:10
Connection:LocalhostviaUNIXsocket
Servercharacterset:latin1
Dbcharacterset:latin1
Clientcharacterset:utf8
Conn.characterset:utf8
UNIXsocket:/var/lib/mysql/mysql.sock
Uptime:5hours18min51sec
Threads:1Questions:10884Slowqueries:0Opens:650Flushtables:1Opentables:268Queriespersecondavg:0.568
--------------
mysql></pre><p>6：查看数据库的表信息</p><pre class="brush:bash;toolbar:false">mysql>showtables;
+---------------------------------------+
|Tables_in_information_schema|
+---------------------------------------+
|CHARACTER_SETS|
|COLLATIONS|
|COLLATION_CHARACTER_SET_APPLICABILITY|
|COLUMNS|
|COLUMN_PRIVILEGES|
|ENGINES|
|EVENTS|
|FILES|
|GLOBAL_STATUS|
|GLOBAL_VARIABLES|
|KEY_COLUMN_USAGE|
|OPTIMIZER_TRACE|
|PARAMETERS|
|PARTITIONS|
|PLUGINS|
|PROCESSLIST|
|PROFILING|
|REFERENTIAL_CONSTRAINTS|
|ROUTINES|
|SCHEMATA|
|SCHEMA_PRIVILEGES|
|SESSION_STATUS|
|SESSION_VARIABLES|
|STATISTICS|
|TABLES|
|TABLESPACES|
|TABLE_CONSTRAINTS|
|TABLE_PRIVILEGES|
|TRIGGERS|
|USER_PRIVILEGES|
|VIEWS|
|INNODB_LOCKS|
|INNODB_TRX|
|INNODB_SYS_DATAFILES|
|INNODB_LOCK_WAITS|
|INNODB_SYS_TABLESTATS|
|INNODB_CMP|
|INNODB_METRICS|
|INNODB_CMP_RESET|
|INNODB_CMP_PER_INDEX|
|INNODB_CMPMEM_RESET|
|INNODB_FT_DELETED|
|INNODB_BUFFER_PAGE_LRU|
|INNODB_SYS_FOREIGN|
|INNODB_SYS_COLUMNS|
|INNODB_SYS_INDEXES|
|INNODB_FT_DEFAULT_STOPWORD|
|INNODB_SYS_FIELDS|
|INNODB_CMP_PER_INDEX_RESET|
|INNODB_BUFFER_PAGE|
|INNODB_CMPMEM|
|INNODB_FT_INDEX_TABLE|
|INNODB_FT_BEING_DELETED|
|INNODB_SYS_TABLESPACES|
|INNODB_FT_INDEX_CACHE|
|INNODB_SYS_FOREIGN_COLS|
|INNODB_SYS_TABLES|
|INNODB_BUFFER_POOL_STATS|
|INNODB_FT_CONFIG|
+---------------------------------------+
59rowsinset(0.00sec)</pre><p>或者使用下面SQL语句查看某个数据库的表信息。</p><pre class="brush:bash;toolbar:false">select*frominformation_schema.tableswheretable_schema='databasename';</pre><p>查看某种具体表的信息</p><pre class="brush:bash;toolbar:false">select*frominformation_schema.tableswheretable_name='table_name'</pre><p>7：查看数据库的所有用户信息</p><pre class="brush:bash;toolbar:false">mysql>selectdistinctconcat('user:''',user,'''@''',host,''';')asqueryfrommysql.user;
+-------------------------------------+
|query|
+-------------------------------------+
|user:'root'@'127.0.0.1';|
|user:'root'@'::1';|
|user:'root'@'gettesx20.test.com';|
|user:'root'@'localhost';|
+-------------------------------------+
4rowsinset(0.00sec)
mysql></pre><p></p><p>8: 查看某个具体用户的权限</p><pre class="brush:bash;toolbar:false">mysql>showgrantsfor'root'@'localhost';
+---------------------------------------------------------------------------------------------------------------------------------+
|Grantsforroot@localhost|
+---------------------------------------------------------------------------------------------------------------------------------+
|GRANTALLPRIVILEGESON*.*TO'root'@'localhost'IDENTIFIEDBYPASSWORD'*C7B1594FD74578DA3A92A61720AC67C6DBE6FC23'WITHGRANTOPTION|
|GRANTPROXYON''@''TO'root'@'localhost'WITHGRANTOPTION|
+---------------------------------------------------------------------------------------------------------------------------------+
2rowsinset(0.00sec)</pre><p>9： 查看数据库的最大连接数</p><pre class="brush:bash;toolbar:false">mysql>showvariableslike'%max_connections%';
+-----------------+-------+
|Variable_name|Value|
+-----------------+-------+
|max_connections|151|
+-----------------+-------+
1rowinset(0.00sec)</pre><p><br/></p><p>mysql></p><p>10：查看数据库当前连接数，并发数。</p><pre class="brush:bash;toolbar:false">mysql>showstatuslike'Threads%';
+-------------------+-------+
|Variable_name|Value|
+-------------------+-------+
|Threads_cached|0|
|Threads_connected|1|
|Threads_created|1|
|Threads_running|1|
+-------------------+-------+
4rowsinset(0.00sec)</pre><p>Threads_cached : 代表当前此时此刻线程缓存中有多少空闲线程。</p><p>Threads_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。</p><p>Threads_created :代表从最近一次服务启动，已创建线程的数量。</p><p>Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态，这里相对应的线程也是sleep状态。</p><p>clip_image005</p><p>11：查看数据文件存放路径</p><pre class="brush:bash;toolbar:false">mysql>showvariableslike'%datadir%';
+---------------+-------------------+
|Variable_name|Value|
+---------------+-------------------+
|datadir|/mysqldata/mysql/|
+---------------+-------------------+
1rowinset(0.00sec)
mysql></pre>