Suggested way to resolve column name/type in a view

I have the following problem that I’m trying to find the best solution for. Let’s say I have a view such as the following:

CREATE VIEW myView AS (
    SELECT 
        country_code, 
        other_column,
        COUNT(1) as cnt 
    FROM mytable 
         JOIN otherDatabase.otherTable ON (id)
    GROUP BY 1,2 ORDER BY 1 LIMIT 1
)

What would be the fastest way to resolve the field names and types of the view? For example, on the above I am looking to get something along the lines of:

{
    country_code: VARCHAR, 
    other_column: BOOL,
    cnt:          INT
}

The first approach is just to run the query (with a limit, if necessary) and then get the types of the result-set from the driver. The downside of this is what if the query takes 50 minutes to resolve?
The second approach I thought of is to ‘follow’ the columns to get their types and then do some parsing to resolve any expressions/literals/etc. This would involve a lot of code but would be orders of magnitude faster than the above. However, the potential downside of this is we may have access to the view but not have access to a table (possibly in another database on the server) that contains the column type, so it’s possible we might not be able to resolve all field names.

What would be the best way to resolve the types of a view? Note I have tagged this as MySQL, but I’m also wondering if there’s a more generic way to resolve types or if it’s something that is non-standard and more needs to be done on a per-database basis?

Update: I believe the correct answer is just to run a DESCRIBE myView, and that would give me the column names and types without running the query?

Answer

In the current version of MySQL at least, INFORMATION_SCHEMA.COLUMNS holds metadata for views as well as base tables:

mysql> create table mytable (id serial primary key, x int);
Query OK, 0 rows affected (0.01 sec)

mysql> create view v as select * from mytable;

mysql> select column_name, data_type from information_schema.columns where table_name='v';
+-------------+-----------+
| COLUMN_NAME | DATA_TYPE |
+-------------+-----------+
| id          | bigint    |
| x           | int       |
+-------------+-----------+

Advertisement

Answer