29

I'm new here so be kind to me. I have the following scenario:

I have many tables which, for the sake of simplicity, are represented in a View in my MySQL database. My problem is that I need a value in this view representing if it is one kind of event or another (a simple boolean), which I tried to achieve with:

`gu`.`StoppingUnitEventME` = `ese`.`MonitoringElement` AS `IsStopingEvent`

The result is represented as int, and so is read by Entity Framework. The problem is that I really need a boolean return value, which I tried to achieve with:

CAST((`gu`.`StoppingUnitEventME` = `ese`.`MonitoringElement`) as boolean) AS `IsStopingEvent` 

This resulted in an error, one that is not displayed to me in MySQL Workbench (I only receive that annoying "You have an error in ...").

Can you guys please help me out?

Tried to solve it in my application, but I really preffer this solved in the database, since it will be used by other software later.

26

Try using the IF function:

SELECT IF(`gu`.`StoppingUnitEventME`=`ese`.`MonitoringElement`, TRUE, FALSE) 
FROM ...

or

SELECT IF(`gu`.`StoppingUnitEventME`=`ese`.`MonitoringElement`, 1, 0) 
FROM ...

Even without the IF function, running

mysql> select ('rolando' = 'rolando') str_compare;
+-------------+
| str_compare |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql>

yield 0 or 1 using the mysql client

The problem is this: CAST() and CONVERT() can only accept and return the following types:

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

Since BOOLEAN is not in this list, it can never be returned by CAST or CONVERT

You could use the IF function to generate strings

SELECT IF(`gu`.`StoppingUnitEventME`=`ese`.`MonitoringElement`, 'TRUE', 'FALSE') 
FROM ...
  • 1
    A boolean is an alias for tinyint. Under no circumstance does IF return a tinyint so I can't see how this answer is right or accepted. – Evan Carroll Dec 11 '17 at 8:06
  • @EvanCarroll My answer is meant to bypass CAST and CONVERT altogether..Using IF function does what his framework won’t do, which is recognize BOOLEAN as an INT (4-byte) rather than TINYINT (1-byte). In this case, you have blame the framework for not working with BOOLEAN values. – RolandoMySQLDBA Dec 11 '17 at 13:33
10

You can do it very simple way, without use of redundant IF() statement:

... `YourField` IS NOT NULL AS `YourField` ...
  • Honestly this is the easiest way. And works for my case. Thanks! – Mike Harrison Aug 12 at 16:36
5

You can also try the classic boolean coercion:

SELECT NOT NOT (whatever);

The good thing about this is that it naturally preserves NULLs, whereas most of the answers here do not.

If you want to coerce NULL to FALSE, then do

SELECT IFNULL(NOT NOT (whatever), FALSE);

  • 1
    I wonder, how does this achieve converting an integer to boolean? – ypercubeᵀᴹ Dec 17 '17 at 15:12
2

You also can use "CASE":

SELECT CASE WHEN yourField=testValue THEN 'TRUE' ELSE 'FALSE' END as boolFieldName

  • You should cast it as boolean, if not it is a char field. – McNets Nov 10 '17 at 14:37
  • Sometimes the consumer of your query wants a character, though – Eric Wilson Oct 11 at 18:00
2

This is currently impossible.

  • MySQL doesn't have a real BOOLEAN type, (or a real array type.. or a real JSON type). It has an alias for TINYINT.
  • Any condition returns an integer. This is the fastest datatype for a CPU, and presumably this implementation detail is reflected here. For instance, 'true' IS TRUE and 1=1 both return 1 as an int.
  • CAST does not provide a TINYINT format.

As far as I can tell you can neither demote a type, nor produce a tinyint in a SELECT.

I would highly suggest migrating to PostgreSQL. It's so much less terrifying... and liberating.

2

Use mysql function CAST_TO_BIT

Examples:

SELECT CAST_TO_BIT(1);

Mysql: SELECT CAST_TO_BIT(0); -> jdbc driver -> Java: Boolean false;

Mysql: SELECT CAST_TO_BIT(1); -> jdbc driver -> Java: Boolean true;

Mysql: SELECT CAST_TO_BIT(NULL); -> jdbc driver -> Java: NULL;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.