All we need is an easy explanation of the problem, so here it is.
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?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
CAST and CONVERT only work to:
- SIGNED [INTEGER]
- 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!
Ta-dah!! Everyone is bit(1) now!!!
Converts numbers between different number bases. Returns a string representation of the number N, converted from base from_base to base to_base. Returns NULL if any argument is NULL. The argument N is interpreted as an integer, but may be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is regarded as a signed number. Otherwise, N is treated as unsigned.
CONV() works with 64-bit precision.
select CONV(9, 10, 2);
Try using case:
CREATE TABLE myNewTable AS SELECT (case myIntThatIsZeroOrOne when 1 then true else false end) AS myBit FROM myOldtable
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