Kenhuang Academy
  • English

      • MySQL Metadata

      Overview

      Metadata is data about data. MySQL provides extensive metadata through system databases and information schema tables, allowing you to query database structure, table definitions, indexes, and more.

      Metadata Sources

      • information_schema: Metadata about all databases
      • mysql: System database with grants
      • performance_schema: Performance metrics
      • sys: Views on performance_schema
      • SHOW Commands: Display metadata directly

      information_schema Database

      Overview

      -- Access information_schema
USE information_schema;

-- List all information_schema tables
SHOW TABLES;

-- Key information_schema tables
-- SCHEMATA: Database information
-- TABLES: Table information
-- COLUMNS: Column information
-- KEY_COLUMN_USAGE: Key usage
-- STATISTICS: Index information
-- USER_PRIVILEGES: User privileges
-- TABLE_CONSTRAINTS: Constraints

      Database Metadata

      -- List all databases
SELECT schema_name AS database_name
FROM information_schema.SCHEMATA
WHERE schema_name NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');

-- Database character sets
SELECT 
    schema_name,
    default_character_set_name,
    default_collation_name
FROM information_schema.SCHEMATA;

-- Database sizes
SELECT 
    table_schema AS database_name,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY size_mb DESC;

      Table Metadata

      -- List tables in current database
SELECT 
    table_name,
    table_type,
    engine,
    row_format,
    table_rows,
    avg_row_length,
    data_length,
    index_length,
    auto_increment,
    create_time,
    update_time,
    check_time,
    table_collation
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
ORDER BY table_name;

-- Filter by engine
SELECT table_name
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
  AND engine = 'InnoDB';

-- Filter by table type
SELECT table_name, table_type
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
  AND table_type = 'BASE TABLE';

      Column Metadata

      -- Get table columns
SELECT 
    column_name,
    ordinal_position,
    column_default,
    is_nullable,
    data_type,
    character_maximum_length,
    numeric_precision,
    numeric_scale,
    character_set_name,
    collation_name,
    column_type,
    column_key,
    extra
FROM information_schema.COLUMNS
WHERE table_schema = DATABASE()
  AND table_name = 'users'
ORDER BY ordinal_position;

-- Find nullable columns
SELECT table_name, column_name, is_nullable
FROM information_schema.COLUMNS
WHERE table_schema = DATABASE()
  AND is_nullable = 'YES';

-- Find auto increment columns
SELECT table_name, column_name
FROM information_schema.COLUMNS
WHERE table_schema = DATABASE()
  AND extra LIKE '%auto_increment%';

      Index Metadata

      -- List indexes
SELECT 
    index_name,
    column_name,
    seq_in_index,
    cardinality,
    index_type,
    is_unique
FROM information_schema.STATISTICS
WHERE table_schema = DATABASE()
  AND table_name = 'users'
ORDER BY index_name, seq_in_index;

-- Index details
SELECT 
    s.table_name,
    s.index_name,
    s.column_name,
    s.cardinality,
    t.table_rows,
    ROUND(s.cardinality / t.table_rows * 100, 2) AS selectivity_pct
FROM information_schema.STATISTICS s
JOIN information_schema.TABLES t 
    ON s.table_name = t.table_name 
    AND s.table_schema = t.table_schema
WHERE s.table_schema = DATABASE()
ORDER BY s.index_name, s.seq_in_index;

-- Unique indexes
SELECT DISTINCT index_name, column_name
FROM information_schema.STATISTICS
WHERE table_schema = DATABASE()
  AND table_name = 'users'
  AND non_unique = 0;

      SHOW Commands

      Database SHOW Commands

      -- Show databases
SHOW DATABASES;
SHOW DATABASES LIKE 'myapp%';
SHOW DATABASES WHERE schema_name LIKE '%dev%';

-- Show current database
SELECT DATABASE();

-- Show character sets
SHOW CHARACTER SET;
SHOW CHARACTER SET LIKE 'utf8%';

-- Show collations
SHOW COLLATION;
SHOW COLLATION LIKE 'utf8mb4%';

-- Show engines
SHOW ENGINES;
SHOW STORAGE ENGINES;

      Table SHOW Commands

      -- Show tables
SHOW TABLES;
SHOW TABLES FROM database_name;
SHOW TABLES LIKE 'user%';
SHOW FULL TABLES;  -- Includes table type

