How to Successfully Cast INT to BIT in MySQL 5.1?

Casting INT to BIT in MySQL 5.1: An Impossible Task

In transitioning from SQL Server to MySQL 5.1, a challenge arises when attempting to create a table with a bit column using a select statement. The intuitive approach of casting an integer column to a bit, as seen in the following example, fails:

CREATE TABLE myNewTable AS
SELECT cast(myIntThatIsZeroOrOne as bit) AS myBit
FROM myOldtable

MySQL expresses disapproval at attempts to cast to bit, prompting the question: How can we instruct it to treat an integer column containing only 0s and 1s as a bit?

The Limitations of Casting

Unfortunately, there is no direct solution. CAST and CONVERT functions in MySQL 5.1 support casting only to specific data types, excluding BIT and related integer types.

Custom Function to the Rescue

Despite the limitation, it is possible to create a custom function to perform the casting. Here's how:

DELIMITER $$

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

Visual Verification

To confirm the functionality of the cast_to_bit function, a view can be created:

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)

Examining the view with DESCRIBE reveals that all values have been converted to bit(1).

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