46

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.

Andriy M
  • 23,261
  • 6
  • 60
  • 103
Bruno
  • 563
  • 1
  • 4
  • 5

7 Answers7

43

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 ...
Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
15

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

... `YourField` IS NOT NULL AS `YourField` ...
Roman Newaza
  • 251
  • 2
  • 5
7

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);

poseidonCore
  • 81
  • 1
  • 1
4

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.

Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
4

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;

Dharmendrasinh Chudasama
  • 141
  • 3
3

You also can use "CASE":

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

RDFozz
  • 11,731
  • 4
  • 25
  • 38
Zvezdochka
  • 131
  • 1
0

CAST(IF(col_name = 1, 'true', 'false') AS JSON) may work for some use cases.

As of MySQL 5.7.8 there is support for a native JSON data type defined by RFC 7159. For my use case I had an INT column that was always either 0 or 1, but I needed the output as bool true or false not string "true" or "false".

https://dev.mysql.com/doc/refman/5.7/en/json.html#json-converting-between-types

Aaron
  • 101
  • 2