如何在 MySQL 中多次使用空变量?

发布于 11-07 03:39 字数 383 浏览 2 评论 0原文

我正在尝试检查 MySQL 语句中是否有 2 个空变量,但我似乎可以得到非常正确的语法。这是我现在所拥有的,它一直给我一个错误。谁能告诉我如何正确地做到这一点?

  SELECT threads, userid, username, usergroupid
    FROM " . TABLE_PREFIX . "user
   WHERE  
    " . iif(!empty($exuserids), "AND userid NOT IN ($exuserids)") . "
    " . iif(!empty($exgroups), "AND usergroupid NOT IN ($exgroups)") . "
ORDER BY threads DESC 
   LIMIT 1

I am trying to check for 2 empty variables in a MySQL statement but I can seem to get the syntax quite right for it. Here is what I have now and it keeps giving me an error. Can anyone please tell me how I can do this properly?

  SELECT threads, userid, username, usergroupid
    FROM " . TABLE_PREFIX . "user
   WHERE  
    " . iif(!empty($exuserids), "AND userid NOT IN ($exuserids)") . "
    " . iif(!empty($exgroups), "AND usergroupid NOT IN ($exgroups)") . "
ORDER BY threads DESC 
   LIMIT 1

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

臻嫒无言2024-11-14 03:39:24

使用:

  SELECT threads, userid, username, usergroupid
    FROM " . TABLE_PREFIX . "user
   WHERE 1 = 1 
    " . iif(!empty($exuserids), "AND userid NOT IN ($exuserids)") . "
    " . iif(!empty($exgroups), "AND usergroupid NOT IN ($exgroups)") . "
ORDER BY threads DESC 
   LIMIT 1

在指定“AND ...”之前需要有一个 WHERE 子句 - 1 = 1 将被优化掉。这是动态 SQL 中使用的一个技巧,可以使 WHERE 子句连接更容易。

Use:

  SELECT threads, userid, username, usergroupid
    FROM " . TABLE_PREFIX . "user
   WHERE 1 = 1 
    " . iif(!empty($exuserids), "AND userid NOT IN ($exuserids)") . "
    " . iif(!empty($exgroups), "AND usergroupid NOT IN ($exgroups)") . "
ORDER BY threads DESC 
   LIMIT 1

There needs to be a WHERE clause before you specify "AND ..." - the 1 = 1 will be optimized out. It's a trick used for dynamic SQL to make WHERE clause concatenation easier.

紙鸢2024-11-14 03:39:24

好吧,难怪它会给你一个错误。您是否使用 PHPkit 或其他为您提供 iif 的东西,因为尽管您没有这么说,但上面的内容是用 PHP 编写的,而 iif 不是该语言的一部分。

现在,即使您拥有它,为什么不漂亮地编译该语句 - 将条件收集在数组中,使用 AND 进行内爆,如果不为空则添加一个 WHERE 条件。

最后,在查询中使用变量是 SQL 注入攻击的一个秘诀。

Well no wonder it gives you an error. Are you using PHPkit or something else that gives you iif Because, despite you do not say so, the above is written in PHP and iif is not part of the language.

Now, even if you have it why not compile the statement prettily -- gather the conditions in an array, implode with AND and add a WHERE condition if it's not empty.

Finally, having variables in queries is a recipe for SQL injection attacks.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文