MySQL，如何在查询中将True/False转换为1/0

Question

如何在MySQL查询中将布尔值转换为整数。我已经尝试了很多方法，但都没有成功。我只是想将true/false的“value”列分别转换为1和0并插入到“finalvalue”列中。请参见屏幕截图链接。

MySQL查询：

SELECT id, value, finalvalue FROM table;

SELECT
  UNIX_TIMESTAMP(timestamp) as time_sec,
  topic as metric,
  data as value,
cast(DATA AS SIGNED) as finalvalue
FROM tcl_mqtt_log
WHERE topic = 'jace/home/heatpump/uvlight_ss';

screenshot of mysql_query_result

- Access _ Max

Answer 1

我认为你需要在这里使用一个CASE表达式：

SELECT
    UNIX_TIMESTAMP(timestamp) AS time_sec,
    topic AS metric,
    data AS value,
    CASE WHEN data = 'true' THEN 1 ELSE 0 END AS finalvalue
FROM tcl_mqtt_log
WHERE topic = 'jace/home/heatpump/uvlight_ss';

- Tim Biegeleisen

---

谢谢，可以了。我尝试使用大小写，但格式很关键。我差点就成功了。再次感谢。 - Access _ Max