MySQL查询数据库、表、字段、索引、唯一键、外键、存储过程、视图是否存在

字段、索引、唯一键、外键、存储过程、视图是否存在"/>

MySQL查询数据库、表、字段、索引、唯一键、外键、存储过程、视图是否存在

1、MySQL查询数据库是否存在

SELECT SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = '数据库名';

2、MySQL查询表是否存在

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名';

3、MySQL查询字段是否存在

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名' AND COLUMN_NAME = '字段名';

4、MySQL查询索引是否存在

SHOW INDEX FROM 表名;

5、MySQL查询唯一键是否存在

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名' AND CONSTRAINT_NAME = '唯一键名';

6、MySQL查询外键是否存在

SELECT COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名' AND CONSTRAINT_NAME = '外键名';

7、MySQL查询存储过程是否存在

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = '数据库名' AND ROUTINE_NAME = '存储过程名';

8、MySQL查询视图是否存在

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '视图名';