Looking for a typecasting capability in mysql

Question

I'm writing a query that returns whether a record is public or private. If the record is public, the user_id column for that record is zero. Otherwise, it is a positive integer. I'd like the query to return a one if the user_id is greater than zero and a zero if it is zero. In PHP, I would probably just cast it to a binary. This doesn't work in mysql, but I'm hoping that there is something else that is similar. I could always use an if statement, but figured there might be something a little more elegant.

SELECT (binary)user_id as is_private FROM mytable WHERE 1

Answer 1

SELECT  user_id > 0 AS is_private
FROM    mytable

It will return BOOLEAN datatype which in MySQL is just a synonym for TINYINT(1)

Note that it will not work when user_id is a NULL (it will return NULL as well), but since your question only mentions zeros and positive integers, I post this without NULL checking.