如何访问MySQL数据字典？执行mysql.routines查询遇3554权限问题求助

Hey there, let's tackle your two questions step by step—accessing MySQL's data dictionary and fixing that frustrating 3554 error when trying to view stored procedures.

MySQL stores its metadata (data dictionary) in two primary places, with recommended and not-so-recommended ways to access it:

• Use INFORMATION_SCHEMA views (Recommended)
  This is the standard, cross-version compatible approach. INFORMATION_SCHEMA is a virtual database that exposes read-only views for all your metadata needs. Examples:
  • List all tables in a specific database: SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database_name';
  • View column details for a table: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'your_table_name';
    This method avoids direct interaction with internal system tables, which can change between MySQL versions.
• Direct access to mysql system tables (Not Recommended for 8.0+)
  In older MySQL versions (pre-8.0), you could query tables like mysql.tables or mysql.columns directly. But starting with MySQL 8.0, most of these tables are now part of the internal data dictionary—they're hidden, not meant for direct queries, and accessing them will throw errors like the one you're seeing.

That error is expected behavior in MySQL 8.0+, even for the root user. Here's why and how to resolve it:

• Why this happens
  The mysql.routines table was replaced with an internal data dictionary component in MySQL 8.0. Directly querying it is blocked by design to protect the integrity of the data dictionary. Even with full privileges, you can't access these internal tables directly.
• The correct way to view stored procedures
  Use the INFORMATION_SCHEMA.ROUTINES view instead. This view exposes all stored procedures and functions, and it's fully accessible to users with appropriate permissions. Here's the query:

  -- View all stored procedures
  SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
  WHERE ROUTINE_TYPE = 'PROCEDURE';
  
  -- If you also want to include stored functions, use this:
  SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
  WHERE ROUTINE_TYPE IN ('PROCEDURE', 'FUNCTION');
  

• Double-check permissions (just to be safe)
  While root should have full access by default, if you're still having issues, you can explicitly grant SELECT access to INFORMATION_SCHEMA with this command:

  GRANT SELECT ON INFORMATION_SCHEMA.* TO 'root'@'localhost';
  

  Replace 'localhost' with your connection host if needed.

内容的提问来源于stack exchange，提问作者Ethan Mofokeng