-- Show table structure
DESCRIBE table_name;
DESC table_name;
EXPLAIN table_name;
SHOW COLUMNS FROM table_name;
SHOW FIELDS FROM table_name;

-- Show create statement
SHOW CREATE TABLE table_name;
SHOW CREATE TABLE table_name\G;

-- Show table status
SHOW TABLE STATUS;
SHOW TABLE STATUS LIKE 'users%';
SHOW TABLE STATUS WHERE name = 'users';

      Index SHOW Commands

      -- Show indexes
SHOW INDEX FROM table_name;
SHOW INDEXES FROM table_name;
SHOW KEYS FROM table_name;

-- Show index details
SHOW INDEX FROM users\G;

      Other SHOW Commands

      -- Show users
SHOW GRANTS FOR CURRENT_USER();
SHOW GRANTS FOR 'username'@'hostname';

-- Show privileges
SHOW PRIVILEGES;

-- Show processes
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- Show variables
SHOW VARIABLES;
SHOW VARIABLES LIKE 'innodb%';
SHOW GLOBAL VARIABLES LIKE 'max%';

-- Show status
SHOW STATUS;
SHOW STATUS LIKE 'Connections%';
SHOW GLOBAL STATUS;

-- Show engines
SHOW ENGINES;

      MySQL System Database

      mysql Database

      -- Use mysql database
USE mysql;

-- User information
SELECT user, host FROM mysql.user;

-- Database privileges
SELECT * FROM mysql.db;

-- Table privileges
SELECT * FROM mysql.tables_priv;

-- Column privileges
SELECT * FROM mysql.columns_priv;

-- Proxies
SELECT * FROM mysql.proxies_priv;

      User Metadata

      -- List all users
SELECT user, host FROM mysql.user;

-- User with all hosts
SELECT DISTINCT user FROM mysql.user;

-- User privileges
SELECT * FROM mysql.user WHERE user = 'root';

-- Password hashes
SELECT user, host, plugin, authentication_string
FROM mysql.user;

      Performance Metadata

      performance_schema Database

      -- Use performance_schema
USE performance_schema;

-- Show available instruments
SELECT * FROM setup_instruments LIMIT 10;

-- Enable instruments
UPDATE setup_instruments 
SET enabled = 'YES', timed = 'YES'
WHERE name LIKE 'statement/%';

-- Show consumers
SELECT * FROM setup_consumers;

      Performance Queries

      -- Wait events
SELECT * FROM events_waits_current
LIMIT 10;

-- Statement performance
SELECT 
    digest_text AS statement,
    count_star AS exec_count,
    avg_timer_wait / 1000000000000 AS avg_wait_ms
FROM events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

-- Table I/O
SELECT 
    object_name AS table_name,
    count_read,
    count_write,
    sum_timer_read / 1000000000000 AS read_time_ms
FROM file_summary_by_instance
WHERE object_name IS NOT NULL
ORDER BY sum_timer_read DESC
LIMIT 10;

      sys Database

      -- Use sys database (MySQL 5.7+)
USE sys;

-- User statistics
SELECT * FROM sys.user_summary;

-- Table statistics
SELECT * FROM sys.schema_unused_tables;
SELECT * FROM sys.schema_redundant_indexes;

-- Wait analysis
SELECT * FROM sys.wait_classes_global_by_latency;
SELECT * FROM sys.waits_global_by_latency;

-- Memory usage
SELECT * FROM sys.memory_global_by_current_bytes;

      Metadata Queries

      Search for Tables

      -- Find tables with specific column
SELECT table_name
FROM information_schema.COLUMNS
WHERE table_schema = DATABASE()
  AND column_name = 'user_id';

-- Find tables without primary key
SELECT t.table_name
FROM information_schema.TABLES t
LEFT JOIN information_schema.KEY_COLUMN_USAGE k 
    ON t.table_name = k.table_name
    AND t.table_schema = k.table_schema
WHERE t.table_schema = DATABASE()
  AND k.constraint_name = 'PRIMARY';

-- Find tables with specific engine
SELECT table_name
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
  AND engine = 'InnoDB';

      Search for Columns

      -- Find all columns named 'email'
SELECT 
    table_name,
    column_name,
    data_type
FROM information_schema.COLUMNS
WHERE table_schema = DATABASE()
  AND column_name = 'email';

