从所有表中选择行的简便方法

Question

我想在我的mysql数据库中选择一些行，我有3个具有相同结构的表，我尝试使用我创建的脚本并且此方法有效，但是似乎我的脚本占用了太多行。

<?php

$check1 = mysqli_query($con, "SELECT * FROM text WHERE keyword='$string'");

$check2 = mysqli_query($con, "SELECT * FROM sticker WHERE keyword='$string'");

$check3 = mysqli_query($con, "SELECT * FROM image WHERE keyword='$string'");

if(mysqli_num_rows($check1) == 0 && mysqli_num_rows($check2) == 0 && mysqli_num_rows($check3) == 0) {

    echo "success";

}

?>

还有另一种方法可以缩短我的脚本吗？谢谢你 ：）

Answer 1

如果所有表都具有相同的结构，则可以使用UNION查询一次返回所有三个表中的行：$check = mysqli_query($con, "SELECT * FROM text WHERE keyword='$string'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;UNION&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SELECT * FROM sticker WHERE keyword='$string'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;UNION&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SELECT * FROM image WHERE keyword='$string'");这将为您提供与三个现有查询相同的行集。请注意，将无法确定给定行来自哪个表，因此您可能需要添加一个额外的字段来表示例如$check = mysqli_query($con, "SELECT *, 'text' AS src FROM text WHERE keyword='$string'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;UNION&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SELECT *, 'sticker' AS src FROM sticker WHERE keyword='$string'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;UNION&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SELECT *, 'image' AS src FROM image WHERE keyword='$string'");

Answer 2

确保在所有3个表的关键字列上都有索引，以便避免完全表扫描。在下面使用创建索引。&nbsp; &nbsp; CREATE INDEX keyword ON text (keyword);&nbsp; &nbsp; CREATE INDEX keyword ON sticker (keyword);&nbsp; &nbsp; CREATE INDEX keyword ON image (keyword);如果您想知道3个表中的任何一个表中是否存在关键字，请仅选择1行。&nbsp; &nbsp; <?php&nbsp; &nbsp; $check = mysqli_query($con, "SELECT 1 FROM text WHERE keyword='$string' limit 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; UNION SELECT 1 FROM sticker WHERE keyword='$string' limit 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; UNION SELECT 1 FROM image WHERE keyword='$string' limit 1");&nbsp; &nbsp; if(mysqli_num_rows($check) == 0 ) {&nbsp; &nbsp; &nbsp; &nbsp; echo "success";&nbsp; &nbsp; }&nbsp; &nbsp; ?>如果要从每个表中选择一些行，请使用限制。我仅以5为例。&nbsp; &nbsp; <?php&nbsp; &nbsp; $check1 = mysqli_query($con, "SELECT * FROM text WHERE keyword='$string' limit 5");&nbsp; &nbsp; $check2 = mysqli_query($con, "SELECT * FROM sticker WHERE keyword='$string' limit 5");&nbsp; &nbsp; $check3 = mysqli_query($con, "SELECT * FROM image WHERE keyword='$string' limit 5");&nbsp; &nbsp; if(mysqli_num_rows($check1) == 0 && mysqli_num_rows($check2) == 0 && mysqli_num_rows($check3) == 0) {&nbsp; &nbsp; &nbsp; &nbsp; echo "success";&nbsp; &nbsp; }&nbsp; &nbsp; ?>