In MySQL, can I get the column type and and check column values in a single SELECT statement?-CodePudding

I'll start this off by saying I know that there are more practical ways to solve this. It's more of an intellectual curiosity than anything else.

I've inherited a MySQL database where some columns are stored as varchar(5) but actually contain the literals "True" or "False". Changing the structure of the data is not an option right now due to other issues. I'm mapping the columns to an ORM (SQLAlchemy), and I want the column to be mapped to a Boolean data type in the supporting codebase using a type adapter. (I've written this adapter already; it's not the problem.)

To help make the mapping process faster, I'm writing a small query to look at the INFORMATION_SCHEMA table and build a line of Python code defining the column using the ORM's syntax. I cannot assume that the data type varchar(5) is a Boolean column - I need to inspect the contents of that column to see if there are values contained in it besides True and False.

Can I write a query that will both get the column type from INFORMATION_SCHEMA and check the actual values stored in that column?

Here is the query I have so far:

SELECT CONCAT(
    "Column(""",
    col.column_name,
    """, ",
    
    (CASE
        WHEN col.DATA_TYPE = "int" THEN "Integer"
        
        -- Code in question
        WHEN
            col.DATA_TYPE = "varchar"
            AND col.CHARACTER_MAXIMUM_LENGTH = 5
            AND NOT EXISTS(
                -- Doesn't seem to work
                SELECT DISTINCT col.COLUMN_NAME
                FROM col.TABLE_NAME
                WHERE col.COLUMN_NAME NOT IN ("True", "False")
            )
        THEN "BoolStoredAsVarchar"
        
        WHEN col.DATA_TYPE = "varchar" THEN CONCAT("String(", col.CHARACTER_MAXIMUM_LENGTH, ")")
        
        -- Default if it's not a recognized column type
        ELSE col.DATA_TYPE
    END),

    "),"
) AS alchemy
FROM information_schema.columns AS col
WHERE
    col.TABLE_SCHEMA = "my_schema"
    AND col.TABLE_NAME = "my_table"
ORDER BY col.ORDINAL_POSITION;

Running this code gives me a permissions error: Error Code: 1142. SELECT command denied to user 'user'@'host' for table 'table_name'. Presumably it's trying to use col.TABLE_NAME as a literal instead of interpreting it.

I've also tried creating a simple stored procedure and making table_name into a variable. However, replacing the FROM clause inside the EXISTS with a variable name gives me a syntax error instead.

Again, it's easy enough to run the query myself to see what's in that column. I'd just like to know if this is possible, and if so, how to do it.

CodePudding user response：

You can't do what you're trying to do in a single query.

The reason is that table names (or any other identifier) must be fixed in the query at the time it is parsed, which is before it has read any values from tables. Thus you can't read the name of a table as a string from information_schema and also read from the table with that name in the same query.

You must read the table name from information_schema and then use that result to format a second query.

This isn't a problem specific to MySQL. It's true of any SQL implementation.