翻转BIT数据类型的值,并将其设为1表示NULL值

时间:2021-07-25 17:03:16

I have a table like this:

我有这样一张桌子:

// numbers
+---------+------------+
|    id   |    numb    |
+---------+------------+
| int(11) |   bit(1)   |
+---------+------------+
| 1       | 1          |
| 2       | 1          |
| 3       | 0          |
| 4       | NULL       |
+---------+------------+

And here is my query:

这是我的查询:

UPDATE numbers SET numb = numb ^ b'1';

And here is current output:

这是当前的输出:

// numbers
+---------+------------+
|    id   |    numb    |
+---------+------------+
| int(11) |   bit(1)   |
+---------+------------+
| 1       | 0          |
| 2       | 0          |
| 3       | 1          |
| 4       | NULL       |
+---------+------------+

And here is expected output:

这是预期的输出:

// numbers
+---------+------------+
|    id   |    numb    |
+---------+------------+
| int(11) |   bit(1)   |
+---------+------------+
| 1       | 0          |
| 2       | 0          |
| 3       | 1          |
| 4       | 1          |
+---------+------------+

As you see, All I'm trying to do is making 1 the result of NULL ^ b'1'. (current result is NULL). How can I do that?

正如你所看到的,我所要做的就是使1为NULL ^ b'1'的结果。 (当前结果为NULL)。我怎样才能做到这一点?

1 个解决方案

#1


1  

The task combines two problems:

该任务结合了两个问题:

  • Flipping a bit, and
  • 翻转一下,然后

  • Dealing with NULL
  • 处理NULL

You can combine bit-toggling solution from one of your other Q&As with IFNULL for an easy-to-read solution:

您可以将来自其他Q&As的位切换解决方案与IFNULL结合使用,以获得易于阅读的解决方案:

UPDATE numbers SET numb = IFNULL(numb ^ b'1', 1)

This is a nearly word-for-word translation of your question:

这是您的问题的几乎逐字翻译:

  • "flip the value of bit" - numb ^ b'1'
  • “翻转位的价值” - 麻木^ b'1'

  • "or set 1 if it's NULL" - IFNULL(..., 1)
  • “或如果它为NULL则设置为1” - IFNULL(...,1)

#1


1  

The task combines two problems:

该任务结合了两个问题:

  • Flipping a bit, and
  • 翻转一下,然后

  • Dealing with NULL
  • 处理NULL

You can combine bit-toggling solution from one of your other Q&As with IFNULL for an easy-to-read solution:

您可以将来自其他Q&As的位切换解决方案与IFNULL结合使用,以获得易于阅读的解决方案:

UPDATE numbers SET numb = IFNULL(numb ^ b'1', 1)

This is a nearly word-for-word translation of your question:

这是您的问题的几乎逐字翻译:

  • "flip the value of bit" - numb ^ b'1'
  • “翻转位的价值” - 麻木^ b'1'

  • "or set 1 if it's NULL" - IFNULL(..., 1)
  • “或如果它为NULL则设置为1” - IFNULL(...,1)