Create a MySQL view that contains a static BIT value

Question

I'm trying to create a view in MySQL 5.6.24 and I need a BIT column. The table I'm creating the view from does not have a BIT column.

My create view statement goes like this,

create view my_view as select id, version, description, 
    b'1' as active from my_table;

The view is created however the active field is of type VARBINARY.

I've also tried (1) as active which produces an INT. true as active also creates an INT.

Is there a way to create a BIT column in a view like this?

Sorry @JuanCarlosOropeza I didn't realize that question was related. — wsams, Nov 02 '15 at 21:45
dont worry, I didnt neither took me a while find the correct answer — Juan Carlos Oropeza, Nov 02 '15 at 21:50

score 1 · Accepted Answer · answered Nov 02 '15 at 21:37

You cannot but you can create a function

CREATE FUNCTION cast_to_bit (N INT) RETURNS bit(1)
BEGIN
    RETURN N;
END


CREATE VIEW view_bit AS
    SELECT
        cast_to_bit(0),
        cast_to_bit(1),
        cast_to_bit(FALSE),
        cast_to_bit(TRUE),
        cast_to_bit(b'0'),
        cast_to_bit(b'1'),
        cast_to_bit(2=3),
        cast_to_bit(2=2)

Create a MySQL view that contains a static BIT value

1 Answers1