How can I cast an int to a bit in MySQL 5.1?

by

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!!!

More Related Contents:

  1. Cast int to varchar
  2. What is the difference between BIT and TINYINT in MySQL?
  3. CAST to DECIMAL in MySQL
  4. Using LIMIT within GROUP BY to get N results per group?
  5. How can I output MySQL query results in CSV format?
  6. Change limit for “Mysql Row size too large”
  7. Converting a date in MySQL from string field
  8. Quick easy way to migrate SQLite3 to MySQL? [closed]
  9. Ordering by specific field value first
  10. CHECK constraint in MySQL is not working
  11. How to change the CHARACTER SET (and COLLATION) throughout a database?
  12. Equivalent of Oracle’s RowID in MySQL
  13. How can I get around MySQL Errcode 13 with SELECT INTO OUTFILE?
  14. Error Code 1292 – Truncated incorrect DOUBLE value – Mysql
  15. Getting all parent rows in one SQL query
  16. Loading utf-8 encoded text into MySQL table
  17. Difference between two table structure
  18. How to retrieve JSON data from MySQL?
  19. MySQL equivalent of Oracle’s SEQUENCE.NEXTVAL
  20. Is incrementing a field in MySQL atomic?
  21. Delete from one table with join
  22. How to store decimal in MySQL?
  23. MySQL column type “TIMESTAMP” implicitly includes “NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP”
  24. When to use a left outer join?
  25. MySQL: Compare BETWEEN time
  26. MySQL: how to do row-level security (like Oracle’s Virtual Private Database)?
  27. Query to count the number of tables I have in MySQL
  28. INSERT INTO Table from multiple tables
  29. Mysql How do you create a clustered index?
  30. Counting associated entries with Sequelize

Leave a Comment