How can I define the datatype of a view in MySQL?

Question

I've written a simple aggregation view over a table, and the view returns different data types that the table. This causes some issues on the application side.

Here's a simplified query for the sake of the example:

SELECT id, FLOOR(date_id / 100) month_id, SUM(value) FROM some_table WHERE....;

The problem is that data_id is an INTEGER(11) column, represented in my app as an int32, and the result of FLOOR(date_id / 100) is a BIGINT, represented in my app as an int64.

The type of id also changes, for reasons I can't explain, from int64 to int32.

Is there any way to cast the column to an int32? CAST seemed not to work. I haven't found any documentation for a way to see what the expected data type of a view result set is.

Answer 1

You can hunt down the column definitions in the INFORMATION_SCHEMA. For example, for the table mydb.mytb and the column name needed is called mycol, do this:

SELECT column_type FROM information_schema.columns
WHERE table_schema='mydb'
AND table_name='mytb'
AND column_name='mycol';

For the query with formulas that you have in your question:

SELECT id, FLOOR(date_id / 100) month_id, SUM(value) FROM some_table WHERE....;

Try creating your own empty temp table using your query with a FALSE-evaluated WHERE clause:

CREATE TABLE IF NOT EXISTS test.mycolumnmap
SELECT id, FLOOR(date_id / 100) month_id, SUM(value) value FROM some_table WHERE 1=2;

That table, though empty, has a definite table structure and some columns types defined. Now, just run the same query to get the column types from the temp table:

SELECT column_name,column_type FROM information_schema.columns
WHERE table_schema='test'
AND table_name='mycolumnmap'
AND column_name IN ('id','month_id','value');

Another alternative is to just ask the INFORMATION_SCHEMA about the columns of the view directly:

SELECT column_name,column_type FROM information_schema.columns
WHERE table_schema='test'
AND table_name='view_name';

Give it a Try !!!

Answer 2

This is taken directly from the MySQL web site and might be your solution:

Posted by The Lynxy on March 9 2009 8:56am

MySQL 5.1 returns a 64-bit integer when using CAST(). For those looking to cast a 32-bit signed integer to a 32-bit unsigned integer: you can use the function this way.

Converting -1062731519 to an unsigned integer (this number is the decimal value of 192.168.1.1):

SELECT CAST(('-1062731519' & 0xFFFFFFFF) AS UNSIGNED INTEGER);

Result is 3232235903, which is correct.

http://dev.mysql.com/doc/refman/5.5/en/cast-functions.html