Mysql特殊查询技巧

今天有个群友在群里提出了类似的疑问：

select * from USER_PRIVILEGES where GRANTEE = char(111)+char(1);
+---------------------------+---------------+--------------------------+--------------+
| GRANTEE                   | TABLE_CATALOG | PRIVILEGE_TYPE           | IS_GRANTABLE |
+---------------------------+---------------+--------------------------+--------------+
| 'mariadb.sys'@'localhost' | def           | USAGE                    | NO           |
| 'mysql'@'localhost'       | def           | SELECT                   | YES          |
| 'mysql'@'localhost'       | def           | INSERT                   | YES          |
| 'mysql'@'localhost'       | def           | UPDATE                   | YES          |
| 'mysql'@'localhost'       | def           | DELETE                   | YES          |
| 'mysql'@'localhost'       | def           | CREATE                   | YES          |
...

 GRANTEE = char(111)+char(1) 可以查出全结果。

思考了一下，发现挺有趣的。记录下来。

char(1)+char(1) 表示0

 select (char(1)+char(1)) from USER_PRIVILEGES;
+-------------------+
| (char(1)+char(1)) |
+-------------------+
|                 0 |
|                 0 |
|                 0 |

GRANTEE=0 表示1

select GRANTEE=0 from USER_PRIVILEGES;
+-----------+
| GRANTEE=0 |
+-----------+
|         1 |
|         1 |
|         1 |

where 1 表示全结果

select count(*) from USER_PRIVILEGES where 1;
+----------+
| count(*) |
+----------+
|       77 |
+----------+

因此就可以用 GRANTEE = char(111)+char(1) 查出全结果了，有点绕。