如何在 MySQL 5.1 中将 int 转换为一点?

2021-12-31 00:00:00 bit casting mysql mysql-5.1

我正在从 SQL Server 过渡到 MySQL 5.1，并且似乎在尝试使用 select 语句创建表时遇到了困难，以便该列有点.

I am transitioning from SQL Server to MySQL 5.1 and seem to be tripped up trying to create a table using a select statement so that the column is a bit.

理想情况下，以下方法可行:

Ideally the following would work:

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

然而，sql 对转换有点不满意.我如何告诉它选择一个 int 列(我知道它只有 0 和 1)?

However sql is very unhappy at casting as a bit. How can I tell it to select an int column (which I know only has 0s and 1s) as a bit?

推荐答案

你不能！

CAST 和 CONVERT 仅适用于:

CAST and CONVERT only work to:

  • 二进制[(N)]
  • CHAR[(N)]
  • 日期
  • 日期时间
  • 十进制[(M[,D])]
  • 签名 [整数]
  • 时间
  • 无符号[整数]

没有空间用于:BIT、BITINT、TINYINT、MEDIUMINT、BIGINT、SMALLINT、......

No room for: BIT, BITINT, TINYINT, MEDIUMINT, BIGINT, SMALLINT, ...

但是，您可以创建自己的函数 cast_to_bit(n):

However, you can create your own function cast_to_bit(n):

DELIMITER $$

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

要自己尝试，您可以创建包含多种转换的视图，例如:

To try it yourself, you can create view with several conversions like:

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)

...然后描述它！

DESCRIBE view_bit;

呸！！现在每个人都位(1)！！！

Ta-dah!! Everyone is bit(1) now!!!

相关文章