SQL新手求助：查询'meta'表列名与数据类型及报错问题

Hey there! Let's work through this since the INFORMATION_SCHEMA approach isn't working in your database setup.

From the query you shared that successfully lists tables in sprl_db, it looks like your database uses a system table structure with schemas, tables, and likely a columns table to store column metadata. Here's how to adapt that to get your meta table's column details:

Basic Query for Column Names and Data Types #

Run this SQL statement to pull the column names and their data types directly:

SELECT 
    columns.name AS column_name,
    columns.data_type
FROM 
    columns
JOIN 
    tables ON columns.table_id = tables.id
JOIN 
    schemas ON tables.schema_id = schemas.id
WHERE 
    schemas.name = 'sprl_db'
    AND tables.name = 'meta';

What this does: #

• Joins the schemas table to target your specific sprl_db database
• Links to the tables table to narrow down to just the meta table
• Pulls column details from the columns table, which stores each column's name and data type

If Data Types Show as Numeric Codes #

Some databases use internal numeric IDs for data types instead of readable names. If you see numbers instead of type names like VARCHAR or INT, try joining with a types system table (common in setups like this):

SELECT 
    columns.name AS column_name,
    types.name AS data_type
FROM 
    columns
JOIN 
    tables ON columns.table_id = tables.id
JOIN 
    schemas ON tables.schema_id = schemas.id
JOIN 
    types ON columns.type_id = types.id
WHERE 
    schemas.name = 'sprl_db'
    AND tables.name = 'meta';

This maps the type ID from the columns table to the human-readable name in the types table.

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