Home  >  Article  >  Database  >  How to Convert INT to BIT in MySQL 5.1?

How to Convert INT to BIT in MySQL 5.1?

Linda Hamilton
Linda HamiltonOriginal
2024-11-01 16:01:021080browse

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn