调用不同SELECT语句的MySQL IF/CASE语法 [英] MySQL IF/CASE syntax that calls different SELECT statements
问题描述
我在使用MySQL的IF或CASE语法时遇到问题
I'm having problems using MySQL's IF or CASE syntax
该过程如下:
我有一个PHP项目调用一个MySQL查询，比如说PHP传递了变量X.
I have a PHP project that calls a MySQL query, let's say PHP passes variable X.
如果X不是0，则它应该执行类似以下的查询:
If X is not 0 then it should perform a query something like:
SELECT * FROM table
WHERE id = X
但是如果X是0.那么它应该执行查询以仅选择所有记录:
But if X is 0. then it should perform a query to just select all records:
SELECT * FROM table
我不想在PHP中使用条件，该条件根据PHP中条件的结果调用另一个MySQL查询.我希望基于传递给X的变量在SQL查询中执行if/else条件.
I don't want to have the condition in PHP which calls a different MySQL query based on the result of the condition in PHP. I want the if / else condition to be performed inside the SQL query based on the variable passed X.
推荐答案
我假设$x是从您的PHP派生的，然后:
I assume $x is derived from your PHP, then:
SELECT * FROM t WHERE IF($x=0, 1, id=$x)
(这是一般性的想法，您必须亲自进行注射等)
(it's to show general idea, you'll have to handle injections e t.c. by yourself)
编辑
我在这里的另一个答案中找到了不错的代码.所以我很好奇-哪个更快?现在我很高兴我的也是这样:
I've found nice code in another answer here. So I was curious - which is faster? Now I'm 'happy' that mine is that as well:
我的版本:
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)
并进行测试:
首先:IF比较
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)
第二个或比较
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)
-好的，那是因为类型转换.修复:
-ok, that was because of type-casting. Fixing:
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倍)
IF comparison seems to be much more faster in this case, than OR (about 3 times for 1E7 benchmark iterations)
这篇关于调用不同SELECT语句的MySQL IF/CASE语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
