一update把性别取反的6种方法
| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |要求 : 把sex列里的m和f互换
解法 : 速度从高到底
1.
异或
UPDATE salary SET sex = CHAR(ASCII(sex) ^ ASCII("m") ^ ASCII("f"));
2.
if函数
UPDATE salary SET sex = IF(sex = "m", "f", "m");
3.
ASCII函数和CHAR函数进行字母互转
UPDATE salary SET sex = CHAR(ASCII("m") + ASCII("f") - ASCII(sex));
4.
replace函数
UPDATE salary SET sex = REPLACE("fm", sex, "");
5.
case when
UPDATE salary
SET
sex = CASE sex
WHEN 'm' THEN 'f'
ELSE 'm'
END;
评论区