我在使用MySQL的IF或CASE语法时遇到问题
该过程如下所示:
我有一个PHP项目调用MySQL查询,比方说PHP传递了变量X.
如果X不为0,则它应该执行类似以下的查询:
SELECT * FROM table
WHERE id = X
但是,如果X为0.那么它应该执行查询以仅选择所有记录:
SELECT * FROM table
我不想在PHP中有条件,该条件根据PHP中条件的结果调用另一个MySQL查询.我希望基于传递给X的变量在SQL查询中执行if / else条件.
解决方法:
我假设$x是从您的PHP派生的,然后:
SELECT * FROM t WHERE IF($x=0, 1, id=$x)
(这是一般性的想法,您必须亲自进行注射等)
编辑
我在这里的另一个答案中找到了不错的代码.所以我很好奇-哪个更快?现在我很高兴我的也是这样:
我的版本:
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.5.27 |
+-----------+
1 row in set (0.00 sec)
现在,测试数据:
mysql> select * from test;
+----+-------+
| id | title |
+----+-------+
| 1 | f |
| 3 | t |
| 4 | s |
+----+-------+
3 rows in set (0.02 sec)
并测试:
第一:中频比较
mysql> select @x;
+------+
| @x |
+------+
| t |
+------+
1 row in set (0.00 sec)
mysql> select benchmark(1E7, if(@x=0, 1, title=@x)) from test;
+---------------------------------------+
| benchmark(1E7, if(@x=0, 1, title=@x)) |
+---------------------------------------+
| 0 |
| 0 |
| 0 |
+---------------------------------------+
3 rows in set (1.66 sec)
mysql> set @x=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select benchmark(1E7, if(@x=0, 1, title=@x)) from test;
+---------------------------------------+
| benchmark(1E7, if(@x=0, 1, title=@x)) |
+---------------------------------------+
| 0 |
| 0 |
| 0 |
+---------------------------------------+
3 rows in set (1.85 sec)
二,OR比较
mysql> select @x;
+------+
| @x |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
mysql> select benchmark(1E7, title = @X or @X = 0) from test;
+--------------------------------------+
| benchmark(1E7, title = @X or @X = 0) |
+--------------------------------------+
| 0 |
| 0 |
| 0 |
+--------------------------------------+
3 rows in set, 65535 warnings (17.31 sec)
-好,那是因为类型转换.定影:
mysql> set @x='0';
Query OK, 0 rows affected (0.00 sec)
mysql> select benchmark(1E7, title = @X or @X = '0') from test;
+----------------------------------------+
| benchmark(1E7, title = @X or @X = '0') |
+----------------------------------------+
| 0 |
| 0 |
| 0 |
+----------------------------------------+
3 rows in set (5.78 sec)
最后,非零:
mysql> set @x='t';
Query OK, 0 rows affected (0.00 sec)
mysql> select benchmark(1E7, title = @X or @X = '0') from test;
+----------------------------------------+
| benchmark(1E7, title = @X or @X = '0') |
+----------------------------------------+
| 0 |
| 0 |
| 0 |
+----------------------------------------+
3 rows in set (4.92 sec)
结论
在这种情况下,IF比较似乎比OR快得多(对于1E7标准迭代,约为3倍)
标签:mysql
来源: https://codeday.me/bug/20191030/1966878.html