-- Find all VARCHAR columns
SELECT table_name, column_name, character_maximum_length
FROM information_schema.COLUMNS
WHERE table_schema = DATABASE()
  AND data_type = 'varchar';

-- Find all datetime columns
SELECT table_name, column_name
FROM information_schema.COLUMNS
WHERE table_schema = DATABASE()
  AND data_type IN ('date', 'datetime', 'timestamp');

      Search for Constraints

      -- Find foreign keys
SELECT 
    table_name,
    column_name,
    referenced_table_name,
    referenced_column_name
FROM information_schema.KEY_COLUMN_USAGE
WHERE table_schema = DATABASE()
  AND referenced_table_schema IS NOT NULL;

-- Find unique constraints
SELECT 
    constraint_name,
    table_name,
    column_name
FROM information_schema.TABLE_CONSTRAINTS c
JOIN information_schema.KEY_COLUMN_USAGE k 
    ON c.constraint_name = k.constraint_name
WHERE c.table_schema = DATABASE()
  AND c.constraint_type = 'UNIQUE';

      Metadata Functions

      Database Information Functions

      -- Current database
SELECT DATABASE();
SELECT SCHEMA();

-- Current user
SELECT USER();
SELECT CURRENT_USER();
SELECT SESSION_USER();

-- Connection ID
SELECT CONNECTION_ID();

-- Last insert ID
SELECT LAST_INSERT_ID();

      Table Information Functions

      -- Row count
SELECT ROW_COUNT();

-- Auto increment value
SELECT @@auto_increment_increment;

-- Check table existence
SELECT COUNT(*) 
FROM information_schema.TABLES 
WHERE table_schema = DATABASE()
  AND table_name = 'users';

      Practical Examples

      Database Documentation

      -- Generate table documentation
SELECT 
    t.table_name,
    t.table_comment,
    t.engine,
    c.column_name,
    c.data_type,
    c.is_nullable,
    c.column_default,
    c.column_comment
FROM information_schema.TABLES t
LEFT JOIN information_schema.COLUMNS c 
    ON t.table_name = c.table_name 
    AND t.table_schema = c.table_schema
WHERE t.table_schema = DATABASE()
ORDER BY t.table_name, c.ordinal_position;

      Schema Comparison

      -- Compare tables between databases
SELECT 
    COALESCE(a.table_name, b.table_name) AS table_name,
    CASE 
        WHEN a.table_name IS NULL THEN 'Only in B'
        WHEN b.table_name IS NULL THEN 'Only in A'
        ELSE 'In both'
    END AS status
FROM (
    SELECT table_name FROM information_schema.TABLES 
    WHERE table_schema = 'database_a'
) a
FULL OUTER JOIN (
    SELECT table_name FROM information_schema.TABLES 
    WHERE table_schema = 'database_b'
) b ON a.table_name = b.table_name;

      Unused Indexes

      -- Find potentially unused indexes
SELECT 
    t.table_name,
    s.index_name,
    s.column_name,
    s.cardinality,
    t.table_rows
FROM information_schema.STATISTICS s
JOIN information_schema.TABLES t 
    ON s.table_name = t.table_name
WHERE s.table_schema = DATABASE()
  AND s.index_name != 'PRIMARY'
  AND s.cardinality IS NULL;

      Foreign Key Report

      -- Generate foreign key documentation
SELECT 
    kcu.table_name AS child_table,
    kcu.column_name AS child_column,
    kcu.referenced_table_name AS parent_table,
    kcu.referenced_column_name AS parent_column,
    rc.update_rule,
    rc.delete_rule
FROM information_schema.KEY_COLUMN_USAGE kcu
JOIN information_schema.REFERENTIAL_CONSTRAINTS rc 
    ON kcu.constraint_name = rc.constraint_name
WHERE kcu.table_schema = DATABASE()
ORDER BY kcu.table_name, kcu.constraint_name;

      Summary

      MySQL metadata provides:

      • Database Information: Names, sizes, engines
      • Table Information: Columns, rows, indexes
      • Column Information: Types, defaults, constraints
      • Index Information: Keys, cardinality, uniqueness
      • Performance Data: Queries, waits, I
      • **User

      Use metadata for database administration, documentation, optimization, and monitoring.

      Previous: Clone Tables

      Next: Sequences