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

2019独角兽企业重金招聘Python工程师标准>>>

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

常用的命令有如下：

desc tableName; desc employees.employees;

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部分：

## 查看所有的库
SELECT
	lower(schema_name) schema_name
FROM
	information_schema.schemata
WHERE
	schema_name NOT IN (
		'mysql',
		'information_schema',
		'test',
		'search',
		'tbsearch',
		'sbtest',
		'dev_ddl'
	)

## 产看某一个库中的所有表
SELECT
	table_name,
	create_time updated_at,
	table_type,
	ENGINE,
	table_rows num_rows,
	table_comment,
	ceil(data_length / 1024 / 1024) store_capacity
FROM
	information_schema.TABLES
WHERE
	table_schema = 'employees'
AND table_name NOT LIKE 'tmp#_%' ESCAPE '#'

##查看某一个库下某一个表的所有字段
SELECT
	lower(column_name) column_name,
	ordinal_position position,
	column_default dafault_value,
	substring(is_nullable, 1, 1) nullable,
	column_type data_type,
	column_comment,
	character_maximum_length data_length,
	numeric_precision data_precision,
	numeric_scale data_scale
FROM
	information_schema.COLUMNS
WHERE
	table_schema = 'employees'
AND table_name = 'employees';


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

SELECT DISTINCT
	lower(index_name) index_name,
	lower(index_type) type
FROM
	information_schema.statistics
WHERE
	table_schema = 'employees'
AND table_name = 'employees';

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

SELECT
	lower(column_name) column_name,
	seq_in_index column_position
FROM
	information_schema.statistics
WHERE
	table_schema = 'employees'
AND table_name = 'employees'
AND index_name = 'primary';

## 查看某一个库下某一个表的注释
SELECT
	table_comment comments
FROM
	information_schema.TABLES
WHERE
	table_schema = 'employees'
AND table_name = 'employees';

## 查看某一个库下某一个表的列的注释
SELECT
	lower(column_name) column_name,
	column_comment comments
FROM
	COLUMNS
WHERE
	table_schema = 'employees'
AND table_name = 'employees';

oracle部分：

#table structure:
SELECT
	lower(table_name) table_name,
	TEMPORARY,
	tablespace_name,
	num_rows,
	duration,
	'ORACLE' table_type,
	partitioned,
	(
		SELECT
			ceil(sum(bytes) / 1024 / 1024)
		FROM
			dba_segments b
		WHERE
			a. OWNER = b. OWNER
		AND a.table_name = b.segment_name
	) AS store_capacity
FROM
	dba_tables a
WHERE
	OWNER = ?
AND table_name NOT LIKE 'TMP%';

SELECT
	lower(column_name) column_name,
	column_id position,
	data_type,
	data_length,
	data_precision,
	data_scale,
	nullable,
	data_default default_value,
	default_length
FROM
	dba_tab_columns
WHERE
	OWNER = ?
AND table_name = ?;

# index
SELECT
	lower(index_name) index_name,
	index_type type
FROM
	dba_indexes
WHERE
	OWNER = ?
AND table_name = ?
AND index_name NOT LIKE 'SYS_IL%';

SELECT
	lower(column_name) column_name,
	column_position,
	descend
FROM
	dba_ind_columns
WHERE
	table_owner = ?
AND table_name = ?
AND index_name = ?;

#collect description
SELECT
	comments
FROM
	dba_tab_comments
WHERE
	OWNER = ?
AND table_name = ?;

SELECT
	lower(column_name) column_name,
	comments
FROM
	dba_col_comments
WHERE
	OWNER = ?
AND table_name = ?;

#database
SELECT
	lower(username) username
FROM
	dba_users
WHERE
	username NOT IN (
		'STDBYPERF',
		'READONLY',
		'APPQOSSYS',
		'ANYSQL',
		'DBFLASH',
		'SYS',
		'SYSTEM',
		'MONITOR',
		'TBSEARCH',
		'MANAGER',
		'SYSMAN',
		'EXFSYS',
		'WMSYS',
		'DIP',
		'TSMSYS',
		'ORACLE_OCM',
		'OUTLN',
		'DBSNMP',
		'PERFSTAT',
		'SEARCH',
		'TOOLS',
		'TBDUMP',
		'DMSYS',
		'XDB',
		'ANONYMOUS',
		'DEV_DDL'
	);

#segsize
SELECT
	round(sum(bytes) / 1024 / 1024, 0) mbytes
FROM
	dba_segments
WHERE
	OWNER = ?
AND segment_name = ?;

关于oralce中的segements，可以参考一下这个系列文章。

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

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

转载于:https://my.oschina.net/zimingforever/blog/64145