Statements used to obtain MySQL table information

The SQL statements described below can obtain important information such as MySQL table information and foreign key relationships, which is of great value. If you have encountered similar problems in MySQL table information, take a look.

 
 

  
  
/* Obtain table information */
  
  
Select table_Comment as Script, table_Name as TableName, 0 as id, Create_Time as createDate,
  
  
CASE table_Type WHEN 'base table' then' U 'when' VIEW 'then' V 'else table_Type END as tableType,
  
  
0 as Category from tables;
  
  
 
  
  
/* Obtain the foreign key relationship */
  
  
SELECT CONSTRAINT_Name as Script, CONSTRAINT_Name as TableName, 0 as id,
  
  
CURRENT_TIMESTAMP as createDate,
  
  
CASE CONSTRAINT_TYPE
  
  
WHEN 'forn n key' THEN 'F'
  
  
WHEN 'Primary key'then 'K'
  
  
ELSE CONSTRAINT_TYPE
  
  
End as tableType, 0 as Category From TABLE_CONSTRAINTS;
  
  
 
  
  
SELECT Table_Name, Column_Name, Column_Type, Column_Key, Column_Comment From Columns;
  
  
 
  
  
/* Obtain column information */
  
  
Select table_name as tablename, column_Name as columnName, column_type as columnType,
  
  
0 as columntypeindex, CHARACTER_OCTET_LENGTH as length, Numeric_Precision as decimaldigits,
  
  
Column_comment as Script, column_default as defaultvalue, is_nullable as isnullable,
  
  
Case extra when 'Auto _ secret' then 1 else 0 end as IsMarking,
  
  
0 as colid, 'U' as ObjectType, data_type from columns;
 
 

The preceding statement is used to obtain MySQL table information.

MySQL statements commonly used in PHP

How to find slow SQL statements in mysql

How to use mysql UPDATE statements

General INSERT usage in MySQL

MySQL index type