如何在MySQL 5.1中将一个int转换为一点？ [英] How can I cast an int to a bit in MySQL 5.1?

问题描述

我正在从SQL Server转换到MySQL 5.1，似乎试图使用select语句创建一个表，使该列有点。

理想情况下，以下操作将起作用：

  CREATE TABLE myNewTable AS 
 SELECT cast（myIntThatIsZeroOrOne as bit）AS myBit 
 FROM myOldtable 
  

但是sql在转换时有点不爽。

解决方案

你可以选择一个int列（我知道只有0和1）不能！

CAST和CONVERT只适用于：

• BINARY [ ）]


• CHAR [（N）]


• DATE


• DATETIME


• DECIMAL [（M [，D]）]


• 已签署[INTEGER]


• TIME


• UNSIGNED [INTEGER]

没有空间：BIT，BITINT，TINYINT，MEDIUMINT，BIGINT，SMALLINT， p>

但是，您可以创建自己的函数cast_to_bit（n）：

  DELIMITER $$ 
 
创建函数cast_to_bit（N INT）RETURNS位（1）
 BEGIN 
返回N; 
 END 
  

要自行尝试，您可以创建具有多个转换的视图：

  CREATE VIEW view_bit AS 
 SELECT 
 cast_to_bit（0），
 cast_to_bit（1） b $ b cast_to_bit（FALSE），
 cast_to_bit（TRUE），
 cast_to_bit（b'0'），
 cast_to_bit（b'1'），
 cast_to_bit ），
 cast_to_bit（2 = 2）
  

...然后描述它！ / p>

  DESCRIBE view_bit; 
  

Ta-dah！现在每个人都是位（1）！

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

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?

解决方案

You cannot!

CAST and CONVERT only work to:

• BINARY[(N)]
• CHAR[(N)]
• DATE
• DATETIME
• DECIMAL[(M[,D])]
• SIGNED [INTEGER]
• TIME
• UNSIGNED [INTEGER]

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

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)

... and then describe it!

DESCRIBE view_bit;

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

这篇关于如何在MySQL 5.1中将一个int转换为一点？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！