mysql怎么查看表结构及已有索引信息

59次阅读

共计 4554 个字符，预计需要花费 12 分钟才能阅读完成。

本篇内容主要讲解“mysql 怎么查看表结构及已有索引信息”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“mysql 怎么查看表结构及已有索引信息”吧!

查看表结构及已有索引信息

需求背景是给一个表名然后给出相应的表结构信息及索引信息

常用的命令有如下：

desc tableName; desc employees.employees;
www.2cto.com
show columns from tableName; show COLUMNS from employees.employees;

describe tableName; DESCRIBE employees.employees;

这三个显示的结果都是一样的，显示表中 filed，type，null，key，default 及 extra。

show create table tableName; show CREATE TABLE employees.employees;

这个语句会显示这个表的建表语句。

select * from columns where table_name= 表名
select * from information_schema.COLUMNS where TABLE_SCHEMA= employees and TABLE_NAME= employees

这个显示的结果就比较全了。

接下来，来点更全的 sql，这个是用来同步 mysql 和 orac 数据字典的所有 sql。

mysql 部分：

01
## 查看所有的库
02
SELECT
03
lower(schema_name) schema_name
04 www.2cto.com
FROM
05
information_schema.schemata
06
WHERE
07
schema_name NOT IN (
08
mysql ,
09
information_schema ,
10
test ,
11
search ,
12
tbsearch ,
13
sbtest ,
14
dev_ddl
15
)
16

17
## 产看某一个库中的所有表
18
SELECT
19
table_name,
20
create_time updated_at,
21
table_type,
22
ENGINE,
23
table_rows num_rows,
24 www.2cto.com
table_comment,
25
ceil(data_length / 1024 / 1024) store_capacity
26
FROM
27
information_schema.TABLES
28
WHERE
29
table_schema = employees
30
AND table_name NOT LIKE tmp#_% ESCAPE #
31

32
## 查看某一个库下某一个表的所有字段
33
SELECT
34
lower(column_name) column_name,
35
ordinal_position position,
36
column_default dafault_value,
37
substring(is_nullable, 1, 1) nullable,
38
column_type data_type,
39
column_comment,
40
character_maximum_length data_length,
41
numeric_precision data_precision,
42
numeric_scale data_scale
43
FROM
44
information_schema.COLUMNS
45 www.2cto.com
WHERE
46
table_schema = employees
47
AND table_name = employees
48

49

50
## 查看某一个库下某一张表的索引
51

52
SELECT DISTINCT
53
lower(index_name) index_name,
54
lower(index_type) type
55
FROM
56
information_schema.statistics
57
WHERE
58
table_schema = employees
59
AND table_name = employees
60

61
## 查看某一个库下某一张表的某一个索引
62

63
SELECT
64
lower(column_name) column_name,
65
seq_in_index column_position
66
FROM
67
information_schema.statistics
68
WHERE
69
table_schema = employees
70
AND table_name = employees
71
AND index_name = primary
72
www.2cto.com
73
## 查看某一个库下某一个表的注释
74
SELECT
75
table_comment comments
76
FROM
77
information_schema.TABLES
78
WHERE
79
table_schema = employees
80
AND table_name = employees
81

82
## 查看某一个库下某一个表的列的注释
83
SELECT
84
lower(column_name) column_name,
85
column_comment comments
86
FROM
87
COLUMNS
88
WHERE
89
table_schema = employees
90
AND table_name = employees
oracle 部分：
www.2cto.com

001
#table structure:
002
SELECT
003
lower(table_name) table_name,
004
TEMPORARY,
005
tablespace_name,
006
num_rows,
007
duration,
008
ORACLE table_type,
009 www.2cto.com
partitioned,
010
(
011
SELECT
012
ceil(sum(bytes) / 1024 / 1024)
013
FROM
014
dba_segments b
015
WHERE
016
a. OWNER = b. OWNER
017
AND a.table_name = b.segment_name
018
) AS store_capacity
019
FROM
020
dba_tables a
021
WHERE
022
OWNER = ?
023
AND table_name NOT LIKE TMP%
024

025
SELECT
026
lower(column_name) column_name,
027
column_id position,
028
data_type,
029
data_length,
030
data_precision,
031
data_scale,
032
nullable,
033
data_default default_value,
034 www.2cto.com
default_length
035
FROM
036
dba_tab_columns
037
WHERE
038
OWNER = ?
039
AND table_name = ?;
040

041
# index
042
SELECT
043
lower(index_name) index_name,
044
index_type type
045
FROM
046
dba_indexes
047
WHERE
048
OWNER = ?
049
AND table_name = ?
050
AND index_name NOT LIKE SYS_IL%
051

052
SELECT
053
lower(column_name) column_name,
054
column_position,
055
descend
056
FROM
057
dba_ind_columns
058
WHERE
059
table_owner = ?
060
AND table_name = ?
061
AND index_name = ?;
062
www.2cto.com
063
#collect description
064
SELECT
065
comments
066
FROM
067
dba_tab_comments
068
WHERE
069
OWNER = ?
070
AND table_name = ?;
071

072
SELECT
073
lower(column_name) column_name,
074
comments
075
FROM
076
dba_col_comments
077
WHERE
078
OWNER = ?
079
AND table_name = ?;
080

081
#database
082
SELECT
083
lower(username) username
084
FROM
085
dba_users
086
WHERE
087
username NOT IN (
088
STDBYPERF ,
089
READONLY ,
090
APPQOSSYS ,
091
ANYSQL ,
092
DBFLASH ,
093
SYS ,
094
SYSTEM ,
095
MONITOR ,
096
TBSEARCH ,
097
MANAGER ,
098 www.2cto.com
SYSMAN ,
099
EXFSYS ,
100
WMSYS ,
101
DIP ,
102
TSMSYS ,
103
ORACLE_OCM ,
104
OUTLN ,
105
DBSNMP ,
106
PERFSTAT ,
107
SEARCH ,
108
TOOLS ,
109
TBDUMP ,
110
DMSYS ,
111
XDB ,
112
ANONYMOUS ,
113
DEV_DDL
114
);
115

116
#segsize
117
SELECT
118
round(sum(bytes) / 1024 / 1024, 0) mbytes
119
FROM
120 www.2cto.com
dba_segments
121
WHERE
122
OWNER = ?
123
AND segment_name = ?;
关于 oralce 中的 segements，可以参考一下这个系列文章。