How to Convert INT to BIT in MySQL 5.1?

Casting an Int to a Bit in MySQL 5.1

While transitioning from SQL Server to MySQL 5.1, attempts to cast an INT column containing only 0s and 1s to a BIT column using the CAST function fail. MySQL does not support casting INTs to BITs.

Resolving the Issue

Since direct casting is not feasible, an alternative solution is to create a custom function to convert INTs to BITs:

<code class="sql">DELIMITER $$

CREATE FUNCTION cast_to_bit (N INT) RETURNS bit(1)
BEGIN
    RETURN N;
END
$$</code>

Example Usage

To demonstrate the function, create a view that converts various values to BITs:

<code class="sql">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)</code>

Inspecting the view's schema using DESCRIBE will show that all columns are now BITs:

<code class="sql">DESCRIBE view_bit;</code>

The above is the detailed content of How to Convert INT to BIT in MySQL 5.1?. For more information, please follow other related articles on the PHP Chinese website!