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

本篇内容主要讲解"mysql怎么查看表结构及已有索引信息"，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习"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，可以参考一下这个系列文章。

http://book.51cto.com/art/201108/288137.htm

总结一下，mysql中查看库表字段信息都在information_schemal中，这些是获取数据字典的必备sql。
本文中mysql的语句都在本地测试过。另外oracle的结构也要熟悉。

到此，相信大家对"mysql怎么查看表结构及已有索引信息"有了更深的了解，不妨来实际操作一番吧！这里是网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！