How to change the data type in a view?

Question

In MySQL is there a way to change the data type of a field, other than the original data type, without having an intermediate table involvement?

Answer 1

Have a look at 12.10. Cast Functions and Operators

The CAST() function takes an expression of any type and produces a result value of a specified type, similar to CONVERT().

CAST(expr AS type)

and

CONVERT(expr,type), CONVERT(expr USING transcoding_name)

Answer 2

CAST(expr AS type) is limited to convert to the following types:

• BINARY
• CHAR
• DATE
• DATETIME
• SIGNED
• TIME
• UNSIGNED

If you need to convert to something else,, e.g., a boolean (BIT (1)), the following will work. Define a function with the return type you need and calculate your value inside the function body.

If you call this function from the definition of a view column, the type of the column will be the return type of your function.