技术标签: show tables MySQL mysql show indexes show databases
大家好!我是只谈技术不剪发的 Tony 老师。今天带来的分享是如何查看 MySQL 数据库模式以及数据表的结构,包括字段定义、主键、外键、唯一等约束和索引信息,如何查看表和索引占用的磁盘空间等。
在 MySQL 中,数据库(database)和模式(schema)是相同的概念,可以使用以下查询列出当前实例中包含的数据库/模式:
-- 方法一
select schema_name as database_name
from information_schema.schemata
order by schema_name;
-- 方法二
show databases;
-- 方法三
show schemas;
查询结果中的 information_schema、mysql、performance_schema 以及 sys 属于系统数据库/模式。
将系统数据库/模式排除之外就是用户创建的数据库/模式:
select schema_name as database_name
from information_schema.schemata
where schema_name not in ('information_schema','mysql','performance_schema','sys')
order by schema_name;
使用 SHOW 命令查看数据库的创建语句:
show create database database_name;
show create schema database_name;
查看当前数据库中的表:
-- 方法一
select table_schema as database_name, table_name
from information_schema.tables
where table_type = 'BASE TABLE'
and table_schema = database()
order by database_name, table_name;
-- 方法二
show tables [LIKE 'pattern' | WHERE expr];
-- 方法三
show table status [LIKE 'pattern' | WHERE expr];
其中,database() 函数返回当前数据库的名称。
查看指定数据库中的表,将查询条件中的 database_name 替换成需要查询的数据库名:
-- 方法一
select table_schema as database_name, table_name
from information_schema.tables
where table_type = 'BASE TABLE'
and table_schema = 'database_name'
order by database_name, table_name;
-- 方法二
show tables {
in | from} database_name [LIKE 'pattern' | WHERE expr];
show tables status {
in | from} database_name [LIKE 'pattern' | WHERE expr];
select table_schema as database_name, table_name
from information_schema.tables
where table_type = 'BASE TABLE'
order by database_name, table_name;
select tab.table_schema as database_name,
tab.table_name
from information_schema.tables tab
left join information_schema.table_constraints tco
on tab.table_schema = tco.table_schema and tab.table_name = tco.table_name and tco.constraint_type = 'PRIMARY KEY'
where tco.constraint_type is null
and tab.table_schema not in('mysql', 'information_schema', 'performance_schema', 'sys')
and tab.table_type = 'BASE TABLE'
-- and tab.table_schema = 'sakila'
order by tab.table_schema, tab.table_name;
select table_schema as database_name, table_name
from information_schema.tables
where engine = 'InnoDB'
and table_type = 'BASE TABLE'
-- and schema_name not in ('information_schema','mysql','performance_schema','sys')
-- and table_schema = 'database_name'
order by table_schema, table_name;
select table_schema as database_name, table_name
from information_schema.tables
where engine = 'MyISAM'
and table_type = 'BASE TABLE'
-- and schema_name not in ('information_schema','mysql','performance_schema','sys')
-- and table_schema = 'database_name'
order by table_schema, table_name;
select table_schema as database_name, table_name, engine
from information_schema.tables
where table_type = 'BASE TABLE'
-- and schema_name not in ('information_schema','mysql','performance_schema','sys')
-- and table_schema = 'database_name'
order by table_schema, table_name;
使用以下脚本查找最近 30 天之内创建的表:
select table_schema as database_name, table_name, create_time
from information_schema.tables
where table_type = 'BASE TABLE'
and create_time > adddate(current_date, interval - 30 day)
-- and schema_name not in ('information_schema','mysql','performance_schema','sys')
-- and table_schema = 'database_name'
order by create_time desc, table_name;
使用以下脚本查找最近 30 天之内被修改过的表:
select table_schema as database_name, table_name, update_time
from information_schema.tables
where table_type = 'BASE TABLE'
and update_time > (current_timestamp() - interval 30 day)
-- and schema_name not in ('information_schema','mysql','performance_schema','sys')
-- and table_schema = 'database_name'
order by update_time desc, table_name;
以上查询返回的结果可能不准确,因为update_time 取决于存储引擎,具体可以参考官方文档。
show create table table_name;
select tab.table_schema as database_schema,
tab.table_name as table_name,
col.ordinal_position as column_id,
col.column_name as column_name,
col.data_type as data_type,
case when col.numeric_precision is not null then col.numeric_precision
else col.character_maximum_length
end as max_length,
case when col.datetime_precision is not null then col.datetime_precision
when col.numeric_scale is not null then col.numeric_scale
else 0
end as 'precision'
from information_schema.tables as tab
join information_schema.columns as col
on col.table_schema = tab.table_schema
and col.table_name = tab.table_name
where tab.table_type = 'BASE TABLE'
and tab.table_schema not in ('information_schema','mysql','performance_schema','sys')
-- 查看当前数据库中的表
-- and tab.table_schema = database()
-- 查看指定数据库中的表
-- and tab.table_schema = 'database_name'
order by tab.table_name, col.ordinal_position;
-- 方法一
desc table_name;
-- 方法二
SHOW [EXTENDED] [FULL] {
COLUMNS | FIELDS}
{
FROM | IN} table_name
[{
FROM | IN} database_name]
[LIKE 'pattern' | WHERE expr];
-- 方法二
select ordinal_position as column_id,
column_name as column_name,
data_type as data_type,
case when numeric_precision is not null then numeric_precision
else character_maximum_length
end as max_length,
case when datetime_precision is not null then datetime_precision
when numeric_scale is not null then numeric_scale
else 0
end as data_precision,
is_nullable,
column_default
from information_schema.columns
where table_name = 'tablename'
and table_schema = 'schema_name'
order by ordinal_position;
select col.table_schema as database_name,
col.table_name,
col.ordinal_position as col_id,
col.column_name,
col.data_type,
col.numeric_precision,
col.numeric_scale
from information_schema.columns col
join information_schema.tables tab
on tab.table_schema = col.table_schema and tab.table_name = col.table_name and tab.table_type = 'BASE TABLE'
where col.data_type in ('tinyint', 'smallint', 'mediumint', 'int', 'bigint',
'decimal', 'bit', 'float', 'double')
and col.table_schema not in ('information_schema', 'sys', 'performance_schema', 'mysql')
-- and col.table_schema = 'database_name'
-- and col.table_name = 'table_name'
order by col.table_schema, col.table_name, col.ordinal_position;
select col.table_schema as database_name,
col.table_name,
col.ordinal_position as col_id,
col.column_name,
col.data_type,
col.numeric_precision,
col.numeric_scale
from information_schema.columns col
join information_schema.tables tab
on tab.table_schema = col.table_schema and tab.table_name = col.table_name and tab.table_type = 'BASE TABLE'
where col.data_type in ('char', 'varchar', 'binary', 'varbinary',
'blob', 'tinyblob', 'mediumblob', 'longblob',
'text', 'tinytext', 'mediumtext', 'longtext'
'enum', 'set')
and col.table_schema not in ('information_schema', 'sys', 'performance_schema', 'mysql')
-- and col.table_schema = 'database_name'
-- and col.table_name = 'table_name'
order by col.table_schema, col.table_name, col.ordinal_position;
select col.table_schema as database_name,
col.table_name,
col.ordinal_position as col_id,
col.column_name,
col.data_type,
col.numeric_precision,
col.numeric_scale
from information_schema.columns col
join information_schema.tables tab
on tab.table_schema = col.table_schema and tab.table_name = col.table_name and tab.table_type = 'BASE TABLE'
where col.data_type in ('date', 'time', 'datetime', 'year', 'timestamp')
and col.table_schema not in ('information_schema', 'sys', 'performance_schema', 'mysql')
-- and col.table_schema = 'database_name'
-- and col.table_name = 'table_name'
order by col.table_schema, col.table_name, col.ordinal_position;
以下查询用于列出字段的详细信息,包括是否主键、外键、唯一、默认值、是否可空以及计算列的表达式等:
select col.table_schema as database_name,
col.table_name,
col.column_name,
col.data_type,
case when col.data_type in ('datetime', 'timestamp', 'time') then col.datetime_precision
else col.numeric_precision
end as 'precision',
col.numeric_scale,
col.character_maximum_length as char_length,
col.column_default,
col.generation_expression,
case when (group_concat(constraint_type separator ', ')) like '%PRIMARY KEY%' then 'YES'
else 'NO'
end as PK,
case when (group_concat(constraint_type separator ', ')) like '%UNIQUE%' then 'YES'
else 'NO'
end as UQ,
case when (group_concat(constraint_type separator ', ')) like '%FOREIGN KEY%' then 'YES'
else 'NO'
end as FK,
col.is_nullable
from information_schema.columns col
join information_schema.tables tab
on col.table_schema = tab.table_schema and col.table_name = tab.table_name and tab.table_type = 'BASE TABLE'
left join information_schema.key_column_usage kcu
on col.table_schema = kcu.table_schema and col.table_name = kcu.table_name and col.column_name = kcu.column_name
left join information_schema.table_constraints tco
on kcu.constraint_schema = tco.constraint_schema and kcu.constraint_name = tco.constraint_name and kcu.table_name = tco.table_name
where tab.table_schema not in ('information_schema','mysql','performance_schema','sys')
-- 查看当前数据库中的表
-- and tab.table_schema = database()
-- 查看指定数据库中的表
-- and tab.table_schema = 'database_name'
group by 1,2,3,4,5,6,7,8,9,13
order by col.table_schema, col.table_name, col.column_name;
select table_schema as database_name,
table_name,
column_name,
data_type,
generation_expression
from information_schema.columns
where length(generation_expression) > 0
and table_schema not in ('information_schema','mysql','performance_schema','sys')
-- 查看当前数据库中的表
-- and table_schema = database()
-- 查看指定数据库中的表
-- and table_schema = 'database_name'
-- 查看指定表
-- and table_name = 'tablename'
order by table_schema, table_name, column_name;
select tab.table_schema as database_schema,
sta.index_name as pk_name,
sta.seq_in_index as column_id,
sta.column_name,
tab.table_name
from information_schema.tables as tab
join information_schema.statistics as sta
on sta.table_schema = tab.table_schema and sta.table_name = tab.table_name and sta.index_name = 'primary'
where tab.table_schema = 'database_name'
and tab.table_type = 'BASE TABLE'
order by tab.table_name, column_id;
select concat(col.table_schema, '.', col.table_name) as 'foreign_table',
col.column_name as column_name,
'->' as rel,
concat(kcu.referenced_table_schema, '.', kcu.referenced_table_name) as primary_table,
kcu.referenced_column_name as pk_column_name,
kcu.constraint_name as fk_constraint_name
from information_schema.columns col
join information_schema.tables tab
on col.table_schema = tab.table_schema and col.table_name = tab.table_name
left join information_schema.key_column_usage kcu
on col.table_schema = kcu.table_schema and col.table_name = kcu.table_name and col.column_name = kcu.column_name
where col.table_schema not in('information_schema','sys', 'mysql', 'performance_schema')
and tab.table_type = 'BASE TABLE'
and kcu.referenced_table_schema is not null
and tab.table_schema = 'hrdb'
order by col.table_schema, col.table_name, col.ordinal_position;
select stat.table_schema as database_name,
stat.table_name,
stat.index_name,
group_concat(stat.column_name order by stat.seq_in_index separator ', ') as columns,
tco.constraint_type
from information_schema.statistics stat
join information_schema.table_constraints tco
on stat.table_schema = tco.table_schema and stat.table_name = tco.table_name and stat.index_name = tco.constraint_name
where stat.non_unique = 0
and stat.table_schema not in ('information_schema', 'sys', 'performance_schema', 'mysql')
and stat.table_schema = 'database_name'
group by stat.table_schema, stat.table_name, stat.index_name, tco.constraint_type
order by stat.table_schema, stat.table_name;
select table_schema as database_name,
table_name,
column_name,
column_default
from information_schema.columns
where column_default is not null
and table_schema not in ('information_schema', 'sys', 'performance_schema','mysql')
and table_schema = 'database_name'
order by table_schema, table_name, ordinal_position;
select table_schema as database_name,
table_name,
index_name,
group_concat(column_name order by seq_in_index) as columns,
index_type,
case non_unique
when 1 then 'Not Unique'
else 'Unique'
end as is_unique
from information_schema.statistics
where table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys')
and table_schema = 'database_name'
group by table_schema, table_name, index_name, index_type, non_unique
order by table_schema, table_name;
-- 方法一
SHOW [EXTENDED] {
INDEX | INDEXES | KEYS}
{
FROM | IN} table_name
[{
FROM | IN} database_name]
[WHERE expr];
-- 方法二
select table_schema as database_name,
table_name,
index_name,
group_concat(column_name order by seq_in_index) as columns,
index_type,
case non_unique
when 1 then 'Not Unique'
else 'Unique'
end as is_unique
from information_schema.statistics
where table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys')
and table_schema = 'database_name'
and table_name = 'table_name'
group by table_schema, table_name, index_name, index_type, non_unique
order by table_schema, table_name;
对于不同的存储引擎使用不同的查询方法:
-- MyISAM 存储引擎表
select table_schema, table_name, table_rows
from information_schema.tables
where table_type = 'BASE TABLE'
and engine = 'MyISAM'
and table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys')
-- and table_schema = 'database_name'
-- and table_name = 'table_name'
order by table_schema, table_name;
-- InnoDB 存储引擎表
select count(*) from table_name;
select table_schema as database_name,
table_name,
round(sum((data_length + index_length)) / power(1024, 2), 2) as used_mb,
round(sum((data_length + index_length + data_free)) /power(1024, 2), 2) as allocated_mb,
round(sum(data_free) /power(1024, 2), 2) as free_mb
from information_schema.tables
where table_type = 'BASE TABLE'
and table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys')
-- and table_schema = 'database_name'
-- and table_name = 'table_name'
group by table_schema, table_name
order by used_mb desc;
select table_schema as database_name,
table_name,
engine,
round(1.0*data_length/1024/1024, 2) as data_size_mb,
round(index_length/1024/1024, 2) as index_size_mb,
round((data_length + index_length)/1024/1024, 2) as total_size_mb
from information_schema.tables
where table_type = 'BASE TABLE'
and table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys')
-- and table_schema = 'database_name'
-- and table_name = 'table_name'
order by total_size_mb desc;
对于 InnoDB 表,data_length 字段表示聚集索引的大小(包含了所有的数据)。InnoDB 表的 index_length 和 data_length 返回的是近似值。
select database_name,
table_name,
index_name,
(1.0*stat_value*@@innodb_page_size/1024/1024) as index_size_mb
from mysql.innodb_index_stats
where stat_name = 'size'
and index_name not in ('PRIMARY', 'GEN_CLUST_INDEX')
-- and database_name = 'database_name'
-- and table_name = 'table_name'
order by index_size_mb desc;
select tab.table_schema as database_name, tab.table_name,
round(sum(data_length + index_length) / power(1024, 2), 2) as used_mb,
round(sum(data_length + index_length + data_free) / power(1024, 2), 2) as allocated_mb
from information_schema.tables as tab
join information_schema.columns as col
on col.table_schema = tab.table_schema and col.table_name = tab.table_name
where tab.table_type = 'BASE TABLE'
and tab.table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys')
and col.data_type in ('blob', 'mediumblob', 'longblob', 'text', 'mediumtext', 'longtext')
-- and tab.table_schema = 'database_name'
-- and tab.table_name = 'table_name'
group by 1,2
order by 2;
以下查询比较两个数据库中的所有表,返回任意数据库中越少的字段:
set @database_1 = 'database_name_1'; -- provide first database name here
set @database_2 = 'database_name_2'; -- provide second database name here
select *
from (
select COALESCE(c1.table_name, c2.table_name) as table_name,
COALESCE(c1.column_name, c2.column_name) as table_column,
c1.column_name as database1,
c2.column_name as database2
from
(select table_name,
column_name
from information_schema.columns c
where c.table_schema = @database_1) c1
right join
(select table_name,
column_name
from information_schema.columns c
where c.table_schema = @database_2) c2
on c1.table_name = c2.table_name and c1.column_name = c2.column_name
union
select COALESCE(c1.table_name, c2.table_name) as table_name,
COALESCE(c1.column_name, c2.column_name) as table_column,
c1.column_name as schema1,
c2.column_name as schema2
from
(select table_name,
column_name
from information_schema.columns c
where c.table_schema = @database_1) c1
left join
(select table_name,
column_name
from information_schema.columns c
where c.table_schema = @database_2) c2
on c1.table_name = c2.table_name and c1.column_name = c2.column_name
) tmp
where database1 is null
or database2 is null
order by table_name,
table_column;
set @database_1 = null;
set @database_2 = null;
写作不易,如果你点击了收藏,请不要忘了关注️、评论、点赞!
文章浏览阅读1.6w次,点赞8次,收藏41次。生活中我们无时不刻不都要在网站搜索资源,但就是缺少一个趁手的资源搜索网站,如果有一个比较好的资源搜索网站可以帮助我们节省一大半时间!今天小编在这里为大家分享5款超厉害的资源搜索网站,每一款都可以让你的资源丰富精彩!网盘传奇一款最有效的网盘资源搜索网站你还在为找网站里面的资源而烦恼找不到什么合适的工具而烦恼吗?这款网站传奇网站汇聚了4853w个资源,并且它每一天都会持续更新资源;..._最全资源搜索引擎
文章浏览阅读4.5k次,点赞5次,收藏18次。阅读测试程序,设计一个Book类。函数接口定义:class Book{}该类有 四个私有属性 分别是 书籍名称、 价格、 作者、 出版年份,以及相应的set 与get方法;该类有一个含有四个参数的构造方法,这四个参数依次是 书籍名称、 价格、 作者、 出版年份 。裁判测试程序样例:import java.util.*;public class Main { public static void main(String[] args) { List <Book>_6-1 book类的设计java
文章浏览阅读613次,点赞28次,收藏27次。相比于以前的传统手工管理方式,智能化的管理方式可以大幅降低学校的运营人员成本,实现了校园导航的标准化、制度化、程序化的管理,有效地防止了校园导航的随意管理,提高了信息的处理速度和精确度,能够及时、准确地查询和修正建筑速看等信息。课题主要采用微信小程序、SpringBoot架构技术,前端以小程序页面呈现给学生,结合后台java语言使页面更加完善,后台使用MySQL数据库进行数据存储。微信小程序主要包括学生信息、校园简介、建筑速看、系统信息等功能,从而实现智能化的管理方式,提高工作效率。
传统上用户登陆状态会以 Session 的形式保存在服务器上,而 Session ID 则保存在前端的 Cookie 中;而使用 JWT 以后,用户的认证信息将会以 Token 的形式保存在前端,服务器不需要保存任何的用户状态,这也就是为什么 JWT 被称为无状态登陆的原因,无状态登陆最大的优势就是完美支持分布式部署,可以使用一个 Token 发送给不同的服务器,而所有的服务器都会返回同样的结果。有状态和无状态最大的区别就是服务端会不会保存客户端的信息。
文章浏览阅读784次。发表于10小时前| 2674次阅读| 来源TechCrunch| 19 条评论| 作者Jon EvansiOSAndroid应用开发产品编程语言JavaObjective-C摘要:即便Android市场份额已经超过80%,对于开发者来说,使用哪一个平台做开发仍然很难选择。本文从开发环境、配置、UX设计、语言、API、网络、分享、碎片化、发布等九个方面把Android和iOS_ios 开发角度
搜索引擎的发展历史可以追溯到20世纪90年代初,随着互联网的快速发展和信息量的急剧增加,人们开始感受到了获取和管理信息的挑战。这些阶段展示了搜索引擎在技术和商业模式上的不断演进,以满足用户对信息获取的不断增长的需求。
文章浏览阅读990次。对象特性是指控制对象的输出参数和输入参数之间的相互作用规律。放大系数K描述控制对象特性的静态特性参数。它的意义是:输出量的变化量和输入量的变化量之比。时间常数T当输入量发生变化后,所引起输出量变化的快慢。(动态参数) ..._控制对象特性
文章浏览阅读5.7w次,点赞50次,收藏276次。FRP搭建内网穿透1.概述:frp可以通过有公网IP的的服务器将内网的主机暴露给互联网,从而实现通过外网能直接访问到内网主机;frp有服务端和客户端,服务端需要装在有公网ip的服务器上,客户端装在内网主机上。2.简单的图解:3.准备工作:1.一个域名(www.test.xyz)2.一台有公网IP的服务器(阿里云、腾讯云等都行)3.一台内网主机4.下载frp,选择适合的版本下载解压如下:我这里服务器端和客户端都放在了/usr/local/frp/目录下4.执行命令# 服务器端给执_locyanfrp
文章浏览阅读687次。题目:http://acm.hust.edu.cn/vjudge/contest/view.action?cid=93745#problem/A题意:给出r*c的01矩阵,可以翻转格子使得0表成1,1变成0,求出最小的步数使得每一行中1的个数相等,每一列中1的个数相等。思路:网络流。容量可以保证每一行和每一列的1的个数相等,费用可以算出最小步数。行向列建边,如果该格子是_uva12534
文章浏览阅读504次。1、Let's Encrypt 90天,支持泛域名2、Buypass:https://www.buypass.com/ssl/resources/go-ssl-technical-specification6个月,单域名3、AlwaysOnSLL:https://alwaysonssl.com/ 1年,单域名 可参考蜗牛(wn789)4、TrustAsia5、Alpha..._csdn alphassl免费申请
文章浏览阅读1.6k次。测试算法的性能 很多时候我们需要对算法的性能进行测试,最简单的方式是看算法在特定的数据集上的执行时间,简单的测试算法性能的函数实现见testSort()。【思想】:用clock_t计算某排序算法所需的时间,(endTime - startTime)/ CLOCKS_PER_SEC来表示执行了多少秒。【关于宏CLOCKS_PER_SEC】:以下摘自百度百科,“CLOCKS_PE_算法性能测试
文章浏览阅读1.2k次。fromhttps://towardsdatascience.com/finding-lane-lines-simple-pipeline-for-lane-detection-d02b62e7572bIdentifying lanes of the road is very common task that human driver performs. This is important ..._lanedetectionlite