View the operation methods of objects in MySQL

Let's talk about how to view objects in MySQL. The secret of the text is that it is close to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading this article on how to operate objects in MySQL.

Table:

Select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,ENGINE from information_schema.tables where TABLE_SCHEMA not in ('performance_schema','information_schema','mysql')

View:

Select TABLE_SCHEMA,TABLE_NAME from information_schema.tables where table_type='view'

Index:

SELECT TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,INDEX_TYPE FROM INFORMATION_SCHEMA.STATISTICS

Stored procedure:

Select db,name from mysql.proc

Function:

Select * from mysql.func

Events:

Select db,name from mysql.event

Trigger:

Select TRIGGER_SCHEMA,TRIGGER_NAME from information_ schema.`TRIGGERS`

Constraint

Select TABLE_SCHEMA,TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from information_ schema.`TABLE _ CONSTRAINTS`

Foreign key:

Select CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME from `information_ schema`.referential _ CONSTRAINTS

Determine whether all tables have primary keys:

SELECT

TABLE_SCHEMA,table_name

FROM

Information_schema.TABLES

WHERE

Table_schema not in ('information_schema','performance_schema','sys','mysql') AND

TABLE_NAME NOT IN (

SELECT

Table_name

FROM

Information_schema.table_constraints t

JOIN information_schema.key_column_usage k USING (

Constraint_name

Table_schema

Table_name

)

WHERE

T.constraint_type = 'PRIMARY KEY'

AND t.table_schema not in ('information_schema','performance_schema','sys','mysql')

);

Statistics of the amount of data in MySQL database

SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS,AVG_ROW_LENGTH,concat (ROUND (DATA_LENGTH/1024/1024/1024,2),'G') "DATA_LENGTH", concat (ROUND (INDEX_LENGTH/1024/1024/1024,2),'G') "INDEX_LENGTH", concat (ROUND ((INDEX_LENGTH/1024/1024/1024) + (DATA_LENGTH/1024/1024/1024), 2),'G') "Total", TABLE_COLLATION,CREATE_TIME FROM

INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'xxx' ORDER BY TABLE_ROWS DESC

For the above view of the operation methods of objects in MySQL, is there anything you don't understand? Or if you want to know more about it, you can continue to follow our industry information section.