How to update flag bit in mysql query?

Question

This is my sql query. In flag(00000) each bit position has a different specification, for example. change 4th bit position to 1 when user is inactive . Here the flag is the varchar (String) data type.

 $sql="select flag from user where id =1" 

I got

 flag=10001 #it may be flag="00001" or flag="00101" 

I want to update the second bit of this flag to 1.

 $sql="update user set flag='-1---' where id=1" #it may be flag='11001' or flag='01001' or flag='01110' 

Actually, I want to update the second bit of this flag to 1, but without updating, like flag = '11001'.I want to do something like this.

 $sql="update user set flag='--change(flag,2bit,to1)--' where id =1" #this is wrong 

What can I do for this using just one SQL query? Is it possible?

Answer 1

 update user set flag = lpad(conv((conv(flag, 2, 10) | 1 << 3), 10, 2), 5, '0') where id = 1 

• conv(flag, 2, 10) converts the flag string from binary to decimal.
• 1 << 3 shifts 1 bit 3 binary places to the left
• | executes binary OR of this to set this bit. This arithmetic operation automatically forces a decimal string to a number; you can use explicit CAST if you want.
• conv(..., 10, 2) converts a decimal string back to a binary string
• lpad(..., 5, '0') adds leading zeros to a 5-character string

FIDDLE DEMO

To set the bit to 0, you use:

 set flag = lpad(conv((conv(flag, 2, 10) & ~(1 << 3)), 10, 2), 5, '0') 

Answer 2

do you want to use bitwise or operator |

 update user set flag = flag | (1 << 1) where id =1 

If the flag is 101, now there will be 111

If the flag was equal to 000, now it will be 010

1 <1 change 1 to one bit - create 10 (binary 2)

edit - not verified, but use

 update user set flag = cast(cast(flag AS SIGNED) | (1 << 1) AS CHAR) where id =1 

Answer 3

If you intend to use VARCHAR, you better use the string management functions: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

 UPDATE user SET flag = CONCAT(LEFT(flag, 1), '1', RIGHT(flag, 3)) WHERE id = 1 

However, you probably want to convert this field to INT so that you can use the bit functions: http://dev.mysql.com/doc/refman/5.0/en/bit-functions.html